在1个像元中合并为值(格式为yyyy-mm-dd hh:mm的文本和日期时间) [英] combine in 1 cell as value ( text and Date Time in formta yyyy-mm-dd hh:mm)

问题描述

在Google表格中，我在行中有2个具有临时状态的单元格.

In google sheets I have 2 cells with temporary status in row.

当我在下拉列表中选择时，然后输入一些文本:

When I choose in drop down list some text then:

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

0(status) | 1 | 2 | 3 |

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

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

Then I would like to combine (1,2) and paste as value to 3rd cell.

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

This formula returns my expected result:

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

但是接下来，我想在动态范围内复制那些合并状态. 如果在单元格上只有Value，那么我对它没有任何问题.

But next I would like to copy those combine status in dynamic range. If on cell is only Value then I didn't have any problem with it.

但是，如果我尝试复制日期或公式并粘贴而不是公式，而是粘贴值，则脚本不会显示任何错误，但根本无法正常工作. 当我录制宏时，它可以工作，但是当我添加一小段代码来更改事件-在我的代码中却没有.

But if I try to copy date or formula and paste not like a formula but like a value then Scripts not showing any errors but not working at all. When I recorder the macro it it's working but when I add small piece of code to change Event - to my code it doesn't.

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

Probably it's so simple but I try all things to solve it and I don't get why is't working.

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

I'm new to JavaScript. Please give me some advice why to write the code to paste formula.

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

推荐答案

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

You chose to use an onChange() trigger, but I would like to suggest:

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

• use an onEdit(e) trigger
• simply the Status History by capturing ALL history in a single cell (Column R). This has the knock-on effect of also simplifying the code; in particular it reduces the number of getValue statements

您会注意到:

状态值和日期的

• 串联可通过简单地将它们与"+"连接在一起来实现.实际上，我添加了分号以更好地区分状态和日期.

• concatenation of the Status value and date are achieved by simply joining them with "+". Actually, I added a semi-colon to better distinguish between the status and date.

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

I added a line break to each line so that the Status History is easier to read. A disadvantage of this is that the row height increases. You could just as easily delete the line break and/or, say, increase the width of the Status History column.

如果愿意，可以保留状态历史记录"的存档列，但每组仅需要一列.

If you wish, you may keep the archival columns of Status History, but only one column per set is required.

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

As usual, there might be several ways that this outcome might be achieved. Consider this answer as one way of doing this.

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

  }

}

---

屏幕截图

---

Screenshot

更新-确保单次使用状态码

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

All status codes can be selected from the dropdown in Column Q, and it is possible to select a status code more than once. However, the Status History should only record a status code once. Therefore, the script should detect whether the selected status code has already been used and, if so, should not update the Status History.

这是通过仅添加几行代码来实现的.

This is achieved by adding only a few lines of code.

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

• var statusExist = historyvalue.indexOf(eValue);
  This uses the javascript "String" indexOf() method which "returns the index within the calling String object of the first occurrence of the specified value... it returns -1 if the value is not found." Ref
• if (statusExist !=-1){
  If the method returns -1, then the Status Code hasn't been used before; any other value indicates that the Status Code was found in the "ALL_status_history" field.

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只需对代码进行最少的更改即可.

---

EDIT 13 August 2019
To allow edit on multiple authorised sheets - achieved by use of SWITCH with minimal changes to code.

• 先前的第5行和第6行-(var sheetname& getSheetByName)已删除.
• SWITCH插入第27至43行.-分配var sheetname，其中"CASE"名称有效；易于添加/删除/编辑有效名称.
• 如果在第46行插入；有条件地执行getSheetByName
• 第55行注释-稍作修改
• 无需进一步更改代码或逻辑

• Previous lines 5 and 6 - (var sheetname & getSheetByName) removed.
• SWITCH inserted at lines 27 to 43. - assign var sheetname where "CASE" name is valid; easy to add/delete/edit valid names.
• IF inserted at line 46; conditionally executes getSheetByName
• Line 55 comment - slight edit
• no further changes to code or logic

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

这篇关于在1个像元中合并为值(格式为yyyy-mm-dd hh:mm的文本和日期时间)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！