Google Sheets is a popular way to share and store data across an organization. Analyzing the latest data in Count can help teams find insights and make decisions faster. Check out the step-by-step instructions below to see how to bring your Google Sheets data into Count.
If you run into any issues, just send us a message in the app, or email us at firstname.lastname@example.org!
Note: This tutorial is if you want to refresh your data daily. If you'd rather, you can always upload a CSV of your Google Sheet to Count.
What You'll Need:
This tutorial will use the Google Cloud Console to set-up a BigQuery dataset using your Google Sheets data. This means you will need to have:
- A project (quick guide)
- A dataset (quick guide)
- Permission to create a table (quick guide)
- URL for your google sheets table you want to bring in
Create a Table
Once you have your project and dataset, you can navigate to the main BigQuery interface. First, you'll need to create a new table in the dataset of your choosing. You can do that by:
- Make sure the dataset is selected in the left hand data menu
- Click "+ Add a Table" in the main console
This will bring up a Create Table window, where you'll specify the following:
Create table from: Drive
Driver URI: URL from google sheet (make sure it doesn't have the /edit... at the end!)
File format: Google Sheets
Project name: select your destination project
Dataset name: select the dataset name
Table name: Create your table name (I like to use a "gs" prefix so I know it's the google sheets table)
Currently, BigQuery doesn't auto-detect the schema (column names and types) for Google Sheet tables. Therefore, you'll need to list each column name and the type.
Note: These are the column names you want to have. They do not have to match the names in the file, just the order.
You can customize additional settings in this section.
If your column names are in the first row, make sure you skip the first row! [Header rows to skip: 1].
Otherwise, you will get an error for any non-string columns upon import.
Now you can click Create table to complete the step.
What kind of table is this?
You'll notice that it looks like your table is empty. It has a size 0 and there is no way to preview the data like you normally can in BigQuery. This is because BigQuery is storing a connection to your table rather than the data itself. Whenever you run a query, it will go pull the latest data from the spreadsheet.
Create a Scheduled Query
In order to bring this data into Count, we need to create a schedule query. A scheduled query is just a SQL query that is set to run at predefined times. The results of the queries can be appended or overwritten in other BigQuery tables.
1. Define your Query
SELECT * FROM `project_name.dataset_name.table_name`
This will select all of the data from your google sheets table.
2. Schedule a Query
Now we want to make sure this query runs at some frequency on it's own.
Select Schedule query to get started.
Details & schedule
First, give your scheduled query a name, and define how frequently you want it to update.
Destination for results
Select which dataset you want the new table to reside.
Create a new table name. (I like to use the "sq" prefix to I know it's the schedule query table)
I don't expect you'll have to use any of these, but for increased permissions or security, you can customize the Advanced options.
Click Schedule to save the scheduled query.
Import into Count
Now we can use a BigQuery integration to import our scheduled query table results.
Check out this guide to see how to set up your BigQuery integration.