
Before you begin
- Labs create a Google Cloud project and resources for a fixed time
- Labs have a time limit and no pause feature. If you end the lab, you'll have to restart from the beginning.
- On the top left of your screen, click Start lab to begin
Pull all the billing data into your spreadsheet
/ 20
Refine your query
/ 10
Organize your spreadsheet
/ 20
Analyze your chart data
/ 20
Analyze your pivot table data
/ 20
Share with a link to the file
/ 10
As organizations grow in size and collect terabytes of complex data, they need tools to store and analyze their information. Each business unit may need to analyze a portion of that data for specific business needs. In Finance, for example, a financial controller may be interested in analyzing their Cloud Billing data to answer specific questions such as:
BigQuery helps users manage and analyze large datasets with high-speed compute power. But not everyone is a BigQuery expert or a data specialist. Many people may be more comfortable using spreadsheets to perform ad hoc data analysis. You can use Google Workspace Connected Sheets to pull your BigQuery data into Google Sheets and perform your analysis.
In this lab, you learn how to perform the following tasks:
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 are made available to you.
This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.
To complete this lab, you need:
In this task, you use Google Workspace Connected Sheets to connect to BigQuery.
To open Google Sheets, click the following link:
If prompted, in the Welcome to Google Sheets dialog, click X.
To open a blank spreadsheet, in the Start a new spreadsheet pane, click Blank ().
In this step you use Sheets to connect to BigQuery and access your billing data.
In the menu at the top, click Data > Data connectors > Connect to BigQuery.
In the Connect and analyze big data in Sheets dialog, click Get connected.
In the Add data connection dialog, select the project name that starts with qwiklabs-gcp-xxxxx.
You're now ready to use the Sheets data connector to pull information from BigQuery.
To open the BigQuery query editor, click Saved queries and query editor.
In the BigQuery query editor, review the right pane. The Schema section shows your project details, which you need to create a query. In this example:
In this task, you use Google Workspace Connected Sheets to query your billing data (from the enterprise_billing
table), and then pull the query results into your spreadsheet.
For more information about writing queries in BigQuery, refer to Syntax for SQL queries in BigQuery.
SELECT
specifies columns. In this task, "*" (asterisk) selects all the columns.
FROM
specifies where the data is pulled from, in the form [dataset].[table]; in this task, the data is pulled from billing_dataset.enterprise_billing
.
A green check confirms that there are no errors in the script.
To view a sample of the returned data, click Preview results.
To pull the data into your Sheet, click Connect.
This creates the Connected Sheet 1
tab and pulls all the data into this tab.
In the Success! Your data is connected dialog, click X.
You can use the Filter feature to organize and analyze the data. However, depending on what you want to analyze, it may be more efficient to pull over a subset of your data.
Click Check my progress to verify your performed task. If you have successfully run a query to pull all the billing data into your spreadsheet, you will receive an assessment score.
In the bottom left side, hover the refresh icon for Preview of full data set, > click More options (), and then click Connection settings.
The BigQuery editor opens.
In the BigQuery query editor, clear the current query, and type the following:
This query uses:
SELECT
to specify the columnsWHERE
to specify only data where the project.name
is CTG - Dev
or CTG - Prod
and the service is Compute Engine
for the month September 2020 to limit the number of rows in the sheet.ORDER BY
to do exactly that: order the results by project.name
When the green check is displayed, click Connect.
Examine your updated spreadsheet to see what changed.
Click Check my progress to verify your performed task. If you have successfully refined your query and inserted data into your spreadsheet, you will receive an assessment score.
In this task, you use some of the features of Sheets to organize and analyze your billing data.
In Google Sheets, click Untitled spreadsheet, and then rename the sheet to My Billing Report.
Click File > Move (), > My Drive and then click New Folder (
).
In the New Folder dialog, type Billing Reports, click Create (), and then click Move.
To highlight all the rows and columns, click the upper left empty cell.
To resize the cell, click the right outside edge of the top cell of the first column, and then double-click.
The cell width changes to fit the text for each column.
Click Check my progress to verify your performed task.
In this task, you create tables and charts to analyze your data.
At the top, click Calculated column.
In the Add calculated column dialog, click Columns, and then select cost.
You may have to scroll down to see the cost option.
In the Enter name field, type Sum of Cost, and then click Add.
Click Apply.
Sum of Cost is added as a column to your spreadsheet.
At the top, click Chart, accept the defaults, and then click Create.
In Chart editor, in the Setup pane, do the following:
a. Click Add X-axis, and then select description.
b. Click Add Series, and then select Sum of Cost.
Click Apply.
A simple column chart is displayed.
To resize the chart, click the chart and drag out the corners.
In the chart, hover over the different areas to see the pop-up descriptions.
Click the chart, click more (), and then select Edit chart.
Click Column chart, and then select Pie chart.
The display changes to a pie chart.
Click Check my progress to verify your performed task.
Return to the Connected Sheet 1 tab, and then click Pivot table.
In the Create pivot table dialog, accept the defaults, and then click Create.
In the Pivot table editor, do the following:
a. For Rows
, click Add, and then select description.
b. For Columns
, click Add, and then select name.
c. For Values
, click Add, and then select Sum of Cost.
Click Apply.
To highlight all the rows and columns, click the upper-left empty cell.
To resize the cell, click the right outside edge of the top cell of the first column, and then double-click.
The cell width changes to fit the text for each column.
Click Check my progress to verify your performed task.
In this task, you periodically refresh your data to keep your spreadsheet current. When you refresh your data, all charts and pivot tables created within the spreadsheet are also refreshed.
To automatically refresh your data, record a macro, and then add a trigger to schedule it.
In Google Sheets, click Sheet1.
In the menu at the top, click Extensions > Macros > Record Macro.
The macro starts recording steps.
Click the Connected Sheet 1 tab, and then click Refresh preview.
In the macro dialog, click Save.
In the Name field, type Refresh Data, and then click Save.
In the menu at the top, click Extensions > Apps Script.
Apps Script opens in a new tab.
In the Recorded Macros window, in the left pane, click Triggers.
In the Welcome dialog, click View Dashboard.
In the Triggers window, click Create a new trigger.
The default trigger starts the macro when you open the spreadsheet. You update it to start the macro every 12 hours.
In the Add Trigger for Recorded Macros dialog, do the following:
a. Click Select event source, and then select Time-driven.
b. Click Select type of time based trigger, and then select Hour timer.
c. Click Select hour interval, and then select Every 12 hours.
d. Click Save.
In the Choose an account dialog, click your lab Username to continue to Recorded Macros.
In the Access your Google account dialog, click Allow.
This allows your spreadsheet to view your data in BigQuery and view and manage spreadsheets that this application has been installed in.
View your trigger in the trigger list.
Now your spreadsheet will refresh the data from BigQuery every 12 hours.
In this task, you determine who can see and edit queries and results by setting access to the spreadsheet. You set access by sharing your Sheet.
Return to the Google Sheets tab.
In Google Sheets, click Share.
In the Share dialog, type an email address of the recipient you want to share the file with.
If you don't want to notify recipients that you are sharing the file, clear the Notify people checkbox. If you notify people, each email address you enter will be included in a notification email.
Click Share.
In this step you share a link to the file.
In Google Sheets, click Share.
Click Restricted, and then select Qwiklabs.
Click Viewer. See how the options change. Anyone in this group with the link can view, but can't change or share the file with others.
Click Commenter. Anyone in this group can make comments and suggestions, but can't change or share the file with others.
Click Editor. Anyone in this group can make changes, accept or reject suggestions, and share the file with others. This includes using Sheets data connector to query the source data.
Click Copy link.
Copy and paste the link into an email.
Click Done.
Click Check my progress to verify your performed task.
You used the Sheets data connector to query BigQuery and pull data into your spreadsheet, then analyzed the data, scheduled the spreadsheet to automatically refresh, and reviewed how you share your spreadsheet.
See Introduction to SQL for BigQuery and Cloud SQL to learn more about BigQuery queries.
Learn more about Google Sheets and the BigQuery connector.
...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 February 06, 2025
Lab Last Tested February 06, 2025
Copyright 2025 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.
此内容目前不可用
一旦可用,我们会通过电子邮件告知您
太好了!
一旦可用,我们会通过电子邮件告知您
One lab at a time
Confirm to end all existing labs and start this one