Modeled Sheet Definition

URL Endpoint

Supported HTTP VERB(s)

PATCH

Request URI

/sheet/modeled/definition

Update the modeled sheet definition matching the provided query parameter. If a sheet with the matching identifier does not exist, a new modeled sheet will be created with the given properties.

Sample Request by Name

https://api.adaptiveplanning.com/api/rest/modeling/v1/globosales/sheet/modeled/definition?name=Capital Model

Sample Request Header

Accept-Language: en

Sample Request Body

See Request Body section

Query Parameters

Name	Description	Required
instanceCode	The instanceCode to retrieve values from. For example, GLOBO. If no instanceCode is specified, the default instance of the user is used.	N
name	The name of the sheet.	N
proceedWithWarnings	Whether the upsert should ignore any warning validation, usually returned when a changed sheet property could delete data from the sheet.	N
reorderColumns	Setting this query parameter to true will change the order of the column list to reflect the ordering of columns given in the api request body. By default this is set to false. See Reference: Re-ordering Columns for Modeled and Cube Sheet Definition JSON APIs.	N

Request Body

Sample Request Body

The same modeled sheet definition format is used as the JSON payload for both endpoints.

Request Format

{ "properties": { "description": "Capital Model", "prefix": "Capital" }, "columns": [ { "properties": { "code": "Base Pay", "name": "Base Pay", "type": "TEXT_SELECTOR", "values": ["value1", "value2"], "lookupTables": [ { "name": "valueLookup1", "type": "VALUE", "decimalPrecision": 1, "displayAs": "CURRENCY" }, { "name": "spreadLookup1", "type": "SPREAD", "decimalPrecision": 0, "displayAs": "NUMBER" } ] }, "availability": [ { "name": "100k", "code": "100k", "available": true } ], "defaultAvailability": false, "defaultActionIfDataIsPresent": "delete" }, { "properties": { "code": "Label", "name": "Label", "type": "TEXT" }, "delete": true } ], "accounts": [ { "name": "AccountName", "code": "AccountCode" } ], "accessibility": { "usernames": [ "test@greenco.com" ], "excludedFromWorkflow": true } }

Sheet definition object

Name	Description	Required
properties	Object containing general sheet properties	N
columns	List of modeled sheet column objects. New columns will be created at the end of the sheet definition, and the relative order of new columns will be respected. Existing columns will only be updated in-place; they will not be reordered.	N
accounts	List of account objects	N
accessibility	Object containing defining user-assigned sheet accessibility.	N

Sheet properties object

Name	Description	Required	Type	Default
description	Sheet description.	N	string	Empty string
code	Sheet code used to prefix account codes and a secondary unique identifier. Required when creating a new sheet.	N	string
prefix	Sheet prefix used for the account code. Required when creating a new sheet. Deprecated in favor of code.	N	string
userAssigned	Whether the sheet is a user-assigned sheet or (by default) a level-assigned sheet.	N	boolean	false
salaryDetail	Whether the sheet contains salary detail.	N	boolean	false
frozenColumnCount		N	integer	0
allowSplits	Whether the sheet allows split rows.	N	boolean	false
allowRollupModelEdits	Whether the sheet allows edits while viewing rollup levels.	N	boolean	false
allowActuals	Whether the sheet is available in actuals versions.	N	boolean	false
recalculateOnDemand	Whether recalculate on demand is enabled for the sheet.	N	boolean	false
timeStratumCode	The time stratum code associated with the data in the sheet.	N	string	GL Time Stratum

Sheet column object

Name	Description	Required	Type	Default
properties	Object containing sheet column properties.	Y	object
availability	List of sheet availability objects for the current column. See Sheet Availability for details on the list format.	N	list
defaultAvailability	The default availability to apply to all values, unless otherwise specified in availability. Valid for level and dimension columns. For dimension columns, if no availability or defaultAvailability is specified, then defaultAvailability will be set to true for new columns. By default, this value will be empty, which means the existing availability is preserved unless specifically changed via availability.	N	boolean
defaultActionIfDataIsPresent	The default action if data is present when deleting a value, unless otherwise specified in availability. Valid for dimension columns. Required when the Default Availability is false for existing columns.	N	string
delete	Request to delete column from the given sheet. Not valid for columns that are dependent on existing columns that are not flagged for deletion (such as dimension attributes).	N	boolean

Sheet columns properties object

Name	Description	Required	Type	Default
name	Column name.	N	string
code	The column code. Required for all columns except level and level currency columns.	Y	string
type	The type of column. Possible values: LEVEL, DIMENSION, LEVEL_ATTRIBUTE, DIMENSION_ATTRIBUTE, TIMESPAN, NUMBER, DATE, TEXT, TEXT_SELECTOR, LEVEL_CURRENCY, DISPLAY, INITIAL_BALANCE, CHECKBOX	Y	string
readOnly	Whether the current column is read only or not. For dimension attribute columns, set readOnly to false to use the attribute as a filter on the dimension. Required to be true (and defaults to true) for level attributes and display columns.	N	boolean	false
allowSplits	Whether the current column is splittable. Only valid for sheets when the general allowSplits property is true.	N	boolean	false
showTotalsAtBottom	Whether to show totals at the bottom of the sheet for this column.	N	boolean	false
allowHidden	Whether this column can be hidden from the sheet viewer in Display Options. Valid for all columns except text selector and timespan columns.	N	boolean	true
editableOnSheet	Whether new values can be added to the dimension column from the sheet. Only valid for dimension and text selector columns.	N	boolean	false
required	Whether each row is required to have a value in this column. Only valid for dimension columns where allowHidden is false.	N	boolean	false
recalculateOnMatch	Whether to recalculate values on match. Only valid dimension and text selector columns for sheets where recalculateOnDemand is true.	N	boolean	false
lookupTables	List of LookupTable objects to define spread and value lookups. Only valid for dimension and text selector columns.	N	LookupTable	null
values	List of strings to add as text selector values. Only valid for text selector columns.	N	string array	null
rowKey	Whether this column can be used as a Row Key. Only valid for text driver columns.	N	boolean	false
displayAs	The Display As format for the column. Only valid for numeric driver columns. Possible values: NUMBER, PERCENT, CURRENCY	N	DisplayAs	false
decimalPrecision	The decimal precision to display. Only valid for numeric driver columns. Possible values: 0 to 9, or -1 to use the currency precision when displayed as CURRENCY.	N	integer	0
showToggle	Whether to show a checkbox column as a toggle. Only valid for checkbox columns.	N	boolean	false
showInEditMode	Whether to show the column on editable rows of the modeled sheet. Only valid for level currency columns.	N	boolean	false
referenceTimePeriod	Indicates the reference time period for a display column. Only valid for account display columns. Possible values: FIRST_NON_ACTUALS_PERIOD, START_OF_PLAN	N	string	FIRST_NON_ACTUALS_PERIOD
startOfRollupRange	Indicates where to start a time period rollup range for a display column. Only valid for account display columns. Possible values: CONTAINING_REFERENCE_PERIOD, BEFORE_REFERENCE_PERIOD, AFTER_REFERENCE_PERIOD	N	string	CONTAINING_REFERENCE_PERIOD
timePeriodsInRollupRange	Number of time periods to include in the rollup range for a display column. Only valid for account display columns.	N	integer	1
startOfRollupRangeTimeStratumCode	Code of the time period to determine the start of the rollup range for a display column. Only valid for account display columns. Possible values include time strata equal to or higher than the sheet time stratum.	N	string	Sheet time stratum
accountCode	Code of the account to determine the modeled account for a display column. Only valid for display columns. Only valid values are accounts on the given modeled sheet	N	string

Sheet column lookup table object

Name	Description	Required	Type	Default
name	Lookup table name.	Y	string
displayAs	The Display As format for this lookup table. Possible values: NUMBER and PERCENT. Only valid for value lookup tables.	N	string	NUMBER
decimalPrecision	The decimal precision for values in this lookup table. Possible values: 0 to 9.	N	integer	0
type	The type of lookup table. Possible values: VALUE, SPREAD.	Y	string

Sheet account object

For more detail, see Reference: Properties in Account JSON Import Payload

Name	Description	Required
name	The account name.	Y
code	The account code.	Y
parent	The parent account.	N
description	The account description.	N
isCumulative	Returns whether the account is cumulative.	N
isActualsByDelta	Returns whether the acount is actuals by delta.	N
isLink	Returns whether the account is a linked account or not.	N
timeRollupType	The type of rollup to perform for time rollups.	N
timeWeightAccount	The weight account used for time rollups.	N
levelDimRollupType	The type of rollup to perform for level/custom dimension rollups.	N
levelDimWeightAccount	The weight account used for leve/custom dimension rollups.	N
levelDimRollupText	The rollup text vlaue used in level/custom dimension rollups.	N
actualsOverlay	The actuals overlay setting for the account.	N
attributeValues	List of accoun attribute value objects for this account.	N
displayAs	The display as setting for the account.	N
formula	The account formula.	N
weightedAverageTranslationsEnabled	Returns whether the account is weighted average translations enabled.	N
weightedAverageTranslationResetStratum	The WAT Rest Stratum for this account.	N
weightedAverageTranslationTransferAccount	The WAT Transfer Account for this account.	N
decimalPrecision	The default decimal precision for the account.	N
exchangeRateType	The exchange rate type for this account.	N
suppressZeros	Returns whether the zero rows should be suppressed for the account on sheets.	N
startExpanded	Returns whether the account starts expanded on the sheet.	N
dataEntryType	The data entry type setting for this account.	N
dataPrivacy	The data privacy setting for this account.	N
hasSalaryDetail	Returns whether the account has salary details or not.	N
isBreakbackEligible	Returns whether break-back is eligible for this account.	N
isSystemAccount	Returns whether this account is a system account or not.	N
isIntercompany	Returns whether this is an intercompany account or not.	N
isAssumption	Returns whether the account is an assumption or not.	N
isMetric	Returns whether the the account is a metric account or not.	N
modeledSheetSpreadCode	The modeled sheet spread code for the associated model spread.	N
spreadTargetAccount	The target account object of the spread account.	N

Sheet accessibility object

Name	Description	Required	Type	Default
usernames	List of usernames to add to the sheet. Must come in the form of valid email addresses.	N	string array
excludedFromWorkflow	updates the Excluded From Workflow checkbox in the sheet accessibility settings	N	boolean	false

Sample Response

204

Successful repsonse is blank with status code 204.

DELETE

Request URI

/sheet/modeled/definition

Delete the modeled sheet definition matching the given query parameter. If a sheet with the matching identifier does not exist, then not found error will occur.

Sample Request by Name

https://api.adaptiveplanning.com/api/rest/modeling/v1/globosales/sheet/modeled/definition?name=Capital Model

Sample Request Header

Accept-Language: en

Sample Request Body

<None>

Query Parameters

Name	Description	Required
name	The name of the modeled sheet.	N
instanceCode	The instanceCode to retrieve values from. For example, GLOBO. If no instanceCode is specified, the default instance of the user is used.	N

Sample Response

204

Successful response is blank with status code 204.

POST

Request URI

/sheet/modeled/definition/validate

Validate a modeled sheet definition request.

Sample Request by Name

https://api.adaptiveplanning.com/api/rest/modeling/v1/default/sheet/modeled/definition/validate?name=My Sheet

Sample Request Header

Accept-Language: en

Sample Request Body

<Documented below>

Query Parameters

See relative endpoint being validated (i.e. PATCH).

Request Body

Sample Request Body

The same modeled sheet definition format is used as the JSON payload for the endpoint being validated. However, the object

validationOptions

is required in the payload when sending a validation request.

Request Format

{

  "validationOptions": {
      "httpMethod": "Patch",
      "dependentDimensions": [
          "MyTestDim1", "MyTestDim2"
      ],
      "dependentAttributes": [
          {
              "attributeName": "MyTestAttr1",
              "attributeType": "DIMENSION_ATTRIBUTE",
              "dimensionName": "MyTestDim2"
          },
          {
              "attributeName": "MyTestAttr2",
              "attributeType": "LEVEL_ATTRIBUTE"
          }
      ]
  },

  "properties": {
      "description": "Capital Model",
      "prefix": "Capital"
  },
  "columns": [
      {
          "properties": {
              "code": "Base Pay",
              "name": "Base Pay",
              "type": "TEXT_SELECTOR",

...

Validation Options Object

Name	Description	Required
httpMethod	Specifies which modeled sheet API endpoint is being validated. Currently supports the following endpoints: PATCH	Y
dependentDimensions	List of dimension names to be assumed existent for validation. Dimensions aren't persisted as a result of the validation request. It is assumed all dependent custom dimensions are flat.	N
dependentAttributes	List of dependent attribute objects. Attributes aren't persisted as a result of the validation request.	N

Dependent Attribute Object

Name	Description	Required
attributeName	Name of the dependent attribute.	Y
attributeType	Type of the dependent attribute. Supported types are: LEVEL_ATTRIBUTE DIMENSION_ATTRIBUTE	Y
dimensionName	Name of the dimension for the dependent attribute. Only applies and is required for dimension attributes. Not allowed for level attributes.	N