Introduction

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 hello@count.co!

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:

  1. A project (quick guide)
  2. A dataset (quick guide)
  3. Permission to create a table (quick guide)
  4. 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:

  1. Make sure the dataset is selected in the left hand data menu
  2. Click "+ Add a Table" in the main console

This will bring up a Create Table window, where you'll specify the following:

Source

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

Destination

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)

Schema

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.

Advanced Settings

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)

Advanced options

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.

Did this answer your question?