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

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

• so5894534201(e)：应作为Installable 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,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,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 Forms和Google Sheets构建支持票证系统。

• 用户将表​​格1提交给电子表格1-票证号是唯一值；
• IT支持人员对此表格进行回复；
• IT支持人员通过将表格＃2提交给Spreadsheet＃2来更新自己的记录；
• 当IT支持通过“打开”以外的其他方式更新状态（在Spreadsheet＃2上）时，应向用户发送电子邮件；
• 由于Spreadsheet＃2在表单响应中未包含所有信息，因此有必要将唯一的票证编号（来自Spreadsheet＃2）与Spreadsheet＃1上的票证编号进行匹配，以便检索yser名称，电子邮件和其他相关数据。

在两个电子表格之间匹配票证编号时遇到问题。

以下脚本演示了将值与数组匹配的方法。关键方面是：

• 此脚本必须从Spreadsheet＃2运行
• onEdit(e)：脚本必须在onEdit触发器上，以便它检测支持更新状态（此逻辑不包含在脚本中）
• openById()：请注意如何访问Spreadsheet＃1
• var formValues = formRange.getValues();：从响应表中获取所有值
• var formTickets = formValues.map(function(e){return e[1];});：使用javascript map方法创建仅凭票号的临时数组
• var TickedID = support.getRange(editedrow,2).getValue();：利用Event Objects
从Spreadsheet＃1获取票证号
• var result = formTickets.indexOf(TicketID);// result is zero-based.：在表单故障单编号的数组中找到支持故障单编号的匹配项；这使用了JavaScript indexOf方法。值-1表示不匹配，否则值为数组中票证编号基于zer0的索引。
• var email = formValues[result][4];：一旦知道“结果”值，就可以从“表单值”中访问该票证的其余详细信息。

function onEdit(e){

  // Spreadsheet#2 containing the IT support logs 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var supportsheetname = "support";
  var support = ss.getSheetByName(supportsheetname);


  // Spreadsheet #1 containing the form responses
  var s2id = "<insert spreadsheetID here>";
  var s2 = SpreadsheetApp.openById(s2id);
  var formsheetname = "Form Responses 1";
  var form = s2.getSheetByName(formsheetname);

  // get data from forms
  var Avals = form.getRange("A1:A").getValues();
  var formLR = Avals.filter(String).length;
  var formRange = form.getRange(2,formLR-1,6);
  var formValues = formRange.getValues();
  //get the ticket Numbers in a temporary array
  var formTickets = formValues.map(function(e){return e[1];});//[[e],[e],[e]]=>[e,e,e]
  // Logger.log(formTickets);//DEBUG

  // get the row that was edited by the IT support
  var editedrow = e.range.getRow();
  var TicketID = support.getRange(editedrow,2).getValue();
  Logger.log("the Ticket ID is "+TicketID);

  // find the ticket number in the responses
  var result = formTickets.indexOf(TicketID);// result is zero-based.

  if (result != -1){
    // Logger.log("DEBUG: the result is "+result); // result is zero-based

      // declare variables
      var Status = e.value; // new status value
      var Timestamp =  formValues[result][0];
      var Name =  formValues[result][2];
      var Phone = formValues[result][3];
      var Email = formValues[result][4];
      var Problem =   formValues[result][5];
      Logger.log("Ticket ID:"+TicketID+",Timestamp:"+Timestamp+",Status:"+Status);
      Logger.log("Name: "+Name+",Phone:"+Phone+",email:"+Email+",problem:"+Problem);
      // send an email using this information

  }
}

更新

使用更常规的表单值循环进行比较，以查找与支持票证的匹配项。

function onEdit(e){

  // example of a loop compared to map/indexOf
  //so5894534203()

  // Spreadsheet#2 containing the IT support logs 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var supportsheetname = "support";
  var support = ss.getSheetByName(supportsheetname);

  // Spreadsheet #1 containing the form responses
  var s2id = "1S9CozVBncgDGceqhnIC6bwK15kDAwRrPu9ObooPgKPc";
  var s2 = SpreadsheetApp.openById(s2id);
  var formsheetname = "Form Responses 1";
  var form = s2.getSheetByName(formsheetname);

  // get data from forms
  var Avals = form.getRange("A1:A").getValues();
  var formLR = Avals.filter(String).length;
  var formRange = form.getRange(2,6);
  var formValues = formRange.getValues();

  // get the row that was edited by the IT support
  var editedrow = e.range.getRow();
  var TicketID = support.getRange(editedrow,2).getValue();
  // Logger.log("DEBUG: the edited ticket is "+TicketID);

  for (var i=0;i<formValues.length;i++){
    // Logger.log("DEBUG: i:"+i+",ticket#:"+formValues[i][1])
    if(TicketID  === formValues[i][1]){
      // Logger.log("DEBUG: i:"+i+" = match"); // zero based plus allow for header row

      // declare variables
      var Status = e.value; // new status value
      var Timestamp = formValues[i][0];
      var Name = formValues[i][2];
      var Phone = formValues[i][3];
      var Email = formValues[i][4];
      var Problem = formValues[i][5];
      Logger.log("Ticket ID:"+TicketID+",problem:"+Problem);
      // send an email using this information

    }

  }

}