Google Sheets

This tutorial demonstrates several useful Google Sheets use cases to help you get started and give you ideas for how you can use Google Sheets in your workflows. To see an implementation of these use cases, review the Workflow Notification Tracking in Google Sheets template in the template library.

This tutorial covers the following Google Sheets use cases:

• Generate a bearer token
• Search for a Google spreadsheet
• Create and update a spreadsheet
• Query data from a spreadsheet
• Remove data from a spreadsheet

Before you begin

All Google Sheet steps require a token for authentication. To generate an authentication token, you need to create a GCP integration. This integration is used to generate the token. The token is then passed in each Google Sheets step.

Trigger a workflow

The first thing you need to do is configure a webhook to which the failed workflow events can be sent. The webhook address needs to be configured as part of the notification process for each workflow whose failed events you want to track.

The webhook itself does not need any configuration since it will only receive events of failed workflows.

When an event arrives, you need to look for escaped characters as those will be parsed differently when passed to Sheets. For example, in the event below, the error message escapes VT_API_KEY``. You can tell it's escaped because it's enclosed in back slashes ( \ \ ).

Use the Escape JSON String utility step and provide the value of the field to escape so the information is passed to Sheets as you expect it. In this case, we'll provide the value {{ $.event.error }}.

In addition, since Sheets expects dates in a certain format, you can use the Split Text utility step to parse the timestamp you receive from Torq. The input is the timestamp and the separator is a space character.

This provides you with an array of the components comprising the timestamp. You will use this later when adding the information to your spreadsheet.

Generate a bearer token

Now that you have pulled in the events and formatted them properly, you can generate a token to work with your GCP account.

The email you enter should be one that has permission to work with the GCP integration you configured. In addition, you should define the scopes for which this token can be used.

To enable searching for files and working with spreadsheets, make sure that you have the following:

• https://www.googleapis.com/auth/drive 
• https://www.googleapis.com/auth/spreadsheets

Search for a spreadsheet

There are multiple ways to search for spreadsheets. You can use the List Spreadsheets step to retrieve all of the spreadsheets in your account.

Alternatively, if you want to check if a specific spreadsheet exists, you can use the Google Drive Search Files step. Using this step, you can provide information about the kind of file you are searching for, perhaps the filename, modification date, etc.

In addition, you can also decide which fields regarding the files you want to return so you can get a specific dataset to work with.

Create and update a spreadsheet

When you create a spreadsheet, in most cases, you will not want to have just an empty spreadsheet. You will want to add header information and the data you need in the sheet.

You create a spreadsheet using the Create Spreadsheet step. In addition to the basic naming parameter, if the spreadsheet includes time and date information, you will want to add the optional Timezone parameter. Also, if you will be entering information in a language other than English, you should set the locale, too.

After you create the workflow, you will want to set the headers. You can do this using either a Write Cells Range or Append Rows step. In the Write Cells Range step, you will need to provide the spreadsheet ID into which you want to add the data, as well as the cell range in the spreadsheet where to put the headers - generally at the very top.

The data input type determines how Sheets treats the information you are sending it.

• Raw means that the content is not parsed and is treated as a string. For example, “=A2+B2” would not be calculated in the cell, rather it would appear as =A2+B2.
• User entered means that the input is parsed exactly as if it were entered into the UI. For example, "Aug 1 2022" becomes a date, and "=row()" calls the Sheets function for the row number.

In the example above, as all of the values are strings, the input type is set to Raw.

The data matrix includes an array nested within an array. The nested array includes values for the 6 column names we want to create.

The Append Rows step is used for adding information to the spreadsheet - the information is added after the last row of the table currently used. Similar to the Write Cells Range step, in the Append Rows step, you need to provide the spreadsheet ID. However, when appending, you need to provide the range in which Sheets needs to find the location in which to add the data. So, whereas the Write Cells Range needs an exact location where to put the information, Append Rows needs a much broader range, called Table Range, where it can locate the place to put the additional data.

In addition, note that the Input Type is defined as User Entered. This is especially important for this data set as we are able to implement the row function to keep track of the rows in which the data is located.

We’ll use this function when trying to locate information in our query step.

Query data from a sheet

There are many instances where you will want to find specific data within a Google Sheet. You can do this using the Query Data step.

Google Sheets supports SQL so you can build robust queries within the steps to get exactly the information you need.

In the following example, we use an SQL query to find the row of any entry that is older than 7 days. The date keyword is used to inform Sheets that the data it is looking for is a date. It’s important to note that to query dates in Sheets, the format must be yyyy-mm-dd.

As in previous steps, you need to provide the sheet ID and the range within the spreadsheet in which you want to look for your data.

You can also optionally add the specific sheet within the spreadsheet in which to search - this is really only necessary if your data is not in the first sheet. Also, you can add the optional output type parameter if you don’t want the step output in JSON format.

Remove data from a sheet

When you want to delete data from a spreadsheet, there are several key pieces of information you will need in addition to the spreadsheet ID:

The sheet ID - the sheet in the spreadsheet from which you want to delete the data. Even if there is only one sheet in the spreadsheet, you will have to provide the sheet ID.

Start index - the earliest row in the sheet from which you want to delete the data, minus 1. Meaning, if the data you want to delete appears in rows 2 through 4, your start index will be 1.

End index - the last row in the sheet at which you want to delete the data. The end index does not require any additional manipulation, meaning, if the data you want to delete appears in rows 2 through 4, your end index will be 4.

You can obtain the sheet ID from the Get Spreadsheet step - it returns a lot of information about the spreadsheet, and the sheet ID can be found towards the bottom. Expand the context window and search for spreadsheetId.

Once you have the sheet ID, you can proceed to delete the data using a Delete Rows step. In the example below, you can see that a Sprig function is used to subtract 1 from the value at which our data starts. This will ensure that the start index is always correct.