Script editor overview in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

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

The Script Editor in Google Sheets allows you to:

A Sort and filter data within the spreadsheet

B Change the font and color of cells in the spreadsheet

C Create charts and graphs from your data

D Write and run custom scripts to automate tasks and add new features

💡 Hint

Think about what scripting means and how it can help with repetitive tasks.

The Script Editor is used to write custom code (scripts) that can automate tasks or add new functions to your Google Sheets. It is not for formatting or chart creation.

📊 Formula Result

intermediate

What will this simple script output in the Logger?

Consider this Google Apps Script code run from the Script Editor:

function logSum() {
  var a = 5;
  var b = 7;
  Logger.log(a + b);
}

What will appear in the Logs after running logSum()?

A 12

B 57

C a + b

D Error: Logger is undefined

💡 Hint

Logger.log prints the value of the expression inside the parentheses.

The script adds 5 and 7, resulting in 12, which is logged. It does not concatenate as strings.

❓ Function Choice

advanced

Which function correctly gets the active spreadsheet in Google Sheets Script Editor?

You want to write a script that works on the currently open spreadsheet. Which function should you use?

A SpreadsheetApp.getActiveSpreadsheet()

B SpreadsheetApp.openById()

C SpreadsheetApp.getUi()

D SpreadsheetApp.create()

💡 Hint

Think about which function returns the spreadsheet you are currently working on.

getActiveSpreadsheet() returns the spreadsheet currently open in the user's browser. The others either open a new spreadsheet or get the user interface.

🎯 Scenario

advanced

You want to create a custom menu in Google Sheets using the Script Editor. Which method adds a new menu to the spreadsheet UI?

Which method should you use to add a custom menu to the Google Sheets interface?

A SpreadsheetApp.createMenu('My Menu').addItem('Say Hello', 'myFunction')

B SpreadsheetApp.getUi().createMenu('My Menu').addItem('Say Hello', 'myFunction').addToUi()

C SpreadsheetApp.getActiveSpreadsheet().addMenu('My Menu')

D SpreadsheetApp.getUi().addMenu('My Menu')

💡 Hint

Menus are part of the user interface, so you need to get the UI first.

The correct way is to get the UI with getUi(), create the menu, add items, and then add it to the UI with addToUi(). Other options are invalid or incomplete.

❓ data_analysis

expert

What will be the value of cell A1 after running this script?

Given this script run from the Script Editor:

function writeValue() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue('Hello');
  sheet.getRange('A1').setValue(sheet.getRange('A1').getValue() + ' World');
}

What will cell A1 contain after writeValue() runs?

A World

B Hello

C Hello World

D Error: Cannot concatenate values

💡 Hint

Look at how the script reads and updates the cell value step by step.

The script first sets A1 to 'Hello'. Then it reads A1's value ('Hello'), adds ' World' to it, and writes back 'Hello World'.