Update

Sets values in a range of a spreadsheet

9 variables
9 variables

Sets values in a range of a spreadsheet. The caller must specify the spreadsheet ID, range, and a valueInputOption

Authorization

To use this building block you will have to grant access to at least one of the following scopes:

  • See, edit, create, and delete all of your Google Drive files
  • View and manage Google Drive files and folders that you have opened or created with this app
  • View and manage your spreadsheets in Google Drive

Input

This building block consumes 9 input parameters

  = Parameter name
  = Format

spreadsheetId STRING Required

The ID of the spreadsheet to update

range STRING Required

The A1 notation of the values to update

valueInputOption ENUMERATION

How the input data should be interpreted

responseDateTimeRenderOption ENUMERATION

Determines how dates, times, and durations in the response should be rendered. This is ignored if response_value_render_option is FORMATTED_VALUE. The default dateTime render option is DateTimeRenderOption.SERIAL_NUMBER

includeValuesInResponse BOOLEAN

Determines if the update response should include the values of the cells that were updated. By default, responses do not include the updated values. If the range to write was larger than than the range actually written, the response will include all values in the requested range (excluding trailing empty rows and columns)

responseValueRenderOption ENUMERATION

Determines how values in the response should be rendered. The default render option is ValueRenderOption.FORMATTED_VALUE

range STRING

The range the values cover, in A1 notation. For output, this range indicates the entire requested range, even though the values will exclude trailing rows and columns. When appending values, this field represents the range to search for a table, after which values will be appended

majorDimension ENUMERATION

The major dimension of the values.

For output, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS will return [[1,2],[3,4]], whereas requesting range=A1:B2,majorDimension=COLUMNS will return [[1,3],[2,4]].

For input, with range=A1:B2,majorDimension=ROWS then [[1,2],[3,4]] will set A1=1,B1=2,A2=3,B2=4. With range=A1:B2,majorDimension=COLUMNS then [[1,2],[3,4]] will set A1=1,B1=3,A2=2,B2=4.

When writing, if this field is not set, it defaults to ROWS

values[] OBJECT_ARRAY

Output

This building block provides 9 output parameters

  = Parameter name
  = Format

updatedRows INTEGER

The number of rows where at least one cell in the row was updated

updatedData OBJECT

Data within a range of the spreadsheet

updatedData.range STRING

The range the values cover, in A1 notation. For output, this range indicates the entire requested range, even though the values will exclude trailing rows and columns. When appending values, this field represents the range to search for a table, after which values will be appended

updatedData.majorDimension ENUMERATION

The major dimension of the values.

For output, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS will return [[1,2],[3,4]], whereas requesting range=A1:B2,majorDimension=COLUMNS will return [[1,3],[2,4]].

For input, with range=A1:B2,majorDimension=ROWS then [[1,2],[3,4]] will set A1=1,B1=2,A2=3,B2=4. With range=A1:B2,majorDimension=COLUMNS then [[1,2],[3,4]] will set A1=1,B1=3,A2=2,B2=4.

When writing, if this field is not set, it defaults to ROWS

updatedData.values[] OBJECT_ARRAY

updatedColumns INTEGER

The number of columns where at least one cell in the column was updated

spreadsheetId STRING

The spreadsheet the updates were applied to

updatedRange STRING

The range (in A1 notation) that updates were applied to

updatedCells INTEGER

The number of cells updated