Skip to main content

Google Sheets

The Google Sheets node allows you to automate work in Google Sheets, and integrate Google Sheets with other applications. Robbot Automation has built-in support for a wide range of Google Sheets credentials, including creating, updating, deleting, appending, removing and getting documents.

On this page, you'll find a list of operations the Google Sheets node supports and links to more resources.

!!! note "Credentials" Refer to Google Sheets credentials for guidance on setting up authentication.

!!! note "Examples and templates" For usage examples and templates to help you get started, take a look at Robbot Automation's Google Sheets list.

Operations​

  • Document
    • Create
    • Delete
  • Sheet within document
    • Append or update: append a new row, or update the current one if it already exists.
    • Clear: clear all data from a sheet
    • Create: create a new sheet
    • Delete: delete columns and rows from a sheet
    • Read rows: read all rows in a sheet.
    • Remove: remove a sheet
    • Update: update rows in a sheet

Refer to Google Sheet's API documentation for more information about the service.

Update operations​

To update data in a sheet:

  1. Select your Authentication method and credential. Refer to Google credentials for more information.

  2. In Resource, select Sheet Within Document.

  3. In Operation, select one of the append operations.

  4. Choose the Document and Sheet you want to edit.

  5. Choose your Data Mode:

    • Auto-Map Input Data to Columns: use this when the table column names (or JSON parameter names) in the node input view match the column names in your spreadsheet. In Column to Match On, select the column name in Google Sheets that you want to map to.

    • Map Each Column Below: use this when the column names and data structure in your node input data doesn't match the names and structure in Google Sheets.

      1. In Column to Match On, select or enter the column name in Google Sheets.

      2. In Value of Column to Match On, drag in the table column (or JSON parameter) whose value you want to search for.

      3. In Values to Send select Add Field.

      4. Enter a Column from your Google Sheet, and the value from the node input data you want to add to that column in Value.

        ??? Details "View example and screenshots" This example uses the Customer Datastore node to provide sample data to load into Google Sheets. It assumes you've already set up your credentials.

        1. Set up a Google Sheet with two columns, `test1` and `test`. In `test1`, enter the names from the Customer Datastore node:
        2. Create the workflow: use the manual trigger, Customer Datastore, and Google Sheets nodes.
        3. Open the Customer Datastore node, enable **Return All**, then select **Save**.
        4. In the Google Sheets node, go through the steps above, using these settings:
            * Select **Update** as the **Operation**.
            * In **Column to Match On**, select `test1`.
            * For **Value of Column to Match On**, drag in the **name** column from the input view.
            * Then set up your **Values to Send**: enter `test2` in **Column**, and drag the **email** column from the input view into **Value**.
        5. Select **Save**.
        6. View your spreadsheet. **test2** should now contain the email addresses that match to the names in the input data.

    • Nothing: don't map any data.

Read operations​

To read from a sheet:

  1. Select your Authentication method and credential. Refer to Google credentials for more information.
  2. In Resource, select Sheet Within Document.
  3. In Operation, select Read Rows.
  4. Choose the Document and Sheet you want to read from.

!!! note "First row" Robbot Automation treats the first row in a Google Sheet as a heading row, and doesn't return it when reading all rows. If you want to read the first row, use the Options to set Data Location on Sheet.

Filters​

By default, the Google Sheets node reads and returns all rows in the sheet. To return a limited set of results:

  1. Select Add Filter.
  2. In Column, select the column in your sheet to search against.
  3. In Value, enter a cell value to search for. You can drag input data parameters here.

If your filter matches multiple rows, Robbot Automation returns the first result. If you want all matching rows:

  1. Under Options, select Add Option > When Filter Has Multiple Matches.
  2. Change When Filter Has Multiple Matches to Return All Matches.

Output formatting​

You can choose how Robbot Automation formats the data returned by Google Sheets:

  1. After setting up the node to read rows, select Add Option > Output Formatting.
  2. In General Formatting, choose one of:
    • Values (unformatted): numbers stay as numbers, but Robbot Automation removes currency signs and other special formatting.
    • Values (formatted): Robbot Automation displays the values as they appear in Google Sheets (for example, retaining commas or currency signs) To do this, Robbot Automation converts the data type from number to string.
    • Formulas: Robbot Automation returns the formula. It doesn't calculate the formula output. For example, if a cell B2 has the formula =A2, Robbot Automation returns B2's value as =A2 (in text).
  3. Choose your preferred Date Formatting.

Append an array​

To insert an array of data into Google Sheets, you must convert the data into a valid JSON (key, value) format. You can use the [Code node](/integrations/builtin/core-nodes/Robbot Automation-nodes-base.code/) to convert the array into JSON format.

Cell formatting for update and append​

You can choose how to format the data in cells:

  1. After setting up the node to append data, select Add Option > Cell Format.
  2. In Cell Format, select one of:
    • Let Robbot Automation format: the new cells in your sheet keep the data type of the data in Robbot Automation.
    • Let Google Sheets format: allow Google Sheets to style the cells as if you typed the data directly into the cells.