How to sync your Google Sheets data into your data warehouse in just a few minutes

I can’t count the number of times I’ve used the phrase: “There’s always a spreadsheet…” When I give a demo of the Keboola platform, I often start with Google Sheets as the data source of choice. The data source connector Google Sheets is one of the most popular components on our platform because, let’s face it, no matter the project, there’s always a spreadsheet involved.

Even internally, we rely on 13 different Google Sheets as secondary data sources. Their uses vary widely from simple lists of our consultants, their current base offices, and time zones to our finance department’s rolling forecast, and many more in between. Our customers have found even more diverse uses, including setting targets, creating custom product hierarchies, and enriching various lists (like locations and personnel) with data not found in their databases.

Spreadsheets are also a useful stop-gap measure when you need to implement a business process not yet supported by primary systems, such as Salesforce, where it should eventually stay. This often happens when you’re still fine-tuning the process and want to iron out the kinks before fully committing to development or acquiring the software that will ultimately manage and provide this data. Just this week, we had to devise a method for our support team to grant free credits to our Freemium customers (you get 60 minutes per month for free on our free plan). Ultimately, this process will be implemented in Salesforce, but for now, a spreadsheet does the job.

Typically, we extract data from the spreadsheet on a schedule, running a flow that pulls data from Google Sheets. This approach works well, but what if you need a specific table updated right away or you need to trigger an update manually? I could go to the Keboola project and run the flow manually, but it’s clear that not everyone who can add rows to the spreadsheet will also have project admin rights. It would be more convenient to push the data from the spreadsheet itself. If only there were a “run upload” button in the sheet itself… But then it hit me – Google Sheets supports scripts, and triggering a flow in Keboola is merely an API call away, assuming you have the right token. So, how challenging could it be to set this up?

After a brief chat with Fisa, our field CTO and my partner in many hacks and crimes, not only did I get confirmation that this was doable, but I also found out that it wasn’t a particularly novel idea. Almost immediately, a script written by our Professional Services team members Leo and David popped up in our Slack thread. From there, it was just a matter of copy-pasting, and about three minutes later…

It worked perfectly!

Since you’ve read this far, I’m guessing you also work with spreadsheets and need to materialize their content into a data warehouse or a BI tool. Maybe you even encounter situations where some transformation or enrichment is required along the way.

I’ll share the script below and guide you through the steps to make this happen.

Step 1: Create a Free Keboola Project

Click here to get started with a free Keboola project.

Step 2: Connect your Google Spreadsheet

In Components, choose the “Google Sheets” data source connector and follow the on-screen prompts to authorize and select your spreadsheet and tab(s).

Step 3: Set up a transformation (Skip If Not Needed)

If your data needs processing – be it simple aggregations or something more complex – the transformation engine is where you should start. In the free account, you can use SQL (running on Snowflake) or Python.

Step 4: Set Up the Database (or Any Other) Data Destination Connector

Choose the appropriate data destination connector based on your needs. This could be Snowflake or another database. If you don’t have a destination database yet, the Snowflake data destination connector will set up one for you. Or, it could be Salesforce, or even MailChimp, as not all data tasks revolve solely around BI and analytics.

Step 5: Set Up the Flow

By now, you should have set up three components in Keboola (or just two if you skipped the transformation step). A flow will consolidate them into a workflow. Create a new flow and, in the “Flow Builder” section, arrange the components in the desired sequence. Since the Google Sheets script will initiate this flow, there’s no need for a scheduled run – unless you want it to run automatically each day.

Step 6: Copy the Script to Google Sheet

Now we’ll be switching between Keboola and your Google Sheet, so it’s helpful to have them open side by side. First, in your sheet, navigate to “Extensions” and then “Apps Script” (this will open in a new tab). Name your script, copy the following code block, and paste it there. Save it, then return to your spreadsheet tab and refresh it. You’ll see a new “Keboola” section in the menu.

Step 7: Authorize the Script

When it runs for the first time, the script asks you for the Orchestration URL and a token. This is where the copy-pasting between Keboola Connection and the Google Sheet will take place. Follow these steps:

1. Click the “Run Flow” option in your Google Sheets’ “Keboola” menu.
2. A dialog will pop up, requesting the flow URL. Switch to the browser tab with the Keboola platform, ensuring you’re on the page of your new flow. Copy the URL, which should look something like this: “https://connection.north-europe.azure.keboola.com/admin/projects/33/flows/67090”
3. Return to the Google Sheet and paste the entire URL into the dialog, then proceed.
4. When prompted for an API Token, go back to the Keboola tab. You’ll need to create a new token with limited privileges to authenticate the script. Click the icon in the upper right corner and select “Project Settings.” Then choose the “API Tokens” tab and create a new token. It’s good practice to limit the privileges of such tokens, so select “restricted access” and allow only “Flow” as the component. The token will be displayed upon saving; copy it. If you don’t copy it immediately, you can have the token sent to you via email later.
5. Finally, return to the Google Sheets tab and enter the token into the dialog.

Step 8: Done!

Google Sheets will save the entered URL and token for future use. If you need to change these settings, use the “Reset Settings” option in the Keboola menu to start fresh.

Try it yourself by creating a free account and connecting your data.