There are many tools out there for data scientists to perform big data analyses, but at the end of the day, don't you still have to justify those results to management? Lots of numbers on paper or in a database is hardly presentable to key stakeholders. This intermediate Google Apps Script codelab leverages a pair of Google's developer platforms, G Suite and Google Cloud Platform (GCP), to help you complete that final mile.

Google Cloud's developer tools let you perform the deep data analysis then take those results, put them into a spreadsheet, and generate a slide presentation with that data, providing a more suitable stage for the results to be delivered to management. This codelab covers the GCP's BigQuery API (as an Apps Script advanced service) and the built-in Apps Script services for Google Sheets and Google Slides.

Motivation/prior art

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

While the Slides API codelab sample app also features BigQuery and Slides, it differs from this

codelab's sample app in several ways:

For this codelab, we wanted to bring as much of the technologies together into a single app while showcasing features and APIs from across Google Cloud in a way that also more closely resembles a real-life use case. The goal is to inspire you to use your imagination and consider problems that allow you to leverage both GCP and G Suite to solve challenging problems for your organization or your customers.

What you'll learn

What you'll need

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 G Suite developer tools & APIs?

Novice Intermediate Proficient

How would rate your experience with Apps Script specifically?

Novice Intermediate Proficient

How would rate your experience with GCP developer tools & APIs?

Novice Intermediate Proficient

Now that you know what this codelab is about, what are you going to do exactly?

  1. Take an existing Apps Script-BigQuery sample and get it working
  2. From that sample, learn how to send a query to BigQuery and get its results
  3. Create a Google Sheet and populate the results from BigQuery into it
  4. Modify the code a bit to slightly alter the data returned and added to the Sheet
  5. Use the Sheets service in Apps Script to create a chart for the data from BigQuery
  6. Use the Slides service to create a new slide presentation
  7. Add a title and subtitle to the default title slide auto-created for all new slide decks
  8. Create a new slide with a data table, then import the Sheet's data cells into it
  9. Add another new slide and add the spreadsheet chart to 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 G Suite development platform that operates as a higher-level than using Google REST APIs. It is a serverless development and application hosting environment that's accessible to a large range of developer skill levels. In one sentence, "Apps Script is a serverless JavaScript runtime for G suite automation, extension, and integration."

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

NOTE: It is, for the most part, outside of the scope of this codelab to teach you Apps Script. There are plenty of online resources to help you with this. The official documentation features an overview with quickstarts, tutorials, and videos as well. And finally, don't forget about the Apps Script introductory codelab, which should be completed before starting this one.

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

A built-in service provides high-level methods that you can use to access to G Suite or Google product data, or other useful utility methods. An advanced service is merely a thin wrapper around a G Suite or Google REST API. Advanced services provide full coverage of the REST API and can often do more than the built-in services, but require more code complexity (while still being easier to use than the REST API itself).

Advanced services must also be enabled for a script project prior to using them. When possible, prefer a built-in service because they're easier to use and do more heavy-lifting than advanced services do. However, some Google APIs don't have built-in services, so an advanced service may be the only option. Google BigQuery is one example of this... no built-in service is available, but a BigQuery advanced service does exist. (Better than no service, right?) If you're new to BigQuery, it's a GCP service that lets you perform simple (or complex) queries on very large data corpuses, say on the order of multiple terabytes, yet still can provide results in seconds, not hours or days.

Accessing Google Sheets & Slides from Apps Script

Unlike BigQuery, which is only available as an Apps Script advanced service, both Google Sheets and Slides have built-in Apps Script services (as well as advanced services, for example, to access features only found in the API and not available built-in). Whenever possible, you'll likely going to choose any built-in service over an advanced equivalent as they provide higher-level constructs and convenience calls, simplifying development. There are docs for both the built-in Sheets service as well as the Slides service, so review those before jumping into the code.

Introduction

We're going to take a big bite out of this codelab with this first task. In fact, once you're done here, you'll be about halfway done with the entire codelab. Broken down into several subsections, you'll be doing all these things:

Setup

  1. Create a new Apps Script project by going to script.google.com . There are different G Suite product lines, and how you create a new project may differ depending on which version you're using. If you see a big +New button in the upper-left click it.

    If not, look for a hamburger menu icon in the upper-left corner and select +New script. For those of you who prefer the command-line, by using clasp, specifically the clasp create command. One other way create a new script project is to simply go to https://script.google.com/create.
  2. In any case, you should be dropped into the Apps Script code editor:
  3. Now that you have a new project, you need to enable the BigQuery advanced service, so pull down Resources -> Advanced Google Services and flip on the bit for the BigQuery API.
  4. A note at the bottom states, "These services must also be enabled in the Google API Console", so click on that link which opens another browser tab to the developer console or "devconsole" for short.
  5. At the devconsole, click the +Enable APIs and Services button at the top, search for "bigquery", select the BigQuery API (not the BigQuery Data Transfer API), and click enable to turn it on. Leave this browser tab open.
  6. Back in your code editor browser tab, you're still on the Advanced Google Services menu, so click OK to close the dialog, leaving you in the code editor. Click the project name at the top, and name it whatever you like, "BigQuery, Sheets, Slides demo", "final mile", or whatever suits your fancy.

Now you're ready to enter the application code, go through the authorization process, and get the first incarnation of this application working.

Upload the application and run it

  1. Copy the code in the box below and paste over everything in the code editor with it:
// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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());
}

Now save the file you just created but rename it from Code.gs to bq-sheets-slides.js. So what does this code do? We already told you that it queries BigQuery and writes the results into a new Google Sheet, but what is this query? You can see 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 data set, and produces the top 10 most frequently-appearing words in all his works, sorted in descending order of popularity. Imagine how (not) fun it would be to do this by hand, and you should have an inkling of an idea of how useful BigQuery is.

  1. We're almost but not quite ready to give this a try. As you see near the top of this code snippet, a valid project ID is required, so we need to add yours to the application code. To get this, go back to the browser window or tab on the devconsole.
  2. Up at the top to the left of your Google account avatar is the pulldown menu selector (). Click it and select Project settings. You'll see the project name, ID, and number. Copy the project ID and set the PROJECT_ID variable at the top of bq-sheets-slides.js to the value you got from the developer console. NOTE: If the menu selector gets sticky and inoperable, reload the page.
  3. The if statement is there to prevent the application from going any further without a project ID in place. Once you've added yours, save the file, and run your code by going up to the menubar and selecting Run > Run function > runQuery, click Review Permissions dialog, This app is unverified, and below is an animated GIF (for another app) illustrating the next few steps:
  4. Once you request to review the permissions, you'll be presented with a new dialog, as shown above. Choose the correct Google account who will be running the script, select Advanced, scroll down, then click "Go to <YOUR PROJECT NAME> (unsafe)" to arrive at the OAuth2 application authorization screen. (Read more about the verification process to learn about why this screen is sitting in between you and the OAuth2 authorization dialog below.)


    NOTE: Once you authorize the app, once you don't need to repeat this process with each execution. It isn't until you get to Task 3 further on in this tutorial that you see this dialog screen again, asking for user permission to create and manage Google Slides presentations.
  5. Once you click Allow on the OAuth2 dialog window, the script starts running... you'll see a pastel yellow dialog at the top. It runs fairly quickly, so you may not notice that it's running or that execution has completed.
  6. That dialog disappears once it's done, so if you don't see it, it's probably finished, so go to your Google Drive (drive.google.com) and look for a new Google Sheet named "Most common words in all of Shakespeare's works" or whatever you assigned to the QUERY_NAME variable:
  7. Open the spreadsheet, and you should see 10 rows of words and their total counts sorted in descending order:

Task 1 summary

Recognize what just happened... you ran some code that queried all of Shakespeare's works (not a HUGE amount of data, but certainly more text than you can easily scan on your own looking at every word in every play, managing a count of such words, then sorting them in descending order of appearances. Not only did you ask BigQuery to do this on your behalf, but you were able to use the built-in service in Apps Script for Google Sheets to put this data in there for easy consumption.

The code for bq-sheets-slides.js (our chosen filename) you pasted in above (aside from PROJECT_ID which should have a real project ID) can also be found in the step1 folder in this codelab's GitHub repo at github.com/googlecodelabs/bigquery-sheets-slides. The code was inspired big the original example in the BigQuery advanced services page which ran a slightly different query... what are the most popular words used by Shakespeare with 10 or more characters. You can also see that sample in its GitHub repo.

If you're interested in other queries you can try against Shakespeare's works or other public data tables, check out this web page as well as this one. Regardless of what query you use, you can always just test the query in the BigQuery console before running it in Apps Script. BigQuery's user interface is available to developers at bigquery.cloud.google.com. For example, here's what our query looks like using the BigQuery UI:

While the steps above leveraged Apps Script's code editor, you can also choose to develop locally via command-line. If you prefer, create a script named bq-sheets-slides.js, paste the code above into it, then upload to Google with the clasp push command. (If you missed it earlier, here again is the link to clasp and how to use it.)

The purpose of runQuery() is to talk to BigQuery and send its results into a Sheet. Now we need to make a chart with the data. Let's make a new function called createColumnChart() that calls the Sheet's newChart() method to do this.

  1. Create chart. Add the body of createColumnChart() featured below to bq-sheets-slides.js right after runQuery(). It gets the sheet with data and requests a columnar chart with all the data. The data range starts at cell A2 because the first row contains the column headers, not data.
/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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);
}
  1. Return spreadsheet. Above, createColumnChart() needs the spreadsheet object, so we need to tweak runQuery() to return spreadsheet object so we can pass it to createColumnChart(). After logging the successful creation of the Google Sheet, return the object at the end of runQuery(), right after the log line:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Driving createBigQueryPresentation() function. Logically segregating the BigQuery and chart-creation functionality is a great idea. Now let's create a createBigQueryPresentation() function to drive the app, calling both and createColumnChart(). The code you add should look something like this:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}
  1. Make code more reusable. You took 2 important steps above: returning the spreadsheet object and creating a driving function. What if a colleague wanted to reuse runQuery() and doesn't want the URL logged? To make runQuery() more digestible for general usage, we need to move that log line. The best place to move it? If you guessed createBigQueryPresentation(), you'd be correct! After moving the log line, it should look like this:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

With these changes above (again except for PROJECT_ID), your bq-sheets-slides.js should now look like the following (and also found in the step2 folder of the GitHub repo):

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

Save the file, then go up at the top of the code editor and switch to execute createBigQueryPresentation() instead of runQuery(). After running it, you'll get another Google Sheet, but this time, a chart shows up in the Sheet next to the data:

The final part of the codelab involves creating a new Google Slides presentation, filling the title and subtitle on the title slide, then adding 2 new slides, one for each of the data cells and another for the chart.

  1. Create slide deck. All of the work on the slide deck will take place in createSlidePresentation() which we're going to add to bq-sheets-slides.js right after createColumnChart(). Let's start with the creation of a new slide deck, then add a title and subtitle to the default title slide we get with all new presentations.
/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Slide deck with results
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');
  1. Add data table. The next step in createSlidePresentation() is to import the cell data from the Google Sheet into our new slide deck. This code snippet, so add it 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 of
  // the dimensions of the data range; fails if Sheet 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]));
    }
  }
  1. Import chart. The final step in createSlidePresentation() is to create one more slide, import the chart from our spreadsheet, and return the Presentation object. Add this final 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;
}
  1. Return chart. Now that our final function is now complete, take another look at its signature. Yes, createSlidePresentation() requires both a spreadsheet and a chart object. We've already adjusted runQuery() to return the Spreadsheet object but now we need to make a similar change to createColumnChart() to return the chart (EmbeddedChart) object. Go back in your application to code to add one last line at the end of createColumnChart() to do that:
  // Return chart object for later use
  return chart;
}
  1. Update createBigQueryPresentation(). Since createColumnChart() returns the chart, we need to save that chart to a variable then pass both the spreadsheet and the chart to createSlidePresentation(). 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 in a Sheet,
 * 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());
}
  1. Save and run createBigQueryPresentation() again. Before it executes though, recognize your app now needs one more set of permissions from your user to view and manage your Google Slides presentations. Once you allow this permission, it'll run as before.
  2. Now in addition to the Sheet that's created, you should also get a new Slides presentation with 3 slides (title, data table, data chart), as shown below:

Congratulations! You've now created an application that leverages the both sides of Google Cloud by performing a Google BigQuery request that queries one of its public data sets, creates a new Google Sheet to store the results, adds a chart based on that just-retrieved data, and finally creates a Google Slides presentation featuring the results as well as chart in the spreadsheet.

That's what you did technically. Broadly speaking, you went from a big data analysis to something you can present to stakeholders, all in code, all automated. We hope this sample inspires you to take this and customize it for your own projects. At the conclusion of this codelab, we'll provide some suggestions as to how you can further enhance this sample app.

With changes above from the final task (again except for PROJECT_ID), your bq-sheets-slides.js should now look like the following (and also found in the final folder in the GitHub repo):

bq-sheets-slides.js

/**
 * 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.
 */

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

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://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 BQ 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 new 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 columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) 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 params.
  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 & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Returns a slide deck with results
 * @see http://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new 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('via GCP and G Suite 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 of
  // the dimensions of the data range; fails if Sheet 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 in a Sheet,
 * 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());
}

Playing no role in this codelab is the "second file" in this codelab, which is the Apps Script manifest file, appsscript.json. You can access it by going to the code editor browser tab and selecting View > Show manifest file from the menu at the top. The contents should look something like this:

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

The manifest file is a system-level configuration file that Apps Script uses to know what execution environment to have available for your application. Covering the contents of a manifest file is outside the scope of this codelab, but you can get an idea of what it does.

The code featured in this codelab is also available at its GitHub repo at github.com/googlecodelabs/bigquery-sheets-slides. (We aim to keep this codelab in-sync with the repo.) Below are additional resources to help you dig deeper into the material covered in this codelab as well as explore other ways of accessing Google developer tools programmatically.

Documentation

Related and general videos

Related and general news & updates

Other codelabs

Introductory

Intermediate

Reference apps

Below are various "code challenges," different ways you can enhance or augment the sample we built in this codelab. This list certainly isn't exhaustive but should provide some inspirational ideas of where you can take the next step.