IMPORTANT: This documentation has been discontinued. Read the updated Google Sheets documentation on our new documentation portal.
Read data and write information in your Google sheet.
Preconditions
1) Google Sheet API Service Activation
To use the Google Sheet services, you must activate the Google Sheets API resource in your GCP console (Google Cloud Platform). Click here to know more about services activation.
2) Authentication
It’s necessary to have a Digibee account of the google-key type. If you don’t have it yet, click here to access the account services in your GCP Console and create a new key. To have more information about how to generate a new authentication key, click here.
3) Sharing
The service account previously generated has a service account email. Use the address to share, in edition mode, the sheet that will receive the data.
4) Errors handling
All the Capsules have standard responses. Whenever a request is successfully processed, the “success” field of boolean type will be returned in the JSON root. Use this information to handle the errors in your pipeline.
Google Sheets Capsules
Get Spreadsheets By Id
This Capsule enables metadata to be checked in a Google Sheet.
Example:
URL: address to access through the navigator
Sheets: list of the pages that exist inside the sheet
Title: name of the sheet
Get Rows Values by Range
This Capsule can read data from the Google Sheet. It’s necessary to specify the name of the page, the columns range and the parameters to control the pagination.
Example:
To read data in the columns A, B, C, D, E, F, G, H, I, J, K, L from line 1 to line 100, these should be the parameters:
First Column: A
Last Column: L
Start Row: 1
Limit Row: 100
Append Data
This Capsule eases data record in your Google Sheet thanks to its capacity to record a single line or a list.
It’s necessary to specify the name of the page where the data will be recorded. If there’s no specification, the record will be made in the first found page.
There’s no need to specify the columns range. However, it’s necessary to inform from which column and line the data writing must be done. The values are always added after the last line.
Example:
Array provided to the Capsule through Double Braces expressions.
IMPORTANT: the “Append Data” Capsule has characteristics that disable data to be written in the same page of your sheet. In case of simultaneous writing, the data can be overwritten by the requests. Don’t use it in flows that allow parallelism.
The JSON data will be transformed into columns, respecting the order of the sent attributes instead of the naming. If you need to reorganize the fields, use one of our transformation components, such as Transformer (JOLT).
Take a look at the following example:
[
{
"operation": "sort",
"spec": {
"*": ""
}
}
]