Sheets with Google Data Studio (Looker) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Connecting Google Sheets to Google Data Studio

You want to create a live report in Google Data Studio using data from a Google Sheet. Which step is required to connect your sheet as a data source?

A Share the Google Sheet with the Data Studio service account email

B Publish the Google Sheet to the web and then add it as a data source in Data Studio

C Use the Google Sheets connector inside Data Studio and select your sheet

D Download the Google Sheet as Excel and upload it to Data Studio

💡 Hint

Think about how Data Studio accesses live data from Sheets without manual uploads.

Google Data Studio has a built-in Google Sheets connector that allows you to select your sheet directly. This keeps the data live and updated automatically.

📊 Formula Result

intermediate

Formula Output for Date Formatting in Sheets

You have a date in cell A1 as 2024-06-15. You use the formula =TEXT(A1, "MMMM yyyy"). What is the output?

A 2024-06

B June 2024

C 15 June 2024

D 06 2024

💡 Hint

The TEXT function formats dates using text patterns like MMMM for full month name.

The format string "MMMM yyyy" converts the date to full month name and year, e.g., "June 2024".

❓ Function Choice

advanced

Choosing the Right Function for Dynamic Data Range

You want to create a dynamic range in Google Sheets that automatically expands as you add new rows of data in column A. Which function is best to use inside Data Studio to ensure all data is included?

A FILTER(A:A, LEN(A:A))

B ARRAYFORMULA(A1:A100)

C OFFSET(A1, 0, 0, COUNTA(A:A), 1)

D INDIRECT("A1:A" & COUNTA(A:A))

💡 Hint

Consider a formula that includes all non-empty cells in column A without fixed limits.

FILTER(A:A, LEN(A:A)) returns all cells in column A that are not empty, dynamically adjusting as data grows.

❓ data_analysis

advanced

Aggregating Data for Google Data Studio

You have sales data in Google Sheets with columns: Date, Product, and Sales Amount. You want to create a summary table showing total sales per product. Which formula in Sheets will correctly calculate total sales for product "Widget"?

A =SUM(C:C * (B:B = "Widget"))

B =SUMIFS(C:C, B:B, "Widget")

C =COUNTIF(B:B, "Widget")

D =SUMIF(B:B, "Widget", C:C)

💡 Hint

Think about summing values in one column based on matching criteria in another.

SUMIF(range, criteria, sum_range) sums values in sum_range where range matches the criteria. Here, it sums sales amounts for "Widget" products.

📊 Formula Result

expert

Understanding Data Studio's Handling of Blank Cells from Sheets

You have a Google Sheet column with values: 10, 15, (blank), 20. You connect this sheet to Data Studio. What will be the sum of this column in Data Studio's aggregation?

A Sum of only non-blank cells: 45

B Null or blank, so sum is 0

C Error due to blank cell

D 45

💡 Hint

Consider how Data Studio treats blank cells in numeric aggregations.

Data Studio ignores blank cells in numeric aggregations like SUM, so it sums only 10 + 15 + 20 = 45.