Loop each cell until text = "timestamp" and then timestamp any changes

Question

I'm working on a spreadsheet with multiple users collaborating. The spreadsheet admin asked me to create a timestamp for any new/changed entries; therefore, I cannot protect the range to prevent anyone from tampering with the timestamp column. Please note users will constantly add/remove rows (including adding rows above header) and columns so the "timestamp" column is always changing. Today the "timestamp" column is in cell E2 but tomorrow it can change to cell C4.

Below is my attempted code where I try to loop though each cell until it finds "timestamp" so that I can get the row & column number. But I am having issues with my code.

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = ss.getActiveSheet();
  var tf = thisSheet.createTextFinder("timestamp");
 
  // This finds Row # where cell = "timestamp"
  var thisRow = tf.findNext().getRow(); 
  // This is supposed to find Col # where it = "timestamp" but I'm having issues 
  var thisCol = tf.findNext().getColumn();
 
 
  var ss = e.source;
  var main_sheet = ss.getSheetByName('sports_team');
  const row = e.range.getRow();
  if (e.source.getActiveSheet().getName() === "sports_team") {
    const currentDate = new Date();
    main_sheet.getRange(thisRow, thisCol).setValue(currentDate);
  }
}

Answer 1

Suggestion: Use getCurrentMatch() to find the column index

Calling the findNext() function again after the looking up the row index is expected to return a null value unless there's another cell with the value "timestamp" in the sheet. To get the column index after looking up the row index, I suggest using getCurrentMatch() instead.

I've made some modifications to your code to have it automatically add a date on the timestamp column when the sheet is edited. Try this:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = ss.getSheetByName("sports_team");
  var tf = thisSheet.createTextFinder("timestamp");
 
  // This finds Row # where cell = "timestamp"
  var thisRow = tf.findNext().getRowIndex(); 
  // I used getCurrentMatch() to find the column index instead of findNext()
  var thisCol = tf.getCurrentMatch().getColumnIndex();
 
  //Finds the current active sheet
  var main_sheet = e.source.getActiveSheet();
  //Finds the index of the row being edited
  var row = e.range.rowStart;

  if (main_sheet.getName() == "sports_team" && row > thisRow) {
    const currentDate = new Date();
    //Inserts the date on the row being edited, under the timestamp column
    main_sheet.getRange(row, thisCol).setValue(currentDate);
  }
}

Note that this code only inserts the date on the timestamp column if the cell/row being edited is below the timestamp header.

Reference:

• getCurrentMatch()