CODEWITHSUNDEEP
google-apps-scriptscriptingtabsgoogle-sheetstimestamp
Jonathan Michala
Jonathan Michala

Reputation: 49

Google Sheets Automatic Timestamp Script for Multiple Tabs

I am making a large google spreadsheet with multiple sheet tabs. When I edit Column 6, I need a timestamp to automatically be entered into Column 1. I need this to happen independently in every sheet. Here is the script that I have so far, and it works well. However, I am going to add at least 10 more tabs, and I was wondering if there was an easier way to do this with less room for error.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "A" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 6 ) { //checks the column
      var nextCell = r.offset(0, -5);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setNumberFormat("MM/dd HH:mm:ss")
        nextCell.setValue(new Date());
    }
  }
  if( s.getName() == "B" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 6 ) { //checks the column
      var nextCell = r.offset(0, -5);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setNumberFormat("MM/dd HH:mm:ss")
        nextCell.setValue(new Date());
    }
  }
  if( s.getName() == "C" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 6 ) { //checks the column
      var nextCell = r.offset(0, -5);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setNumberFormat("MM/dd HH:mm:ss")
        nextCell.setValue(new Date());
    }
  }
}

Upvotes: 4

Views: 2564

Answers (1)

JPV
JPV

Reputation: 27292

Yes, there is. Just put all the sheet names in an array and check if the currently edited sheet is in that array (using indexOf). If it is not in the array it will return -1. In that case the script exits. See if something like this works:

function onEdit(e) {
var s = e.source.getActiveSheet(),
    sheets = ["A", "B", "C"],
    stampCell = e.range.offset(0, -5);
if (sheets.indexOf(s.getName()) == -1 || e.range.columnStart !== 6 || !e.value || stampCell.getValue()) return;
stampCell.setValue(new Date()).setNumberFormat("MM/dd HH:mm:ss")
}

Upvotes: 1

Related Questions