I'm very unexperienced when it comes to coding, so I've used AI to help me construct a script on Google Apps that is supposed to read the input from a spreadsheet coupled to a google forms. The onFormSubmit trigger works and is connected to the form, but it seems as the e.values are null and can't be converted to an array. Maybe because the spreadsheet cant be found, but I've used the spreadsheet ID and sheet name.

I've tried this, but there seems to be no connection to the spreadsheet and thus no e.values

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.openById("9PVSu_t2YQnjhDSUeDwl4Z6ZhOJC5IIp-LVQ5ptV-Ggo").getSheetByName("Formulärsvar 1");

  Logger.log(JSON.stringify(e));
  
  var row = e.values;
  
  // Extrahera värden från raden
  var email = row[1];  // E-post
  var namnOrg = row[2];  // Namn eller organisation
  var bookingDate = new Date(row[4]);  // Datum (om det är i rätt format)
  var affiliation = row[5];  // Affiliation
  var purpose = row[6];  // Ändamål
  var other = row[7];  // Övrigt
  var today = new Date();  // Nuvarande datum

I'm very unexperienced when it comes to coding, so I've used AI to help me construct a script on Google Apps that is supposed to read the input from a spreadsheet coupled to a google forms. The onFormSubmit trigger works and is connected to the form, but it seems as the e.values are null and can't be converted to an array. Maybe because the spreadsheet cant be found, but I've used the spreadsheet ID and sheet name.

I've tried this, but there seems to be no connection to the spreadsheet and thus no e.values

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.openById("9PVSu_t2YQnjhDSUeDwl4Z6ZhOJC5IIp-LVQ5ptV-Ggo").getSheetByName("Formulärsvar 1");

  Logger.log(JSON.stringify(e));
  
  var row = e.values;
  
  // Extrahera värden från raden
  var email = row[1];  // E-post
  var namnOrg = row[2];  // Namn eller organisation
  var bookingDate = new Date(row[4]);  // Datum (om det är i rätt format)
  var affiliation = row[5];  // Affiliation
  var purpose = row[6];  // Ändamål
  var other = row[7];  // Övrigt
  var today = new Date();  // Nuvarande datum

• google-sheets
• google-apps-script

• If you trigger is for the form then there is no e.value array. Note there are two onFormSubmit triggers. – Cooper Commented Feb 6 at 14:40

1 Answer 1

Get values from the response sheet

Looking at your code, it seems that you are trying to get the values on the spreadsheet that contains the form responses and therefore, you should not be getting it from the e object rather, you should get it from the SpreadSheet object.

I modified your code to get the latest responses from the spreadsheet using SpreadSheet object stored in the sheet variable

Sample Code

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.openById("1I_loUGmx6Uq5R4SdtVqME3NxHRNnkYhv4zgQJX_oFK0").getSheetByName("Formulärsvar 1");
  var lastRow = sheet.getLastRow();
  var row = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  var email = row[1];
  var namnOrg = row[2];
  var bookingDate = new Date(row[4]);
  var affiliation = row[5];
  var purpose = row[6];
  var other = row[7];
  var today = new Date(); 
  
  Logger.log("Email: " + email);
  Logger.log("NamnOrg: " + namnOrg);
  Logger.log("Booking Date: " + bookingDate);
  Logger.log("Affiliation: " + affiliation);
  Logger.log("Purpose: " + purpose);
  Logger.log("Other: " + other);
}

Sample Output

4:59:18 PM Info Email: [email protected]

4:59:18 PM Info NamnOrg: Test nanmOrg

4:59:18 PM Info Booking Date: Thu Feb 27 2025 00:00:00 GMT+(GMT here) (TimeZone here)

4:59:18 PM Info Affiliation: Test purpose

4:59:18 PM Info Purpose: Other

4:59:18 PM Info Other: undefined

References:

SpreadSheet App