Store and Increment a value by one on every new Google Form submission

Question

When a customer completes a form, I want a new ticket number to be created and sent in an email.

The first error:

"cannot read property "range" from undefined (line 9, file "Code")

and then, on lines 11, 13, and 21, I have some code to control a ticket counter to generate a unique ticket number each time the form is submitted. When I ran 2 test forms through the system, the ticket number did not populate and the counter variable did not change.

function onFormSubmit(e) {

  var activateSheet = SpreadsheetApp.getActiveSpreadsheet();

  SpreadsheetApp.setActiveSheet(activateSheet.getSheetByName('Form Responses 1'));

  var sheet = SpreadsheetApp.getActiveSheet();

  var row = e.range.getRowIndex(); *** line 9***

  var ticketCounter = 100; ***line 11***

  var ticketNumber = "FCB" + ticketCounter; ***line 13***

  var etrolControlsServiceEmail = "[email protected]";

  var timestamp = sheet.getRange(row, 1).getValues();

  var ticketNumberLocation = sheet.getRange(row, 2);

  ticketNumberLocation.setValue(ticketNumber); ***line 21***

  var location = sheet.getRange(row, 3).getValues();

  var reportedBy = sheet.getRange(row, 4).getValues();

  var customerEmailAddress = sheet.getRange(row, 5).getValues();

  var priority = sheet.getRange(row, 6).getValues();

  var customerIssue = sheet.getRange(row, 7).getValues();

  var subject =  "An issue has been reported at " + location + " " + timestamp + " " + "Ticket Number " + ticketNumber;

  var emailBody = "To: Envirotrol Controls Service Department " + "\nRE: Issue reported at " + location + "." + "\n\nAn issue has been reported  at " + location + ". Please see the details below:" + "\n\nTicket Number: " + ticketNumber + "\nLocation: " + location + "\nReported By: " + reportedBy + "\nPriority Level: " + priority + "\nIssue: " + customerIssue;

  MailApp.sendEmail(customerEmailAddress + ", " + etrolControlsServiceEmail, subject, emailBody);

  ticketCounter += 1};

Answer 1

This code shows the strategy for storing, retrieving and incrementing the ticketCounter value in the Spreadsheets Document Properties.

function onFormSubmit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      shFormResponses = ss.getSheetByName('Form Responses 1');

  ss.setActiveSheet(shFormResponses);

  var row = e.range.getRowIndex(),
      ticketCounter = PropertiesService
        .getDocumentProperties()
        .getProperty('ticketCounter');

  if (ticketCounter === undefined || ticketCounter === null) {
    PropertiesService.getDocumentProperties().setProperty('ticketCounter', '100');
    ticketCounter = '100';
  };

  var ticketNumber = "FCB" + ticketCounter,
      etrolControlsServiceEmail = "[email protected]",
      timestamp = shFormResponses.getRange(row, 1).getValues(),
      ticketNumberLocation = shFormResponses.getRange(row, 2);

  ticketNumberLocation.setValue(ticketNumber);

  var location = shFormResponses.getRange(row, 3).getValues();
  var reportedBy = shFormResponses.getRange(row, 4).getValues();
  var customerEmailAddress = shFormResponses.getRange(row, 5).getValues();
  var priority = shFormResponses.getRange(row, 6).getValues();
  var customerIssue = shFormResponses.getRange(row, 7).getValues();
  var subject =  "An issue has been reported at " + location + " " + 
       timestamp + " " + "Ticket Number " + ticketNumber;
  var emailBody = "To: Envirotrol Controls Service Department " + 
      "\nRE: Issue reported at " + location + "." + 
      "\n\nAn issue has been reported  at " + location + 
      ". Please see the details below:" + "\n\nTicket Number: " + 
      ticketNumber + "\nLocation: " + location + "\nReported By: " + 
      reportedBy + "\nPriority Level: " + priority + "\nIssue: " + 
      customerIssue;

  MailApp.sendEmail(customerEmailAddress + ", " + etrolControlsServiceEmail, subject, emailBody);

  //Values are stored as text in the Properties Service
  var numberAsNumber = Number(ticketCounter);
  ticketCounter = (numberAsNumber + 1).toString(); 

  PropertiesService
    .getDocumentProperties()
    .setProperty('ticketCounter', ticketCounter);
};