Skip to main content

How-to automate Google Sheets

Reading and writing a spreadsheet from a flow. The example is the SSL Watch template, which reads a list of domains from a sheet and writes results back to it.

1. Install the package​

Google Sheets is an add-on, so install it from Packages on the Designer rail — see Packages.

2. Put the credential in a vault​

Google Sheets nodes authenticate with a service-account or OAuth2 JSON file, and that file belongs in a vault rather than in the flow.

Create a vault if you have none, then add a Document item and paste the JSON into it — the steps are in Vaults. Getting the JSON in the first place is covered in Google authentication; either a service account or OAuth2 works.

Which one

A service account for unattended flows — it does not need a human to approve anything. OAuth2 when the flow should act as a particular person, and expect a browser tab asking for consent on first use.

3. Build the flow​

Drag the nodes from the palette, or right-click the canvas and search by name:

A Google Sheets flow

The template above uses Open Spreadsheet → Get Range → a loop → Set Cell Value. A minimal read is shorter: Inject → Open Spreadsheet → Get Range → Debug.

4. Point Open Spreadsheet at the document​

Set the spreadsheet URL, and set Credentials to the vault item from step 2. The node fetches the credential from the vault at run time, so the secret is never in the flow.

note

The account behind that credential needs access to the spreadsheet. Sharing the sheet with the service account's email address is the usual reason a working flow returns nothing.

5. Read a range​

Get Range properties

Note the Input section: Spreadsheet Id is bound to msg.spreadsheet_id rather than typed in. That is the normal pattern — Open Spreadsheet puts the id on the message and the nodes after it read from there, so the document is named in one place.

Get Range has two options worth knowing:

  • Headers — treat the first row as column names, so you get row.domain instead of row[0]
  • Jsonify — return rows as objects rather than arrays

Both are on by default in the template, which is why its Function node can say msg.row.domain.

6. Write back​

Set Cell Value properties

Set Cell Value takes a cell name and a value, both usually bound to message fields — the template computes "B" + (index + 2) in a Function node and passes it in.

7. Run it and look​

Add a Debug node after Get Range and run the flow. The rows appear in the Output panel, which is how you check the shape of the data before building the rest.

See also​