Spreadsheets enable you to work with large, complex data in a familiar, collaborative, and controlled environment. Use these endpoints to manage spreadsheets and their sheets in the Workiva platform.
Spreadsheet
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
created | Action | When the action was performed, and details about the user who did it | read-only |
id | string | The unique identifier of the spreadsheet | read-only |
modified | Action | When the action was performed, and details about the user who did it | read-only |
name | string | The name of the spreadsheet | read-only |
sheets | [Sheet] | An array of partial information about the sheets in this spreadsheet. Optionally included in the response when the $expand query parameter is provided. | read-only |
template | boolean | Whether the spreadsheet is a template | read-only |
Example
{
"created": {
"dateTime": "2019-10-30T15:03:27Z",
"user": {
"displayName": "string",
"email": "string",
"id": "V1ZVd2VyFzU3NiQ1NDA4NjIzNzk2MjD"
}
},
"id": "124efa2a142f472ba1ceab34ed18915f",
"modified": {
"dateTime": "2019-10-30T15:03:27Z",
"user": {
"displayName": "string",
"email": "string",
"id": "V1ZVd2VyFzU3NiQ1NDA4NjIzNzk2MjD"
}
},
"name": "Year-end review",
"sheets": [
{
"id": "27f1b61c04ae4b0991bc73c631914e1d",
"name": "Q1"
}
],
"template": false
}
SpreadsheetExport
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
csvOptions | SpreadsheetToCsvOptions¦null | Optional options to export the spreadsheet as a comma-separated values (.CSV) file. If no options are provided, exportAsFormulas defaults to False. | |
format | string | The file format to export the spreadsheet as. | |
pdfOptions | SpreadsheetToPdfOptions¦null | Optional options to export the spreadsheet as a portable document file (.PDF). If no options are provided, all options default to False except: - pageHeight , which defaults to 11- pageWidth , which defaults to 8.5- pageOrientation , which defaults to "portrait"- pageScale , which defaults to "actualSize" | |
sheets | [string] | The IDs of the sheets within the spreadsheet to export. Omit to export the entire spreadsheet. Note: When exporting to .CSV, you can export only the entire spreadsheet or a single sheet. When exporting the entire spreadsheet, the resulting file is a .ZIP of .CSV files, with one .CSV file per sheet. | |
xlsxOptions | SpreadsheetToXlsxOptions¦null | Optional options to export the spreadsheet as a Microsoft Excel (.XLSX) file. If no options are provided, exportAsFormulas defaults to False, and exportPrecision defaults to fullPrecision . |
Enumerated Values
Property | Value |
---|---|
format | pdf |
format | xlsx |
format | csv |
Example
{
"format": "xlsx",
"sheets": [
"7c8d8c4a46784455bg68t36f9d8232d8",
"54bgd83b471e5902f1a8e8c9a299c9fb"
],
"xlsxOptions": {
"exportAsFormulas": true,
"exportPrecision": "displayed"
}
}
SpreadsheetToPdfOptions
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
includeComments | boolean | Whether to include comments when exporting to .PDF False by default. | |
includeDraftWatermark | boolean | Whether to include draft watermark when exporting to .PDF. False by default. | |
includeHyperlinks | boolean | Whether to include hyperlinks when exporting to .PDF. False by default. | |
includeLeaderDots | boolean | Whether to include leader dots when exporting to .PDF. False by default. | |
includeTrackChanges | boolean | Whether to include track changes when exporting to .PDF. False by default. | |
onlyExportPrintAreas | boolean | Whether to only export print areas when exporting to .PDF. False by default. | |
pageHeight | number(double) | The height of the exported .PDF, in inches. 11 by default. | |
pageOrientation | string | The orientation of the exported .PDF, such as "portrait" or "landscape". "portrait" by default. | |
pageScale | string | The scale of the exported .PDF. "actualSize" by default. | |
pageWidth | number(double) | The width of the exported .PDF, in inches. 8.5 by default. | |
showCellFills | boolean | Whether to show cell fills when exporting to .PDF. False by default. | |
showGridlines | boolean | Whether to show gridlines when exporting to .PDF. False by default. | |
useCmykColorspace | boolean | Whether to use CMYK colorspace when exporting to .PDF. False by default. |
Enumerated Values
Property | Value |
---|---|
pageOrientation | portrait |
pageOrientation | landscape |
pageScale | actualSize |
pageScale | fitToWidth |
Example
{
"includeComments": true,
"includeDraftWatermark": true,
"includeHyperlinks": true,
"includeLeaderDots": true,
"includeTrackChanges": true,
"onlyExportPrintAreas": true,
"pageHeight": 14,
"pageOrientation": "landscape",
"pageScale": "fitToWidth",
"pageWidth": 11,
"showCellFills": true,
"showGridlines": true,
"useCmykColorspace": true
}
SpreadsheetToXlsxOptions
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
exportAsFormulas | boolean | Whether to export cells that contain formulas as the formula or its result when exporting to .XLSX. False by default. | |
exportPrecision | string | How to export values in the sheet when exporting to .XLSX "fullPrecision" by default. |
Enumerated Values
Property | Value |
---|---|
exportPrecision | fullPrecision |
exportPrecision | rounded |
exportPrecision | displayed |
Example
{
"exportAsFormulas": true,
"exportPrecision": "displayed"
}
SpreadsheetToCsvOptions
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
exportAsFormulas | boolean | Whether to export cells containing formulas as the formula or the formula result. False by default. |
Example
{
"exportAsFormulas": true
}
Sheet
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
children | [Sheet] | An array of partial information about any child sheets | read-only |
dataset | Dataset¦null | The range of the dataset on this sheet, if one exists. * To modify a dataset, either update or delete it. | read-only |
id | string | The unique identifier of the sheet | read-only |
index | integer | The integer index of the sheet relative to its parent sheet or to the spreadsheet, if no parent sheet. To position a sheet at the end of its siblings, use the special value -1. | |
name | string | The name of the sheet | |
parent | Sheet¦null | The sheet's ID and name |
Example
{
"children": [
{
"id": "27f1b61c04ae4b0991bc73c631914e1d",
"name": "Q1"
}
],
"dataset": {
"range": "A1:B2"
},
"id": "27f1b61c04ae4b0991bc73c631914e1d",
"index": 1,
"name": "Q1",
"parent": {
"id": "27f1b61c04ae4b0991bc73c631914e1d",
"name": "Q1"
}
}
SheetCopy
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
sheetIndex | integer | The integer index of where within the siblings to place the new sheet; 0 by default. To place the sheet at the end of its siblings, use the special value -1. | |
sheetName | string¦null | The name of the new sheet, if different than the source sheet. | |
sheetParent | string¦null | The ID of the parent sheet to copy the sheet into. To place the sheet at the top level of the spreadsheet, use the default null. | |
spreadsheet | string | The unique identifier of the spreadsheet to copy a sheet into |
Example
{
"sheetIndex": 2,
"sheetName": "Q1",
"sheetParent": "5bbf8aa3cea54465762af96e3ca411c7",
"spreadsheet": "c65d9572a7464037a383d6235633cf74"
}
Dataset
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
range | string | A1 style notation describing the range. Datasets are always located in the top left-hand corner of the sheet, so there is no need to specify range when creating a dataset. | read-only |
sheet | string | The unique identifier of the sheet to which this dataset belongs. | |
values | [array] | A row-major ordered multidimensional array of cell values. |
Example
{
"range": "A1:B2",
"sheet": "27f1b61c04ae4b0991bc73c631914e1d",
"values": [
[
1,
4
],
[
2,
""
]
]
}
Datasets
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
[Dataset] | An array of Datasets to submit at once |
Example
[
{
"range": "A1:B2",
"sheet": "27f1b61c04ae4b0991bc73c631914e1d",
"values": [
[
1,
4
],
[
2,
""
]
]
}
]
RangeValues
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
range | string | The range of values, in A1-style notation. | |
values | [array] | A row-major ordered multidimensional array of cell values. |
Example
{
"range": "A1:B2",
"values": [
[
1,
4
],
[
2,
""
]
]
}
SheetData
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
cells | [array] | Cell data in row-major order | |
columnMetadata | [ColumnMetadata] | Metadata about the columns in the request range | |
merges | [Range] | Merged ranges that intersect with the request range | |
range | Range | A range in a sheet. If any field is omitted or null, the range is unbounded in that direction. | |
rowMetadata | [RowMetadata] | Metadata about the rows in the request range |
Example
{
"cells": [
[
{
"calculatedValue": null,
"effectiveFormats": {
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
},
"formats": {
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
},
"value": null
}
]
],
"columnMetadata": [
{
"hidden": true,
"size": 0
}
],
"merges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"range": {
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
},
"rowMetadata": [
{
"filtered": true,
"hidden": true,
"size": 0
}
]
}
SheetUpdate
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
applyBorders | SheetUpdateApplyBorders | Apply list of border format requests to the sheet | |
applyFormats | SheetUpdateApplyFormats | Apply list of format requests to the sheet | |
clearBorders | SheetUpdateClearBorders | Clears borders in ranges | |
clearFormats | SheetUpdateClearFormats | Clear formats from ranges | |
deleteColumns | SheetUpdateDeleteColumns | Delete columns from the sheet | |
deleteRows | SheetUpdateDeleteRows | Delete rows from the sheet | |
editCells | SheetUpdateEditCells | Edit a list of cells | |
editRange | SheetUpdateEditRange | Edit all of the cells in a contiguous range | |
hideColumns | SheetUpdateHideColumns | Hide columns in the sheet | |
hideRows | SheetUpdateHideRows | Hide rows in the sheet | |
insertColumns | SheetUpdateInsertColumns | Insert columns into the sheet | |
insertRows | SheetUpdateInsertRows | Insert rows into the sheet | |
mergeRanges | SheetUpdateMergeRanges | Merge ranges | |
resizeColumns | SheetUpdateResizeColumns | Resize columns to the specified size | |
resizeColumnsToFit | SheetUpdateResizeColumnsToFit | Auto-size columns to fit content | |
resizeRows | SheetUpdateResizeRows | Resize rows to the specified size | |
resizeRowsToFit | SheetUpdateResizeRowsToFit | Auto-size rows to fit content | |
unhideColumns | SheetUpdateUnhideColumns | Unhide columns in the sheet | |
unhideRows | SheetUpdateUnhideRows | Unhide rows in the sheet | |
unmergeRanges | SheetUpdateUnmergeRanges | Unmerge merges that intersect the provided ranges |
Example
{
"applyBorders": {
"borders": [
{
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerHorizontal": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerVertical": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
}
]
},
"applyFormats": {
"formats": [
{
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
}
]
},
"clearBorders": {
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
},
"clearFormats": {
"cellFormatFields": [
"indent",
"backgroundColor",
"leaderDots"
],
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormatFields": [
"bold",
"italic",
"underline"
],
"valueFormatFields": [
"shownIn",
"precision",
"currencySymbol"
]
},
"deleteColumns": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"deleteRows": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"editCells": {
"cells": [
{
"column": 0,
"row": 0,
"value": null
}
]
},
"editRange": {
"range": {
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
},
"values": [
[
null
]
]
},
"hideColumns": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"hideRows": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"insertColumns": {
"inheritFrom": "NONE",
"insertions": [
{
"count": 1,
"index": 0
}
]
},
"insertRows": {
"inheritFrom": "NONE",
"insertions": [
{
"count": 1,
"index": 0
}
]
},
"mergeRanges": {
"force": true,
"mergeType": "ALL",
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
},
"resizeColumns": {
"resizeIntervals": [
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
]
},
"resizeColumnsToFit": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"resizeRows": {
"resizeIntervals": [
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
]
},
"resizeRowsToFit": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"unhideColumns": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"unhideRows": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"unmergeRanges": {
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
}
}
SheetUpdateApplyFormats
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
formats | [ApplyFormats] | The list of formats to apply to the sheet |
Example
{
"formats": [
{
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
}
]
}
SheetUpdateClearBorders
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
ranges | [Range] | The ranges to clear borders |
Example
{
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
}
SheetUpdateClearFormats
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
cellFormatFields | [string] | List of CellFormat fields to clear. Use "*" to clear all fields. | |
ranges | [Range] | The ranges to clear formats | |
textFormatFields | [string] | List of TextFormat fields to clear. Use "*" to clear all fields. | |
valueFormatFields | [string] | List of ValueFormat fields to clear. Use "*" to clear all fields. |
Example
{
"cellFormatFields": [
"indent",
"backgroundColor",
"leaderDots"
],
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormatFields": [
"bold",
"italic",
"underline"
],
"valueFormatFields": [
"shownIn",
"precision",
"currencySymbol"
]
}
SheetUpdateDeleteColumns
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
force | boolean | Force the deletion of links, xbrl, footnotes, etc | |
intervals | [Interval] | [An interval of rows or columns. If either the start or end is null or omitted, the interval is unbounded in that direction.] |
Example
{
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateApplyBorders
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
borders | [ApplyBorders] | The list of border formats to apply to the sheet |
Example
{
"borders": [
{
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerHorizontal": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerVertical": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
}
]
}
SheetUpdateDeleteRows
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
force | boolean | Force the deletion of links, xbrl, footnotes, etc | |
intervals | [Interval] | [An interval of rows or columns. If either the start or end is null or omitted, the interval is unbounded in that direction.] |
Example
{
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateEditCells
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
cells | [CellEdit] | The cells to edit |
Example
{
"cells": [
{
"column": 0,
"row": 0,
"value": null
}
]
}
SheetUpdateEditRange
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
range | Range | A range in a sheet. If any field is omitted or null, the range is unbounded in that direction. | |
values | RangeEditValues | Row-major ordered two-dimensional array of cell values |
Example
{
"range": {
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
},
"values": [
[
null
]
]
}
SheetUpdateHideColumns
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
force | boolean | Force the hiding of footnotes | |
intervals | [Interval] | The intervals of columns to hide |
Example
{
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateHideRows
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
force | boolean | Force the hiding of footnotes | |
intervals | [Interval] | The intervals of rows to hide |
Example
{
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateInsertRows
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
inheritFrom | InheritFrom | Where to inherit formats from when performing an insertion | |
insertions | [Insertion] | List of row insertions |
Example
{
"inheritFrom": "NONE",
"insertions": [
{
"count": 1,
"index": 0
}
]
}
SheetUpdateInsertColumns
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
inheritFrom | InheritFrom | Where to inherit formats from when performing an insertion | |
insertions | [Insertion] | List of column insertions |
Example
{
"inheritFrom": "NONE",
"insertions": [
{
"count": 1,
"index": 0
}
]
}
SheetUpdateMergeRanges
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
force | boolean | Force the merge through links, xbrl, footnotes, etc | |
mergeType | string | How cells should be merged | |
ranges | [Range] | The ranges to merge |
Enumerated Values
Property | Value |
---|---|
mergeType | ALL |
mergeType | HORIZONTAL |
mergeType | VERTICAL |
Example
{
"force": true,
"mergeType": "ALL",
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
}
SheetUpdateResizeColumns
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
resizeIntervals | array |
Example
{
"resizeIntervals": [
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
]
}
SheetUpdateResizeColumnsToFit
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
intervals | [Interval] | The intervals of columns to resize |
Example
{
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateResizeRows
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
resizeIntervals | array |
Example
{
"resizeIntervals": [
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
]
}
SheetUpdateResizeRowsToFit
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
intervals | [Interval] | The intervals of rows to resize |
Example
{
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateUnhideRows
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
intervals | [Interval] | The intervals of rows to unhide |
Example
{
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateUnhideColumns
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
intervals | [Interval] | The intervals of columns to unhide |
Example
{
"intervals": [
{
"end": 0,
"start": 0
}
]
}
SheetUpdateUnmergeRanges
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
ranges | [Range] | The ranges to unmerge |
Example
{
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
}
ApplyBorders
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
bottom | Border | The type of border that should be applied | |
innerHorizontal | Border | The type of border that should be applied | |
innerVertical | Border | The type of border that should be applied | |
left | Border | The type of border that should be applied | |
ranges | [Range] | The ranges to apply borders | |
right | Border | The type of border that should be applied | |
top | Border | The type of border that should be applied |
Example
{
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerHorizontal": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerVertical": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
}
ApplyFormats
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
cellFormat | CellFormat | Cell Formats. Fields that are omitted will be ignored. | |
ranges | [Range] | The ranges to format | |
textFormat | TextFormat | Text formats. Fields that are omitted will be ignored. | |
valueFormat | ValueFormat | Value Formats. Fields that are omitted will be ignored. |
Example
{
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
}
Border
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
color | HexColor | A hex color code | |
style | string | The type of border to apply | |
weight | number | The thickness of the border, in points. Rounded to the nearest hundredth. |
Enumerated Values
Property | Value |
---|---|
style | SINGLE |
style | DOUBLE |
style | DASHED1 |
style | DASHED2 |
style | DASHED3 |
style | DASHED4 |
style | DASHED5 |
Example
{
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
CellData
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
calculatedValue | any | String, numeric, or boolean value result value of the cell. If the cell is a formula, this value will be the calculated result. | |
effectiveFormats | EffectiveFormats | Formats that could be directly applied or applied through inheritance. | |
formats | Formats | Formats on a cell | |
value | any | String, numeric, or boolean value of the cell. If the cell is a formula, this value will be the formula string. |
Example
{
"calculatedValue": null,
"effectiveFormats": {
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
},
"formats": {
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
},
"value": null
}
CellEdit
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
column | integer | The column of the cell to edit | |
row | integer | The row of the cell to edit | |
value | any | String, numeric, or boolean value |
Example
{
"column": 0,
"row": 0,
"value": null
}
CellFormat
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
backgroundColor | HexColor | A hex color code | |
borders | object | The borders applied to a cell. Borders may be set by setting applyBorders | read-only |
borders.bottom | Border | The type of border that should be applied | |
borders.left | Border | The type of border that should be applied | |
borders.right | Border | The type of border that should be applied | |
borders.top | Border | The type of border that should be applied | |
horizontalAlign | string | The horizontal alignment of the content in the cell | |
indent | object | Indentation of content in the cell | |
indent.unit | string | The unit of the size | |
indent.value | number | The size of the indent | |
leaderDots | string | The leader dot pattern to show on the cell | |
textRotation | string | The text orientation | |
verticalAlign | string | The vertical alignment of the content in the cell |
Enumerated Values
Property | Value |
---|---|
horizontalAlign | LEFT |
horizontalAlign | RIGHT |
horizontalAlign | CENTER |
horizontalAlign | JUSTIFIED |
unit | INCHES |
unit | CENTIMETERS |
leaderDots | NARROW |
leaderDots | WIDE |
textRotation | HORIZONTAL |
textRotation | ASCENDING |
textRotation | DESCENDING |
verticalAlign | TOP |
verticalAlign | MIDDLE |
verticalAlign | BOTTOM |
Example
{
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
}
ColumnMetadata
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
hidden | boolean | Whether the column is hidden | |
size | integer | The width of the column, in points |
Example
{
"hidden": true,
"size": 0
}
EffectiveFormats
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
cellFormat | CellFormat | Cell Formats. Fields that are omitted will be ignored. | |
textFormat | TextFormat | Text formats. Fields that are omitted will be ignored. | |
valueFormat | ValueFormat | Value Formats. Fields that are omitted will be ignored. |
Example
{
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
}
Formats
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
cellFormat | CellFormat | Cell Formats. Fields that are omitted will be ignored. | |
textFormat | TextFormat | Text formats. Fields that are omitted will be ignored. | |
valueFormat | ValueFormat | Value Formats. Fields that are omitted will be ignored. |
Example
{
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
}
HexColor
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
string | A hex color code |
Example
"#4bdf58"
InheritFrom
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
string | Where to inherit formats from when performing an insertion |
Enumerated Values
Property | Value |
---|---|
anonymous | NONE |
anonymous | BEFORE |
anonymous | AFTER |
Example
"NONE"
Insertion
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
count | integer | The number of rows or columns to insert | |
index | integer | The index to insert at |
Example
{
"count": 1,
"index": 0
}
Interval
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
end | integer¦null | The last index of the interval, inclusive | |
start | integer¦null | The first index of the interval, inclusive |
Example
{
"end": 0,
"start": 0
}
Range
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
startColumn | integer¦null | The index of the first column of the range, inclusive | |
startRow | integer¦null | The index of the first row of the range, inclusive | |
stopColumn | integer¦null | The index of the last column of the range, inclusive | |
stopRow | integer¦null | The index of the last row of the range, inclusive |
Example
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
RangeEditValues
Properties
None
Example
[
[
null
]
]
ResizeRowIntervals
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
intervals | [Interval] | The intervals of rows to resize | |
size | integer | The new size for the rows, in points |
Example
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
ResizeColumnIntervals
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
intervals | [Interval] | The intervals of columns to resize | |
size | integer | The new size for the columns, in points |
Example
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
RowMetadata
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
filtered | boolean | Whether the row is filtered | |
hidden | boolean | Whether the row is hidden | |
size | integer | The height of the row, in points |
Example
{
"filtered": true,
"hidden": true,
"size": 0
}
SheetUpdate
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
applyBorders | SheetUpdateApplyBorders | Apply list of border format requests to the sheet | |
applyFormats | SheetUpdateApplyFormats | Apply list of format requests to the sheet | |
clearBorders | SheetUpdateClearBorders | Clears borders in ranges | |
clearFormats | SheetUpdateClearFormats | Clear formats from ranges | |
deleteColumns | SheetUpdateDeleteColumns | Delete columns from the sheet | |
deleteRows | SheetUpdateDeleteRows | Delete rows from the sheet | |
editCells | SheetUpdateEditCells | Edit a list of cells | |
editRange | SheetUpdateEditRange | Edit all of the cells in a contiguous range | |
hideColumns | SheetUpdateHideColumns | Hide columns in the sheet | |
hideRows | SheetUpdateHideRows | Hide rows in the sheet | |
insertColumns | SheetUpdateInsertColumns | Insert columns into the sheet | |
insertRows | SheetUpdateInsertRows | Insert rows into the sheet | |
mergeRanges | SheetUpdateMergeRanges | Merge ranges | |
resizeColumns | SheetUpdateResizeColumns | Resize columns to the specified size | |
resizeColumnsToFit | SheetUpdateResizeColumnsToFit | Auto-size columns to fit content | |
resizeRows | SheetUpdateResizeRows | Resize rows to the specified size | |
resizeRowsToFit | SheetUpdateResizeRowsToFit | Auto-size rows to fit content | |
unhideColumns | SheetUpdateUnhideColumns | Unhide columns in the sheet | |
unhideRows | SheetUpdateUnhideRows | Unhide rows in the sheet | |
unmergeRanges | SheetUpdateUnmergeRanges | Unmerge merges that intersect the provided ranges |
Example
{
"applyBorders": {
"borders": [
{
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerHorizontal": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"innerVertical": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
}
]
},
"applyFormats": {
"formats": [
{
"cellFormat": {
"backgroundColor": "#4bdf58",
"borders": {
"bottom": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"left": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"right": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
},
"top": {
"color": "#4bdf58",
"style": "SINGLE",
"weight": 1
}
},
"horizontalAlign": "LEFT",
"indent": {
"unit": "INCHES",
"value": 0
},
"leaderDots": "NARROW",
"textRotation": "HORIZONTAL",
"verticalAlign": "TOP"
},
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormat": {
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
},
"valueFormat": {
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}
}
]
},
"clearBorders": {
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
},
"clearFormats": {
"cellFormatFields": [
"indent",
"backgroundColor",
"leaderDots"
],
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
],
"textFormatFields": [
"bold",
"italic",
"underline"
],
"valueFormatFields": [
"shownIn",
"precision",
"currencySymbol"
]
},
"deleteColumns": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"deleteRows": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"editCells": {
"cells": [
{
"column": 0,
"row": 0,
"value": null
}
]
},
"editRange": {
"range": {
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
},
"values": [
[
null
]
]
},
"hideColumns": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"hideRows": {
"force": true,
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"insertColumns": {
"inheritFrom": "NONE",
"insertions": [
{
"count": 1,
"index": 0
}
]
},
"insertRows": {
"inheritFrom": "NONE",
"insertions": [
{
"count": 1,
"index": 0
}
]
},
"mergeRanges": {
"force": true,
"mergeType": "ALL",
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
},
"resizeColumns": {
"resizeIntervals": [
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
]
},
"resizeColumnsToFit": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"resizeRows": {
"resizeIntervals": [
{
"intervals": [
{
"end": 0,
"start": 0
}
],
"size": 3
}
]
},
"resizeRowsToFit": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"unhideColumns": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"unhideRows": {
"intervals": [
{
"end": 0,
"start": 0
}
]
},
"unmergeRanges": {
"ranges": [
{
"startColumn": 0,
"startRow": 0,
"stopColumn": 0,
"stopRow": 0
}
]
}
}
TextFormat
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
bold | boolean | Text bold format | |
fontColor | HexColor | A hex color code | |
fontFamily | string | Text font format | |
fontSize | number | Text font size, in points | |
italic | boolean | Text italic format | |
strikethrough | boolean | Text strikethrough format | |
underline | boolean | Text underline format |
Example
{
"bold": true,
"fontColor": "#4bdf58",
"fontFamily": "Times New Roman",
"fontSize": 12,
"italic": true,
"strikethrough": true,
"underline": true
}
ValueFormat
Properties
Name | Type | Description | Restrictions |
---|---|---|---|
currencySymbol | object | The currency symbol to display. Valid for ACCOUNTING and CURRENCY. Either generic or currency should be set, but not both. | |
currencySymbol.currency | object | An ISO currency format | |
currencySymbol.currency.code | string | The ISO currency identifier | |
currencySymbol.currency.display | string | How to display the currency. CODE simply displays the ISO currency code while SYMBOL displays the corresponding currency symbol. For codes where we support two different symbols, SYMBOL and SYMBOL2 display as follows: | code |
currencySymbol.generic | string | Generic currency options | |
dateAbbreviateMonth | boolean | Use month abbreviations instead of full month names. Valid for DATE. | |
dateFormatString | string | Format to use when rendering the date. Valid for DATE. | |
dateUppercaseAll | boolean | Uppercase all characters in the date string. Valid for DATE. | |
displayZeroAs | string | The symbol to use for zero. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. This field controls the symbol to use for zero when not using showNumbersAsWords. | |
enteredIn | string | The scale cell values are entered in. Valid for AUTOMATIC, ACCOUNTING, CURRENCY, and NUMBER. | |
numbersAsWordsOptions | object | Options relevant when showing numbers as words. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. In order for these options to take effect showNumbersAsWords must be set to true. | |
numbersAsWordsOptions.capitalizeFirstWord | boolean | Capitalize the first word. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
numbersAsWordsOptions.displayZeroAs | string | The word to use for zero. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
percentSymbol | string | Render numbers with a percent symbol. Valid for PERCENT. | |
periodFormat | object | Options for formatting a duration string. Valid for PERIOD | |
periodFormat.capitalizeFirstWord | boolean | Capitalize the first word | |
periodFormat.display | string | Method of displaying the period value | |
periodFormat.precision | integer¦null | Precision to use when rounding decimal numbers for display. Renders with automatic precision if null. | |
periodFormat.separator | string | The separator to use between denominations if multiple are displayed | |
periodFormat.showLabels | boolean | Render a label after each denomination | |
periodFormat.showNumbersAsWords | boolean | Render the numbers as words instead of digits | |
precision | object | Precision to use when rounding numbers for display. Valid for AUTOMATIC, ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
precision.auto | boolean | Render with automatic precision based on the value in the cell | |
precision.value | integer | Explicit precision value to use. Required unless auto is true. | |
prefix | string | Custom prefix value to render in the cell. Valid for ACCOUNTING, CURRENCY, NUMBER, PERCENT, and DATE. | |
showCurrencySymbol | boolean | Render numbers with a currency symbol. Valid for ACCOUNTING and CURRENCY. | |
showLeadingZero | boolean | Include a leading zero for decimal numbers with no whole number part. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
showNumbersAsWords | boolean | Render the number as words instead of digits. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
showPositiveSign | boolean | Render the positive sign on numbers greater than zero. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
showSignRoundedZero | boolean | Render the sign on values rounded to zero. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
showThousandsSeparator | boolean | Render the thousands separator. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
shownIn | string | The scale cell values are displayed in. Valid for AUTOMATIC, ACCOUNTING, CURRENCY, and NUMBER. | |
suffix | string | Custom suffix value to render in the cell. Valid for ACCOUNTING, CURRENCY, NUMBER, PERCENT, and DATE. | |
symbolAlign | string | Where to render the symbol relative to the value. All values valid for ACCOUNTING and CURRENCY. Left values valid for NUMBER. Right values valid for PERCENT. | |
useParensForNegatives | boolean | Render parentheses around the number instead of a negative symbol. Valid for ACCOUNTING, CURRENCY, NUMBER, and PERCENT. | |
valueFormatType | string | The value format type of the content. Setting this property will clear any other ValueFormat properties that are not valid for the new value format type. |
Enumerated Values
Property | Value |
---|---|
code | AUD |
code | BGN |
code | BRL |
code | CAD |
code | CHF |
code | CNH |
code | CNY |
code | CZK |
code | DKK |
code | EUR |
code | GBP |
code | HKD |
code | HRK |
code | HUF |
code | INR |
code | ISK |
code | IDR |
code | JPY |
code | KRW |
code | MXN |
code | MYR |
code | NOK |
code | PHP |
code | PLN |
code | RON |
code | RSD |
code | RUB |
code | SEK |
code | SGD |
code | THB |
code | TRY |
code | TWD |
code | UAH |
code | USD |
code | ZAR |
code | VND |
display | SYMBOL |
display | SYMBOL2 |
display | CODE |
generic | DOLLAR |
generic | CENT |
displayZeroAs | ZERO |
displayZeroAs | EM DASH |
enteredIn | BASIS POINTS |
enteredIn | HUNDREDTHS |
enteredIn | ONES |
enteredIn | THOUSANDS |
enteredIn | TEN THOUSANDS |
enteredIn | MILLIONS |
enteredIn | HUNDRED MILLIONS |
enteredIn | BILLIONS |
enteredIn | TRILLIONS |
displayZeroAs | ZERO |
displayZeroAs | NO |
displayZeroAs | NONE |
displayZeroAs | NOTHING |
displayZeroAs | NIL |
displayZeroAs | NOT |
displayZeroAs | NOMINAL |
displayZeroAs | IMMATERIAL |
percentSymbol | NONE |
percentSymbol | SYMBOL |
percentSymbol | WORD |
display | RAW |
display | LARGEST |
display | YEARS |
display | ALL |
separator | NONE |
separator | COMMA |
shownIn | BASIS POINTS |
shownIn | HUNDREDTHS |
shownIn | ONES |
shownIn | THOUSANDS |
shownIn | TEN THOUSANDS |
shownIn | MILLIONS |
shownIn | HUNDRED MILLIONS |
shownIn | BILLIONS |
shownIn | TRILLIONS |
symbolAlign | LEFT |
symbolAlign | LEFT INSIDE |
symbolAlign | RIGHT |
symbolAlign | RIGHT INSIDE |
symbolAlign | RIGHT SPACED INSIDE |
symbolAlign | RIGHT SPACED |
valueFormatType | AUTOMATIC |
valueFormatType | AUTOMATIC ACCOUNTING |
valueFormatType | AUTOMATIC CURRENCY |
valueFormatType | AUTOMATIC NUMBER |
valueFormatType | AUTOMATIC PERCENT |
valueFormatType | AUTOMATIC DATE |
valueFormatType | AUTOMATIC PERIOD |
valueFormatType | AUTOMATIC TEXT |
valueFormatType | ACCOUNTING |
valueFormatType | CURRENCY |
valueFormatType | NUMBER |
valueFormatType | PERCENT |
valueFormatType | DATE |
valueFormatType | PERIOD |
valueFormatType | TEXT |
Example
{
"currencySymbol": {
"currency": {
"code": "AUD",
"display": "SYMBOL"
},
"generic": "DOLLAR"
},
"dateAbbreviateMonth": true,
"dateFormatString": "d/m/yyyy",
"dateUppercaseAll": true,
"displayZeroAs": "ZERO",
"enteredIn": "BASIS POINTS",
"numbersAsWordsOptions": {
"capitalizeFirstWord": false,
"displayZeroAs": "ZERO"
},
"percentSymbol": "NONE",
"periodFormat": {
"capitalizeFirstWord": false,
"display": "RAW",
"precision": null,
"separator": "NONE",
"showLabels": true,
"showNumbersAsWords": false
},
"precision": {
"auto": false,
"value": -15
},
"prefix": "string",
"showCurrencySymbol": true,
"showLeadingZero": true,
"showNumbersAsWords": true,
"showPositiveSign": true,
"showSignRoundedZero": true,
"showThousandsSeparator": true,
"shownIn": "BASIS POINTS",
"suffix": "string",
"symbolAlign": "LEFT",
"useParensForNegatives": true,
"valueFormatType": "AUTOMATIC"
}