Import Google Sheets

How to bring data from a Google Sheet into Count using Python or BigQuery

There are several methods available to import Google Sheets into Count, in order of increasing security (and complexity):

• If the sheet is public - use the pandas read_csv() method

• If the sheet is private but will only be accessed in Count by people with permission to access it anyway - use the gspread Python library

• If the sheet is private and security is important - import the sheet into BigQuery

Option 1 - pandas.read_csv()

This is the simplest option, but only works if the sheet is public.

Step 1 - Make the sheet public

Head to your Sheet and click the Share menu in the upper-right. Under General access, enable the option for Anyone with the link:

Enable link sharing in Google Sheets

Step 2 - Get the sheet key

Go to your Google Sheet and check the URL - it should look like:

https://docs.google.com/spreadsheets/d/SOME_LONG_KEY/edit#gid=0

Make a note of the long string of text after /d/ - this is the key for your sheet.

Step 3 - Read the sheet from Count

Head to Count and create a new Python cell, and insert:

import pandas as pd

key = 'SOME_LONG_KEY'
sheet_name = 'SHEET_NAME'
csv_url = f'https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}'

pd.read_csv(csv_url)

where you should replace SOME_LONG_KEY and SHEET_NAME with the appropriate values for your Sheet.

Option 2 - the gspread package

gspread is a Python package for interacting with Google Sheets, and you can use it in Count to access the the contents of your sheets. This means that access to the Sheet is controlled by credentials that you create, but those credentials will be visible to anyone that can view the canvas, so care must be taken when using this method. If security is important, use the third option below.

Step 1 - Create a service account

If you haven't enabled the Google Drive and Google Sheets API, head to the GCP APIs dashboard , select Library and search for the Google Drive and Google Sheets APIs.

Once enabled, head back to the APIs dashboard and select Credentials > Create credentials > Service account. Create a new service account and download a key in JSON format.

Step 2 - Invite the service account to your Sheet

Head to your Sheet and click the Share menu in the upper-right. Under People with access, ensure that you have invited the email address for your service account.

Paste in the email address for your service account

Step 3 - Access the sheet data in Count

In Count, create a new Python cell and enter your service account details:

import gspread
import pandas as pd

credentials = {
  'type': 'service_account',
  'project_id': '...',
  'private_key_id': '...',
  'private_key': '...',
  'client_email': '...',
  'client_id': '...',
  'auth_uri': '...',
  'token_uri': '...',
  'auth_provider_x509_cert_url': '...',
  'client_x509_cert_url': '...',
  'universe_domain': '...'
}

gs = gspread.service_account_from_dict(credentials)

sheet = # gs.open('SHEET_NAME') TODO - use the correct sheet name
worksheet = # sheet.sheet1 TODO - use the correct worksheet name

pd.DataFrame.from_records(worksheet.get_all_records())

Option 3 - BigQuery

This option is the most complex to implement, but it has the benefits of being completely secure compared to the alternative methods.

Step 1 - Import your Google Sheet to BigQuery as a table

In your BigQuery editor, select the ellipsis next to a dataset and select Create table:

Fill in the details required to create a table from Google Sheets

If your Google Sheet has a header row, remember under to select to skip 1 header row under Advanced options.

Step 2 - Create a new view

For example, by executing the following SQL query in your editor:

create table [new table] as (select * from [gsheet connected table])

Step 3 - Set up a scheduled query

If you'd like the data you use in Count to be up to date, set up a scheduled query at your desired interval (hourly is the most frequent).

This determines how frequently data from your Google Sheet is refreshed in Count. Be sure that the refresh frequency aligns with how often you expect edits to occur.

For more information on scheduled queries in BigQuery, check out this page.

Step 4 - Refresh your BigQuery schema in Count

If you haven't yet connected BigQuery to Count, first add your BigQuery connection and your Google Sheet should appear.

Otherwise, refresh your schema to see your Google Sheet in your list of tables.