Working with Google Sheets

# Connecting Google Sheets

To get started, Google Sheets will need to be connected with your Axiom.ai account. Follow the steps below to connect your Google Sheets account to axiom.ai:

  1. Open Axiom.ai.
  2. Navigate to Credentials and API key.
  3. In the Google Sheets section, click Connect Google Sheets.
  4. Follow the on-screen instructions to connect Google Sheets.
how to connect your Google Sheets account in axiom.ai

# Disconnect Google Sheets

To disconnect Google Sheets, follow the steps below:

  1. Open Axiom.ai.
  2. Navigate to Credentials and API key.
  3. In the Google Sheets section, click Disconnect Google Sheets.
  4. Follow the on-screen instructions to disconnect Google Sheets.
how to disconnect your Google Sheet account in axiom.ai

# Create a new Google Sheet

The Create a new Google Sheet step will allow you to create a new Google Sheet within your Google Drive. This will return a variable that contains URL of the newly created Google Sheet which can then be used to read or write data to the Google Sheet.

# Reading data from a Google Sheet

The Read data from a Google Sheet step can be used to read data into your automation. Details on the various options that are available in this step are listed below.

  • Spreadsheet: this autocomplete form can be used to select your spreadsheet, simply start typing the sheet name, or alternatively, paste in the URL. This can be set using a variable.
  • Sheet name: if you would like the automation to reference a specific sheet within your spreadsheet, you can select it from the dropdown.
  • First cell: when enabled, this allows you to set the first cell that you would like to read into your automation, for example, A2 allows you to skip any title rows that you have in your spreadsheet.
  • Last cell: when enabled, this allows you to set the last cell that you would like to read into your automation, for example, B10.
  • Continue when empty: when enabled, allows the automation to continue when the sheet does not have any content.

This step will output a variable that can then be used. For example, you may use this variable in a "loop through data" step to loop through each row and use the data from each row in your automation.

# Writing data to a Google Sheet

The Write data to a Google Sheet step can be used in your automations to write data to a Google Sheet. Details on the various options that are available in this step are listed below.

  • Spreadsheet: this autocomplete form can be used to select your spreadsheet, simply start typing the sheet name, or alternatively, paste in the URL. This can be set using a variable when setting the toggle to set sheet url from data.
  • Sheet name: if you would like the automation to reference a specific sheet within your spreadsheet, you can select it from the dropdown.
  • Data: the data that you would like to write into the sheet, this will be a variable that has been created in a previous step. Select the data variable from the dropdown.
  • Write options: the method that you would like to use to write the data to your sheet. Select "Clear data before writing" to delete all data before writing any new data; select "Add to existing data" to write the new data to the sheet without first deleting existing data.
  • Write method: this is the method that will be used to write the data. By default, this is set to "raw" which will input the data as-is, "user entered" will enter the data as if the user has input this - useful for adding formula.

# Edit a row in a Google Sheet

The Write data to a Google Sheet step may be used to edit a row of data within a Google Sheet. Set up your step as you normally would, modifying the following settings:

  • Write options: set this to "Add to existing data" to prevent the clearing of existing data.
  • First cell: this should be set to the cell that you wish to write to, for example, A2. This could be used in combination with the Find row in a Google Sheet step.

# Find a row in a Google Sheet

The Find row in a Google Sheet step can be used to find a row in a Google Sheet that matches certain criteria. Details on the various options that are available in this step are listed below.

  • Spreadsheet: this autocomplete form can be used to select your spreadsheet, simply start typing the sheet name, or alternatively, paste in the URL. This can be set using a variable.
  • Sheet name: if you would like the automation to reference a specific sheet within your spreadsheet, you can select it from the dropdown.
  • Values: a comma-separated list of values that you would like to use to find the row within the spreadsheet.
  • Exact match: this setting can be activated to check for the whole value rather than finding a partial match.
  • Value matching mode: select "any" to find rows that contain any of the values enterted in the values field, or "all" to match all.
  • Columns: the columns that you would like to search for a match in. this is a numeric value, for example, 1,2 would be the first and second column.

Output: the variable that this step produces will include a row number if the step has found a matching row.

# Delete rows from a Google Sheet

The Delete rows from a Google Sheet step can be used to specify rows you wish to delete from your sheet.

  • Spreadsheet: this autocomplete form can be used to select your spreadsheet, simply start typing the sheet name, or alternatively, paste in the URL. This can be set using a variable.
  • Sheet name: if you would like the automation to reference a specific sheet within your spreadsheet, you can select it from the dropdown.
  • First row to delete: Enter the number of the first row to delete.
  • Last row to delete: Enter the number of the last row to delete.

# Clear data from a Google Sheet

The Clear data from a Google Sheet step can be used to clear rows.

  • Spreadsheet: this autocomplete form can be used to select your spreadsheet, simply start typing the sheet name, or alternatively, paste in the URL. This can be set using a variable.
  • Sheet name: if you would like the automation to reference a specific sheet within your spreadsheet, you can select it from the dropdown.
  • First cell: Enter the column & row to start from, for example AB3.
  • Last cell: Enter the column & row to end at, for example AB33.

# Google token expiry

When you connect your Google Sheets account to Axiom.ai, we do not store your login credentials, instead we use a token that Google provides us to authenticate your account. On occasion, this token will expire. This is not something that Axiom.ai has control over, though we are always looking at ways that we can improve this experience.

Google does not publicise the reasons for this token revocation. However, we suspect that this may be related to the speed of automations reading/writing content to Google Sheets. If you notice this happening often, consider running your automations less frequently.