Sheets API basics in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to automate the process of updating monthly sales data in Google Sheets using the Sheets API.

📊 Data: You have a Google Sheet with sales data for different products and months. The sheet has columns: Product, Month, Sales.

🎯 Deliverable: Create a Google Sheets document and use the Sheets API to add new sales data, update existing sales, and read total sales per product.

Sample Data

Product	Month	Sales
Apples	January	120
Bananas	January	150
Apples	February	130
Bananas	February	160
Cherries	January	90
Cherries	February	100

1

Step 1: Create a new Google Sheet and enter the sample sales data into columns A, B, and C starting from row 1 with headers.

Manually input data or use Sheets UI to create the table as shown in sample_data.

Expected Result

Sheet with columns Product, Month, Sales and 6 rows of data.

2

Step 2: Use the Sheets API to append a new row with sales data: Product='Apples', Month='March', Sales=140.

Use the API method 'spreadsheets.values.append' with range='Sheet1!A:C' and values=[['Apples','March',140]].

Expected Result

New row added at the bottom with Apples, March, 140.

3

Step 3: Use the Sheets API to update the sales for Bananas in February to 170.

Use 'spreadsheets.values.update' with range='Sheet1!C4' and valueInputOption='USER_ENTERED', values=[[170]].

Expected Result

Cell C4 updated from 160 to 170.

4

Step 4: Use the Sheets API to read all sales data and calculate total sales per product.

Use 'spreadsheets.values.get' for range='Sheet1!A2:C' and then sum sales grouped by product in your code or sheet.

Expected Result

Totals: Apples=390, Bananas=320, Cherries=190.

5

Step 5: In the Google Sheet, create a summary table with unique products and total sales using formulas.

In E1 enter 'Product', F1 enter 'Total Sales'. In E2 enter =UNIQUE(A2:A7). In F2 enter =SUMIF(A:A,E2,C:C) and drag down.

Expected Result

Summary table showing Apples 390, Bananas 320, Cherries 190.

Final Result

Product   Month    Sales
Apples   January   120
Bananas  January   150
Apples   February  130
Bananas  February  170
Cherries January   90
Cherries February  100
Apples   March     140

Summary:
Product   Total Sales
Apples    390
Bananas   320
Cherries  190

✓Apples have the highest total sales of 390 units.

✓Bananas sales were updated for February to 170 units.

✓New sales data for Apples in March was successfully added.

Bonus Challenge

Use the Sheets API to create a chart in the Google Sheet that visualizes total sales per product.

Show Hint

Use the 'spreadsheets.batchUpdate' method with a 'AddChartRequest' specifying chart type and data ranges.

Practice

1. What is the main purpose of the Google Sheets API?

easy

A. To read and write data in Google Sheets programmatically

B. To create charts manually in Google Sheets

C. To design the layout of a Google Sheet

D. To send emails from Google Sheets

Solution

1. Step 1: Understand the API's function

   The Sheets API allows programs to access and change sheet data automatically.

2. Step 2: Compare options with API purpose

   Only To read and write data in Google Sheets programmatically describes reading and writing data programmatically, which matches the API's main use.

3. Final Answer:

   To read and write data in Google Sheets programmatically -> Option A

4. Quick Check:

   Sheets API = read/write data [OK]

Hint: Think: API means program controls sheet data [OK]

Common Mistakes:

• Confusing API with manual tasks like chart creation
• Thinking API designs sheet layout
• Assuming API sends emails

2. Which of the following is the correct way to specify a range in the Sheets API to read cells A1 to C3 on a sheet named 'Data'?

easy

A. "Sheet1!A1-C3"

B. "A1:C3!Data"

C. "Data:A1-C3"

D. "Data!A1:C3"

Solution

1. Step 1: Recall range format in Sheets API

   The correct format is "SheetName!StartCell:EndCell" to specify a range.

2. Step 2: Match the correct option

   "Data!A1:C3" uses "Data!A1:C3", which matches the correct format for sheet 'Data' and cells A1 to C3.

3. Final Answer:

   "Data!A1:C3" -> Option D

4. Quick Check:

   Range format = SheetName!Start:End [OK]

Hint: Remember: SheetName!CellRange format [OK]

Common Mistakes:

• Putting sheet name after the range
• Using colon between sheet and range incorrectly
• Using wrong sheet name like 'Sheet1' instead of 'Data'

3. Given the following Sheets API call to read values:
spreadsheets.values.get({ spreadsheetId: 'abc123', range: 'Sheet1!B2:B4' })
and the sheet has values B2=10, B3=20, B4=30, what will be the returned values array?

medium

A. [[10, 20, 30]]

B. [[10], [20], [30]]

C. [10, 20, 30]

D. [10; 20; 30]

Solution

1. Step 1: Understand Sheets API value format

   Values are returned as a 2D array where each inner array is a row. Since range is a single column B2:B4, each row is an array with one value.

2. Step 2: Match the correct array format

   [[10], [20], [30]] shows [[10], [20], [30]], which matches the expected 2D array for three rows and one column.

3. Final Answer:

   [[10], [20], [30]] -> Option B

4. Quick Check:

   Single column range returns array of single-item arrays [OK]

Hint: Remember: Single column = array of single-item arrays [OK]

Common Mistakes:

• Expecting a flat array instead of 2D array
• Confusing rows and columns in returned data
• Using wrong brackets or separators

4. You wrote this Sheets API call to update cell A1 with value 'Hello':
spreadsheets.values.update({ spreadsheetId: 'xyz789', range: 'Sheet1!A1', values: 'Hello' })
But it gives an error. What is the mistake?

medium

A. The spreadsheetId is missing

B. The range should be 'A1!Sheet1' instead of 'Sheet1!A1'

C. The 'values' field should be a 2D array, not a string

D. The API does not support updating single cells

Solution

1. Step 1: Check the 'values' parameter format

   The Sheets API expects 'values' to be a 2D array (array of arrays), even for single cells.

2. Step 2: Identify the error cause

   Passing a string 'Hello' instead of [['Hello']] causes the error.

3. Final Answer:

   The 'values' field should be a 2D array, not a string -> Option C

4. Quick Check:

   Values must be 2D array for update [OK]

Hint: Always wrap values in double arrays [[value]] [OK]

Common Mistakes:

• Passing a plain string instead of array
• Swapping sheet name and range
• Assuming API can't update single cells

5. You want to use the Sheets API to copy data from range 'Sheet1!A1:B2' to 'Sheet2!C3:D4' in the same spreadsheet. Which approach correctly achieves this?

hard

A. First read values from 'Sheet1!A1:B2' using spreadsheets.get, then write them to 'Sheet2!C3:D4' using values.update

B. Directly call values.copy with source 'Sheet1!A1:B2' and destination 'Sheet2!C3:D4'

C. Use values.append to add 'Sheet1!A1:B2' data to 'Sheet2!C3:D4'

D. Change the spreadsheetId to 'Sheet2' and call values.update on 'A1:B2'

Solution

1. Step 1: Understand Sheets API capabilities

   The Sheets API does not have a direct values.copy method. To copy data, you must first read the values from the source range, then write them to the destination range.

2. Step 2: Match the correct option

   First read values from 'Sheet1!A1:B2' using spreadsheets.get, then write them to 'Sheet2!C3:D4' using values.update correctly describes the process.

3. Final Answer:

   First read values from 'Sheet1!A1:B2' using spreadsheets.get, then write them to 'Sheet2!C3:D4' using values.update -> Option A

4. Quick Check:

   Copying requires read then write steps [OK]

Hint: Use read then write to copy ranges [OK]

Common Mistakes:

• Assuming a direct copy method exists
• Using append which adds data below existing
• Confusing spreadsheetId with sheet name