Adding to spreadsheet using Google Script Editor

Question

I am writing a google script on top of a spreadsheet based on a google form about Auctioning used cars. My teacher gave me these instructions:

In the spreadsheet add a google script with a function that look for the highest bid and update the Google form field Function 1: Retrieve all the values in the bid column and find the highest value using a loop. Function 2: Open the Google Form and update the form text field (e.g. Current highest bid is $xxxx, please put in your bid). Where xxxx is the highest bid. (reference: https://developers.google.com/apps-script/reference/forms/) Create a trigger in the script that will trigger the above functions and update the Google form.

And here is my code:

function myFunction(e) {
  var ss = SpreadsheetApp.getActive().getSheetByName('Used Car Auction')
  var form = FormApp.openById('1D0XE9womWv2T_rFYTtc9enQgIhCkxaK8SErxNkTh7NE')
  ScriptApp.newTrigger('onFormSubmit')
  .forForm(form)
  .onFormSubmit()
  .create();
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
  var bid = values [88000]
  var highest = Math.max(bid).toString();
  formsetTitle('Used car auction:' + 'The highest bid is:' + highest)
  .setDescription ('Description of Form')
  .setConfirmationMessage('Thanks for bidding!')
  .setAllowResponseEdits(false)
  .setAcceptingResponses(true);
  }

Google says:

TypeError: Cannot call method "getSheetByName" of null.

What am I doing wrong? I've just coded for five days, and this is confusing me.

Other than that, am I on the right track, as per the instructions above?

It's triggered when a form submits but at that point there is no spreadsheet open / active. So you'll want to specify the ID of the spreadsheet and open it through openById(id) — Casper
– Casper, Commented Jun 24, 2017 at 10:10

Parag Jadhav · Accepted Answer · 2017-06-24 15:54:23Z

1

There are some fundamental errors in your code. To write values to a worksheet in Google Spreadsheet you'll have to follow certain steps.

Step-1: Get Spreadsheet Object.

e.g. var spreadsheetObject = SpreadsheetApp.getActiveSpreadsheet(); -> If you are using bounded script.

OR e.g. var spreadsheetObject = SpreadsheetApp.openById(<SPREADSHEET_ID>); -> If you are using standalone script.

Step-2: Get Worksheet Object.

e.g. var worksheetObject = spreadsheetObject.getSheetByName(<NAME_OF_WORKSHEET>);

You are getting this TypeError: Cannot call method "getSheetByName" of null error because you are trying to open a worksheet without getting its spreadsheet object.

Hope this helps :)

answered Jun 24, 2017 at 15:54

Parag Jadhav

1,8992 gold badges26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding to spreadsheet using Google Script Editor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related