How-To: Connect your Google Sheet to Actions

Overview

This how-to walks you through how to connect your Google Sheet data to an Action.

If you’re creating a new Action with a Google Sheet datasource, you will need to refresh your Google token (see how-to).

If you are editing an existing Action to change the datasource option to be Google Sheets, you will need to share the Google Sheet with whomever originally created the Action and have them refresh their Google token (see how-to).

Instructions

Basics

1. Navigate to the Set Datasource page from one of two ways:

   1. New Action

      1. On the Actions' homepage, click ‘Create New’.

      2. Select your Action Type.

      3. Name your Action, select your authenticated account, and click ‘Next’

   2. Existing Action

      1. On the Actions' homepage, click the name of your Action.

      2. Click the progress tracker above ‘Set Datasource’.

2. Select 'Google Sheets' as your Datasource.

   

3. Add the link to your Google Sheet and name of the tab that contains your data, if you have more than one.

   1. (google) Authenticated Account: used to access the Google Sheet. By default this will be you (the action creator)

   2. URL: the URL of your google sheet

   3. Sheet Name (optional): the name of the individual sheet (tab) within the Google Sheet where the data resides. If left blank, it will automatically default to Sheet1

      

4. Before moving forward, review the Required Data Source Columns at the bottom of the page. The table will provide you with the column headers (names of the columns in your data) required to complete the action, as well as tips to the expected data type or values required by the vendor. Ensure the data you are meets these requirements.

   

5. Click ‘Save’. Your Action is now set up to connect to your Google Sheet.

   1. If you’re setting up a new Action, complete the set up process:

      1. On the ‘Review Action’ page, add a schedule if needed. Refer to the How-To: Schedule Your Action to Run documentation for questions.

      2. Click ‘Publish’.

Actions ignores any filters applied within Google Sheets and will pull in all data present on the tab you select.

If you don’t want to send all of that data, you can either filter your data or update the tab regularly to ensure only the data you want to send is present there.

If you need filtered data, create a new tab with just the data you’d like to pass to Actions - for tips on how to filter to the current date (for example), see below.

Filtering Required

This assumes you have a “base” tab, with all the data you want to action on, but you want to filter the data. A common use case is filtering for rows by date, to pull in only the current date. The instrcutions below will detail how to do just that.

In Your Google Sheet

1. Create a new tab, name it appropriately (llike Actions Sheet - DO NOT EDIT).

2. Copy the headers from your first tab here.

It’s important to note when using Google Sheets, that your columns need to be named to match what Actions is expecting. At the Set Datasoruce step, Actions details the column names it expects, so adhere to those requirements.

3. In Cell A2 of your new tab, place and update this function =IFERROR(FILTER('Sheet1'!A:H,'Sheet1'!E:E=TODAY()),"")

   1. Sheet1 should be the name of your first sheet

   2. A:H should be the columns from your sheet you want to pull in

   3. E:E should be the row your date is present in

4. Check your google sheet timezone

   1. this is impactful because TODAY() gets the current date in the timezone. So if your action runs at 1 am eastern time on 2/10, but your google sheet is in pacific time, TODAY() in pacific time would be 2/9 (at 10 pm). TLDR - make sure your google sheet timezone makes sense with 1) what time & timezone your action will run in, and 2) what date you put in the first tab.

   2. File > Settings > Timezone

Example Filter Function

• Assuming my base data tab is called Base Data and I want to pull in columns A through H: =FILTER('Base Data'!A:H,'Base Data'!E:E=TODAY())

In Your Action

1. Review the Basic Setup instructions above

Video Tutorial

Review this for a step by step walk through of setting up filtering in your google sheet, for common pitfalls, and for some tips & tricks on how to ensure things run as smoothly as you need them to!

Related articles