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()
methodIf the sheet is private but will only be accessed in Count by people with permission to access it anyway - use the
gspread
Python libraryIf the sheet is private and security is important - import the sheet into BigQuery
Option 1 - pandas.read_csv()
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:
Step 2 - Get the sheet key
Go to your Google Sheet and check the URL - it should look like:
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:
where you should replace SOME_LONG_KEY
and SHEET_NAME
with the appropriate values for your Sheet.
Option 2 - the gspread
package
gspread
packagegspread
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.
Step 3 - Access the sheet data in Count
In Count, create a new Python cell and enter your service account details:
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.
Last updated