Why Apps Script automates Google Sheets - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to get the active spreadsheet in Apps Script.

Google Sheets

var sheet = SpreadsheetApp.[1]();

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

AgetActiveSheet

BopenById

CopenByUrl

DgetActiveSpreadsheet

Attempts:

3 left

💡 Hint

Common Mistakes

Using getActiveSheet() returns a sheet, not the whole spreadsheet.

openById requires a spreadsheet ID parameter.

The method getActiveSpreadsheet() returns the spreadsheet the script is bound to or currently active.

2fill in blank

medium

Complete the code to get the value of cell A1 in the active sheet.

Google Sheets

var value = sheet.getRange('[1]').getValue();

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

AB2

BA1

CC3

DD4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong cell reference like B2 or C3.

Forgetting quotes around the cell reference.

The cell A1 is the top-left cell in the sheet, so to get its value, use getRange('A1').

3fill in blank

hard

Fix the error in the code to set the value 'Hello' in cell B2.

Google Sheets

sheet.getRange('B2').[1]('Hello');

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

AsetValue

BgetValue

CsetValues

DgetValues

Attempts:

3 left

💡 Hint

Common Mistakes

Using getValue() which reads a value instead of setting it.

Using setValues() which expects an array of values.

To set a single cell's value, use setValue(). The others are for getting values or setting multiple cells.

4fill in blank

hard

Fill both blanks to create a function that logs the value of cell C3.

Google Sheets

function logCell() {
  var sheet = SpreadsheetApp.[1]();
  var value = sheet.getRange('[2]').getValue();
  Logger.log(value);
}

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

AgetActiveSpreadsheet

BopenById

CC3

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Using openById without an ID parameter.

Using the wrong cell reference like A1.

The function gets the active sheet and then reads the value from cell C3 to log it.

5fill in blank

hard

Fill all three blanks to create a function that sets 'Done' in cell D4 of the active sheet.

Google Sheets

function markDone() {
  var sheet = SpreadsheetApp.[1]();
  var range = sheet.getRange('[2]');
  range.[3]('Done');
}

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

AgetActiveSpreadsheet

BD4

CsetValue

DgetActiveSheet

Attempts:

3 left

💡 Hint

Common Mistakes

Using getActiveSpreadsheet() instead of getActiveSheet().

Using getValue() instead of setValue().

The function gets the active sheet, selects cell D4, and sets its value to 'Done'.

Practice

1. Why do people use Apps Script to automate tasks in Google Sheets?

easy

A. To change the Google Sheets interface colors

B. To make Google Sheets run slower

C. To delete all data automatically

D. To save time by automating boring or repetitive tasks

Solution

1. Step 1: Understand the purpose of Apps Script

   Apps Script is designed to automate tasks that are boring or repetitive in Google Sheets.

2. Step 2: Identify the benefit of automation

   Automation saves time and effort by letting the computer do the work instead of doing it manually.

3. Final Answer:

   To save time by automating boring or repetitive tasks -> Option D

4. Quick Check:

   Automation = Save time [OK]

Hint: Think about why automation helps daily work [OK]

Common Mistakes:

• Confusing automation with changing colors
• Thinking it slows down Sheets
• Believing it deletes data automatically

2. Which of these is the correct way to start a function in Apps Script for Google Sheets?

easy

A. function myFunction() {

B. def myFunction():

C. func myFunction() {

D. function: myFunction()

Solution

1. Step 1: Recall Apps Script syntax

   Apps Script uses JavaScript syntax, where functions start with the keyword 'function' followed by the name and parentheses.

2. Step 2: Compare options

   function myFunction() { matches JavaScript syntax. The other options use syntax from other languages or are invalid.

3. Final Answer:

   function myFunction() { -> Option A

4. Quick Check:

   JavaScript function syntax = function name() { [OK]

Hint: Remember Apps Script uses JavaScript syntax [OK]

Common Mistakes:

• Using Python or other language syntax
• Adding colons after function name
• Missing parentheses or braces

3. What will this Apps Script code do when run in Google Sheets?

function fillCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1:A3').setValue('Hello');
}

medium

A. Fill only cell A1 with 'Hello', leave others blank

B. Fill cells A1, A2, and A3 with the word 'Hello'

C. Cause an error because setValue needs a number

D. Clear the contents of cells A1 to A3

Solution

1. Step 1: Understand the code actions

   The code gets the active sheet and selects the range A1 to A3, then sets the value 'Hello' to that range.

2. Step 2: Know setValue behavior on ranges

   setValue sets the same value to all cells in the range, so A1, A2, and A3 will all have 'Hello'.

3. Final Answer:

   Fill cells A1, A2, and A3 with the word 'Hello' -> Option B

4. Quick Check:

   setValue on range = same value in all cells [OK]

Hint: setValue fills all cells in the selected range [OK]

Common Mistakes:

• Thinking only the first cell is filled
• Assuming setValue only accepts numbers
• Confusing setValue with clearContent

4. Identify the error in this Apps Script code snippet:

function sendEmail() {
  MailApp.sendEmail('[e-mail]', 'Subject', 'Body text')
}

medium

A. Missing parentheses after function name

B. MailApp is not a valid service in Apps Script

C. Missing semicolon at the end of the sendEmail line

D. Function name cannot be sendEmail

Solution

1. Step 1: Check syntax for JavaScript in Apps Script

   JavaScript statements should end with a semicolon; the sendEmail line is missing it.

2. Step 2: Verify other parts

   MailApp is a valid service, function name is allowed, and parentheses are present.

3. Final Answer:

   Missing semicolon at the end of the sendEmail line -> Option C

4. Quick Check:

   JavaScript lines end with semicolon [OK]

Hint: Check for missing semicolons in JavaScript code [OK]

Common Mistakes:

• Thinking MailApp is invalid
• Believing function names are restricted
• Ignoring missing semicolons

5. You want to automate sending a weekly report from Google Sheets using Apps Script. Which approach best describes how Apps Script helps?

hard

A. Write a function to gather data, format it, and send email; then set a time trigger to run weekly

B. Manually copy data and send emails every week without code

C. Use Apps Script only to change cell colors weekly

D. Write a function that deletes all data weekly to save space

Solution

1. Step 1: Understand automation goals

   Automating a weekly report means collecting data, formatting it, and sending it automatically.

2. Step 2: Use Apps Script features

   Apps Script can write functions to do these tasks and use time triggers to run them weekly without manual work.

3. Final Answer:

   Write a function to gather data, format it, and send email; then set a time trigger to run weekly -> Option A

4. Quick Check:

   Automation + time trigger = weekly report sent [OK]

Hint: Combine functions with triggers for scheduled automation [OK]

Common Mistakes:

• Thinking manual work is automation
• Using Apps Script only for formatting colors
• Deleting data instead of sending reports