在谷歌表中，我有2个单元格具有临时状态。
当我在下拉列表中选择一些文本时：
0（状态）| 1 | 2 | 3 |
在第一个单元格上添加当前日期时间
将状态从下拉列表添加到第二个单元格
然后，我想结合（1,2）并将其值粘贴到第三个单元格。
此公式返回我的预期结果：
=IF(AR1="";"";TEXT(AR1;"mm/dd/yyyy hh:mm"))&"->"&AS1
但是接下来，我想在动态范围内复制那些合并状态。
如果在单元格上只有Value，那么我就没有任何问题。
但是，如果我尝试复制日期或公式并粘贴而不是公式，而是粘贴值，则脚本不会显示任何错误，但根本无法正常工作。
当我录制宏时，它是有效的，但是当我添加一小段代码来更改事件-在我的代码中却没有。
也许它是如此简单，但是我尝试了所有方法来解决它，但我不知道为什么它不起作用。
我是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"))');
}}}
最佳答案
您选择使用onChange（）触发器，但是我建议：
使用onEdit（e）触发器
通过捕获单个单元格（R列）中的所有历史记录，可以简单地获取状态历史记录。这具有简化代码的连锁效应。特别是它减少了getValue语句的数量
您会注意到：
只需将状态值和日期与“ +”连接即可实现状态值和日期的串联。实际上，我添加了分号以更好地区分状态和日期。
我为每行添加了一个换行符，以便更轻松地阅读状态历史记录。这样的缺点是行高增加。您可以轻松地删除换行符和/或增加“状态历史记录”列的宽度。
如果需要，可以保留“状态历史记录”的存档列，但是每组仅需要一列。
与往常一样，可能有几种方法可以实现这一结果。将此答案视为执行此操作的一种方法。
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
}
}
屏幕截图
UPDATE-确保状态码的单次使用
可以从Q列的下拉菜单中选择所有状态代码，并且可以多次选择一个状态代码。但是，状态历史记录仅应记录一次状态代码。因此，脚本应检测是否已使用选定的状态代码，如果已使用，则不应更新状态历史记录。
这可以通过仅添加几行代码来实现。
var statusExist = historyvalue.indexOf(eValue);这使用javascript“ String”
indexOf()方法，该方法“返回指定值首次出现的调用String对象内的索引...如果找不到该值，则返回-1。” Refif (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。-在“ CASE”名称有效的情况下分配
var sheetname；易于添加/删除/编辑有效名称。如果插入第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
}
}
关于javascript - 在1个像元中合并为值(格式为yyyy-mm-dd hh:mm的文本和日期时间)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57315867/