Creating a table in BigQuery with your Google Sheet data

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.

group settings

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.


Posted

in

, , ,

by

Comments

10 responses to “Creating a table in BigQuery with your Google Sheet data”

  1. Tom Avatar
    Tom

    I cannot get this to work…how do I get fresh Dara in big query from sheets?

    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 synchronis function.

    1. Torbjorn Zetterlund Avatar
      Torbjorn Zetterlund

      I’m not sure about your use case. You have another option that you can use, and that is connecting your Google Sheet in BigQUery directly. When you create a table in BigQuery you can choose a Google Sheet as your data source.

      1. TJ Avatar
        TJ

        As I update data in my sheet, I am not seeing the change in The Big Query table.

        1. Torbjorn Zetterlund Avatar
          Torbjorn Zetterlund

          There is another option that you can use, in BigQuery you can now connect your sheet directly. Goto BigQuery creates a dataset than add a table, the table option uses Google Sheet and add in your Google Sheet id. that is all you need to do and any changes in Google Sheet will update automatically BigQuery.

  2. Onur Avatar
    Onur

    When I want to update (manipulate) my table in Google Sheets, using BigQuery, I get en error ‘DML over table `test.googlesheetfile`is not supported’. For example I want to insert new values or update existing values with a query like following:

    UPDATE `project.dataset.table`
    SET Winner = ‘Usain Bolt’ WHERE competition = ‘100m’

    I use standard SQL, not legacy. How can I overcome this? Or is there another way to manipulate my tables in Google Sheet

    1. Torbjorn Zetterlund Avatar
      Torbjorn Zetterlund

      You do not need to update the BigQuery table by using a SQL statement like
      UPDATE `project.dataset.table`
      SET Winner = ‘Usain Bolt’ WHERE competition = ‘100m’
      You have all the data in your Google Sheet, you can just update the Google Sheet or to add new data, this will be automatically reflected in the BigQuery table.
      You can see it from the perspective that the Google Sheet is your UI to the BigQuery table.
      I hope this answered your question?
      Torbjorn

      1. Onur Avatar
        Onur

        Actually no. I have a Google Sheet document that everyone has access to, and it needs to be updated regularly (via inserting new data and/or updating existing records) using other datasets I have in BigQuery.

        For instance, in that small example I gave, imagine there are many competitions of which the records are kept in several datasets in BigQuery. In this scenario, I would prefer to have a scheduled query to update my table regularly, so that when people open that summary table in Google Sheets, they see the most up-to-date data.

  3. Onur Avatar
    Onur

    Actually no. I have a Google Sheet document that everyone has access to, and it needs to be updated regularly (via inserting new data and/or updating existing records) using other datasets I have in BigQuery.

    For instance, in that small example I gave, imagine there are many competitions of which the records are kept in several datasets in BigQuery. In this scenario, I would prefer to have a scheduled query to update my table regularly, so that when people open that summary table in Google Sheets, they see the most up-to-date data.

    1. Onur Avatar
      Onur

      Sorry for the duplicate reply, you can remove this one.

  4. Eric Avatar
    Eric

    Nice Job! Would help if your screen shot of the table create showed the options for Autodetect checked instead of having that in the text and that you have to check advanced options, skip header rows in order to have the column names match the sheet’s.

    One cool thing, you can add a field to the BigQuery schema on the BigQuery side, then do the same on the sheet and the new column gets updated! Very nice to play around with a table structure! And of course the two way communication is far better than just using the data connector starting from the sheet!

Leave a Reply

Your email address will not be published. Required fields are marked *