Overview

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:

• What was the total cost of a project last month?
• Which project consumed the most networking resources?
• What is the monthly cost broken down by team?

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.

Note: Google Workspace Connected Sheets for BigQuery is available only to Google Workspace Enterprise and Education accounts.

Objectives

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

• Use Google Workspace Connected Sheets to connect to BigQuery.
• Access data through BigQuery and import the data into Sheets.
• Explore ways to analyze the data in Sheets and to then share the output with other users.

Setup and requirements

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 Google Skills 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 Sheets and connect to BigQuery

In this task, you use Google Workspace Connected Sheets to connect to BigQuery.

1. Open Google Sheets.

2. In the Welcome to Google Sheets dialog, click X.

3.To open a blank spreadsheet, in the Start a new spreadsheet pane, click Blank ().

Connect to BigQuery

Note: Be sure you query BigQuery from your lab account. You may incur charges for queries if you use your personal account.

In this step you use Sheets to connect to BigQuery and access your billing data.

1. In the menu at the top, click Data > Data connectors > Connect to BigQuery.

2. In the Connect and analyze dialog, click Get connected.

3. 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.

4. To open the BigQuery query editor, click Write custom query.

5. 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:

   • Project: [qwiklabs-gcp-xxx]
   • Dataset: billing_dataset
   • Table: enterprise_billing
   • Columns: all values listed under enterprise_billing

Task 2. Write a query

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.

Pull all the billing data into your spreadsheet

1. In the BigQuery query editor, type the following:

SELECT * FROM billing_dataset.enterprise_billing

• 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.

2. To see a sample of the returned data, click Preview results.

3. To pull the data into your Sheet, click Connect.

   This creates the Connected Sheet 1 tab and pulls all the data into this tab.

4. In the Success 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.

Test completed task

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.

Pull all the billing data into your spreadsheet.

Refine your query

1. In the Preview of full data set pane, click More options (), and then click Connection settings.

   The BigQuery editor opens.

2. In the BigQuery query editor, clear the current query, and type the following:

SELECT billing_account_id, usage.amount, usage.unit, credits, Cost, Currency, project.ancestry_numbers, project.id, project.name, sku.description, usage_start_time, usage_end_time, FROM billing_dataset.enterprise_billing WHERE project.name IN ('CTG - Dev', 'CTG - Prod') AND service.description = 'Compute Engine' AND cost > 0 AND EXTRACT(month FROM usage_end_time) = 9 AND EXTRACT(year FROM usage_end_time) = 2020 ORDER BY project.name

This query uses:

• SELECT to specify the columns
• WHERE 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

3. When the green check is displayed, click Connect.

4. Examine your updated spreadsheet to see what changed.

Test completed task

Click Check my progress to verify your performed task. If you have successfully refine your query and insert data into your spreadsheet, you will receive an assessment score.

Refine your query.

Task 3. Organize and analyze your spreadsheet

In this task, you use some of the features of Sheets to organize and analyze your billing data.

Organize your spreadsheet

1. In Google Sheets, click Untitled spreadsheet, and then rename the sheet to My Billing Report

2. Click Move (), and then click New Folder ().

3. In the New Folder dialog, type Billing Reports, click Create Folder (), and then click Move here.

4. To highlight all the rows and columns, click the upper left empty cell.

5. 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.

Test completed task

Click Check my progress to verify your performed task.

Organize your spreadsheet.

Task 4. Analyze your data

In this task, you create tables and charts to analyze your data.

Calculated column

1. At the top, click Calculated column.

2. In the Add calculated column dialog, click Columns, and then select cost.

   You may have to scroll down to see the cost option.

3. In the Enter name field, type Sum of Cost, and then click Add.

4. Click Apply.

   Sum of Cost is added as a column to your spreadsheet.

Chart

1. At the top, click Chart, accept the defaults, and then click Create.

2. 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.

3. Click Apply.
   A simple column chart is displayed.

4. To resize the chart, click the chart and drag out the corners.

5. In the chart, hover over the the different areas to see the pop-up descriptions.

6. Click the chart, click more (), and then select Edit chart.

7. Click Column chart, and then select Pie chart.

   The display changes to a pie chart.

Test completed task

Click Check my progress to verify your performed task.

Analyze your chart data.

Pivot table

1. Return to the Connected Sheet 1 tab, and then click Pivot table.

2. In the Create pivot table dialog, accept the defaults, and then click Create.

3. 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.

   d. Click Apply.

4. To highlight all the rows and columns, click the upper-left empty cell.

5. 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.

Test completed task

Click Check my progress to verify your performed task.

Analyze your pivot table data.

Task 5. Schedule a refresh of your data

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.

Note: Re-running a query in BigQuery to refresh data on your personal account may incur additional charges.

To automatically refresh your data, record a macro, and then add a trigger to schedule it.

Record a macro to refresh your data

1. In Google Sheets, click Sheet1.

2. In the menu at the top, click Extensions > Macros > Record Macro.

   The macro starts recording steps.

3. Click the Connected Sheet 1 tab, and then click Refresh preview.

4. In the macro dialog, click Save.

5. In the Name field, type Refresh Data, and then click Save.

Schedule your macro

1. In the menu at the top, click Extensions > Apps Script.

   Apps Script opens in a new tab.

2. In the Recorded Macros window, in the left pane, click Triggers.

3. In the Welcome dialog, click View Dashboard.

4. In the Triggers window, click Add trigger.

   The default trigger starts the macro when you open the spreadsheet. You update it to start the macro every 12 hours.

5. In the Add Trigger 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.

6. In the Choose an account dialog, click your lab Username to continue to Recorded Macros.

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

8. View your trigger in the trigger list.

Now your spreadsheet will refresh the data from BigQuery every 12 hours.

Task 6. Change who can see and edit queries and results

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.

Share with specific people

1. Return to the Google Sheets tab.

2. In Google Sheets, click Share.

3. In the Share dialog, type an email address of the recipient that you want to share with the file with.

Note: If the email address is not within Google Workspace Enterprise or Education, the recipient will only be able to view the file. Note: You cannot share files from within Google Skills with an email address that is not a Google Skills-owned account.

4. 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.

5. Click Send.

Share with a link to the file

In this step you share a link to the file.

1. In Google Sheets, click Share.

2. Click Restricted, and then select Qwiklabs.

3. 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.

4. Click Commenter. Anyone in this group can make comments and suggestions, but can't change or share the file with others.

5. 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.

6. Click Copy link.

7. Copy and paste the link into an email.

8. Click Done.

Test completed task

Click Check my progress to verify your performed task.

Share with a link to the file.

Congratulations!

In this lab, you learned how to:

• Use Google Workspace Connected Sheets to connect to BigQuery.
• Access data through BigQuery and import the data into Sheets.
• Explore ways to analyze the data in Sheets and to then share the output with other users.

End your lab

When you have completed your lab, click End Lab. Google Skills 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 2026 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.