• 131
• 255
• 14

Creating a table in BigQuery with your Google Sheet data

by bernt & torsten

Did you know that you can link your Google Sheet with a BigQuery table and that any chances that you make in your Google Sheet are automatically updated in the BigQuery table?

If you read on, I will provide the instructions on how you can do this, first I will provide some of my views on why you would want to do this.

1. If you have a team that is collaborating on a Google Sheet and you want to build a report in Data Studio this would be a great option of having the latest updates available for the report.
2. Having the data in BigQuery will open up the Google services for your data, you may have a mobile app in which you want to make the data available as a graph or a table, you could use a Google Cloud Functions with an HTTP trigger, that queries the BigQuery and the trigger is triggered from your mobile app.
3. You have an idea of building a machine learning model of your dataset with the Google AutoML service, you can connect your BigQuery with the AutoML and over time you can train and build a model for predicting an outcome on your data.
4. Using BigQuery as your data warehouse, and make sure that Google Sheet data is part of the data warehouse.

There may be many other scenarios, it’s just your own imagination that may be the limitation in imagining ways of utilizing the different Google Services with your Google Sheet data.

Let get started here are the instructions on how to connect your Google Sheet with BigQuery, and make Google Sheets part of your data warehouse solution. I also will show how you can use Google Sheets to query a BigQuery table.

Connecting

How do you connect your Google Sheet with BigQuery is easy! Here’s how to do it

Create BigQuery Dataset

In the BigQuery Web UI, click on create a dataset

Create BigQuery Table

Create a table and choose the source like Google Drive

Google Sheet URL

Paste the URL for your Sheet into the location bar. Note: Make sure you copy the URL from the worksheet in Google Sheets that you want to make into a table in BigQuery.

Choose either CSV or Sheets as the format. Note: CSV format will allow you to check “Auto-detect Schema.” Sheets’ format will allow you to specify the column names and types.

Paste Google Sheet URL in Bigquery

Click create and if you have chosen autodetect schema your table will be created, if you have not selected autodetect you will have to define your table. If you choose autodetect make sure your Google Sheet first row is the column header.

Query BigQuery table

Now when your table has been created, you can check your new schema and just do a query to see if you have the data as you expected, in BigQuery do a select to get the data view.

SELECT * FROM `<your BigQuery table>` LIMIT 10

The query will show you the data as shown in your google sheet.

Now you can write queries in BigQuery on your dataset from Google Sheets, you can now join your table from Google Sheets with any other database to do advanced queries.

Latest data from the Google Sheet, Query the most up-to-date data

Unlike other external connections to BigQuery, with a Google Sheet linked to BigQuery, you can edit your Google sheet and the changes appear immediately in BigQuery without building a synchronise function.

Control share settings

Your data needs to stay protected in BigQuery and Google Sheet. There’s a way to bridge access between the two, to help you manage access for groups of users – just create a Google Group to serve as an access control group.

Here’s an example where I used the group called “global-data-group” using that group you can assign a Google Group access to the Google Sheet.

A note I put the Google Sheet in TeamDrive, that should not be an issue as the group having access should be the TeamDrive members. Share the BigQuery dataset with the “global-data-group” for BigQuery.

This gives the access to manage access to both the spreadsheet and the dataset.

Query a BigQuery table from a Google Sheet

Google Cloud Platform has many options on how you can use and manipulate data, it’s hard to know all the options that you have available to you. With API’s there are so many options available, it’s your own imagination on how you can put a data architecture together, maybe you need to just get a push for someone like me.

In this part, I’m going to show how you can use App Scripts to query a BigQuery database and display the results in Google Sheets.

Go to https://script.google.com/home

Go to Settings to enable Google Apps Script API

It’s good to know how an Apps Script project is defined within the Google Cloud Platform – please read this – https://developers.google.com/apps-script/guides/cloud-platform-projects

Now in the G Suite Developer Hub click NEW and select New Script

To run a script to query BigQuery and create a google sheet from that query, please replace the default details in the code I’m sharing below.

The scripts are using Advanced Google services and provide the means to use Google APIs (including but not limited to the G Suite product APIs) within Apps Script. Which means that the API needs to be enabled. More here – https://developers.google.com/apps-script/reference/

function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = '<your project id>';

  var request = {
    query: 'SELECT brand, brandid FROM <BigQuery Table Name> LIMIT 10;'
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    var spreadsheet = SpreadsheetApp.create('BiqQuery Results');
    var sheet = spreadsheet.getActiveSheet();

    // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);

    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
    }
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

    Logger.log('Results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

To enable BigQuery for your Apps script, you need to enable it by selecting Resources -> Advanced Google Services

At the bottom of the last step, in the highlighted text, you need to enable the BigQuery API in the Cloud Platform project, you can click the link that says Cloud Platform project and you will get to this screen. You need to enable BigQuery API.

That should be it, save your script file in the script editor, the first time you run you will get asked to authenticate with Google.

That is it, that should allow you to run the query, go to your google drive after you have run the query to find the Google Sheet where the data from the BigQuery query is stored.

Update

In a recent update to Google Sheets I found that in the Enterprise and Education edition, you are now able to query BigQuery directly, by selecting Data connectors – > Bigquery.

Depending on the edition you have G Suite, you can either use the script I provided or use the built-in BigQuery for sheets.

This is all for now, any questions you can comment below.