Protected sheets and ranges in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Understanding who can edit a protected range

You have a Google Sheet with a protected range set on cells A1:A10. Only users with edit access to the sheet can modify these cells. If a user has view-only access to the sheet, what happens when they try to edit a cell in the protected range?

A They can edit the cells without any restrictions.

B They can edit the cells only if they have a Google account.

C They can edit the cells but changes are automatically reverted.

D They receive an error message and cannot edit the cells.

💡 Hint

Think about what 'view-only' access means for editing protected ranges.

Users with view-only access cannot edit any cells, including protected ranges. They will get an error if they try.

📊 Formula Result

intermediate

Effect of protection on formula recalculation

You have a protected sheet in Google Sheets. Cell B1 contains the formula =SUM(A1:A5). If a user without edit permission tries to change any value in A1:A5, what will be the value shown in B1?

A The sum including the user's attempted changes.

B An error message in B1 indicating protected range violation.

C The sum of the original values in A1:A5 before the attempted change.

D The cell B1 will be blank.

💡 Hint

Consider if the user can change values in A1:A5 without permission.

Since the user cannot edit protected cells, the values remain unchanged, so the formula sums the original values.

❓ Function Choice

advanced

Choosing the correct method to protect a sheet programmatically

You want to protect an entire sheet using Google Apps Script so that only specific users can edit it. Which method correctly applies protection to the sheet?

A sheet.protect().addEditors(['[e-mail]']).setWarningOnly(false);

B sheet.setProtection().setEditors(['[e-mail]']);

C sheet.protectRange().setEditors(['[e-mail]']);

D sheet.lock().setEditors(['[e-mail]']);

💡 Hint

Look for the official method to protect sheets in Google Apps Script.

The protect() method is used to protect sheets or ranges. Then addEditors() defines who can edit.

❓ data_analysis

advanced

Analyzing protection impact on collaborative editing

A Google Sheet has two ranges protected: Range1 (A1:A10) editable only by User1, and Range2 (B1:B10) editable only by User2. User3 has edit access to the sheet but is not listed as an editor for either range. What happens when User3 tries to edit cells in Range1 and Range2?

A User3 can edit both ranges because they have edit access to the sheet.

B User3 cannot edit either range and will get an error message.

C User3 can edit Range1 but not Range2.

D User3 can edit Range2 but not Range1.

💡 Hint

Think about how range-specific protection overrides sheet-level edit access.

Range protection restricts editing to specified users only, regardless of sheet-level edit rights.

🧠 Conceptual

expert

Understanding the difference between 'Warning Only' and 'Restrict who can edit this range'

In Google Sheets, when setting protection on a range, you can choose 'Show a warning when editing this range' or 'Restrict who can edit this range'. What is the key difference in behavior between these two options?

A 'Warning Only' allows anyone to edit after seeing a warning; 'Restrict' blocks unauthorized edits completely.

B 'Warning Only' restricts edits to owners; 'Restrict' allows all editors to edit.

C Both options block unauthorized edits but differ in notification style.

D 'Warning Only' blocks edits; 'Restrict' only shows a warning but allows edits.

💡 Hint

Consider what happens after a user sees the warning in 'Warning Only' mode.

'Warning Only' shows a message but lets users proceed with editing. 'Restrict' prevents unauthorized users from editing at all.