Is it possible to run consecutive functions on a single trigger (i.e. "On Form Submit") in a google apps script?

Question

I know you can only run one doGet() or doPost() per project and I am trying to do 2 things on a custom form submit. It is not possible to use google forms as the form is far too complex.

Firstly I want to send the form data to google sheets and enter it into a new row. Secondly I want to send an email to someone that displays the data the submission in a reader friendly way.

I have tried to use a promise chain for this but I am having trouble with my props and accessing parameters. I am really bashing my head into the keyboard on this one.

As a side note I am aware that apps script doesn't support promises so I have used a polyfill that someone has kindly set up.

I think I just need to pass the properties/parameters through properly? or maybe the whole thing is just wrong?

Here is what I have so far:


var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function doPost(e) {

(function () { 
  
 // this loads the es6-promises polyfill to make promise syntax available in Apps Script
 // copyright notice - https://raw.githubusercontent.com/jakearchibald/es6-promise/master/LICENSE

 var Promise,
    setTimeout = setTimeout || function (func,ms) {
      Utilities.sleep(ms);
      func();
    };

  // get the polyfill and eval

  if (!Promise) {
    var result = UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/es6-promise/3.2.1/es6- 
    promise.min.js');
    eval (result.getContentText());

    // add done for compatibility with other promise systems
    // Promise.prototype.done = Promise.prototype.done || Promise.prototype.then ;

  }
  
}());
  
var p1 = handleResponse(e);
var p2 = sendEmail(e);

Promise.all ([p1,p2,], 3000).then ( 
  function (results) {
     Logger.log("success");
  },
  function (errors) {
     Logger.log("Something went wrong: " + errors);
  }
);
  
}

function handleResponse(e) {
  
  return new Promise (function ( resolve, reject ) {

    var lock = LockService.getScriptLock();
    lock.tryLock(10 * 1000);
  
    try {
    // next set where to write the data
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    //var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];
    // loop through the header columns
    for (var i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
      
    resolve(sheet.getRange(nextRow, 1, 1, row.length).setValues([row]));

    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
} catch(error){
  // if error return this
  reject(error);
  return ContentService
  .createTextOutput(JSON.stringify({"result":"error", "error": error}))
  .setMimeType(ContentService.MimeType.JSON);
} finally {
  return lock.releaseLock();
}
  })

}

function sendEmail(e) {
  
    return new Promise (function ( resolve, reject ) {
    
      var lock = LockService.getScriptLock();
      lock.tryLock(10 * 1000);
      
      try {
        // fetching the data
        var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
        var sheet = doc.getSheetByName(SHEET_NAME);
        
        //var headRow = e.parameter.header_row || 1;
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        var nextRow = sheet.getLastRow()+1; // get next row
        var lastRow = sheet.getLastRow(); // get last row
        var formValues = sheet.getRange(36, 1, 1, sheet.getLastColumn()).getValues()[0];
        
        let emailValues = new Map();
        for (var i in formValues){
          if (formvalues[i] !== '') {
            emailValues.set(headers[i], formvalues[i]);
          }
        }
        Logger.log(emailValues);
        
        var htmlTemplate = HtmlService.createTemplateFromFile("message.html");
        htmlTemplate.row = emailValues;
        var message = htmlTemplate.evaluate().getContent();
        
        var email = "insert-email@here.com";
        var subject = "Test";
        var body = "";
        resolve(MailApp.sendEmail(email, subject, body, {
          htmlBody: message,
          name: "Send Mail Test"
        }));
        Logger.log("mikesteeletaylor@gmail.com");
        Logger.log(MailApp.getRemainingDailyQuota());
      } catch(error) {
        Logger.log("There is a problem sending the email: " + error);
        reject(error);
      } finally {
        return lock.releaseLock();
      }
    })
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

edit: After @TheMaster 's comments, this is my updated code:

var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function doPost(e) {
  
 handleResponse(e);
 sendEmail(e);
  
}

function handleResponse(e) {

    var lock = LockService.getScriptLock();
    lock.tryLock(10 * 1000);
  
    try {
    // next set where to write the data
    
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];
    // loop through the header columns
    for (var i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
     
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
} catch(error){
  // if error return this
  Logger.log(error);
  return ContentService
  .createTextOutput(JSON.stringify({"result":"error", "error": error}))
  .setMimeType(ContentService.MimeType.JSON);
} finally {
  return lock.releaseLock();
}

}

function sendEmail(e) {
    
      var lock = LockService.getScriptLock();
      lock.tryLock(10 * 1000);
      
      try {
        // fetch the data
        var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
        var sheet = doc.getSheetByName(SHEET_NAME);
        
        var headRow = e.parameter.header_row || 1;
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        var nextRow = sheet.getLastRow()+1; // get next row
        var lastRow = sheet.getLastRow(); // get last row
        var formValues = sheet.getRange(36, 1, 1, sheet.getLastColumn()).getValues()[0];
        
        let emailValues = new Map();
        for (var i in formValues){
          if (formvalues[i] !== '') {
            emailValues.set(headers[i], formvalues[i]);
          }
        }
        Logger.log(emailValues);
        
        var htmlTemplate = HtmlService.createTemplateFromFile("message.html");
        htmlTemplate.row = emailValues;
        var message = htmlTemplate.evaluate().getContent();
        
        var email = "insert-email@here.com";
        var subject = "Test";
        var body = "";
        MailApp.sendEmail(email, subject, body, {
          htmlBody: message,
          name: "Send Mail Test"
        });
        Logger.log("mikesteeletaylor@gmail.com");
        Logger.log(MailApp.getRemainingDailyQuota());
      } catch(error) {
        Logger.log("There is a problem sending the email: " + error);
      } finally {
        return lock.releaseLock();
      }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Just call two functions consecutively. There's no async in apps script urlfetch. No need for promises — TheMaster, Sep 22 '20 at 03:49
Simply means `e` is `undefined`. See https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas to find out real errors if any. Also, you need to create a new version and publish that version after a change in code — TheMaster, Sep 22 '20 at 04:29
@Rubén Sorry for the late response, yes it did work, not right away though, after I got both functions running then I was not recieving the values from the form submit. I tried everything (logging all over the place) except running my setup script again. That did the trick. Thankyou for your help. I feel stupid but it's worth it. — Mike Taylor, Sep 22 '20 at 12:51

score 0 · Accepted Answer · answered Sep 22 '20 at 03:36

0

Yes, it's possible to run "consecutive functions" in a single trigger

Super simple example:

/**
 * Function to be called by a trigger
 *
 * @param {Object} event Event object
 */
function myTrigger(event){
  myFirstFunction(event); // This could be the function that send the form data to an spreadsheet assumin that event holds the form data
  mySecondFunction(event); // This could be the function that send an email
  myThirdFunction(); // This could be another function
}

answered Sep 22 '20 at 03:36

Rubén

34,714
9
70
166

I did try that initially, but nothing happened so I assumed there was a problem with the timing of functions etc. There must have been some other error in my code I couldn't see. I just need to figure out why I am geting reference errors now and it should work I guess. – Mike Taylor Sep 22 '20 at 04:30

Is it possible to run consecutive functions on a single trigger (i.e. "On Form Submit") in a google apps script?

1 Answers1