Database Reference
In-Depth Information
Querying BigQuery from a Spreadsheet
Spreadsheets allow you to generate charts and visualizations of your data,
and enable advanced data manipulation features like pivot tables. However,
spreadsheets aren't designed for Big Data—they tend to not scale well and
become difficult to manage when they get too large. That said, the world
seems to run on spreadsheets, and many data analysts want to use a
spreadsheet to access their large datasets. To squeeze the large data sets
into a small spreadsheet, the usual mechanism is to run a query over the
raw data to pre-aggregate it into something more easily manipulated in a
spreadsheet.
BigQuery provides two different mechanisms for running queries on
spreadsheets, depending on what software you use. If you use Google
Spreadsheets, you can use the Apps Script language to script your access
to BigQuery. If you use Microsoft Excel, you can use the BigQuery Excel
Connector to run your queries. The former is a richer interface that gives
you a lot of control over how you run your queries. The latter is a simpler
mechanism but, due to the limitations of sending HTTP requests in Excel,
lacks some of the bells and whistles of the Apps Script version.
BigQuery Queries in Google Spreadsheets (Apps Script)
Apps Script is a programming language based on JavaScript that allows
you to extend Google Apps to talk to outside services. You can make HTTP
requests, perform a mail merge, and run queries on BigQuery. You can add
buttons and menu items, and generally customize Google's apps to do things
that you wish they could do but don't already.
The main use case for the BigQuery integration in Apps Script is to run
queries in Google Spreadsheets. However, you also could use them to run
an import job periodically from Google Cloud Storage. On the BigQuery
team, we use Apps Script to automate a number of query tasks. For example,
when we get a customer who reports a problem in a certain job ID, to find
information about the job, we need to also have the project ID. We can use
Apps Script to make a BigQuery query into our metadata table to match the
job ID with the project. This is a relatively simple operation, but it saves a
lot of typing.
