The Grouparoo Blog


Google Sheets Source

Tagged in Connections Engineering 
By Brian Leonard on 2020-04-15


Grouparoo has the ability to connect to a Google Sheet to bring data into its customer data platform (CDP). 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.

Creating a Google 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.

Naming a Google Service Account

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.

Making a Google Service Account key

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

JSON for a Google Service Account key

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.

Creating a Grouparoo app for Google Sheets

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

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

Google Sheets in the app list

Give access to the document

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

Give the Service Account access to the document

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.

Data preview of new source from Google Sheets

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

Data preview of new source from Google Sheets

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.

Add a profile property rule for the column

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.

Google Sheet source rule and schedule

Results

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

Profile has the data from the Google sheet

And you can also use it to make groups.

Groups can use the data from the Google sheet

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.


Stay up to date

We will let you know about our launch and new content.

Share this post