체크포인트
Query BigQuery and log results to Sheet
/ 25
Use Formulas in Connected sheet
/ 25
Use Charts in Connected Sheets
/ 25
Create a new Google Sheet and enter a street address
/ 25
Integrate BigQuery Data and Google Workspace using Apps Script: Challenge Lab
- ARC133
- Overview
- Setup and requirements
- Challenge scenario
- Task 1. Query BigQuery and log the results to Google Sheets
- Task 2. Perform calculations on charts with Connected Sheets
- Task 3. Use Google Charts with Connected Sheets
- Task 4. Use Apps Script to create a new Google Sheets worksheet and enter data
- Congratulations!
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).
- Time to complete the lab---remember, once you start, you cannot pause a lab.
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 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 project to a name of your choice.
Enter and run your application code
- Copy the code in the box below and paste it over everything in the code editor:
Code.gs
-
Change the file name to
bq-sheets.gs
and press Enter. -
Review the query code for the function
runQuery()
:
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.
- Save the file and run your code by clicking the Run option in the menu bar.
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.
Task 2. Perform calculations on charts with Connected Sheets
- 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
- Make the connection from new Blank Spreadsheet to BigQuery dataset.
- Connect a BigQuery dataset to Google Sheets using Data connectors.
- Select your Project ID
> Public datasets > chicago_taxi_trips > taxi_trips.
Use formulas with Connected Sheets
- 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.
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.
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.
-
To create a new sheet, open Google Sheets.
-
On a blank spreadsheet, click into the first cell in the upper-left corner (A1). It should be in column A and row 1.
-
Enter the following address in the first cell.
Address 76 9th Ave, New York
Click Check my progress to verify the objective.
Congratulations!
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 August 9, 2024
Lab Last Tested August 9, 2024
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.