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 to use various features of Google Sheets in order to analyze the BigQuery Connected Sheets data to answer the solar grant household eligility data question.

Objectives

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

• Employ the sort functionality in BigQuery Connected Sheets Extracts.
• Create calculated fields.
• Reference subsets of the larger data Extract to highlight the most relevant data.

Prerequisites

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

• Locating a specific data table using BigQuery Connected Sheets.
• Downloading a specific data table into Google Sheets using BigQuery Connected Sheets.
• Extracting BigQuery columns in Connected Sheets.
• Filtering BigQuery columns in Connected Sheets.

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

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.

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.

3. Create a data extract using the BigQuery Connected Sheets interface.

4. Use the Extract editor to select the four most useful data columns needed for the data analysis:

   • geo_id
   • occupied_housing_units
   • owner_occupied_housing_units
   • mortgaged_housing_units

5. Use the Extract editor to select and configure the two most useful column filter criteria:

   • median_year_structure_built less than or equal to 1960.
   • median income less than or equal to 60000.

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. Using the Extract editor to sort the remaining data

The data you are presented with has been reduced from 74,000 rows from the initial connection with BigQuery, to now less than 500 rows of the most relevant data needed to answer the solar grant household eligibility question.

In this task, you use the Sort option in the Extract editor in Google Sheets to present the highest number of owner occupied housing units in descending order.

1. In the Extract editor menu, click the Add button in the Sort section.

2. Type in or search for the owner_occupied_housing_units column name and select it.

3. On the newly added owner_occupied_housing_units sort selection, click until it displays Desc, corresponding to using a descending sort order.

4. Please confirm the owner_occupied_housing_units column is now displaying its data from highest number to lowest.

Proceed to the next task.

Click Check my progress to verify the objective. Use the Extract editor to sort the remaining data

Task 2. Create a calculated field to highlight only the needed geo_id data

In Google Sheets, a calculated field is a field that uses existing data fields and then applies additional logic to them, empowering you to create new data from your existing data. A calculated field either performs some calculation on specific data fields to create a value that is newly derived, or selects values within your existing data based on some customized criteria.

In this task, you will create a new calculated field to separate only the state and county information from the data contained in the geo_id column. You will copy only the first five characters of the geo_id fields: the first two characters corresponding to a US state, and the final three characters identifying the county name.

1. In the Extract 1 tab, add a new column name geo_id_county to the F1 cell.

2. Type the following LEFT function statement in the F2 cell: =LEFT(A2, 5) which tells Google Sheets to copy only the first five characters from the A2 cell into the F2 cell.

3. Select the F2 cell and using the very small square in the lower right-hand side of the field highlight, click and drag the field highlight for the next 100 rows, to implement the function for the geo_id data in those rows.

Please proceed to the next task.

Click Check my progress to verify the objective. Create a calculated field to highlight only the needed

Task 3. Referencing the answer data in another Sheet

Within one spreadsheet in Google Sheets, you can replicate data and copy it from one sheet to another. Referencing data from other sheets

In this task, you will refrence the data needed to answer our data question about solar grant housing eligibility in a new sheet, to more cleanly display the most useful data.

1. Create a new sheet by click on the + button in the lower left-hand corner of the Google Sheets user interface.

2. In the newly created sheet, add the column title geo_id county in the B1 cell.

3. Type the following reference formula in the B2 cell: ='Extract 1'!F2 which tells Google Sheets to copy the data from the F2 cell in the Extract 1 sheet into the B2 cell on this one.

4. Select the B2 cell and using the very small square in the lower right-hand side of the field highlight, click and drag the field highlight for the next 100 rows, to implement the formula to reference the data for the remaining 99 cells.

5. Repeat steps 2 through 4 beginning in the C2 field in the new sheet, referencing the owner_occupied_housing_units column data using the same methods.

Click Check my progress to verify the objective. Referencing the answer data in another sheet

Congratulations!

You have successfully performed a data analysis process using data imported from BigQuery using BigQuery 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.