onFormSubmit running multiple times

2019-07-27 04:23发布

问题:

I have a maintenance workflow set up using Google Forms. There are 2 forms that submit to the same spreadsheet. When a "request" form is submitted a random 6 digit code is generated as well as a pre filled url to the "assign" form. Then an email is sent with this info. The recipient then can open the email, click on the pre filled url and assign the work. When this form is submitted this information is then emailed to the worker assigned.

This all works HOWEVER it appears that the "onFormSubmit" trigger is being "triggered" multiple times. This is causing multiple emails to be sent and the 6 digit code being changed.

I searched stackoverflow and a solution that I found was to use "LockService". However, this doesn't seem to be working.

I have installed the trigger to run a "director" script to determine which sheet was updated ("request" or "assign") and then run the appropriate code.

I know I am missing something because I have used the add-on 'formMule' for something similar and it doesn't send multiple emails.

The only thing I can think that is causing the issue is the fact that I have 2 forms submitting to the same spreadsheet, but I cannot see why that would be a problem.

The code for a request can be found below.

Thanks in advance for any guidance or suggestions.

function director(e){
  var frm = e.range.getSheet().getName();
  switch(frm){
    case "Requests":
      doRequest();
      break;
    case "Assignments":
      doAssign();
      break;
  }
  Logger.log(frm);
}

function doRequest(){
  // Get a script lock, because we're about to modify a shared resource.
  var lock = LockService.getScriptLock();
  // Wait for up to 30 seconds for other processes to finish.
  lock.waitLock(30000);

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requests');
  var theRange = ss.getRange(ss.getLastRow(), 1, 1, ss.getLastColumn());
  var theInfo = theRange.getValues();

  theInfo[0][5]=theID();
  theRange.offset(0, 5, 1, 1).setValue(theInfo[0][5]);
  theInfo[0][6] = preFilledURL(theInfo,frmAssignId);
  theRange.offset(0, 6, 1, 1).setValue(theInfo[0][6]);

  var rslt = sendEmail(theInfo);
  if(rslt){Logger.log("Got done with request")};

  lock.releaseLock();
}

/**
 * Creates a pre filled URL 
 *
 * @param {obj} theData Array of the data
 * @param {string} formID The ID of the form
 * @return {string}  Returns the URL
 */

function preFilledURL(theData, formID){

  //get the form and form items
  var form = FormApp.openById(formID)
  var items = form.getItems();

  var resp = form.createResponse();
  resp.withItemResponse(items[0].asTextItem().createResponse(theData[0][5]));
  resp.withItemResponse(items[1].asTextItem().createResponse(theData[0][1]));
  resp.withItemResponse(items[2].asTextItem().createResponse(theData[0][2]));
  resp.withItemResponse(items[3].asParagraphTextItem().createResponse(theData[0][3]));

  return resp.toPrefilledUrl();  
}

function sendEmail(theInfo) {
  var html = "<b>A maintenatnce request has been submitted</b><hr/>Details:<br>Building: " + theInfo[0][1] ;
  html+= "<br>Room or Location: " + theInfo[0][2] + "<br>Issue: " + theInfo[0][3] + "<br>Timestamp: " + theInfo[0][0] + "<br>RCN: " + theInfo[0][5];
  html+= "<br><br><a href=\"" + theInfo[0][6] + "\">Assign Request</a>";

  Logger.log(html);
  GmailApp.sendEmail(recipient, "Maintenance Request - " + theInfo[0][5],"The message",{htmlBody: html});
 // MailApp.sendEmail(recipient, "Maintenance Request - " + theInfo[0][5],"The message",{htmlBody: html});

  return true;
}


/**
* Random string of 6 characters all Caps and Numbers
*
*/
function theID(){
  var randomArray = new Array();
  var chars = "ABCDEFGHIJKLMNOPQRSTUVWXTZ0123456789";
  var string_length = 6;

  var randomstring = '';

  for (var i=0; i<string_length; i++) {
    var rnum = Math.floor(Math.random() * chars.length);
    randomstring += chars.substring(rnum,rnum+1);
  }

  return randomstring;
}