google-apps-script - 比较电子表格 1 和电子表格 2，然后发送电子邮件问题

问题描述

谁能帮我。如果我不能正确解释一切，我想说抱歉，因为英语不是我的第一语言。我正在做的是创建一个谷歌表单。这就是例子。

第一部分：

谷歌表格 1 | 电子表格 1：在谷歌表单中，输入信息后。电子表格更新了每一行。IT 人员将根据报告解决问题。一旦 IT 人员完成，他将使用 Google Form 2。

第二部分：

谷歌表格 2 | 电子表格 2：IT 人员使用第二个 Google 表单关闭工单或待处理工单。如果 IT 人员提交表格，它会向电子表格 1 中的电子邮件地址发送一封电子邮件，并说工单 ID（在电子表格 1 和 2 上找到的列），说工单状态（在电子表格 2 上找到的列），说在电子邮件消息中报告问题详细信息（电子表格 1 中的列）。怎么做？

这是我制作的代码。

function myFunction(e) {

// declare variables
   var Timestamp = e.values[0];
   var TicketID = e.values[1];
   var Email = e.values[2];
   var Status = e.values[3];

var ss = SpreadsheetApp.openById(INSERT SPREADSHEET ID 1 INSIDE);
var DetailsCol = 6; // column number of Report Problem Details from Spreadsheet 1

/* This is the part I not know how to get. I want to compare Ticket ID 
from Spreadsheet 1 to Spreadsheet 2, and if they’re same, then it returns the value of 
“Report Problem Details” that is associate to the Ticket ID match, 
and save it to Spreadsheet 2 column F, then send email with this value.*/
var ss1value = ss.getDataRange().getValues(); 
  for(var i = 0; i<data.length;i++){
    if(data[i][1] == TicketID){  //start 0 index
        return i+1;
    }
  }
ss1value.getRange(i, 5).setValue(Report Problem Details);

// Email Send
   var Subject = "Ticket Status";
   var Message = 
 '<!DOCTYPE html> ' +
 "<html><head>" +
    "<table border='1' width='300'>" +  
    "<tr><td>Case ID</td><td>" + TicketID +
    "<tr><td>Status</td><td>" + Status +
    "<tr><td>Problem Description</td><td>" + 'Details' +
    "</table>" +
MailApp.sendEmail(Email, Subject, "", {htmlBody: Message});

}

标签： google-apps-scriptgoogle-forms

解决方案

您正在使用 Google Forms 和 Google Sheets 构建支持票证系统。您在匹配两个电子表格中的票号时遇到问题。

提供以下脚本是为了简化票证的管理，因此不需要“匹配”。有两个脚本：

so5894534201(e): 应该作为可安装的 onFormSubmit 触发器安装。
onEdit(e)：执行 onEdit 的脚本。

so5894534201(e)
一般的方法是用户提交更新表单响应表的表单。onFormSubmit 捕获响应并将其添加到“支持”表中。它还添加了一个额外的字段“状态”，它分配了一个数据验证规则（选项是“打开”、“待定”和“关闭”），并将初始值设置为“打开”。

onEdit(e)
这会监视支持表上状态的变化。预期的操作是向提交票证的人发送一封电子邮件。我把这个留给你完成。

function so5894534201(e) {

  // to be installaed as an Installable onFormSubmit trigger

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // reference the sheets
  var responsesheetname = "Form Responses 1";
  var response = ss.getSheetByName(responsesheetname);
  var supportsheetname = "support";
  var support = ss.getSheetByName(supportsheetname);
  var settingssheetname = "settings";
  var settings = ss.getSheetByName(settingssheetname);

  // event objects
  //Logger.log(JSON.stringify(e)); // DEBUG

  // create temporary array to hold response values
  var responsedata = [];
  responsedata.push(e.values[0]);
  responsedata.push(e.values[1]);
  responsedata.push(e.values[2]);
  responsedata.push(e.values[3]);
  responsedata.push(e.values[4]);
  responsedata.push(e.values[5]);

  // get last row on Support
  var Avals = support.getRange("A1:A").getValues();
  var supportLR = Avals.filter(String).length;
  // Logger.log("DEBUG: the last row in support = "+supportLR)

  // get the range to the next support response
  var supportRange = support.getRange(supportLR+1,1,1,6);
  // Logger.log("DEBUG: the suppport target range = "+supportRange.getA1Notation())

  // build status cell on Support
  var cell = support.getRange(+supportLR+1,7);
  // Logger.log("DEBUG: the status cell range is "+cell.getA1Notation());

  // build the data validation rule
  var settingsRange = settings.getRange("A2:A4");
  var statusrule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(settingsRange, true)
    .build();

  //set the rule
  cell.setDataValidation(statusrule);

  // Update the initial Status value 
  cell.setValue("Open");

  // Update the support range with the latest response
  supportRange.setValues([responsedata]);

}

function onEdit(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var supportsheetname = "support";
  var support = ss.getSheetByName(supportsheetname);

  // get the event objects
  //Logger.log(JSON.stringify(e)); // DEBUG
  var column = e.range.getColumn();
  var sheet = e.range.getSheet().getName();
  var newStatus = e.value; // new status value
  var oldStatus = e. oldValue // old value

  // if column = column 7 (G) && sheet = "support" && newstatus isn't Open (i.e. isn't the new support response
  if(column == 7 && sheet == supportsheetname && newStatus != "Open"){
    // do stuff
    //get the data
    var row = e.range.getRow();
    var supportdataRange = support.getRange(row,1,1,7);
    Logger.log("support data range = "+supportdataRange.getA1Notation());
    Logger.log("Edited row = "+row);
    var supportdata = supportdataRange.getValues();
    var timeStamp =  supportdata[0][0];
    var ticketNumber = supportdata[0][1];
    Logger.log("Error logged:"+timeStamp+", Ticket Number:"+ticketNumber)
    var name =  supportdata[0][2];
    var phone = supportdata[0][3];
    var email = supportdata[0][4];
    var problem =   supportdata[0][5];
    Logger.log("Name:"+name+", Phone:"+phone+", Email:"+email+", Problem:"+problem);

    // Logger.log("DEBUG: onedit was triggered the old status was "+oldStatus+", and the new status is "+newStatus)
    // send an email with this information
  }
else
  {
    // nothing to do because status wasn't chnaged from "Open"
    Logger.log("DEBUG: do nothing - status wasn't changed FROM 'Open'")

  }
}

支持表

设置表

google-apps-script - 比较电子表格 1 和电子表格 2，然后发送电子邮件问题

问题描述

解决方案

推荐阅读