Adapter - Google Sheets
This page describes version 2.0.5 of the adapter.
Overview
This adapter uses the Google Sheets API (v4) to read and write the values stored in Google Sheets.
Configuration
A valid Google Cloud Platform service account is required to use this adapter. Refer to the Google Cloud Platform documentation for instructions on how to setup a service account and obtain its client email and private key properties to authenticate UIP with the Google Cloud Platform.
The target spreadsheet(s) must be "Shared" with the service account that is used for this adapter.
Properties
| Name |
Display |
Is Required |
Comments |
Type |
Default Value |
Maximum Value |
Minimum Value |
Maximum Length |
Minimum Length |
| credential_private_key |
Google Cloud authentication information(private_key) |
True |
In order to use this adapter, you need to set up authentication information(private_key) with a contract with Google Cloud. |
password |
|
|
|
|
|
| credential_client_email |
Google Cloud authentication information(client_email) |
True |
In order to use this adapter, you need to set up authentication information(client_email) with a contract with Google Cloud. |
string |
|
|
|
|
|
Commands
Write Range (writerange)
Sets values in a range of a spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet to update. |
True |
string |
| range |
Range |
The A1 notation of the values to update. Example: YourSheet!A1:C4 |
True |
string |
| valueInputOption |
Treat Value Input As |
How the input data should be interpreted. Expected one of ["RAW", "USERENTERED"]. For input=RAW the values the user has entered will not be parsed and will be stored as-is. For input=USERENTERED the values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. |
False |
string |
| majorDimension |
Write Values By |
Determines if the arrays included are interpreted as arrays of columns or rows. Expected one of ["ROWS", "COLUMNS"]. 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. |
False |
string |
| values |
Values |
The data to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell. Supported value types are: bool, string, and double. Null values will be skipped. Example: [[1,2],[3,4]] |
True |
string |
Response Properties
| Name |
Description |
Type |
| updatedColumns |
The number of columns where at least one cell in the column was updated. |
string |
| updatedRows |
The number of rows where at least one cell in the row was updated. |
string |
| updatedCells |
The number of cells updated. |
string |
Write Cell (writecell)
Sets value in a cell of a spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet to update. |
True |
string |
| sheet |
Sheet Name |
The name of the sheet to update. If left empty, the first sheet in the spreadsheet is used. |
False |
string |
| cell |
Cell |
The cell to update. Example: A2 |
True |
string |
| valueInputOption |
Treat Value Input As |
How the input value should be interpreted. Expected one of ["RAW", "USERENTERED"]. For input=RAW the value the user has entered will not be parsed and will be stored as-is. For input=USERENTERED the value will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. |
False |
string |
| value |
Value |
The value to be written in specified cell. The value of the specified cell will be cleared if this is empty. Supported value types are: bool, string, and double. |
False |
string |
Response Properties
| Name |
Description |
Type |
| updatedCell |
The Updated Cell. |
string |
Append Data (appenddata)
Appends values in a range of a spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet to update. |
True |
string |
| range |
Range |
The A1 notation of the values to update. Example: YourSheet!A1:C4 |
True |
string |
| valueInputOption |
Treat Value Input As |
How the input data should be interpreted. Expected one of ["RAW", "USERENTERED"]. For input=RAW the values the user has entered will not be parsed and will be stored as-is. For input=USERENTERED the values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. |
False |
string |
| insertDataOption |
Insert data option |
How the input data should be inserted. Expected one of ["OVERWRITE", "INSERT_ROWS"]. For input=OVERWRITE the new data overwrites existing data in the areas it is written. (Note: adding data to the end of the sheet will still insert new rows or columns so the data can be written.). For input=INSERT_ROWS rows are inserted for the new data. |
False |
string |
| values |
Values |
The data to be appended. This is an array of arrays, the outer array representing all the data and each inner array representing a row. Each item in the inner array corresponds with one cell. Supported value types are: bool, string, and double. Null values will be skipped. Example: [[1,2],[3,4]] |
True |
string |
Response Properties
| Name |
Description |
Type |
| updatedColumns |
The number of columns where at least one cell in the column was updated. |
string |
| updatedRows |
The number of rows where at least one cell in the row was updated. |
string |
| updatedCells |
The number of cells updated. |
string |
Read Range (readrange)
Reads values from a specified range of a spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet to read from. |
True |
string |
| range |
Range |
The A1 notation of the range to read from. Example: YourSheet!A1:C4 |
True |
string |
| majorDimension |
Read Values By |
Determines if the arrays returned are arrays of columns or rows. Expected one of ["ROWS", "COLUMNS"]. For output, with range=A1:B2 and majorDimension=ROWS then A1=1,B1=2,A2=3,B2=4 will return [[1,2],[3,4]]; with range=A1:B2 and majorDimension=COLUMNS then A1=1,B1=2,A2=3,B2=4 will return [[1,3],[2,4]]. |
False |
string |
Response Properties
| Name |
Description |
Type |
| values |
The content of the values from the specified range |
string |
Read Cell (readcell)
Reads value from a specified cell.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet to read from. |
True |
string |
| sheet |
Sheet Name |
The name of the sheet to read from. If left empty, the first sheet in the spreadsheet is used. |
False |
string |
| cell |
Cell |
The cell to read from. Example: A2 |
True |
string |
Response Properties
| Name |
Description |
Type |
| value |
The content of the value from the specified cell |
string |
Add Sheet (addsheet)
Add a new sheet to the spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet where the sheet is added. |
True |
string |
| title |
Sheet Name |
The name of the sheet to add. The name must be unique within the spreadsheet (matching is case insensitive). |
False |
string |
| index |
Sheet Index |
The 0-based index where to add the sheet amongst the other sheets in the spreadsheet. If it is empty, the sheet is added to the end of spreadsheet. |
False |
string |
Response Properties
| Name |
Description |
Type |
| sheetId |
The ID of the sheet which was added. |
string |
| title |
The name of the sheet which was added. |
string |
Delete Sheet (deletesheet)
Delete a sheet from the spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet where the sheet is to be deleted. |
True |
string |
| title |
Sheet Name |
The name of the sheet to be deleted (matching is case insensitive). |
True |
string |
Response Properties
| Name |
Description |
Type |
| isSuccess |
Was the delete operation successful? |
boolean |
Query Data (query)
Queries values from a specified range of a spreadsheet.
Request Properties
| Name |
Display |
Description |
Is Required |
Type |
| spreadsheetId |
Spreadsheet ID |
Specify the ID of the spreadsheet to query. |
True |
string |
| range |
Range |
The A1 notation of the range to query from; this must include the sheet name and a named range. Examples: 'YourSheet!A:C' or 'NewSheet!A2:D12 |
True |
string |
| query |
Query Statement |
The query to perform (written in Google Visualization API Query Language). Example: select B, C where A='Engineering' |
True |
string |
Response Properties
| Name |
Description |
Type |
| values |
The content of the values from the query |
string |
Release History
| Version |
Type |
Description |
Tracking # |
Date |
| 2.0.1 |
Initial |
First release in the Adapter Type store. |
|
|
| 2.0.3 |
Maintenance |
Updated third-party components and improved maintainability. |
NAP-11518 |
|
| 2.0.4 |
Maintenance |
Updated third-party components and improved maintainability. |
NAP-23945 |
2022-11-29 |
| 2.0.5 |
Maintenance |
Updated third-party components and improved maintainability. |
NAP-27635 |
2024-07-31 |
Categories