谷歌表单。确认电子邮件脚本。带有编辑url

Question

我在Google脚本中有两个工作触发函数，当表单响应电子表格获得新的提交时触发。其中一个在电子表格中插入“编辑您的提交”url。另一个查找回复的电子邮件，并向他们发送确认。

我很难理解的是如何首先填充url ，然后发送包含该url的电子邮件。

(_Google脚本与浏览器:_中的js不同)

设置触发器

function Initialize() {

        var triggers = ScriptApp.getScriptTriggers();

        for (var i in triggers) {
            ScriptApp.deleteTrigger(triggers[i]);
        }

        ScriptApp.newTrigger("SendConfirmationMail")
            .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
            .onFormSubmit()
            .create();

           assignEditUrls();
    }

在表单提交时，搜索标题为“客户电子邮件”的列，并将格式化的电子邮件发送给他们。

    function SendConfirmationMail(e) {

        try {

            var ss, cc, sendername, subject, columns;
            var message, value, textbody, sender;
            var url;

            // This is your email address and you will be in the CC
            cc = Session.getActiveUser().getEmail();

            // This will show up as the sender's name
            sendername = "XXXX";

            // Optional but change the following variable
            // to have a custom subject for Google Docs emails
            subject = "Form Complete: Mobile App - Client Questionnaire";



            // This is the body of the auto-reply
            message = "Confirmation text here.<br><br>Thanks!<br><br>";

            ss = SpreadsheetApp.getActiveSheet();
            columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

            // This is the submitter's email address
            sender = e.namedValues["Clients Email"].toString();

            // Only include form values that are not blank
            for ( var keys in columns ) {
                var key = columns[keys];
                if ( e.namedValues[key] ) {
                    message += key + ' :: '+ e.namedValues[key] + "<br />"; 
                }
            }

            textbody = message.replace("<br>", "\n\n");

            GmailApp.sendEmail(sender, subject, textbody, 
                                {cc: cc, name: sendername, htmlBody: message});

        } catch (e) {
            Logger.log(e.toString());
        }

    }

单独的功能。查找表单并将编辑URL应用于第26列。

    function assignEditUrls() {
      var form = FormApp.openById('10BVYipGhDa_AthabHE-xxxxxx-hg');
        //enter form ID here

      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');

        //Change the sheet name as appropriate
      var data = sheet.getDataRange().getValues();
      var urlCol = 26; // column number where URL's should be populated; A = 1, B = 2 etc
      var responses = form.getResponses();
      var timestamps = [], urls = [], resultUrls = [];

      for (var i = 0; i < responses.length; i++) {
        timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
        urls.push(responses[i].getEditResponseUrl());
      }
      for (var j = 1; j < data.length; j++) {

        resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
      }
      sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
    }

Answer 1

程序员不能直接控制触发函数在依赖同一事件时将被调用的顺序。

但是，在您的情况下，有几个选项可用。

1. 只对事件使用一个触发器函数，并让它按照所需的顺序调用当前函数。(或者，将assignEditUrls()设置为唯一的触发器函数，并让它在完成任务后调用SendConfirmationMail()。
2. 让SendConfirmationMail()检查编辑URL的可用性，如果Utilities.sleep()还没有准备好，就调用Utilities.sleep()。(在循环中，直到准备就绪。)