Using Google Sheets

Google Sheets integration with Axiom makes it easy to scrape data and automate tasks effectively. You can input custom text, navigate through lists of links, and much more.

To use Google sheets, you first must give Axiom permissions to read your google account. You can do this in two ways:

  1. Click the "Connect Google sheets" button from the Google Sheets and API Key page, and follow the instructions
  1. When adding a Google sheet step, if you have not already connected your Google account you will be prompted to do so

# Read data from a sheet

To read in data from a Google Sheet, click the "Add step" button in the builder and select "Read data from Google Sheet" from the list.

Select the sheet you want to read from the autocomplete. Alternatively, you can paste the URL of the spreadsheet here - this can be easily found by opening the spreadsheet in Google Drive or Google Sheets and copying the URL.

Once this is entered, you can select the sheet you want by choosing it from the "Sheet name" drop down list:

You can also optionally specify the first and last cell to begin reading from, which is useful to skip reading in heading data or other blank space. These values can be set by taking the column and row values as they appear in Google sheets. For example, if you want to start reading data from column B and row 2, enter "B2" into the "First cell" section.

# Write data to a sheet

To write some data to a Google Sheet, click the "Add step" button in the builder and select "Write data to a Google Sheet" from the list.

Select the sheet you want to write from the autocomplete. Alternatively, you can paste the URL of the spreadsheet here - this can be easily found by opening the spreadsheet in Google Drive or Google Sheets and copying the URL.

Once this is entered, you can select the sheet you want by choosing it from the "Sheet name" drop down list:

To select which data you want to write, click on the "Insert data" dropdown and select the data you wish to write from the dropdown.

# Write options

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

Enable the "Write method" option and select "User Entered" if you want to write formulas to the sheet.

# Separate Human, Input & Output Sheets

When feeding data into axiom, we recommend keeping sheets that humans edit separate from sheets axiom is using.

We recommend creating a dedicated input sheet, which axiom reads from, and an output sheet, which axiom writes data to. The output data could be a log of completed rows, or it could be data you have scraped during axiom's run.

Separating data inputs and output sheets makes for simpler automations that are more easily understood.

Your data input sheet can reference data in your human-editable sheet adjacent to it, with this formula:

='Human Editable'!A1

Where 'Human Editable' is the name of the human-editable sheet. This can then be dragged across rows and columns.

Here's an example sheet with that principle and formula illustrated.

# Select the last row from a sheet

There are two approaches to do this.

# Input new data into row 1

If you can control where data is being added, input into row 1. Then, within axiom, set within Axiom 'First Cell' as A1 in 'Read data from Google Sheet'.

# Select last row using Google Sheets Formula

Google sheets has the following formula to get the value value of Column A:

=index(A:A,max(row(A:A)*(A:A<>"")))

https://support.google.com/docs/thread/8883630/how-to-get-the-last-non-empty-cell-in-a-column?hl=en

If you'd like to see that practically implemented:

Here's an example sheet with that formula illustrated.

In this example, using axiom, you can now Read from Google sheet with First cell H2, and Last cell L2 to get the last row.

# Delete rows from a Google sheet

To delete rows from a Google Sheet, click the “Add step” button in the builder and select “Delete rows from a Google Sheet” from the list.

Then add your Google Sheet and set the first row and the last row you want to delete. Everything between the first and last rows will also be deleted.

# Create a new Google sheet

Using the step "Create a new Google sheet", it's possible to generate a new sheet. The step will output the URL of the newly created sheet, so that it can be used in other steps.

# Clear data from a Google sheet

To clear data from a Google Sheet without deleting the rows, leaving blank cells behind, click the “Add step” button in the builder and select “Clear data from a Google Sheet” from the list.

Then add your Google Sheet and set the first cell and the last cell you want to clear data from. Everything between the first and last cells will also be cleared of data, leaving empty cells.

# Upload a CSV to a Google sheet

To upload a CSV file to a Google Sheet using Axiom, follow these steps.

  1. Add "Import CSV File" step to your Axiom. In this step, you will specify the source CSV file that you want to upload. Configure the necessary parameters, such as the file path or URL, and any additional settings required for your specific use case.
  1. Add a "Write Data to Google Sheet" step to your workflow. In this step, you will configure the destination Google Sheet where you want to insert the CSV data. In the "Data" field, select the "imported-csv" data token. This token represents the data that you've imported in the previous step and ensures that the CSV data is transferred to the designated Google Sheet.