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.

Fortunately, 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 extracting and filtering BigQuery data in Connected Sheets.

Objectives

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

• Extract BigQuery columns in Connected Sheets.
• Filter BigQuery columns in Connected Sheets.

Prerequisites

In order to complete this lab exercise, you must have previous knowledge with and experience performing the following tasks:

• Locating a specific data table using BigQuery Connected Sheets.
• Downloading a specific data table into Google Sheets using BigQuery Connected Sheets.

Note: If you would like to learn how to perform these tasks, or would like to refresh your knowledge, please review Pulling Data into Google Sheets lab exercise and then return here.

Setup

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.

3. On the Sheets main page, click the + (plus sign) for Blank spreadsheet.

Stage your Google Sheets workbook

You must properly stage your sheet before you can complete the tasks in this lab exercise. To begin, please perform the following steps:

1. Use BigQuery Connected Sheets to locate the censustract2018_5yr_top10000_housingunits data table, contained within the project name, and public_sector dataset.

2. Use BigQuery Connected Sheets to download the censustract2018_5yr_top10000_housingunits data table into Google Sheets.

Note: If you cannot perform the following steps, please refer back to the Prerequisites section of this lab for instructions on where to gain this knowledge and experience.

Click Check my progress to verify the objective. Stage your Google Sheets workbook

Task 1. Extract BigQuery columns in Connected Sheets

After pulling BigQuery data into Connected Sheets, you may find that you have more data columns than you actually need for your analysis. For example, from the 242 columns included in the 2018 U.S. Census Bureau data, you are only interested in specific housing details related to median income, owner occupancy, and structure age for U.S. counties and are not interested in other columns such as population breakdown by age.

In this task, you use the Extract option in Connected Sheets to select desired data columns and copy them to a new tab in Google Sheets.

1. In the Connected Sheets menu, click Extract.

2. For Insert to, select New sheet, and click Create.

3. In the Extract editor, identify the Columns section, and click Edit.

4. Select Select individual columns, and search for or scroll to geo_id.

5. Click geo_id to add the column to the extract.

6. Repeat steps 4-5 to add three additional columns to the extract:

• occupied_housing_units
• owner_occupied_housing_units
• mortgaged_housing_units

7. Click Edit again to close the search.

Leave the Extract editor open, and proceed to the next step.

Click Check my progress to verify the objective. Extract BigQuery columns in connected sheets

Task 2. Filter BigQuery columns in Connected Sheets

In Connected Sheets, the Extract editor also provides options to filter data based on specific column values. For example, from the 10,000 rows included in the adapted 2018 U.S. Census Bureau data, you want to extract only the rows for U.S. counties that fulfill certain criteria based on median income or age of structures.

In this task, you add filters to your extraction to further refine the extracted data to only U.S. counties with both a median income less than $60,000 USD and a median build year for structures before 1960.

1. In the Extract editor, identify the Filters section, and click Add.

2. Search for or scroll to median_year_structure_built.

3. Click median_year_structure_built to add the column to the filter.

4. Click Showing all items.

5. For Filter by condition, select Less than or equal to.

6. For Value, enter 1960.

7. Click OK.

8. Repeat steps 1-7 to add a new filter for median_income is less than or equal to 60000.

9. Click Apply.

Click Check my progress to verify the objective. Filter BigQuery columns in connected sheets

Congratulations!

You have successfully used the BigQuery data connector to extract and filter BigQuery data in Connected Sheets!

Last Tested Date September 23, 2024

Last Updated Date September 23, 2024

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.