What is the BigQuery Data Connector for Google Sheets?
BigQuery connectors allow users to integrate BigQuery with other platforms—either data sources or analytics tools. Google recently launched a BigQuery connector for Google Sheets which allows greater accessibility to your data. Large datasets can be intimidating for non-experts within your organization, and not all users need access to your full range of data. For big data practitioners, it is better to free up smaller amounts of data and distribute it to the members of an organization who need it, rather than requiring those who are less tech-savvy to query the system themselves. Google Sheets is intuitive and familiar for any users who are used to working with spreadsheets. Learn more about the BiqQuery Data Connector in our first post in this series.
How to automate this new feature
1. Use Our Google Apps Script
Here at Sheetgo, we are all about automation and we love providing you with solutions—so we created a tool ourselves to automatically update the BigQuery data in your Sheets! We made a script for you to install in your Google Sheets so that you can get data from BigQuery to Google Sheets automatically! Read on for a tutorial on how to quickly set up the script so you can get started on more important tasks!
Step 1: Make a Copy of the Script
Open the template by clicking here. Click ‘File’ → ‘Make a Copy’ to get your own copy of this script, it will automatically save to your Google Drive. Be sure to make a copy of the template rather than requesting editing access.
Step 2: Set up the Script
Your copy of the script will open automatically in a new tab. Click ‘Tools’ → ‘Script Editor’. A new tab will open in your browser. You then need to complete two steps: enable the BigQuery API (2.1) and get the Project ID (2.2).
Step 2.1: Enable the BigQuery API
To enable the Big Query API, select ‘Resources’ → ‘Advanced Google Services’. A new window will pop up, make sure the Big Query API is turned On.
Step 2.2: Get the Project ID
To get the Project ID, follow these steps. The screenshots below show detailed steps.
- Click the hyperlink ‘Google Cloud Platform API Dashboard’.
- Click the ‘ENABLE APIS AND SERVICES’ button.
- Type BigQuery into the search bar and click BigQuery API.
- Click ‘Enable’
- In the browser URL copy the text project-id-##################
- Go back to the BigQuery Queries tab in your browser and paste that text into the script code after Project ID. (See screenshot below)
- You can close the tab
Step 3: Automate the data transfer
To automate, inside the script, click on the clock icon.
Then, click on ‘>Add Trigger’.
In the following screen, you can set it up the way you prefer.
Step 4: Run the Script
Go back to your spreadsheet. Click ‘Sheetgo’ → ‘Run Query’ → ‘Continue’ → ‘Login’.
That’s it! The script has been created! Now you can automatically import data from BigQuery into your Google Sheets spreadsheets!
2. Follow Google’s approach
First, record a macro to refresh your data. Open the spreadsheet that contains the data connected to the BigQuery. Then record a macro from a different tab than the one you want to refresh. After starting to record, switch to the tab you want to refresh. At the bottom left, click ‘Refresh‘ and after that, click ‘Save‘.
Second, schedule your macro. Go to the Menu ‘Tools‘ > ‘Script Editor‘. Inside the Script Editor, go to ‘Edit‘ > ‘Current project’s triggers‘. On the bottom right, click on ‘Add triggers‘ and you can select your desired options. To finish, click on ‘Save‘. That’s it, you are all set! Leave a comment down below if you have any questions/remarks. 🙂
Make sure to learn more about Google´s BigQuery in the following blog posts:
“BigQuery Data Connector for Google Sheets: How to Get More Than 10,000 Rows”
