Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2024-09-20
Modeled Sheet Definition

Modeled Sheet Definition

URL Endpoint

HTTPS://api.adaptiveplanning.com/api/rest/modeling/<version>/<tenant>/sheet/modeled/definition
Version: v1
Category
Data submission
Description
Metadata Modification
Permissions Required to Invoke
Model Management Access
Model includes: sheets, accounts, dimensions, and formulas
Parameters Required on Request
name

Supported HTTP VERB(s)

HTTP Verb
Single Resource
Collection Resource
Description
PATCH
Supported
Not Supported
Insert or Update a modeled sheet definition
DELETE
Supported
Not Supported
Delete modeled sheet definition
POST
Supported
Not Supported
Validation mode for modeled sheet definition upsert.
OPTIONS
Supported
Not Supported
Returns the list of HTTP verbs supported for this collection resource.

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
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