Google sheet date of last revision in a row

Question

Help! In Google sheets, i just want one column to input the last date and time that any cell in a row is updated or revised. For example: a1 - should get the last revision date, b1-z1 (any number of cells after a1) - everytime any cell in that row is updated or revised, a1 date is updated.

I want to apply the same script to more than one sheet.

I've found this script, which seemed to work fine, initially:

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();


  //Script Last Update Timing

  var actSht = event.source.getActiveSheet();
  var actRng = event.source.getActiveRange();

  var activeCell = actSht.getActiveCell();
  var row = activeCell.getRow();
  var column = activeCell.getColumn();

  if(row < 2)   return; //If header row then return
    var colNums  = [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,  20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34]; //Coulmns, whose edit is considered
  if(colNums.indexOf(column) == -1) return; //If column other than considered then return


  var index = actRng.getRowIndex();
  var dateCol = actSht.getLastColumn();
  var lastCell = actSht.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT+5:30", "dd/MM/yyyy HH:mm:ss");
  // Note: Insert the Date when someone update the row in the last coulmn
  lastCell.setValue(date);
}

When I was testing it, it worked fine, but after that day it wasn't working or sometimes it works, or works when I do revisions myself but it doesn't work with the person/s I am sharing the file with.

I don't know anything about scripting but after a week of searching, I have opted to post, hoping someone can lead me to the right direction.

Any help would be greatly appreciated! Thank you in advance!

score 0 · Answer 1 · answered Jul 08 '15 at 07:55

See if this helps:

function onEdit(e) {
if (e.range.columnStart < 5 || e.range.rowStart < 21) return;
e.source.getActiveSheet().getRange(e.range.rowStart, 1) //first col
    .setValue(Utilities.formatDate(new Date(), "GMT+5:30", "dd/MM/yyyy HH:mm:ss"));
}

Google sheet date of last revision in a row

1 Answers1