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:

• Create a data-appropriate visualization using chart functionality within Google Slides.
• Modify a chart's formatting options.
• Share the complete workbook with others.

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.
• Extracting BigQuery columns using Connected Sheets.
• Filtering BigQuery columns using Connected Sheets.
• Sorting columns using Connected Sheets.
• Creating calculated fields using Connected Sheets.
• Referencing data from one sheet in another using Google 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, Extracting and Filtering BigQuery Data using Google Sheets and Performing the Data Analysis 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.

Click Check my progress to verify the objective.

Stage your Google Sheets workbook

6. Use the Extract editor to sort by the column owner_occupied_housing_units in descending order.

Click Check my progress to verify the objective.

Use the Extract editor to sort the remaining data

7. Create a calculated field with the heading geo_id county that selects and displays only the first five (5) characters of the geo_id column.

Click Check my progress to verify the objective.

Create a calculated field to highlight only the needed geo_id data

8. Create a new sheet in your workbook and reference both the geo_id county calculated field (in column B) and owner_occupied_housing_units (in column C) for only the top 100 rows.

Click Check my progress to verify the objective.

Referencing the answer data in another sheet 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.

Task 1. Create a column chart using reference data

At this point in the process, the data has already been pulled from BigQuery into Google Sheets, a data extraction has been created using only the most relevant columns, and the actual data analysis steps have been taken. All that is left to do is to visualize the answer data by creating a chart, and then share the sheet with others. We will focus on the first task now.

In this task, you create a column chart specifically using previously identified reference data.

1. In the Reference data tab, select columns B and C to highlight their data for use in the new chart.

2. From the Insert option in the Google Sheets menu bar, click the Chart option.

3. In the Setup section of the Chart editor, choose the Column chart option if not already selected.

Leave the Chart editor open, and proceed to the next task.

Click Check my progress to verify the objective.

Create a column chart using reference data

Task 2. Modify the column chart formatting

Google Sheets provides many different methods for adjusting and refining the presentation of charts. From color schemes, to font choices to layouts and more, you can adjust your chart to display the clearest interpretation of the story your data tells. Customizing column charts in Google Sheets

In this task, you change the global font for your chart, its color scheme and then move the chart to its own tab within the workbook.

1. In the Customize section of the Chart editor, expand the Chart style section, and choose the Roboto font from the Font drop-down menu.

2. Expand the Series section, and click on the Fill color drop-down menu, then select a color of your choice. WCAG 3.0 recommended accessibility guidelines

3. Click on the Opacity drop-down menu and select a value of 90%.

Proceed to the next task.

Click Check my progress to verify the objective.

Modify the column chart formatting

Task 3. Configuring sheet sharing parameters in Google Sheets

Google Workspace provides a lightweight but powerful way to share and collaborate on your sheets, docs, slides and more with others. These controls also ensure that you provide them with only what they need to see and strict enforcement on how much they can interact or modify that content. Sharing workbooks

In this task, you configure the sharing options for this workbook and share it with yourself.

1. Replace the Untitled spreadsheet default title in the upper left-hand corner of the screen with a title of your own choosing.

2. Click on the Share button in the top right-hand side of the workbook.

3. Add a sample email address to the email address bar.

4. Click on the role permissions drop-down menu next to the selected email address and choose the Editor role.

5. Enter a short message explaining to the person with whom you are sharing this workbook what this data is in order to provide them some frame of reference for why you are choosing to share this with them.

Note: When you do this in the real world, you would click the Send button to share this workbook, but do not share the document in the lab environment.

Click Check my progress to verify the objective.

Configuring sheet sharing parameters in Google Sheets

Congratulations!

You have successfully created and modified a column chart and shared a workbook with someone.

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.

Last Tested Date September 23, 2024

Last Updated Date September 23, 2024

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.