Skip to main content Skip to complementary content

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:

Reading data from a spreadsheet.

The Get Data From Spreadsheet block. Its Input tab contains fields for the Spreadsheet Id, Sheet Name, and the first and last rows and columns to be accessed.

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:

A spreadsheet.

An example sheet of four rows and three columns. The top row is a header row.

Example automation:

an automation that can read the spreadsheet.

The Get Data From Sheet With Headers block.

Result:

Result of an automation reading a spreadsheet.

Data is output for each row with the correct headers.

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:

Adding a row to a spreadsheet.

The Append One Row To Sheet Inputs tab. It contains fields for the Spreadsheet Id, the Sheet name, and the values of individual cells within the row.

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:

an automation that writes a data matrix to a spreadsheet.

an automation consisting of a Start block, a List Contacts block, and an Append Rows To Sheet block. The List Contacts sheet is input in the Append Rows To Sheet Data Matrix field.

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.):

Adding items to the row variable.

an automation containing a Start block, a Varbale - rows block, a List Contacts block, a loop containing a Variable - row block and a Variable - rows block, and an Append Rows To Sheet block. The Variable - row block is selected. Items from the List Contacts block are added to the row.

Add the variable row to the variable rows:

Adding items to the rows variable.

As above, but the second Variable - rows block is selected. The row is added to the list of rows.

Finally, use the variable rows as input for the Sheet:

Adding rows to a spreadsheet.

As above, but the Append Rows To Sheet block is selected. The rows data matrix is used 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!