Turn your big data into insights using Google Sheets and Slides1. From big data analysis to slide presentationThere 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. Show
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. MotivationThe 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 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
What you'll need
2. SurveyHow will you use this codelab/tutorial?Read through it for informational purposes, possibly passing it on to technical colleaguesGo through it as far as I can and try as many of the exercises as I canCome hell or high water, I'm going to complete the entire codelabHow would rate your experience with Google Workspace developer tools & APIs?NoviceIntermediateProficientHow would rate your experience with Apps Script specifically?NoviceIntermediateProficientHow would rate your experience with the Cloud Console developer tools & APIs?NoviceIntermediateProficient3. OverviewNow that you know what this codelab is about, here's what you're going to do:
Let's get started with some background information on Apps Script, BigQuery, Sheets, and Slides. Google Apps Script and BigQueryGoogle 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:
NOTE: It's 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 also features an overview with quickstarts, tutorials, and videos. Also, these 2 introductory codelabs should be completed before starting this one: Fundamentals of Apps Script with Google Sheets and Hands-on with Google Apps Script. Apps Script interfaces with other Google technologies in 2 different ways:
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 ScriptUnlike 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 SheetsIntroductionWe'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:
Setup
Next, you'll need to create a Cloud Console project to query data in BigQuery. Note: If you're running this codelab from a kiosk at a Google-hosted event, then a Cloud Console project is already created for you. Open the project and skip to step 5 below.
Note: BigQuery is a paid service with costs associated for both storing and querying data. This codelab queries a small amount of data that should fall under the free usage tier. For more information, go to the BigQuery pricing guide.
Note: You can also find your project's details on the Dashboard page in the Cloud Console. Next, you'll connect your Apps Script project to the Cloud Console project.
The final step is to turn on the BigQuery API on the Cloud Console.
NOTE: After the API is turned on, you might see a note on this page stating "To use this API, you need to create credentials...". Don't worry about this for nowApps Script will take care of this step for you. 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
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.
Note: If you're using a gmail.com account, you might get a "This app isn't verified" dialog when you first use your script. Google uses this to warn users who may be using code from unknown or untrusted authors. If you see this dialog, it's OK to proceed since you're the script author. Follow the on-screen prompts to continue authorizing the script:
Throughout this codelab, you might get several permission prompts. You can read more about this process in Authorization for Google Services.
To show or hide the execution log, click Execution log at the top of the editor.
Task 1 summaryTo 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:5. Task 2: Create a chart in Google SheetsThe 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.
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: 6. Task 3: Put the results data into a slide deckThe 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.
7. ConclusionCongratulations, 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 stakeholdersall 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 resourcesBelow 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
Other codelabsIntroductory
Intermediate
9. The next step: code challengesListed 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.
|