Script editor overview in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple function that logs 'Hello World' in the script editor.

Google Sheets

function sayHello() {
  Logger.[1]('Hello World');
}

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

Aprint

Bwrite

Clog

Dshow

Attempts:

3 left

💡 Hint

Common Mistakes

Using print instead of log causes an error because print is not a Logger method.

Using write or show are not valid Logger methods.

The Logger class in Google Sheets script editor uses the log method to output messages to the log.

2fill in blank

medium

Complete the code to get the active spreadsheet in the script editor.

Google Sheets

var spreadsheet = SpreadsheetApp.[1]();

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

AgetActiveSheet

BgetActiveSpreadsheet

CgetActive

DgetActiveSpreadsheet()

Attempts:

3 left

💡 Hint

Common Mistakes

Using getActiveSheet returns a sheet, not the spreadsheet.

Using getActive without Spreadsheet is invalid.

The correct method to get the active spreadsheet object is getActiveSpreadsheet(). In code, parentheses are required, but here we only fill the method name without parentheses.

3fill in blank

hard

Fix the error in the code to set the value 'Test' in cell A1 of the active sheet.

Google Sheets

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange('A1').[1]('Test');

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

Aset_val

Bsetvalue

CputValue

DsetValue

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase 'setvalue' causes a method not found error.

Using 'putValue' or 'set_val' are not valid methods.

The correct method to set a single cell's value is setValue with a capital V.

4fill in blank

hard

Fill both blanks to create a function that returns the value of cell B2 from the active sheet.

Google Sheets

function getCellValue() {
  var sheet = SpreadsheetApp.[1]();
  return sheet.getRange([2]).getValue();
}

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

AgetActiveSheet

BgetActiveSpreadsheet

C'B2'

D'A1'

Attempts:

3 left

💡 Hint

Common Mistakes

Using getActiveSpreadsheet instead of getActiveSheet returns the spreadsheet object, which does not have getRange.

Using 'A1' instead of 'B2' returns the wrong cell.

To get the active sheet, use getActiveSheet(). To get the value of cell B2, pass the string 'B2' to getRange.

5fill in blank

hard

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

Google Sheets

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

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

AgetActiveSheet

B'C3'

CsetValue

DgetActiveSpreadsheet

Attempts:

3 left

💡 Hint

Common Mistakes

Using getActiveSpreadsheet instead of getActiveSheet causes errors when calling getRange.

Using wrong cell address or method names causes runtime errors.

Use getActiveSheet() to get the sheet, 'C3' as the cell address, and setValue to set the cell's value.

Practice

1. What is the main purpose of the Script Editor in Google Sheets?

easy

A. To import data from other spreadsheets only

B. To write JavaScript code to customize and automate tasks in the spreadsheet

C. To format cells with colors and fonts

D. To create charts and graphs automatically

Solution

1. Step 1: Understand the Script Editor's role

   The Script Editor allows writing JavaScript code to customize Google Sheets.

2. Step 2: Identify the main use

   It is mainly used to automate tasks and add custom features, not just formatting or charting.

3. Final Answer:

   To write JavaScript code to customize and automate tasks in the spreadsheet -> Option B

4. Quick Check:

   Script Editor = JavaScript customization [OK]

Hint: Script Editor = write code to automate sheets [OK]

Common Mistakes:

• Thinking Script Editor is for formatting only
• Confusing it with chart tools
• Believing it only imports data

2. Which of the following is the correct way to start a function in Google Sheets Script Editor?

easy

A. function myFunction() { }

B. def myFunction() { }

C. func myFunction() { }

D. function: myFunction() { }

Solution

1. Step 1: Recall JavaScript function syntax

   Google Sheets scripts use JavaScript, where functions start with the keyword 'function'.

2. Step 2: Match the correct syntax

   Only 'function myFunction() { }' matches JavaScript syntax correctly.

3. Final Answer:

   function myFunction() { } -> Option A

4. Quick Check:

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

Hint: JavaScript functions start with 'function' keyword [OK]

Common Mistakes:

• Using Python or other language syntax
• Adding colons after function keyword
• Using incorrect keywords like 'def' or 'func'

3. Given this script in the Script Editor:

function showAlert() {
  SpreadsheetApp.getUi().alert('Hello!');
}

What happens when you run showAlert()?

medium

A. An error occurs because alert is not a valid method

B. The message 'Hello!' is printed in the console only

C. Nothing happens because the function is empty

D. A popup alert with the message 'Hello!' appears in the spreadsheet

Solution

1. Step 1: Understand the function code

   The function calls SpreadsheetApp.getUi().alert('Hello!'), which shows a popup alert in the spreadsheet UI.

2. Step 2: Identify the effect of running the function

   Running showAlert() triggers the alert popup with the message 'Hello!'.

3. Final Answer:

   A popup alert with the message 'Hello!' appears in the spreadsheet -> Option D

4. Quick Check:

   alert() shows popup message [OK]

Hint: alert() shows popup in spreadsheet UI [OK]

Common Mistakes:

• Thinking alert prints to console
• Assuming function does nothing
• Believing alert method is invalid

4. You wrote this script in the Script Editor:

function addNumbers() {
  var sum = 5 + ;
  Logger.log(sum);
}

What is the error and how do you fix it?

medium

A. Syntax error due to incomplete addition; fix by adding a number after '+'

B. Runtime error because Logger.log is not defined; fix by importing Logger

C. No error; script runs and logs 5

D. Syntax error because 'var' is not allowed; fix by removing 'var'

Solution

1. Step 1: Identify the syntax error in the addition

   The expression '5 + ;' is incomplete and causes a syntax error because a number is missing after '+'.

2. Step 2: Fix the error by completing the addition

   Add a number after '+' like '5 + 3' to fix the syntax error.

3. Final Answer:

   Syntax error due to incomplete addition; fix by adding a number after '+' -> Option A

4. Quick Check:

   Incomplete expression causes syntax error [OK]

Hint: Check for missing operands in math expressions [OK]

Common Mistakes:

• Thinking Logger.log causes error
• Assuming 'var' is invalid
• Ignoring incomplete expression

5. You want to create a custom menu in Google Sheets using the Script Editor that runs a function named processData. Which script snippet correctly adds this menu when the spreadsheet opens?

hard

A. function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Run Process')
.addToUi();
}

B. function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Run Process', processData)
.addToUi();
}

C. function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Run Process', 'processData')
.addToUi();
}

D. function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Run Process', 'processData()')
.addToUi();
}

Solution

1. Step 1: Understand how to add a custom menu

   The method addItem requires two arguments: the menu label as a string and the function name as a string without parentheses.

2. Step 2: Identify the correct syntax

   function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('Run Process', 'processData') .addToUi(); } correctly passes 'processData' as a string and chains the calls properly.

3. Final Answer:

   function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('Run Process', 'processData') .addToUi(); } -> Option C

4. Quick Check:

   addItem needs function name as string [OK]

Hint: Pass function name as string in addItem [OK]

Common Mistakes:

• Passing function reference without quotes
• Including parentheses in function name string
• Omitting second argument in addItem