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 Google Sheets extractor 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 practical stop-gap measure when implementing a business process not yet supported by primary systems, such as Salesforce. 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 devised a method for our support team to grant free credits to our Freemium customers (who get 60 minutes per month for free on our free plan). While this process will eventually be implemented in Salesforce, for now, a spreadsheet gest the job done.

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 immediately or need to trigger an update manually? Sure, I could go to the Keboola project and run the flow manually, but not everyone who can add rows to the spreadsheet will have project admin rights. It would be much more convenient to push the data from the spreadsheet itself. If only there were a “run upload” button in the sheet itself… And 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, I confirmed it was not only possible but also surprisingly straightforward. Almost immediately, a script written by our Professional Services team members, Leo and David, popped up in our Slack feed. 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 are 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 the Components section, select the “Google Sheets” extractor and follow the on-screen prompts to authorize and choose your spreadsheet and tab(s).

Step 3: Set Up a Transformation (Skip if Not Needed)

If your data needs processing—whether it's simple aggregations or complex transformations—the transformation engine is your go-to tool. In the free account, you can work with SQL (powered by Snowflake) or Python.

Step 4: Set Up a Database (or Other) Writer

Choose the writer that fits your needs. This could be Snowflake or another database. If you don’t already have a destination database yet, the Snowflake writer can set one up for you. Alternatively, your destination could be a platform like Salesforce, or even MailChimp, as not all data tasks revolve solely around BI and analytics.

Step 5: Set Up the Flow

By this point, you should have set up three components in Keboola—or just two if you skipped the transformation step. A flow ties these components into a cohesive workflow. Create a new flow, and in the “Flow Builder” section, arrange the components in your desired sequence. Since the Google Sheets script will trigger this flow, there’s no need for a scheduled run—unless you also want it to run automatically each day.

Step 6: Copy the Script to Google Sheets

Now, switch between Keboola and your Google Sheets. Having them open side by side will make the process easier. In your spreadsheet, go to “Extensions,” then select “Apps Script” (this will open in a new tab). Name your script, copy the following code block, and paste it into the editor. Save your changes, return to your spreadsheet tab, and refresh the page. You’ll now see a new “Keboola” option in the menu.

Step 7: Authorize the Script

When the script runs for the first time, it will prompt you for the Orchestration URL and an API token. This is where you'll copy and paste information between Keboola and Google Sheets. Follow these steps:

1. Click the “Run Flow” option in the “Keboola” menu of your Google Sheets.
2. A dialog box will appear, 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 Google Sheets, paste the URL into the dialog box, and proceed.
4. When prompted for an API Token, go back to the Keboola tab and create a new token with the required privileges. Click the icon in the upper-right corner and select “Project Settings.” Then,
   navigate to the “API Tokens” tab and create a new token. You can choose one of the following options:
   Option 1: Create a token with full access
    This grants the token access to all components, including the flow, its variables, and the target  bucket or storage. This option simplifies the process and avoids potential permission issues.
   ‍ Option 2: Create a restricted token with specific permissions
    Ensure the restricted token has access to the flow, all its components, and any variables used.  Also, grant access to the target bucket or storage to prevent errors during execution.
5. Return to Google Sheets and enter the token into the dialog box.

Step 8: Done!

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

Give it a try by creating a free account.