Using Google Sheets

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 Dashboard page, and follow the instructions
  2. 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 a 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 a step" button in the builder and select "Write 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:

By default, the output of the previous step will be automatically populated for writing. You will see this in the "DATA" section:

If you want to change this, click the cross on the data to remove it, and then click on "Click to connect step". The sidebar on the right will open, containing the data that is output from the other steps. Click on the yellow button for the data you want to connect it to your spreadsheet.

# 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-eeitable 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 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 illustated.

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 data from a Google sheet

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

Then add your Google Sheet and set the row you wish to start from, finally setting the row you want to end on.