3

I want to take an existing script from a Google Spreadsheets and change it so it only updates the last column when I make a change to a specific column in the last row. I can't get it to only key off of the one column, it continues to change the last column whenever I make any change to the sheet. Any ideas?

function onEdit(event)
{ 
    var sheet = event.source.getActiveSheet(),getcolumn=("s");

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  var dateCol = sheet.getLastColumn();  
  var lastCell = sheet.getRange(index,dateCol);
   var date = Utilities.formatDate(new Date(), "EST", "MM-dd-yyyy' 'HH:mm:ss' _'");

  lastCell.setValue("'" + date);
}
  • So you only want it to edit the last column if you edit the last row? – Punchlinern Nov 13 '14 at 9:46
  • 1
    Does your script even compile without errors? The line event.source.getActiveSheet(),getcolumn=("s") looks strange. When that's fixed, you're not checking indexor cindex to see if the edit event occured in the desired column. – Vidar S. Ramdal Nov 13 '14 at 10:15
2

This script checks if column B is updated, then updates the last column in that row with a timestamp:

function onEdit(event) { 
  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  if (cindex == 2) { // 1 == Column A, 2 == Column B, 3 == Column C, etc.
   var dateCol = sheet.getLastColumn();  
   var lastCell = sheet.getRange(index,dateCol);
   var date = Utilities.formatDate(new Date(), "EST", "MM-dd-yyyy' 'HH:mm:ss' _'");
   lastCell.setValue("'" + date);
  }
}

Note that the term "last column" will mean that, when starting with an empty spreadsheet, the value you enter in e.g. column B will be overwritten with the timestamp, since column B will be the "last column".

I set up an example spreadsheet to demonstrate. Feel free to copy it.

  • Thanks for the help. I tried the script you offered but it didnt quite provide the needed result. I'm very new to scripts so I likely didn't communicate the ask very well. In short, I have a spreadsheet with 24 columns. New rows are added daily with detail added along each cell of the row from columns A thru W. I want column X to be populated with a day/time time stamp that reflects the day/time when any value was added or changed for column S. Again, thank you for your feedback – Damien Nov 14 '14 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.