Sheets API basics in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to get the values from a sheet named 'Sheet1'.

Google Sheets

values = sheet.values().get(spreadsheetId=spreadsheet_id, range=[1]).execute().get('values', [])

Drag options to blanks, or click blank then click option'

A"Sheet1!A1:D10"

B"Sheet2!A1:D10"

C"Data!A1:D10"

D"Summary!A1:D10"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong sheet name in the range string.

Forgetting to include the exclamation mark (!) after the sheet name.

The range must specify the correct sheet name. Here, "Sheet1!A1:D10" targets cells A1 to D10 in 'Sheet1'.

2fill in blank

medium

Complete the code to update a cell value in the sheet using the Sheets API.

Google Sheets

body = {"values": [[[1]]]}
result = sheet.values().update(spreadsheetId=spreadsheet_id, range="Sheet1!B2", valueInputOption="USER_ENTERED", body=body).execute()

Drag options to blanks, or click blank then click option'

A"Hello"

BHello

C123

D"=SUM(A1:A5)"

Attempts:

3 left

💡 Hint

Common Mistakes

Not putting text values inside quotes, causing errors.

Using numbers without quotes when text is expected.

The value must be a string inside quotes to be passed correctly as a cell value. So "Hello" is correct.

3fill in blank

hard

Fix the error in the code to append a new row to the sheet.

Google Sheets

body = {"values": [["John", "Doe", 30]]}
result = sheet.values().append(spreadsheetId=spreadsheet_id, range=[1], valueInputOption="USER_ENTERED", insertDataOption="INSERT_ROWS", body=body).execute()

Drag options to blanks, or click blank then click option'

A"Sheet1!A1:C1"

B"Sheet1"

C"Sheet1!A1"

D"Sheet1!A:C"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a cell range instead of just the sheet name for append range.

Including column letters or row numbers in the range when appending.

For appending rows, the range should be just the sheet name like "Sheet1" without specific cells.

4fill in blank

hard

Fill both blanks to create a dictionary comprehension that maps each word to its length if the length is greater than 3.

Google Sheets

{word: [1] for word in words if [2]

Drag options to blanks, or click blank then click option'

Alen(word)

Bword > 3

Clen(word) > 3

Dword

Attempts:

3 left

💡 Hint

Common Mistakes

Using the word itself as the value instead of its length.

Comparing the word string directly to a number instead of its length.

The value should be the length of the word using len(word). The condition checks if the length is greater than 3 using len(word) > 3.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps uppercase words to their values if the value is positive.

Google Sheets

result = [1]: [2] for [3] in data.items() if [2] > 0}

Drag options to blanks, or click blank then click option'

Ak.upper()

Bv

Ck, v

Ditem

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'item' instead of 'k' and 'v' in the loop.

Not converting keys to uppercase.

Use k.upper() to convert keys to uppercase, v for values, and k, v for the key and value variables in the loop.