Google Sheets
The Google Sheets connector allows you to read data from a Google Sheet and write data to a Google Sheet.
See also following similar connectors:
- Microsoft Office 365: work with Excel files in the cloud
- Airtable
Reading data
Use the block Get data from Sheet
to read data from a sheet:
The output is a matrix
, meaning a list of lists (array of arrays). Example in JSON:
[
[ "First name", "Last name", "Email" ],
[ "John", "Doe", "john@doe.com" ],
[ "Bill", "Davis", "bill@acme.com" ]
]
Each row from the Sheet becomes one row in the output, and the rows themselves are also lists (arrays) where each cell/column is also one item in the list.
If the Google Sheet has a header row, you can also use the block Get data from sheet with headers
. The result will be a list of objects. Each row is now an object with key/values. The keys are taken from the first row (the header row).
Example Sheet:
Example automation:
Result:
Writing data
You can use the block Append one row to sheet
to add a row to a Sheet. The row will be added below the last row in the Sheet that contains data:
You can also write multiple rows at once by using the block Append rows to sheet
. The input of this block should be a matrix (lists of lists) or a list of objects.
Example adding rows with a list of objects:
The keys of the objects in the list are ignored. The values are added to the sheet in the order of the keys of each object. Warning: this method is not safe. The order of keys in an object can change and if keys are missing in certain objects in the list, the data will shift (e.g. last name
might end up in a different column for different records).
The correct way is to build a matrix
, using variables.
Define two variables of type list: row
and rows
. Make sure to make variables empty before using them, to avoid having incorrect data in the variable.
Loop over data, for example Salesforce list contacts
. Add items to variable row
, where each item is one field from a contact (first name, last name, email etc.):
Add the variable row
to the variable rows
:
Finally, use the variable rows
as input for the Sheet:
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!