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:

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.

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 field's value 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.

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 have 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:

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.

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 enter 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 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 send.

In the example above, as all 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 adds 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 must provide the range in which Sheets needs to find the location to add the data. So, whereas the Write Cells Range needs an exact location 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 can implement the row function to keep track of the rows in which the data is located.

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

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 older than 7 days. The date keyword informs Sheets that the data it seeks 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 must provide the sheet ID and the range within the spreadsheet where you want to look for your data.

You can also optionally add the specific sheet within the spreadsheet in which to search - this is 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.

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 must 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 where you want to delete the data. The end index does not require 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 the spreadsheet.

Once you have the sheet ID, delete the data using the 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.