回复反馈

编码级别：初级
时长：15 分钟
项目类型：包含自定义菜单和事件驱动型触发器的自动化操作

目标

  • 了解该解决方案的用途。
  • 了解 Apps Script 服务在解决方案中的作用。
  • 设置脚本。
  • 运行脚本。

关于此解决方案

自动创建电子邮件回复草稿，以回复 Google 表单中的反馈。此解决方案侧重于收集学生的课程反馈，但您也可以将其应用于通过 Google 表单接收反馈的任何用例。

通过 Gmail 发送的表单提交回复

工作原理

该脚本会安装一个事件驱动型触发器，每当用户提交表单时，该触发器就会运行。每次有人提交表单时，该脚本都会在 Gmail 中创建电子邮件草稿。该电子邮件会发送给提交表单的人员，其中包含表单回复和通用致谢消息。您可以在发送前修改电子邮件。

Apps Script 服务

此解决方案使用以下服务：

  • 脚本服务 - 安装在有人提交表单时触发的事件驱动型触发器。
  • 电子表格服务 - 将表单回复发送到 Gmail。
  • Gmail 服务 - 创建包含感谢消息和表单回复的电子邮件草稿。

前提条件

如需使用此示例，您需要满足以下前提条件：

  • Google 账号（Google Workspace 账号可能需要管理员批准）。
  • 一个能够访问互联网的网络浏览器。

设置脚本

点击以下按钮，即可复制回复反馈示例电子表格。此解决方案的 Apps 脚本项目已附加到电子表格中。
复制

运行脚本

  1. 依次点击表单回复工具 > 启用自动草拟回复。您可能需要刷新页面，此自定义菜单才会显示。

  2. 根据提示为脚本授权。如果 OAuth 意见征求界面显示此应用未经验证警告，请依次选择高级 > 前往 {Project Name}（不安全）以继续操作。

  3. 依次点击表单回复工具 > 启用自动草拟回复。

  4. 依次点击工具 > 管理表单 > 前往正式版表单。

  5. 填写表单，然后点击提交。

  6. 打开 Gmail 并查看草稿。您应该会看到包含表单回复的新草稿。

查看代码

如需查看此解决方案的 Apps 脚本代码，请点击下方的查看源代码：

查看源代码

Code.gs

solutions/automations/course-feedback-response/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/course-feedback-response

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Creates custom menu for user to run scripts.
 */
function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Form Reply Tool')
      .addItem('Enable auto draft replies', 'installTrigger')
      .addToUi();
}

/**
 * Installs a trigger on the Spreadsheet for when a Form response is submitted.
 */
function installTrigger() {
  ScriptApp.newTrigger('onFormSubmit')
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onFormSubmit()
      .create();
}

/**
 * Creates a draft email for every response on a form
 *
 * @param {Object} event - Form submit event
 */
function onFormSubmit(e) {
  let responses = e.namedValues;

  // parse form response data
  let timestamp = responses.Timestamp[0];
  let email = responses['Email address'][0].trim();

  // create email body
  let emailBody = createEmailBody(responses);

  // create draft email
  createDraft(timestamp, email, emailBody);
}

/**
 * Creates email body and includes feedback from Google Form.
 *
 * @param {string} responses - The form response data
 * @return {string} - The email body as an HTML string
 */
function createEmailBody(responses) {
  // parse form response data
  let name = responses.Name[0].trim();
  let industry = responses['What industry do you work in?'][0];
  let source = responses['How did you find out about this course?'][0];
  let rating = responses['On a scale of 1 - 5 how would you rate this course?'][0];
  let productFeedback = responses['What could be different to make it a 5 rating?'][0];
  let otherFeedback = responses['Any other feedback?'][0];

  // create email body
  let htmlBody = 'Hi ' + name + ',<br><br>' +
    'Thanks for responding to our course feedback questionnaire.<br><br>' +
      'It\'s really useful to us to help improve this course.<br><br>' +
        'Have a great day!<br><br>' +
          'Thanks,<br>' +
            'Course Team<br><br>' +
              '****************************************************************<br><br>' +
                '<i>Your feedback:<br><br>' +
                  'What industry do you work in?<br><br>' +
                    industry + '<br><br>' +
                      'How did you find out about this course?<br><br>' +
                        source + '<br><br>' +
                          'On a scale of 1 - 5 how would you rate this course?<br><br>' +
                            rating + '<br><br>' +
                              'What could be different to make it a 5 rating?<br><br>' +
                                productFeedback + '<br><br>' +
                                  'Any other feedback?<br><br>' +
                                    otherFeedback + '<br><br></i>';

  return htmlBody;
}

/**
 * Create a draft email with the feedback
 *
 * @param {string} timestamp Timestamp for the form response
 * @param {string} email Email address from the form response
 * @param {string} emailBody The email body as an HTML string
 */
function createDraft(timestamp, email, emailBody) {
  console.log('draft email create process started');

  // create subject line
  let subjectLine = 'Thanks for your course feedback! ' + timestamp;

  // create draft email
  GmailApp.createDraft(
      email,
      subjectLine,
      '',
      {
        htmlBody: emailBody,
      }
  );
}

贡献者

此示例由 benlcollins.com 的教育者兼 Google 开发者专家 Ben Collins 创建。

此示例由 Google 维护，并由 Google 开发者专家提供帮助。

后续步骤