This is the most secure method for importing private Google Sheets into Count.
Steps:
Import Your Google Sheet to BigQuery:
In your BigQuery editor, select the ellipsis (...) next to your desired dataset and click Create table.
In the "Create table from" section, choose Google Drive.
Enter the URL of your Google Sheet.
Select the appropriate File format (usually Google Sheets).
Specify the Destination table details (dataset and table name).
Under Advanced options, if your sheet has a header row, check the box for "Skip header rows" and enter
1.Click Create table.
Create a New View (Optional but Recommended for Scheduled Updates):
In the BigQuery editor, write and run an SQL query to create a view (or a new table) from the Google Sheets connected table. For example: SQL
CREATE VIEW your_dataset.your_view AS ( SELECT * FROM your_dataset.your_google_sheets_table );
Replace
your_datasetand the table/view names with your actual names.
Set Up a Scheduled Query (For Automatic Updates):
In the BigQuery editor, click Schedule and then Create a scheduled query.
Configure the schedule according to how frequently you want your data in Count to be updated (e.g., hourly).
In the "Destination table" section, specify the BigQuery table or view you created in the previous step.
Click Create.
Connect BigQuery to Count (If Not Already Connected):
In Count, go to Data Sources and click Add new.
Select BigQuery and follow the prompts to connect your Google Cloud project.
Refresh Your BigQuery Schema in Count:
If you've already connected BigQuery, go to your BigQuery data source in Count and click the refresh icon to see your newly imported table or view in the list of available tables.
You can now access your Google Sheet data securely within your Count canvas through your BigQuery connection.