Issue manipulating the Spreadsheet connected to a Google Form

Question

Sorry if this question has been answered before, I did not find any entries with this issue. I'm trying to set up a newsletter subscription service for our non-profit organisation.

I'm using a Google Form to collect user input. OnFormSubmit a script on the form is to write additional information into the connected Spreadsheet.

Problem: I am unable to call the Spreadsheet:

TypeError: Cannot call method "getActiveSheet" of null.

Do I need to initialise the Spreadsheet or must I create the Script outside of the Form?

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheets()[0]);

Is it possible to manipulate the Spreadsheet which is collecting the Form input via the Form Google Script?

This is the code behind the Google Form which is set to run onFormSubmit:

function onFormSubmit(e) { 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet() 
  var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheets()[0]); 
  var row = sheet.getLastRow(); sheet.getRange(row,4).setValue(row); 
}

Answer 1

You need to paste the code in the script editor of the responses spreadsheet. You are probably using it inside Forms.