Google表格.确认电子邮件脚本.使用编辑网址 [英] Google Form. Confirmation Email Script. With edit url

问题描述

我在Google脚本中有两个有效的触发功能，当表单响应电子表格获得新的提交时会触发这些功能.一个将编辑您的提交" URL插入电子表格.另一个查询响应的电子邮件，并向他们发送确认.

I have two working trigger functions in Google Script that fire when a form response spreadsheet gets a new submission. One inserts the "edit your submission" url into the spreadsheet. The other looks up the response's email and sends them a confirmation.

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

What I'm having a hard time understanding is how to populate the url first, and then send an email containing that 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();
    }

在表单提交时，搜索标题为客户电子邮件"的列，并向他们发送格式化的电子邮件.

On form submit, searches for column titled "Clients Email", and sends a formatted email to them.

    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列.

Separate function. Looks up form and applies the edit URL to the 26th column.

    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);  
    }

推荐答案

当程序员依赖于同一事件时，不会直接控制程序员调用触发函数的顺序.

Programmers are provided no direct control over the order that trigger functions will be invoked, when they are dependent on the same event.

不过，根据您的情况，有两种选择.

In your situation though, there are a couple of options available.

1. 仅对事件使用一个触发函数，并使其按所需顺序调用当前函数.(或者，将 assignEditUrls()设置为唯一的触发函数，并在完成任务后调用 SendConfirmationMail().

1. Use only one trigger function for the event, and have it invoke the current functions in the order you require. (Alternatively, set assignEditUrls() up as the only trigger function, and have it call SendConfirmationMail() after completing its task.

具有 SendConfirmationMail()检查编辑URL的可用性，然后调用

Have SendConfirmationMail() check for the availability of the Edit URL, and call Utilities.sleep() if the URL isn't ready yet. (In a loop, until ready.)

这篇关于Google表格.确认电子邮件脚本.使用编辑网址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！