正在加载…
未找到任何结果。

在 Google Cloud 控制台中运用您的技能

Analyzing and Visualizing Data the Google Way

访问 700 多个实验和课程

Pulling BigQuery Data into Google Sheets using BigQuery Connected Sheets

实验 30 分钟 universal_currency_alt 5 个积分 show_chart 入门级
info 此实验可能会提供 AI 工具来支持您学习。
访问 700 多个实验和课程

Overview

In this scenario, you are a data analyst with strong experience in Google Sheets but are new to BigQuery. You are employed with a solar energy company who is interested in identifying U.S. counties with the highest number of homeowners who can benefit from a new grant. The new United States (U.S.) federal grant is available to homeowners with homes built before 1960 and annual incomes below $60,000 USD. You know that the necessary data to identify these homeowners is in your company's BigQuery data warehouse and would like to analyze the BigQuery data in Google Sheets.

Through the BigQuery data connector in Google Sheets, Connected Sheets provides you with the ability to access, analyze, visualize, and share BigQuery data without the need for any SQL.

In this lab, you learn how to get started with BigQuery data in Google Sheets by using the BigQuery data connector to pull data from BigQuery tables (in a Google Cloud project) into Google Sheets.

Objectives

In this lab, you will learn how to perform the following tasks:

  • Open the BigQuery data connector in Google Sheets.
  • Connect to a BigQuery table and pull the data into a Google Sheet.

Setup

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

  1. Sign in to Qwiklabs using an incognito window.

  2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
    There is no pause feature. You can restart if needed, but you have to start at the beginning.

  3. When ready, click Start lab.

  4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

  5. Click Open Google Console.

  6. Click Use another account and copy/paste credentials for this lab into the prompts.
    If you use other credentials, you'll receive errors or incur charges.

  7. Accept the terms and skip the recovery resource page.

Task 1. Open a new Google Sheet

In this first task, you log into Google Workspace in this lab environment using the provided credentials and then open a new Google Sheet.

  1. To open Sheets, right-click this provided link for Open Google Sheets, and select the option to open the link in a new incognito window.

  2. To sign into Google Workspace, use the credentials (username and password) provided on the current lab page.

Be sure to:

  • Accept the terms and conditions.
  • Do not add recovery options or two-factor authentication (because this is a temporary account).
  • Exit the Welcome to Google Sheets window.
Note: Be sure to log into Google Workspace using the provided lab credentials. If you use your personal Google Cloud account, you may incur charges when connecting to BigQuery and other Google Cloud resources.
  1. On the Sheets main page, click the + (plus sign) for Blank spreadsheet.

Task 2. Pull data using the BigQuery data connector

In BigQuery, a table ID contains all of the information needed to locate the table (including the names of the Google Cloud Project, the BigQuery dataset, and the table) and is structured as follows:

  • project_name.dataset_name.table_name

In this task, you use the BigQuery data connector to connect and pull the data from the following table in your lab-provided Google Cloud project into Google Sheets:

  • .public_sector.censustract_2018_5yr
  1. In the menu bar of your Google Sheet, select Data > Data connectors > Connect to BigQuery.

  1. For Choose a cloud project, search for the project name.

  2. For Choose a dataset, select public_sector.

  3. For Choose a table or view, select censustract_2018_5yr.

  4. Click Connect.

After a successful data connection, the data will populate the Google Sheet and resemble the following table structure with 74,000 records.

geo_id do_date total_pop households more columns...
72021031033 2014-01-01 4234 1536 ...
72137122100 2014-01-01 2483 834 ...
72025200700 2014-01-01 3832 1445 ...
72021031021 2014-01-01 3555 1376 ...
72135510601 2014-01-01 5915 1550 ...

Click Check my progress to verify the objective. Pull data from censustract_2018_5y using the BigQuery data connector

Challenge

Test your new skills by pulling in another BigQuery dataset into your Google Sheet.

For this challenge, repeat the lab tasks as needed to pull data from the following BigQuery table ID:

  • .public_sector.censustract_2018_5yr_top10000_housingunits

After a successful data connection, the data will populate the Google Sheet and have the same table structure as censustract_2018_5yr but only contain 10,000 records.

Click Check my progress to verify the objective. Pull data from censustract_2018_5yr_top10000_housingunits using the BigQuery data connector

Congratulations!

You have successfully used the BigQuery data connector to pull data from a BigQuery table into your Google Sheet!

Last Tested Date: November 04, 2022

Last Updated Date: November 04, 2022

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

  • 1 star = Very dissatisfied
  • 2 stars = Dissatisfied
  • 3 stars = Neutral
  • 4 stars = Satisfied
  • 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 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.

上一步 下一步

准备工作

  1. 实验会创建一个 Google Cloud 项目和一些资源,供您使用限定的一段时间
  2. 实验有时间限制,并且没有暂停功能。如果您中途结束实验,则必须重新开始。
  3. 在屏幕左上角,点击开始实验即可开始

此内容目前不可用

一旦可用,我们会通过电子邮件告知您

太好了!

一旦可用,我们会通过电子邮件告知您

一次一个实验

确认结束所有现有实验并开始此实验

使用无痕浏览模式运行实验

请使用无痕模式或无痕式浏览器窗口运行此实验。这可以避免您的个人账号与学生账号之间发生冲突,这种冲突可能导致您的个人账号产生额外费用。
预览