What is BigQuery?
BigQuery is Google’s solution to enterprise-level data storage. It is a cloud-based service that offers rapid SQL querying for big data at an affordable price. Because it is a Google product it is reliable, secure, and always improving. With BigQuery, large companies no longer have to manage their data warehouses with expensive, on-site hardware. BigQuery runs and backs up on Google’s infrastructure. Google BigQuery allows users to take full advantage of Google product infrastructure. Today we are breaking down Google’s new BigQuery connector for Google Sheets, and how you can use the free tool we developed specifically for automating the data transfer!
New Google Sheets Data Connector
Google has recently launched a new data connector for BigQuery and Google Sheets users! This is great news for data analysts who want to improve and automate the ease in which they manage data. This solution is designed for big data practitioners who want to use Sheets to crunch big numbers—with this connector you can scan up to 1TB of BigQuery data to extract up to 10,000 rows of data into one sheet through standard SQL. This allows huge amounts of information to be analyzed in an intuitive and collaborative environment. Users who need to process even more data into a spreadsheet can follow our tutorial on how to get more than 10,000 rows using Sheetgo.
Limitations
As with all newly released tools, there are some limitations. Although we can be patient while Google improves this tool, it is good to be aware of what we can and cannot do.
Mobile: Currently, this tool is not mobile friendly. Although, you can view your results in mobile. So, you will need to run your queries on a computer but can shared results can be viewed by and commented on team members on mobile devices.
Automation: There are not currently automation features with this tool. We hope that this changes soon, but in the meantime, one of our Sheetgo engineers has created a free Google Apps Script that you can use to automate your process.
High-volumes: As mentioned before, although this connector supports HUGE data queries at a remarkably fast speed, if you want more than 10,000 rows in a single spreadsheet, you will have to use another method: how to get more than 10,000 rows.
Get Started Using The BigQuery Data Connector
Step 1.
To run the Data Connector click Data → Data connectors → BigQuery.
Step 2.
A window will open up in your Sheet. Select the billing project you want to work on and click Write Query.
Step 3.
Using the parameter is not a required step, but comes in handy if you need to add spreadsheet value to the query. Click on Add to add Parameters.
Step 4.
The parameter name (which is used as a reference) is related to the cell address in the spreadsheet. Give your parameter a unique name for you to remember it easily and choose a cell for reference.
Step 5.
In this example, “
Step 6.
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.
Step 7.
In case of writing a Query with an error, the BigQuery query editor automatically detects the errors. This is an example of how it detects the spelling error “
Step 8.
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.
Step 9.
If you look at the 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. However, for you to have updated data, open the spreadsheet, and in this tab, press Refresh.
– To learn how you can automate this with Google Apps Script, click to read about it in our blog post How to Automatically Get Data From BigQuery to Google Sheets.
Now that you have successfully created links between BigQuery and your Google Sheets, it’s time to put that data to work! Make sure to learn more about Google’s BigQuery in the following blog post: When does it make sense to use Google BigQuery?.
