Script for Date Stamping on Multiple Sheets

Question

I am very very very new to all this. I need help, I am trying to use script editor to get the date statically stamped in one column when something is entered in a different column. I figured how to do this for one tab but I need this to happen on multiple tabs in the same sheet and I'm struggling to get it to work. Is there one code that will work for this? This is the script I was using for one tab:

/**
* Creates a Date Stamp if a column is edited.
*/

//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,-8];
// Sheet you are working on
var SHEETNAME = 'Sheet 2'

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());
      }
  }
}

Thank you for your time in advance.

Answer 1

To proceed with the function on multiple sheets, you can check for the sheet name in an array of acceptable names.

function onEdit() {

    var colToCheck = 9;

    // Offset from the input [row, column]
    var dateOffset = [0, -8];

    // Sheets to proceed on
    var sheetNames = ['Sheet 2', 'Sheet 3'];

    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var name = sheet.getName();

    if (sheetNames.indexOf(name) > -1) {

      var cell = sheet.getActiveCell();
      var col = cell.getColumn();

      if (col == colToCheck) {

          var dateTimeCell = cell.offset(dateOffset[0], dateOffset[1]);
          dateTimeCell.setValue(new Date());
        }
    }
}

---

References

Arrays

indexOf()

---

EDIT ONE

If you want multiple options, you could set them up in arrays. The order of the elements in the arrays must match.

This code assumes that the timestamp is always on the same row.

function onEdit() {

    var sheetNames = ['Sheet 2', 'Sheet 3'];
    var colsToCheck = [9, 15];
    var colOffsets = [-8, -4];
    
    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var name = sheet.getSheetName();
      
    var index = sheetNames.indexOf(name);
    
    if (index > -1) { 
      var cell = sheet.getActiveCell();
      var col = cell.getColumn();
    
      if (col == colsToCheck[index]) { 
          var dateTimeCell = cell.offset(0, colOffsets[index]);
          dateTimeCell.setValue(new Date());
        }
    }
}

---

EDIT TWO

For those of you who would prefer objects

function onEdit() {

    var sheets = {
    
        'Sheet 2': {
            checkCol: 9,
            offset: -8
        },
    
        'Sheet 3': {
            checkCol: 15,
            offset: -4
        }
    };

    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var name = sheet.getSheetName();
  
    var settings = sheets[name];

    if (settings) { 
      var cell = sheet.getActiveCell();
      var col = cell.getColumn();

      if (col == settings.checkCol) { 
          var dateTimeCell = cell.offset(0, settings.offset);
          dateTimeCell.setValue(new Date());
        }
    }
}