Google Sheets Source

Grouparoo is the Reverse ETL platform to connect Google Sheets data to your SaaS tools. This enables all of those crazy sheets out there to be the source of truth for your profiles and be fed into your marketing tools. Don't forget: with great power comes great responsibility!

Google setup

In Grouparoo, apps make the connection to facilitate data movement in the form of sources and destinations. In the Google Sheet case, what we need is to teach Grouparoo how to be able to read things in your Google Drive. It uses something called "Service Accounts." So let's make one of those if you haven't already.

In the credentials section of the Google API console, you can make a new Service Account.

This allows you to essentially make a user that will have access to the documents that are shared with them. After that, there are some other screens about making roles, but those are not necessary. You can hit "Cancel" on that.

When clicking on your new Service Account, you can now make a key. This is the information you will give to Grouparoo to be able to connect as this new user.

Creating a key in this way, automatically downloads a .json file that looks something like this:

We will need the private_key and the client_email to setup an app in Grouparoo.

Create a Grouparoo app

In Grouparoo, you make a new app from the "Platform" section in the navigation. Things in this section are meant to be done by engineers (as indicated by all the "credentials" and "JSON" talk above).

For Google Sheets, you paste in the client_email and the private_key straight from the JSON file. The private_key is a bit long, but just go with it. It's ok to include those \n characters as-is.

You can "Test Connection" to make sure everything is working.

So now, you should have your app ready to go!

Give access to the document

In a document that you want to share with Grouparoo, you share it with this Service Account. In this example case, it was grouparoo-access@sample-sources.iam.gserviceaccount.com. Yours will likely look similar to this:

I like this model because it means that the owners of the document can control what Grouparoo has access to.

Create a Grouparoo source

You are ready to start pulling in data from Google Sheets. You do this through creating a Grouparoo source for each sheet. Note: there can be many sheets within a document. These words are hard.

You paste the URL in for the sheet to create a new source. It gives you a data preview so you know it's working.

How does Grouparoo know who is who in this sheet? You teach it through making a mapping.

In this case, my spreadsheet had a UserID and I could map them to the same concept that I've already been loading in from a MySQL source. If this was another sheet (like the result of a Google Form) and we had their email address, we could map it that.

Create a profile property rule and a schedule

For each column in the sheet, you now can pull that in and associate it with the profile. In Grouparoo, this is done via a profile property rule. So let's go ahead and make the the rule.

How often should Grouparoo check that sheet for new data? You can tell it by making a schedule.

Now, you have everything you need. It's on auto-pilot from here on out. It will check the Google Sheet every hour and update all the profiles with the data.

Results

When it runs, you can then see the results in each profile.

And you can also use it to make groups.

Because of the schedule, it will always be up to date. This data can be sent to destinations like Mailchimp.

Implementation Details

I researched for a quite a bit to figure out the best way to connect to Google Drive. Service Accounts seemed to be the best option. I like that the owner of the document can choose to share it. I also didn't want to have multiple people have to OAuth.

The Google API for this stuff is probably very powerful, but it seemed quite complicated just to read these sheets. The google-spreadsheet library really simplified things.