Reading and writing cell values in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to read the value from cell A1.

Google Sheets

= [1]("A1")

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

AINDIRECT

BREAD

CCELL

DVALUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CELL instead of INDIRECT, which returns cell info but not the value directly.

Using VALUE which converts text to number but does not read cell content.

Using READ which is not a valid Google Sheets function.

The INDIRECT function returns the value of the cell reference given as a string, so INDIRECT("A1") reads the value in cell A1.

2fill in blank

medium

Complete the formula to write the value 100 into cell B2 using Apps Script syntax.

Google Sheets

sheet.getRange("B2").[1](100);

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

AsetValues

BsetValue

CgetValues

DgetValue

Attempts:

3 left

💡 Hint

Common Mistakes

Using getValue which reads instead of writes.

Using setValues which expects an array, not a single value.

Using getValues which reads multiple cells.

setValue(100) writes the value 100 into the specified cell B2.

3fill in blank

hard

Fix the error in this formula to read the value from cell C3.

Google Sheets

=INDIRECT([1])

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

AC3

BC"3"

CINDIRECT("C3")

D"C3"

Attempts:

3 left

💡 Hint

Common Mistakes

Passing C3 without quotes causes a reference error.

Using INDIRECT("C3") inside INDIRECT is redundant.

Using invalid syntax like C"3".

The INDIRECT function requires a text string as input, so the cell address must be in quotes: INDIRECT("C3").

4fill in blank

hard

Fill both blanks to create a formula that writes the sum of cells A1 and A2 into cell B1 using Apps Script.

Google Sheets

sheet.getRange("B1").[1](sheet.getRange("A1").[2]() + sheet.getRange("A2").getValue());

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

AsetValue

BgetValue

CsetValues

DgetValues

Attempts:

3 left

💡 Hint

Common Mistakes

Using setValues instead of setValue for a single cell.

Using getValues which returns arrays, not single values.

Mixing up getValue and setValue methods.

Use getValue() to read each cell's value and setValue() to write the sum into B1.

5fill in blank

hard

Fill all three blanks to create a dictionary (object) in Apps Script that maps cell addresses to their values for A1, B1, and C1.

Google Sheets

const values = {"A1": sheet.getRange("A1").[1](), "B1": sheet.getRange("B1").[2](), "C1": sheet.getRange("C1").[3]()};

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

AgetValue

BsetValue

CgetValues

DsetValues

Attempts:

3 left

💡 Hint

Common Mistakes

Using setValue or setValues which write values instead of reading.

Using getValues which returns arrays, not single values.

Use getValue() to read the value of each single cell and store them in the object.

Practice

1. What does the cell reference A1 mean in Google Sheets?

easy

A. It is a formula to add values.

B. It refers to the first sheet in the workbook.

C. It refers to the cell in column A and row 1.

D. It is a function to read data.

Solution

1. Step 1: Understand cell references

   Cell references like A1 point to a specific cell by column letter and row number.

2. Step 2: Identify column and row

   A1 means column A and row 1, the top-left cell in the sheet.

3. Final Answer:

   It refers to the cell in column A and row 1. -> Option C

4. Quick Check:

   Cell reference A1 = column A + row 1 [OK]

Hint: Column letter + row number = cell address [OK]

Common Mistakes:

• Thinking A1 is a formula
• Confusing cell reference with sheet name
• Assuming A1 is a function

2. Which of the following is the correct way to write a formula that adds cells A1 and B1 in Google Sheets?

easy

A. SUM(A1+B1)

B. A1+B1

C. =SUM(A1 B1)

D. =A1+B1

Solution

1. Step 1: Recognize formula syntax

   Formulas start with an equal sign (=) in Google Sheets.

2. Step 2: Use correct addition syntax

   To add two cells, use =A1+B1 with plus sign and no extra spaces or missing symbols.

3. Final Answer:

   =A1+B1 -> Option D

4. Quick Check:

   Formula starts with = and uses + for addition [OK]

Hint: Always start formulas with = sign [OK]

Common Mistakes:

• Omitting the = sign
• Using SUM without commas
• Writing formula without operators

3. If cell A1 contains 5 and cell B1 contains 10, what will be the result in cell C1 after entering the formula =A1*B1?

medium

A. 50

B. 15

C. 510

D. Error

Solution

1. Step 1: Identify values in cells

   Cell A1 has 5 and B1 has 10.

2. Step 2: Calculate multiplication

   =A1*B1 multiplies 5 by 10, resulting in 50.

3. Final Answer:

   50 -> Option A

4. Quick Check:

   5 * 10 = 50 [OK]

Hint: Use * for multiplication in formulas [OK]

Common Mistakes:

• Adding instead of multiplying
• Concatenating numbers as text
• Forgetting = sign

4. A user types the formula =SUM(A1 B1) in cell C1. What is wrong with this formula?

medium

A. SUM cannot be used with cell references.

B. Missing comma between cell references.

C. Formula should not start with = sign.

D. Cell references must be lowercase.

Solution

1. Step 1: Check SUM function syntax

   SUM requires arguments separated by commas, like SUM(A1, B1).

2. Step 2: Identify missing comma

   The formula uses a space instead of a comma between A1 and B1, causing an error.

3. Final Answer:

   Missing comma between cell references. -> Option B

4. Quick Check:

   SUM(A1, B1) needs commas [OK]

Hint: Separate arguments with commas in functions [OK]

Common Mistakes:

• Using spaces instead of commas
• Removing = sign
• Using lowercase cell references (not required)

5. You want to write a formula in cell C1 that sums the values from A1 to A5 and then multiplies the result by the value in B1. Which formula should you enter in C1?

hard

A. =SUM(A1:A5)*B1

B. =SUM(A1:A5+B1)

C. =SUM(A1:A5)*SUM(B1)

D. =SUM(A1:A5*B1)

Solution

1. Step 1: Sum the range A1 to A5

   Use SUM(A1:A5) to add all values from A1 through A5.

2. Step 2: Multiply the sum by B1

   Multiply the sum by B1 using *B1 outside the SUM function.

3. Final Answer:

   =SUM(A1:A5)*B1 -> Option A

4. Quick Check:

   Sum range then multiply by B1 [OK]

Hint: Use colon for ranges and * for multiplication [OK]

Common Mistakes:

• Adding B1 inside SUM instead of multiplying
• Multiplying inside SUM causing wrong calculation
• Using multiple SUM functions unnecessarily