CODEWITHSUNDEEP
google-sheets
Nathan Taylor
Nathan Taylor

Reputation: 113

Timestamp When One or More Cells Change In A Row in Googlesheets

I have this sheet: https://docs.google.com/spreadsheets/d/1DwBP16AzO1vEow7K4NHsyDPTSxkteIo3RcivsG9hchY/edit#gid=1547957478

I'm trying to put a timestamp in Column D ("Updated") but I want this to timestamp if I make changes to the data in Column B/C/D. Currently I have this script which is doing what I want but only for Column B - I've tried a few different versions to get this working across all 3 columns but I can't seem to get it working.

function onEdit()
{
var sheet = SpreadsheetApp.getActiveSheet();
var capture = sheet.getActiveCell();
if (sheet.getName() == "Raw") //"order data" is the name of the sheet where you want to run this script.
{
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf("Updated") + 1;
var orderCol = headers[0].indexOf("What Do I Need To Travel") + 1;
if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
{
sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "GMT+4","MM-dd-yyyy hh:mm:ss"));
}
}
}

Any suggestions?

Upvotes: 1

Views: 83

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9907

Your approach probably can be simplified to as follows. I included a couple checks such as only if one row is being edited. You can see the conditions in the if statement.

/**
 * @OnlyCurrentDoc
 */
function onEdit(e){
var capture = e.range;
const theDateColumn = 5;
var sheet = capture.getSheet();
var theColumn = capture.getColumn();
if (sheet.getName() == "Raw" && 
  theColumn<theDateColumn && 
  theColumn>1 && 
  capture.getRow()==capture.getLastRow()){
  var yourDate = new Date();
  sheet.getRange(capture.getRow(), theDateColumn).setValue(Utilities.formatDate(yourDate, "GMT+4","MM-dd-yyyy hh:mm:ss"));
  }
}

Upvotes: 1

idfurw
idfurw

Reputation: 5852

You have inserted Column A after you posted the question, right?

The condition editColumn == orderCol is limiting the execution to only if What Do I Need To Travel column.

Upvotes: 0

Related Questions