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

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

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 :)

Adding to spreadsheet using Google Script Editor

1 Answers