Google Sheets

The Google Sheets steps connect your workflows to your spreadsheets:

Use them to keep a log of every generated document, update a status column, or pull data from a sheet into your documents.

Example

An operations manager tracks client orders in a sheet with an Order ID and a Status column. When an order form is submitted, the workflow:

  1. Generates an order confirmation with a Document step.
  2. Uses a Google Sheets - Write row step to update the Status column in the sheet.

Connect your account

  1. Add a Google Sheets step and click Sign in under Account.
  2. Sign in with Google and allow access.
  3. Back in the workflow editor, the Manage Google Access window opens. Click Add spreadsheets and pick the spreadsheets TemplateDocs may use.

TemplateDocs can only use the spreadsheets you approve. Approved spreadsheets are available to all workflows in your organization. To approve another spreadsheet later, click Add next to the Spreadsheet setting. Only the person who connected the Google account can add spreadsheets.

Write row

Settings

  • Account, Spreadsheet and Sheet - Where to write.
  • Action - Append row adds a new row at the bottom of the sheet. Update row changes an existing row.
  • Row values - The cell values, in column order. Type @ to insert variables or formulas. Date columns also let you pick a date or a date relative to today.
  • Table contains header - Turn this on if the first row holds column names. The values are then labeled by column name. Without a header, click Add column to add values for columns A, B, C and so on.

Updating a row

With Update row, you also set:

  • Row to update - Conditions that find the row, for example "Invoice Number is Invoice Number". Exactly one row must match. If more than one row matches, the step fails.
  • If no row matches - Fail the step, Append the row, or Skip the step. An appended row also gets the values from your is conditions, unless you set those cells yourself.
  • Column types - How each column is read when matching (Text, Number, Checkbox, Date or Date and time). Types are detected from the sheet. Change one only if it was detected wrongly.

Only cells with a value are updated. Empty values leave the existing cells unchanged.

Output variables

  • Spreadsheet ID / Spreadsheet URL - The spreadsheet that was written to.
  • Sheet Name - The sheet (tab) that was written to.
  • Updated Range - The cells that were written, such as Sheet1!A12:E12.
  • Updated Rows - The number of rows written (0 when skipped).
  • Operation - Appended, Updated or Skipped.

Example: append the client's name, email and agreement status to a spreadsheet.

Append the client's name, email and agreement status to a spreadsheet.

Find rows

Settings

  • Account, Spreadsheet and Sheet - Where to search.
  • Table contains header - Turn this on if the first row holds column names. The header row is then not returned as data.
  • Column types - How each column is read (see above).
  • Conditions - Click Add condition and choose a column, a comparison (such as is, contains, is empty or is after) and a value. Choose All conditions or Any condition. With no conditions, every row is returned.
  • Sorting - Click Add sort to order the rows by one or more columns.
  • Maximum rows - Up to 1,000 rows. The default is 100.
  • When more rows match - Fail the step or Return the first matching rows.

If no rows match, the step still succeeds and returns an empty list. The step can read sheets with up to 50,000 rows.

Output variables

  • Rows - The matching rows. Each row has a Row Number and one value per column.
  • Row Count - The number of rows returned.
  • Has More - true if more rows matched than the maximum.
  • Spreadsheet ID / Spreadsheet URL - The spreadsheet that was searched.
  • Sheet Name - The sheet (tab) that was searched.

Credits

Each Google Sheets step that runs (Write row or Find rows) costs 1 credit. The first 3 credits after the trigger are included in every run. See Credits.