Static timestamp for multiple columns

Question

I am having quite a headache trying to get this to work. I want a script for my Google sheet that will give me a static timestamp based on specific values for multiple columns. I've used the script below:

//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 9;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,13];
// Sheet you are working on
var SHEETNAME = 'Pipeline'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
  }
}

This works great for a single column, but it doesn't work when I try to duplicate the script for other columns. I've tried to make an array without success either. Keep in mind I'm no expert with this stuff.

For context: There are six different stages of progress that I track in columns 9-14, and I track them using four different values. I want to get a timestamp for each time I change from one value to the next, so that I can track the time each stage takes. I'm new to this, thanks for your patience.

Image of spreadsheet here

user30hu · Answer

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  const cols = [9,10,11,12,13];
  const idx = cols.indexOf(e.range.columnStart);
  if(sh.getName() == "Your sheet name" && ~idx){
    e.range.offset(0,6).setValue(new Date())
  }
}

Static timestamp for multiple columns

1 Answers