单元格值更改时添加时间线（谷歌脚本） [英] Add Timeline when cell value is changed (google script)

问题描述

我希望有一个函数在我的电子表格中的特定单元格值发生更改时输出时间轴：单元格A1中的值是从时常。当A1改变时，我想在B1中添加当前值。 C1显示了数值已被更改的时间戳。

下一次更改A1时，新值将显示在B2上，即C2上的时间戳。第三个变化是B3 / B4，第四个是B4 / C4等。

它对我来说似乎是每个平常的谷歌形式都有的功能，但我我不知道如何将其转换为自定义脚本。

任何人都可以提供帮助吗？

解决方案

做你想做的事情，你可以选择使用push或unshift将新值添加到B或C列的顶部或底部......我更喜欢因为我在评论中给出的原因而不加改变，但这取决于你; - ）

  function timeline（）{
 var sh = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）$ b $如果（sh.getActiveRange（）。getA1Notation（）！='A1'）{return} 
 var last = lastRowinCol（sh，'B'）; 
 var cellA = sh.getRange（'A1'）。getValues（）; 
 var colB = sh.getRange（'B1：B'+ last）.getValues（）; 
 var colC = sh.getRange（'C1：C'+ last）.getValues（）; 
 // colB.unshift（cellA）; //添加到顶部
 // colC.unshift（[new Date（）]）; 
 colB.push（cellA）; //在底部添加
 colC.push（[new Date（）]）; 
 sh.getRange（1,2，colB.length，1）.setValues（colB）; 
 sh.getRange（1,3，colC.length，1）.setValues（colC）; 
 
 
函数lastRowinCol（sh，col）{
 var coldata = sh.getRange（col +'1：'+ col）.getValues（）; 
 for（var c in coldata）{if（coldata [c] [0] ==''）{break}} 
 return c 
} 
  
 
编辑：我忘了提及你需要为该函数分配一个onEdit触发器来让它自动执行。  
 
 
 
 
 
 编辑2：下面是来自Mogsdad的相关评论，我建议您替换for在函数lastRowinCol 中的下一个循环，其代码向后迭代，以便处理列中的空单元格。此外，他的代码有一个有趣的构造，因为循环限制和条件是在同一个语句中。
 函数lastRowinCol（sh，col ）{
 var coldata = sh.getRange（col +'1：'+ col）.getValues（）; 
 for（var c = coldata.length;（c）&& coldata [c-1] [0] ==''; c--）{} 
 return c 
 } 
  
 
I would like to have a function that outputs a timeline when a specific cell value on my spreadsheet is changed:

In cell A1 is a value that is changed from time to time. When A1 is changed i want the current value to be added in B1. C1 shows the timestamp on which the value has been changed.

The next time A1 is changed the new value is shown on B2, the timestamp on C2. The third change goes to B3/B4, the fourth on B4/C4 and so on.

It seemes to me this is a function every usual google form does, but I am not sure how to convert this into a custom script. 

Could anyone help?
解决方案
Here is a example code that does what you want, you can choose wether the new values are added on top or at the bottom of column B and C by using push or unshift... I prefer unshift for the reasons I gave in the comment but it's up to you ;-)
function timeline() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  if(sh.getActiveRange().getA1Notation()!='A1'){return}
  var last = lastRowinCol(sh,'B');
  var cellA = sh.getRange('A1').getValues();
  var colB = sh.getRange('B1:B'+last).getValues();
  var colC =  sh.getRange('C1:C'+last).getValues();
//  colB.unshift(cellA); // add on top
//  colC.unshift([new Date()]);
  colB.push(cellA); //add at the bottom
  colC.push([new Date()]);
  sh.getRange(1,2,colB.length,1).setValues(colB);
  sh.getRange(1,3,colC.length,1).setValues(colC);
}

function lastRowinCol(sh,col){
  var coldata = sh.getRange(col+'1:'+col).getValues();
  for(var c in coldata){if(coldata[c][0]==''){break}}
  return c
  }
note : there are other ways to implement this but this one is pretty "didactic" I think... using arrays, subfunction call, batch read and write... 

Edit:  I forgot to mention that you need to assign an onEdit trigger to that function to let it execute automatically. 



EDIT 2 : following the very pertinent comment from Mogsdad below I suggest you replace the for-next loop in function lastRowinCol with his code that iterates backwards so that it handles empty cells in the column. Moreover his code has an interesting construction since the loop limits and the condition are in the same statement.
function lastRowinCol(sh,col){
  var coldata = sh.getRange(col+'1:'+col).getValues();
  for (var c=coldata.length; (c) && coldata[c-1][0]==''; c--) {}
  return c
 }


                        
这篇关于单元格值更改时添加时间线（谷歌脚本）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！