Turn your big data into insights using Google Sheets and Slides

1. From big data analysis to slide presentation

There are many tools for data scientists to perform big data analyses, but in the end analysts still have to justify results to management. Lots of numbers on paper or in a database is hardly presentable to key stakeholders. This intermediate Google Apps Script codelab uses 2 Google developer platforms (Google Workspace and Google Cloud Console) to help you automate that final stretch.

Google Cloud's developer tools let you perform the deep data analysis. You can then take the results, insert them into a spreadsheet, and generate a slide presentation with the data. This offers a more suitable way to deliver data to management. This codelab covers Cloud Console's BigQuery API (as an Apps Script advanced service) and the built-in Apps Script services for Google Sheets and Google Slides.

Motivation

The sample app in this codelab was inspired by these other code samples:

The Google Apps Script BigQuery Service sample app that's open-sourced on GitHub.
The sample app featured in the Generating slides from spreadsheet data developer video and published in this blog post.
The sample app featured in the Google Slides API codelab.

While the Slides API codelab sample app also features BigQuery and Slides, it differs from this codelab's sample app in several ways:

Their Node.js app vs. our Apps Script app.
They use REST APIs while we use Apps Script services.
They use Google Drive but not Google Sheets whereas this app uses Sheets but not Drive.

For this codelab, we wanted to bring multiple technologies together into a single app while showcasing features and APIs from across Google Cloud in a way that resembles a real-life use case. The goal is to inspire you to use your imagination and consider using both Cloud Console and Google Workspace to solve challenging problems for your organization and your customers.

What you'll learn

How to use Google Apps Script with multiple Google services
How to use Google BigQuery to analyze big data
How to create a Google Sheet and insert data into it
How to create a chart in Sheets
How to transfer data and charts from Sheets to a Google Slides presentation

What you'll need

A web browser with access to the internet
A Google Account (Google Workspace accounts might require administrator approval)
Basic familiarity with Google Sheets
Ability to read Sheets A1 Notation
Basic JavaScript skills
Knowledge of Apps Script development is helpful but not required

2. Survey

How will you use this codelab/tutorial?

Read through it for informational purposes, possibly passing it on to technical colleagues

Go through it as far as I can and try as many of the exercises as I can

Come hell or high water, I'm going to complete the entire codelab

How would rate your experience with Google Workspace developer tools & APIs?

Novice

Intermediate

Proficient

How would rate your experience with Apps Script specifically?

Novice

Intermediate

Proficient

How would rate your experience with the Cloud Console developer tools & APIs?

Novice

Intermediate

Proficient

3. Overview

Now that you know what this codelab is about, here's what you're going to do:

Take an existing Apps Script-BigQuery sample and get it working.
From the sample, learn how to send a query to BigQuery and get the results.
Create a Google Sheet and insert the results from BigQuery into it.
Modify the code to slightly alter the data returned and inserted into the Sheet.
Use the Sheets service in Apps Script to create a chart for the BigQuery data.
Use the Slides service to create a Google Slides presentation.
Add a title and subtitle to the default title slide.
Create a slide with a data table and export the Sheet's data cells into it.
Create another slide and insert the spreadsheet chart into it.

Let's get started with some background information on Apps Script, BigQuery, Sheets, and Slides.

Google Apps Script and BigQuery

Google Apps Script is a Google Workspace development platform operating at a higher level than Google REST APIs. It's a serverless development and application hosting environment that's accessible to all developer skill levels. Essentially, Apps Script is a serverless JavaScript runtime for Google Workspace automation, extension, and integration.

It uses server-side JavaScript, similar to Node.js, but focuses on tight integration with Google Workspace and other Google services rather than fast, asynchronous event-driven application hosting. It also features a development environment that might be different from what you're used to. With Apps Script, you:

Develop scripts using a browser-based code editor but can also develop locally when using clasp, the command-line deployment tool for Apps Script.
Write code in a specialized version of JavaScript customized to access Google Workspace and other Google or external services (using Apps Script's URL Fetch or JDBC services).
Can avoid writing authorization code as Apps Script handles it for you.
Don't have to host your app—it lives and runs on Google's servers in the cloud.

Apps Script interfaces with other Google technologies in 2 different ways:

As a built-in service
As an advanced service

A built-in service has high-level methods for interacting with user data, other Google systems, and external systems. An advanced service is essentially a thin wrapper around a Google Workspace API or Google REST API. Advanced services provide full coverage of the REST API and can often do more than built-in services, but they require more code complexity (while still being easier to use than the full REST API). Advanced services must also be turned on for a script project before using them.

When possible, developers should use a built-in service because they're easier to use and they do more than advanced services. However, some Google APIs don't have built-in services, so an advanced service might be the only option. For example, Google BigQuery has no built-in service, but the BigQuery Service does exist. The BigQuery service is a Cloud Console service that lets you use the Google BigQuery API to perform queries on large data corpuses (for example, multiple terabytes) yet it can still provide results in seconds.

Access Sheets & Slides from Apps Script

Unlike BigQuery, both Sheets and Slides have built-in services. They also have advanced services to access features found only in the API. View the docs for both the built-in Sheets and Slides services before jumping into the code. Note there's also docs for the advanced services of both Sheets and Slides.

4. Task 1: Run BigQuery and log results to Sheets

Introduction

We're going to do a large portion of this codelab with this first task. In fact, once you're through, you'll be about halfway done with the entire codelab. Broken down into several subsections, you'll:

Create both a Google Apps Script and Cloud Console project.
Turn on access to the BigQuery advanced service.
Open the script editor and enter the application source code.
Navigate the app authorization process (OAuth2).
Run the application that sends a request to BigQuery.
Review the new Google Sheet created using the BigQuery results.

Setup

To create an Apps Script project, go to script.google.com and click New project.
To rename your Apps Script project, click Untitled project, enter a title for your project, and click Rename.

Next, you'll need to create a Cloud Console project to query data in BigQuery.

To create a Cloud Console project, use this shortcut link to create a project, give the project a name, and click Create.

When project creation is complete, a notification appears on the page. Make sure your new project is selected in the projects list at the top of the page.
Click Menu and go to APIs & Services > OAuth consent screen (direct link).
Click Internal > Create to build an app for Google Workspace users within your organization.
In the App name field, enter "Big Data Codelab".
Enter contact emails for the User support and Developer contact information fields.
Click Save and continue > Save and continue.
Click More on the navigation bar and select Project settings (direct link).
Copy the value listed under Project number. (A separate Project ID field is used later in the codelab.)

Next, you'll connect your Apps Script project to the Cloud Console project.

Switch to the App Script editor and click Project Settings .
Under Google Cloud Platform (GCP) Project, click Change project.
Enter the project number and click Set project.
Next, click Editor to begin adding the BigQuery advanced service.
Beside Services, click Add a service .
On the Add a service dialog, select BigQuery API and click Add.

The final step is to turn on the BigQuery API on the Cloud Console.

To do this, switch to the Cloud Console and click APIs & Services > Dashboard. (Make sure you're still on the same project you created in Step 3.)
Click Enable APIs and Services.
Search for "big query", select the BigQuery API (not the BigQuery Data Transfer API), and click Enable to turn it on.

You're now ready to enter the application code, go through the authorization process, and get the first iteration of this application working.

Upload the application and run it

In the script editor, replace the default myFunction() code block with the following code:

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

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

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  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;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}

Click Save .
Beside Code.gs, click More > Rename. Change the title from Code.gs to bq-sheets-slides.js.
Next, let's review the code that queries BigQuery and writes the results to a Google Sheet. You can view it near the top of runQuery():

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10

This query looks through Shakespeare's works, part of BigQuery's public dataset, and produces the top 10 most frequently appearing words in all his works, sorted in descending order of popularity. You get an idea of how useful BigQuery can be when you imagine how much work it would be to do this compilation by hand.

The function also declares a PROJECT_ID variable that requires a valid Cloud Console project ID. The if statement under the variable is there to prevent the application from proceeding without the project ID.

Switch to your Cloud Console project, click More on the navigation bar, and select Project settings.
Copy the value listed under Project ID.
Switch back to the App Script editor, locate the PROJECT_ID variable in bq-sheets-slides.js, and add the value.
Click Save > Run.
Click Review permissions to continue.

Once the script starts running, the built-in execution log opens and logs script actions in real time.

Once the execution log reads "Execution completed", go to your Google Drive (drive.google.com) and find the Google Sheet named "Most common words in all of Shakespeare's works" (or the name you assigned to the QUERY_NAME variable, if you updated it):
Open the spreadsheet to view the 10 most common words and their total counts sorted in descending order:

Task 1 summary

To review, you ran some code that queried all of Shakespeare's works looking at every word in every play. It counted the words and sorted them in descending order of appearance. You also used the Apps Script built-in service for Google Sheets to display this data.

The code you used for bq-sheets-slides.js can also be found in the step1 folder of this codelab's GitHub repository at github.com/googlecodelabs/bigquery-sheets-slides. The code was inspired by this original example in the BigQuery advanced services page that ran a slightly different query retrieving the most popular words with 10 or more characters used by Shakespeare. You can also see an example in its GitHub repo.

If you're interested in other queries you can build with Shakespeare's works, or other public data tables, visit How to Query the BigQuery Sample Tables and this GitHub repo.

You can also run queries using the BigQuery page on the Cloud Console before running them in Apps Script. To find it, click Menu and go to BigQuery UI > SQL workspace (direct link). For example, here's how our query appears on the BigQuery graphical interface:

BigQueryUI

5. Task 2: Create a chart in Google Sheets

The purpose of runQuery() is to use BigQuery and to send its data results to a Google Sheet. Next we need to make a chart using the data. Let's make a new function called createColumnChart() that calls Sheets' newChart() method.

In the Apps Script editor, add the createColumnChart() function to bq-sheets-slides.js after runQuery(). The code gets the sheet and requests a columnar chart with all the data. The data range starts at cell A2 since the first row contains the column headers.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

The createColumnChart() function requires a spreadsheet object parameter, so we need to update runQuery() to return a spreadsheet object we can pass to createColumnChart(). At the end of runQuery(), return the spreadsheet object after logging the successful creation of the sheet:

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

Create a createBigQueryPresentation() function to call both runQuery() and createColumnChart(). Logically separating the BigQuery and chart creation functionality is best practice:

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

You took 2 important steps above: returning the spreadsheet object and creating the entry function. To make runQuery() more usable, we need to move the log line from runQuery() to createBigQueryPresentation(). Your method should now look like this:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

With the above changes (except for PROJECT_ID), your bq-sheets-slides.js should now look like the following. This code is also found in step2 of the GitHub repo.

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

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

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  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;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

In the script editor, save your script project. Then select createBigQueryPresentation from the functions list and click Run. After it completes, another spreadsheet is created in your Google Drive, but this time a chart is included in the sheet next to the data:

Sheet with chart

6. Task 3: Put the results data into a slide deck

The final part of the codelab involves creating a Google Slides presentation, adding the title and subtitle to the title slide, and then building slides for the data cells and the chart.

In the Apps Script editor, add the createSlidePresentation() function to bq-sheets-slides.js after createColumnChart(). All the work on the slide deck takes place in this function. Let's start with the creation of a slide deck and then add a title and subtitle to the default title slide.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

The next step in createSlidePresentation() is to import the cell data from the Google Sheet into our new slide deck. Add this code snippet to the function:

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

The final step in createSlidePresentation() is to add one more slide, import the chart from our spreadsheet, and return the Presentation object. Add this code snippet to the function:

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

Now that our function is complete, look again at its signature. The createSlidePresentation() requires both spreadsheet and chart object parameters. We've already adjusted runQuery() to return the Spreadsheet object but we need to make a similar change to createColumnChart() so it returns a chart object (EmbeddedChart). Return to createColumnChart() and add the following code snippet to the end of the function:

  // NEW: Return the chart object for later use.
  return chart;
}

Since createColumnChart() now returns a chart object, we need to save the chart to a variable. We then pass both the spreadsheet and the chart variables to createSlidePresentation(). As well, since we log the URL of the newly created spreadsheet, let's also log the URL of the new slide presentation. Update your createBigQueryPresentation() so it looks like this:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}

Save and run createBigQueryPresentation() again. Before it executes though, your app needs one more set of permissions from your user to view and manage Google Slides presentations. Once you review and allow these permissions, it will run as before.
Now, in addition to the spreadsheet that's created, you should also get a new Slides presentation with 3 slides (title, data table, data chart), as shown below:

7. Conclusion

Congratulations, you've created an application that uses both sides of Google Cloud. It performs a Google BigQuery request that queries one of its public datasets, creates a Google Sheets spreadsheet to store the results, adds a chart based on the data, and finally creates a Google Slides presentation featuring the data and chart results from the spreadsheet.

These steps are what you did technically. Broadly speaking, you went from big data analysis to a result you can present to stakeholders—all automated using code. We hope this sample inspires you to customize it for your own projects. At the conclusion of this codelab, we'll provide some suggestions on how you can further enhance this sample app.

With the changes from the final task (except for PROJECT_ID), your bq-sheets-slides.js should now look like the following:

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

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

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  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;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
  
  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

This code sample can also be found in the final folder in the GitHub repo.

8. Additional resources

Below are more resources to help you dig deeper into the material covered in this codelab and to explore other ways of accessing Google developer tools programmatically.

Resources for this application

Documentation

Videos

News & updates

Google Cloud Platform blog
Google Cloud Data Analytics blog
Google Developers blog
Twitter: Google Developers (@googledevs)
Google Workspace Developers blog
Twitter: Google Workspace Developers (@workspacedevs)

Other codelabs

Introductory

[Google Sheets] Fundamentals of Apps Script with Google Sheets
[REST APIs] Use Google Workspace & Google APIs to access files & folders in your Google Drive

Intermediate

[Apps Script] CLASP Apps Script command-line tool
[Apps Script] Google Workspace Add-ons for Gmail
[Apps Script] Custom bots for Hangouts Chat
[REST APIs] Use Google Sheets as your application's reporting tool
[REST APIs] Generate Google Slides presentations using the BigQuery API

9. The next step: code challenges

Listed below are different ways you can enhance or augment the sample we built in this codelab. This list isn't exhaustive but it provides some inspirational ideas on how you can take the next step.

Application—Don't want to be limited to using JavaScript or by the restrictions imposed by Apps Script? Port this application to your favorite programming language that uses the REST APIs for Google BigQuery, Sheets, and Slides.
BigQuery—Experiment with a different query for the Shakespeare dataset that interests you. Another sample query can be found in the original Apps Script BigQuery sample app.
BigQuery—Experiment with some of BigQuery's other public datasets to find one that's more meaningful to you.
BigQuery—Earlier, we mentioned other queries you can build with Shakespeare's works, or other public data tables. They can be found on this web page and this GitHub repo.
Sheets—Experiment with other chart types in the Chart Gallery.
Sheets & BigQuery—Use your own large spreadsheet dataset. In 2016, the BigQuery team introduced a feature to allow developers to use a Sheet as the data source. For more information, go to (Google BigQuery integrates with Google Drive.
Slides—Add other slides to the generated presentation, such as images or other assets tied to your big data analysis. Here's the reference documentation for the Slides built-in service.
Google Workspace—Use other Google Workspace or Google built-in services from Apps Script. For example, Gmail, Calendar, Docs, Drive, Maps, Analytics, YouTube, etc., as well as other advanced services. For more information, go to the reference overview for both built-in and advanced services.