CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Daniel
Daniel

Reputation: 23

Can I Use getActiveRange method with OnFormSubmit Trigger?

I am using Google Apps Script in a Form, and am trying to use the getActiveRange function, but it doesn't seem to work with the OnFormSubmit trigger. (See code below)

function offSetFunction() {

  var s = SpreadsheetApp.getActiveSheet();
  var ss = s.getActiveRange();
  var rowOfProblem = ss.getRow();
  var columnOfProblem = ss.getColumn();
  var allText = s.getRange(rowOfProblem, columnOfProblem ).getValue();
  var recipient = "meEmail";
   var subject1 = ss.offset(0,-8).getValue();
   var subject9 = ss.offset(0,-7).getValue();
   var subject2 = ss.offset(0,-6).getValue();
   var subject3= ss.offset(0,-5).getValue();
   var subject4 = ss.offset(0,-4).getValue();
   var subject5 = ss.offset(0,-3).getValue();
   var subject6 = ss.offset(0,-2).getValue();
   var subject7 = ss.offset(0,-1).getValue();
   var subject8 = ss.offset(0,0).getValue();
   var body = subject1 + "," + subject9 + "," + subject3 + "," + subject4 + "," +subject5 + "," + subject6 + "," + subject7 + "," + subject8 + "," + subject9;



  MailApp.sendEmail(recipient,"the problem that" + subject1 + "submitted, was fixed", body);


}

Any help will be appreciated.

Upvotes: 2

Views: 130

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

The "On Form Submit" trigger can be used with either a Form or a spreadsheet. If you are using it with the Form, there is no active Sheet associated with that Form. But even if you put an "On Form Submit" trigger into the Sheet, the "On Form Submit" trigger can run when the sheet is closed. So, if the Sheet is closed, there is no active range. You probably need a way to identify what response is associated with the current form submission. So, you would need to do something like record the response ID to the sheet, then get the response ID of the response being submitted, get all the data, loop through it and compare the current response ID to the response ID of every row in the sheet. Trying to identify the correct row by using the timestamp is probably not advisable.

Upvotes: 1

Related Questions