We highly recommend you to read our blog Analyze BigQuery Data Within Google Sheets before reading this one.
The New BigQuery Google Sheets Connector
If you are using the new BigQuery Google Sheets Connector, you are probably loving how you can extract your enterprise data from BigQuery right from within the Google Sheets interface. Using Google Sheets to extract big data is an awesome way for users to have access to valuable business information from within an intuitive and collaborative platform. However, because this data connector is still relatively new, not all of the features you would expect have been launched, and there are still some limitations. We know Google is working hard to develop this tool to be perfect for its users, but in the meantime, users need some workaround solutions. Don’t worry, we here at Sheetgo have you covered.
What are the limitations?
Two of the main issues new users have encountered is the lack of an automation feature and the fact that Google Sheets has a limit on how many rows you can get at once. You might find it a little annoying to have to manually refresh your data. If you have not already automated your system for pulling data from BigQuery using our free Google Apps Script, be sure to check out our tutorial on how to set it up quickly.
Today we are showing you how to use Sheetgo, to get more than 10,000 rows. Sheetgo is a Google Sheets add-on that allows you to automatically connect spreadsheets so that you can improve collaboration and workflows. We’ve added some useful enhancements to the Sheetgo interface, including the ability to filter, consolidate, and append the data that you are integrating with other Spreadsheets.
Use Sheetgo to get more than 10,000 rows
Extracting the data into the spreadsheets
- To run the Data Connector click Data → Data connectors → BigQuery
2. A window will open up in your Sheet. Select the billing project you want to work on and click
3. Write Query.
4. In the following screenshot, you can find the list of all tables and structures of each table that you have in your BigQuery account. Think of it as your Table of Content.
5. Once the query is valid, Google tells you so at the bottom. So, make sure that the query is valid before clicking on INSERT RESULTS.
6. If you look at the following screenshot, you will notice a couple of things:
- The icon in the tab indicates that this is not your regular Google Sheets tab. This tab is different as its sole purpose is to show the BigQuery results -which you cannot edit!
- The special menu at the bottom, inside the three dots, allows you to control and edit your BiqQuery connection.
- This connection cannot be automated from here so to update manually, click “Refresh”. Now, let´s automate this in the following steps with our workaround.
Consolidating BigQuery´s
Create one Google Sheets per BigQuery and have them organized in a new Google Drive Folder. You can see an example in the following screenshot.
Next, inside Google Sheets, open the Sheetgo add-on (if you haven’t installed it yet, click here to install Sheetgo). Inside the Sheetgo add-on, click on the green + button > SELECT FILES and search for the folder you’ve just created (“BigQuery Project 1” in the example). Once inside the folder, click on SELECT THIS FOLDER and choose the tab that contains the BigQuery´s, as can be seen in the example below.
Click on SAVE CONNECTION to let Sheetgo create a new tab with your the BigQuery´s.
To automate this consolidation, go to the Sheetgo add-on and click on the three dots next to the connection and click on EDIT CONNECTION. In Settings, click enable automatic updates and choose your frequency, followed by saving the connection.
Make sure to learn more about Google’s BigQuery and how to solve cell limits in the following blog posts.
How to Analyze BigQuery Data Within Google Sheets
How to Automatically Get Data From BigQuery to Google Sheets
