Get Spreadsheet Values Explainer

Aim of the puzzle: Use Apps Script to complete the 1st of 3 steps in writing a function to automate contract creation.

Walk through of the solution: For the last topic of this course, you’ll use Apps Script to create a function that automates the process of creating contracts. You’ve already written 2 helper functions that will be used in the main function.

In this puzzle, you’ll complete the 1st of 3 steps in the main function.

The main function has been divided into 3 steps:

  1. Open a spreadsheet and get a range of values

  2. Loop through every row in the range and call makeCopy() to create and name a new copy of the contract

  3. Open each copy and use updateDoc() to replace its placeholder text with text from the spreadsheet

To complete the puzzle, complete step 1.

Inside the main() function declaration, create a variable called sheet1 that stores the spreadsheet accessed by SpreadsheetApp.openById(spreadsheetId). Then, create another variable called values that stores the values returned by sheet.getRange("A2:C5").getValues(). Finally, print values[0][0] to the console.

JavaScript concepts: Variables, Strings, Nested Arrays, Array Indexing
Apps Script concepts: SpreadsheetApp, .openById(), .getRange().getValues()

function main(id,limit) {
var sheet=SpreadsheetApp.openById(id);
return sheet.getRange(limit).getValues();
}

let values=main(spreadsheetId,‘A2:C5’);
console.log(values[0][0]);

I don’t know what I’m doing wrong here

I cannot Identify whats wrong with my code:

Look closely at the informations you wrote after the “=” sign of your “values” variable. A word has not been written right.

If you haven’t found it:

You wrote “sheets” instead of “sheet”

but it doesn’t work bruh

Send a screenshot of your code

As @Joandy pointed out, you have a variable named sheet, but have typed sheets on the next line.

Another issue that might be solving the problem is the console.log(values[0][0]). values is declared inside the function, and can’t be accessed outside of it. Try moving that line so it’s inside the function.

Hope this helps!
Ben

Hurray! I have got it correct! Thanks @Grasshopper_Ben ! By the way, thanks for @Joandy too!

2 Likes

What did I do wrong I am stuck.

Your console.log(values[0][0]) must be inside the main function declaration.

Oh thanks it worked finally.

1 Like


I tried it but it said the starter code was modified and to reset it.

Remove the return statement in the main function declaration and remove the values variable you wrote.

After that, write let values = sheet.getRange('A2:C5').getValues() and then below that write console.log(values[0][0])(this two lines of code must be inside the main function declaration’s code block({ }).

Here’s a sheet which I made with Google Sheets:

I made it myself again! :wink:

Thanks,
Pummarin :slightly_smiling_face:

2 Likes

I added that, but it’s now telling me to reset.

Reset it and do it again.

Hey there, sorry the feedback message is unhelpful! It looks like the function call was removed. Just add main() at the very bottom of the code, like this:

function main() {
  let sheet = SpreadsheetApp.openById(spreadsheetId);
  let values = sheet.getRange("A2:C5").getValues();
  console.log(values[0][0]);
}

main();

Thanks Ben, that really helped! It fixed it!