Google Sheets actions

The Google Sheets block reads and writes cells, rows, tabs and spreadsheets. For the plain-language guide, see Google Sheets.

When to use it

Use it to read a working schedule into a workflow, append results such as a list of matched payments, or keep one row per record current with upsertRow. There is no Google Sheets trigger; start the workflow on a schedule, from the API or from another system's event.

Connection

• Provider id: google-sheets. Google OAuth with the spreadsheets and drive.file scopes.
• Pin an account with config.connection. Without it, the step uses the company's default Google Sheets connection.

Actions

Settings

spreadsheetId is the id in the sheet URL: /spreadsheets/d/<id>/edit.

Ranges and values

• Ranges are A1 notation. Quote a sheet title that has a space or apostrophe: 'Q3 Data'!A1:C10.
• values (for appendValues and updateValues) is a 2-D array, one inner array per row. rowValues (for insertRow and upsertRow) is one row, a flat array.
• Writes default to RAW. Set valueInputOption to USER_ENTERED only when Google should parse formulas, dates or numbers.
• Google allows 60 writes per minute per user. Put many rows in one appendValues call instead of one step per row.
• getSpreadsheet returns the numeric sheetId that copySheet, insertRow and insertColumn need.

Upsert and insert

• upsertRow reads range, finds the first row where column keyColumn (zero-based, inside the range) equals keyValue as text, and overwrites that row. When no row matches, or keyColumn is not set, it appends the row. keyValue is required when keyColumn is set. The output's upsertResult is updated or appended.
• insertRow inserts an empty row at the zero-based rowIndex of the sheet with id sheetId, then writes rowValues into it. When you also pass sheetTitle, it must match the sheet's title or the step fails.
• deleteSheet deletes the sheet named sheetTitle and returns deleted: true.

Irreversible actions

deleteSheet, clearValues, batchClearValues, batchClearValuesByDataFilter and batchUpdateSpreadsheet change the file for good. batchUpdateSpreadsheet takes raw Sheets API Request objects and can delete sheets, rows and ranges.

Output

• readValues returns spreadsheetId, range, majorDimension and values (an empty array when the range is blank). Cell values come back as formatted text unless you set valueRenderOption.
• appendValues returns spreadsheetId, tableRange, updatedRange, updatedRows, updatedColumns and updatedCells.
• createSpreadsheet returns spreadsheetId, spreadsheetUrl, title, locale, timeZone and sheets.

Read fields with {{steps.<stepId>.<path>}}, for example {{steps.read_accruals.values}}.

Example

Read the accruals tab, then add or update the row for one vendor. The caller passes vendorId and amount.

{
  "name": "Update the accruals tracker",
  "trigger": { "type": "api" },
  "steps": [
    {
      "id": "read_accruals",
      "type": "action",
      "name": "Read accruals",
      "action": "google-sheets.readValues",
      "config": {
        "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
        "range": "Accruals!A1:D200"
      }
    },
    {
      "id": "upsert_vendor",
      "type": "action",
      "name": "Add or update the vendor row",
      "action": "google-sheets.upsertRow",
      "config": {
        "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
        "range": "Accruals!A2:D200",
        "rowValues": ["{{input.vendorId}}", "{{input.amount}}", "{{run.id}}"],
        "keyColumn": 0,
        "keyValue": "{{input.vendorId}}",
        "valueInputOption": "USER_ENTERED"
      }
    }
  ]
}

Live catalog

• GET /api/v1/blocks/google-sheets returns the actions and fields.
• GET /api/v1/blocks/google-sheets/actions/<operation>/examples returns stored input and output examples.

Errors and limits

• Each Google call has a 30-second limit.
• A missing required field or an unknown valueInputOption fails the step before Google is called. Google errors fail the step with Google's message.
• A sheet title or id that is not in the spreadsheet fails deleteSheet and insertRow with "not found in spreadsheet".