ARC133

Overview

In a challenge lab you’re given a scenario and a set of tasks. Instead of following step-by-step instructions, you will use the skills learned from the labs in the course to figure out how to complete the tasks on your own! An automated scoring system (shown on this page) will provide feedback on whether you have completed your tasks correctly.

When you take a challenge lab, you will not be taught new Google Cloud concepts. You are expected to extend your learned skills, like changing default values and reading and researching error messages to fix your own mistakes.

To score 100% you must successfully complete all tasks within the time period!

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab---remember, once you start, you cannot pause a lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

Challenge scenario

You are a junior cloud engineer assigned to a team. So far you have been helping your team create and manage Google Cloud resources.

For this lab, your challenge is to use Google Cloud's BigQuery API (as an Apps Script advanced service) and the built-in Apps Script services for Google Sheets and Google Slides to perform data analysis. In addition, you also need to create a Google Sheets spreadsheet and populate data into it, as well as create a chart with spreadsheet data.

You are expected to have the skills and knowledge to complete the tasks that follow.

Your challenge

In this lab, you are asked to:

• Query BigQuery and log the results to a Google Sheets worksheet with Apps Script.
• Connect a BigQuery dataset to Google Sheets.
• Use Google Charts to visualize spreadsheet data with Connected Sheets.
• Use Apps Script to create a new worksheet and populate it with data.

Task 1. Query BigQuery and log the results to Google Sheets

For this task, as a prerequisite for the steps that follow, you need to create a new Apps Script project by navigating to script.google.com and then rename the name of the project as per your choice (for example: "Bigquery", "Sheets", "Slides demo" etc).

Enter and run your application code

1. Copy the code in the box below and paste it over everything in the code editor:

/** * 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"; var PROJECT_ID = "{{{project_0.project_id|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()); }

2. Change the file name to bq-sheets-slides.gs and press Enter.

3. Review the query code for the function 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.

4. Save the file and run your code by clicking the Run option in the menu bar.

Note: After saving and running the code, if you get an error Exception: Service BigQuery API has not been enabled for your Apps, remove the BigQuery API Service and add it again.

Click Check my progress to verify the objective. Query BigQuery and log results to a sheet

Task 2. Perform calculations on charts with Connected Sheets

1. For this task, you need to analyze a public dataset containing data on taxi trips in Chicago. To start, open the Google Sheets home page.

Connect a BigQuery dataset to Google Sheets

2. Make the connection from new Blank Spreadsheet to BigQuery dataset.
3. Connect a BigQuery dataset to Google Sheets using Data connectors.
4. Select your Project ID > Public datasets > chicago_taxi_trips > taxi_trips.

Use formulas with Connected Sheets

5. Next, you can use different formulas with Connected Sheets.

• Find out how many taxi companies there are in Chicago.
• Find the percentage of taxi rides in Chicago that included a tip.
• Find the total number of trips where the fare was greater than 0.

Click Check my progress to verify the objective. Use formulas with Connected Sheets

Task 3. Use Google Charts with Connected Sheets

For this task, you use Charts (in this instance, pie and line charts) to inspect popularity of rides and trends of payment types.

View the following information in Google Charts:

• As a pie chart, what forms of payments are people using for their taxi rides?
• As a line chart, how has revenue from mobile payments for taxi trips changed over time?
• As a line chart, how have mobile payments changed over time since revenue peaked in 2015?

Click Check my progress to verify the objective. Use Charts with Connected Sheets

Task 4. Use Apps Script to create a new Google Sheets worksheet and enter data

For this task, you need to enter a street address in a new Google Sheet to prepare to use the Apps Script editor.

1. To create a new sheet, open Google Sheets.

2. On a blank spreadsheet, click into the first cell in the upper-left corner (A1). It should be in column A and row 1.

3. Enter the following address in the first cell.

   Address
   76 9th Ave, New York

Now you're ready to enter the Apps Script editor and write some code.

Click Check my progress to verify the objective. Create a new Google Sheets worksheet and enter a street address

Congratulations!

You have successfully integrated BigQuery data and Google Workspace using Apps Script to log query results in a Google Sheets worksheet, connected a BigQuery dataset to Google Sheets, used Google Charts to visualize spreadsheet data with Connected Sheets, and used Apps Script to create a new worksheet and populate it with data.

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated November 21, 2023

Lab Last Tested November 22, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.