javascript - 在 1 个单元格中合并为值(文本和日期时间格式为 yyyy-mm-dd hh :mm)

Question

在 google 表格中，我有 2 个单元格在行中具有临时状态。

当我在下拉列表中选择一些文本时:

0(状态) | 1 | 2 | 3 |

1. 在第一个单元格上添加当前日期时间
2. 将状态从下拉列表添加到第二个单元格

然后我想合并 (1,2) 并将其作为值粘贴到第三个单元格。

此公式返回我的预期结果:

=IF(AR1="";"";TEXT(AR1;"mm/dd/yyyy hh:mm"))&"->"&AS1

但接下来我想复制动态范围内的那些组合状态。如果单元格上只有值，那么我没有任何问题。

但是，如果我尝试复制日期或公式，并且不像公式那样粘贴，而是像值那样粘贴，那么脚本不会显示任何错误但根本不起作用。当我录制宏时它可以工作，但是当我添加一小段代码来更改事件时 - 我的代码却没有。

可能它很简单，但我尝试了所有方法来解决它，但我不明白为什么不起作用。

我是 JavaScript 新手。请给我一些建议，为什么要编写粘贴公式的代码。

function onChange(e) {


  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
 var actionCol = 17;
 var mailactionCol = 15;
 var nr_id = 12
   var sourceRange = sheet.getRange(row, actionCol).getValue();
   var mailSourceRange = sheet.getRange(row, mailactionCol).getValue();
   var nr_idRange = sheet.getRange(row, nr_id).getValue();

    //check name of sheets
    var sheetName = sheet.getName()
    if(sheetName != "My_name_sheet"){
       return                            //exit function
    }  

 /
   var currentCOL = sheet.getActiveRange().getColumnIndex();

  switch(currentCOL) 
 {

/// case is column 15
   case 15:
   //currentCOL = 15
  //id_uniq
       if(mailSourceRange == "" && nr_idRange >0) {return}
       if(mailSourceRange !== "" && nr_idRange =="")
      {
      var msr = sheet.getRange(1, 52);
      var mtr = sheet.getRange(row,12);
      msr.copyTo(mtr, {contentsOnly:true});
      }
     break;

  //case 17 - case is column 17


  case 17:

       var sourceRange1_17 = sheet.getRange(row, 17);
       var sourceRange1_19 = sheet.getRange(row, 19).getValue();
       var sourceRange1_20 = sheet.getRange(row, 20).getValue();

       var targetRange1_18 = sheet.getRange(row, 18);
       var targetRange1_19 = sheet.getRange(row, 19);
       var targetRange1_17 = sheet.getRange(row, 17);
       var targetRange1_20 = sheet.getRange(row, 20);
       var targetRange1_21 = sheet.getRange(row, 21);


     if(sourceRange != "wordInMyCell") {return} {
     if(sourceRange1_20 == "wordInMyCell") {return} 
     // if(sheet.getRange(row, 20).getValue() == "wordInMyCell") {return}

         sourceRange1_17.copyTo(targetRange1_20, {contentsOnly:true});
         targetRange1_19.setValue(new Date()).setNumberFormat('M/d/yyyy H:mm:ss'); 


/// PROBLEMS 
//// 1 not working those method to paste date-time

          targetRange1_19,copyTo(sheet.targetRange1_21, {contentsOnly: true});

  OR 
          sheet.getRange(row, 19).copyTo(sheet.getRange(row, 21), 
           SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);


/// 2 try to add formula to paste on cell not as current row but form recorder 
///    In recorder its works but when i add here to my code it doesn't.


          //       sheet.getRange(row, 18).setFormulaR1C1('=CONCATENATE(U2&" 
                    ";X2&" ";AA2&" ";AD2&" ";AG2&" ";AJ2)');

          var ss2 = SpreadsheetApp.getActiveSpreadsheet();
          var sheet2 = ss2.getSheets()[0];
            var cell2 = sheet2.getRange("U2");
            cell2.setFormula('=IF(V2="";"";W2&TEXT(V2;"mm/dd/yyyy hh:mm"))');     
  }}}

Answer 1

您选择使用 onChange() 触发器，但我想建议:

• 使用 onEdit(e) 触发器
• 通过在单个单元格(R 列)中捕获所有历史记录来简化状态历史记录。这具有简化代码的链式 react ；特别是它减少了 getValue 语句的数量

你会注意到:

• 状态值和日期的连接只需用“+”连接即可。实际上，我添加了一个分号以更好地区分状态和日期。

• 我为每一行添加了一个换行符，以便状态历史记录更易于阅读。这样做的缺点是行高会增加。您可以轻松地删除换行符和/或增加“状态历史记录”列的宽度。

• 如果您愿意，您可以保留 Status History 的存档列，但每组只需要一个列。

像往常一样，可能有几种方法可以实现这一结果。将此答案视为执行此操作的一种方式。

---

function onEdit(e) {
  // 5731586703

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "MAIN";
  var sheet = ss.getSheetByName(sheetname);

  // get a list of the event objects
  // Logger.log(JSON.stringify(e));


  // get the event source data
  var editedCell = e.range;
  var editRow = editedCell.getRow();
  var editCol = editedCell.getColumn();
  var eValue = e.value;
  var editedSheet = editedCell.getSheet().getName();
  //Logger.log("DEBUG: the cell = "+editedCell.getA1Notation()+", the column = "+editCol+", the row is "+editRow+", the value is "+eValue+", the edited sheet is "+editedSheet);


  // create some variables for column and row range  
  var statusColumn = 17; // Column Q
  var minstatusRow = 2; // row 2


  // test for a change in column Q, row 2 and higher on Sheet MAIN
  if (editedSheet === sheetname && statusColumn === editCol && editRow>=minstatusRow && eValue.length !=0 ){


    // set the range and value for Column R - ALL_status_history
    var historyrange = sheet.getRange(e.range.rowStart,e.range.columnStart).offset(0,1);
    var historyvalue = historyrange.getValue();
    // Logger.log("DEBUG: The history range = "+historyrange.getA1Notation()+", value = "+historyvalue+", length = "+historyvalue.length);

    // get the modified value of the STATUS cell from the event object
    // Logger.log("DEBUG: The Status  value = "+e.value);

    // get the date of the change
    var changeDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(),  "MM/dd/yyyy hh:mm") ;
    //Logger.log("DEBUG: The change date is "+changeDate);

    // build the value of the modified status and the change date
    var statusHistory = e.value+" "+changeDate;
    // Logger.log("DEBUG: the statusHistory is "+statusHistory);

    // if historyvalue is blank
    if (historyvalue.length !=0){
      // there's already some history so insert a blank line
      //Logger.log("DEBUG: there's existing history - insert a line");
      var newhistory = historyvalue+"\n"+statusHistory;
      // Logger.log("DEBUG: the new status history = "+newhistory)
    }
    else
    {
      // this is the first entry
      Logger.log("DEBUG: there's no existing history just insert data");
      var newhistory = statusHistory;
      // Logger.log("DEBUG: the new status history = "+newhistory)
    }

    // Update the status history
    historyrange.setValue(newhistory);

  }
  else
  {
  // the edited cell wasn't in row2 or higher in Column Q
  // do nothing

  }

}

---

截图

---

更新 - 以确保状态代码的单一使用

所有状态代码都可以从 Q 列的下拉列表中选择，并且可以多次选择一个状态代码。但是，状态历史应该只记录一次状态代码。因此，脚本应检测所选状态代码是否已被使用，如果已使用，则不应更新状态历史记录。

这只需添加几行代码即可实现。

• var statusExist = historyvalue.indexOf(eValue);
  这使用 javascript“String”indexOf() 方法，该方法“返回指定值第一次出现的调用 String 对象中的索引……如果未找到该值，则返回 -1。 “Ref
• if (statusExist !=-1){
  如果该方法返回-1，则该状态码之前没有被使用过；任何其他值表示在“ALL_status_history”字段中找到了状态代码。

---

function onEdit(e) {
  // 5731586704

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "MAIN";
  var sheet = ss.getSheetByName(sheetname);

  // get a list of the event objects
  // Logger.log(JSON.stringify(e));


  // get the event source data
  var editedCell = e.range;
  var editRow = editedCell.getRow();
  var editCol = editedCell.getColumn();
  var eValue = e.value;
  var editedSheet = editedCell.getSheet().getName();
  //Logger.log("DEBUG: the cell = "+editedCell.getA1Notation()+", the column = "+editCol+", the row is "+editRow+", the value is "+eValue+", the edited sheet is "+editedSheet);


  // create some variables for column and row range  
  var statusColumn = 17; // Column Q
  var minstatusRow = 2; // row 2


  // test for a change in column Q, row 2 and higher on Sheet MAIN
  if (editedSheet === sheetname && statusColumn === editCol && editRow>=minstatusRow && eValue.length !=0 ){


    // set the range and value for Column R - ALL_status_history
    var historyrange = sheet.getRange(e.range.rowStart,e.range.columnStart).offset(0,1);
    var historyvalue = historyrange.getValue();
    // Logger.log("DEBUG: The history range = "+historyrange.getA1Notation()+", value = "+historyvalue+", length = "+historyvalue.length);

    // test for an existing Status code in the historyvalue
    var statusExist = historyvalue.indexOf(eValue);
    //Logger.log("DEBUG: The status code: " + eValue + " returned " + statusExist); // if -1 = does not exist, any other value = does not exist

    if (statusExist !=-1){
      // do nothing, the statusCode already exists
      Logger.log("DEBUG: do nothing, the Status Code:"+eValue+" has already been used");
    }
    else
    {
      Logger.log("DEBUG: the Status Code:"+eValue+" hasn't been registered yet, so proceed");
      // the status code hasn't been registered yet, so proceed

      // get the modified value of the STATUS cell from the event object
      // Logger.log("DEBUG: The Status  value = "+e.value);

      // get the date of the change
      var changeDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(),  "MM/dd/yyyy hh:mm") ;
      //Logger.log("DEBUG: The change date is "+changeDate);

      // build the value of the modified status and the change date
      var statusHistory = e.value+" "+changeDate;
      // Logger.log("DEBUG: the statusHistory is "+statusHistory);

      // if historyvalue is blank
      if (historyvalue.length !=0){
        // there's already some history so insert a blank line
        //Logger.log("DEBUG: there's existing history - insert a line");
        var newhistory = historyvalue+"\n"+statusHistory;
        // Logger.log("DEBUG: the new status history = "+newhistory)
      }
      else
      {
        // this is the first entry
        Logger.log("DEBUG: there's no existing history just insert data");
        var newhistory = statusHistory;
        // Logger.log("DEBUG: the new status history = "+newhistory)
      }

      // Update the status history
      historyrange.setValue(newhistory);

   }

  }
  else
  {
  // the edited cell wasn't in row2 or higher in Column Q
  // do nothing

  }

}

---

编辑 2019 年 8 月 13 日
允许在多个授权工作表上进行编辑 - 通过使用 SWITCH 实现，只需对代码进行最少的更改。

• 前面的第 5 行和第 6 行 - (var sheetname & getSheetByName) 已删除。
• 在第 27 到 43 行插入 SWITCH。- 分配 var sheetname，其中“CASE”名称有效；易于添加/删除/编辑有效名称。
• IF 在第 46 行插入；有条件地执行 getSheetByName
• 第 55 行评论 - 轻微修改
• 没有进一步更改代码或逻辑

---

function onEdit(e) {
  // 5731586706
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get a list of the event objects
  // Logger.log(JSON.stringify(e));  

  // get the event source data
  var editedCell = e.range;
  var editRow = editedCell.getRow();
  var editCol = editedCell.getColumn();
  var eValue = e.value;
  var editedSheet = editedCell.getSheet().getName();
  //Logger.log("DEBUG: the cell = "+editedCell.getA1Notation()+", the column = "+editCol+", the row is "+editRow+", the value is "+eValue+", the edited sheet is "+editedSheet);

  // create some variables for column and row range  
  var statusColumn = 17; // Column Q
  var minstatusRow = 2; // row 2

  switch (editedSheet) {
    case "MAIN":
      var sheetname = "MAIN";
      break;
    case "AAA":
      var sheetname = "AAA";
      break;
    case "BBB":
      var sheetname = "BBB";
      break;
    case "CCC":
     var sheetname = "CCC";
      break;
    default:
      var sheetname = "";
      break;
  }

  if (sheetname.length !=0){
    // Logger.log("DEBUG: the name of the edited sheet = "+sheetname);
    var sheet = ss.getSheetByName(sheetname);
  }
  else{
    // Logger.log("DEBUG: the name of the edited sheet was not on the list");
  }

  // test for a change in column Q, row 2 and higher on a valid sheet
  if (editedSheet === sheetname && statusColumn === editCol && editRow>=minstatusRow && eValue.length !=0 ){

    // set the range and value for Column R - ALL_status_history
    var historyrange = sheet.getRange(e.range.rowStart,e.range.columnStart).offset(0,1);
    var historyvalue = historyrange.getValue();
    // Logger.log("DEBUG: The history range = "+historyrange.getA1Notation()+", value = "+historyvalue+", length = "+historyvalue.length);

    // test for an existing Status code in the historyvalue
    var statusExist = historyvalue.indexOf(eValue);
    //Logger.log("DEBUG: The status code: " + eValue + " returned " + statusExist); // if -1 = does not exist, any other value = does not exist

    if (statusExist !=-1){
      // do nothing, the statusCode already exists
      Logger.log("DEBUG: do nothing, the Status Code:"+eValue+" has already been used");
    }
    else
    {
      Logger.log("DEBUG: the Status Code:"+eValue+" hasn't been registered yet, so proceed");
      // the status code hasn't been registered yet, so proceed

      // get the modified value of the STATUS cell from the event object
      // Logger.log("DEBUG: The Status  value = "+e.value);

      // get the date of the change
      var changeDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(),  "MM/dd/yyyy hh:mm") ;
      //Logger.log("DEBUG: The change date is "+changeDate);

      // build the value of the modified status and the change date
      var statusHistory = e.value+" "+changeDate;
      // Logger.log("DEBUG: the statusHistory is "+statusHistory);

      // if historyvalue is blank
      if (historyvalue.length !=0){
        // there's already some history so insert a blank line
        //Logger.log("DEBUG: there's existing history - insert a line");
        var newhistory = historyvalue+"\n"+statusHistory;
        // Logger.log("DEBUG: the new status history = "+newhistory)
      }
      else
      {
        // this is the first entry
        Logger.log("DEBUG: there's no existing history just insert data");
        var newhistory = statusHistory;
        // Logger.log("DEBUG: the new status history = "+newhistory)
      }
      // Update the status history
      historyrange.setValue(newhistory);
   }   
  }
  else
  {
  // the edited cell wasn't in row2 or higher in Column Q
  // do nothing
  }
}