Add Timeline when cell value is changed (google script)

Question

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?

How is A1 value changed? Is it directly by a user in the SS? And don't you think it wouldn't be more easy to read if the newest data was on top of the column? In the situation you describe one wouldn't see the latest value after a few changes... they'd be at the bottom of the page... — Serge insas

Serge insas Serge insas · Accepted Answer · 2013-03-04T17:41:10

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
 }

Add Timeline when cell value is changed (google script)

1 Answers