Get the Values Explainer

Puzzle Explainer
#1

Aim of the puzzle: Use Apps Script to access a spreadsheet in Sheets and print some of its data.

Walk through of the solution: Apps Script has custom code functions that connect to Sheets. One example is SpreadsheetApp.openById(), which works just like DocumentApp.openById(). It takes an id and gets the matching spreadsheet from Sheets.

Another is .getRange().getValues(), which selects a range of cells on a spreadsheet and returns their values as a nested array.

To complete the puzzle, change the string inside the parentheses of .getRange() from 'A3:B6’ to 'A2:B6'.

Sample code solution:

let id = '1QE4pZs8efMoHKGOEHyXGT_nPZf1b_Sw7ssFCk2J2ueo';

let sheet = SpreadsheetApp.openById(id);
let values = sheet.getRange("A2:B6").getValues();
console.log(values[0][0]);

JavaScript concepts: Variables, Strings, Nested Arrays

Apps Script concepts: SpreadsheetApp, .openById(), .getRange().getValues()

#2
#3

Hello. I still do not understand this puzzle. If it’s going to take ranges A2:B6 then why only prints out Jose’s Berets and why not Rain Gear (that is B6)?

#4

Hi. It is because the item defined to be accessed in the print is [0] [0]. If item [1] [0] was accessed, Zola's Colas would be printed, if it were [0] [1], it would be Hats.
The code to print “Rain Gear” is console.log(values[4][1]).
Hope it helps!

1 Like