CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamptriggers
Alex
Alex

Reputation: 5

adding timestamp on three google sheets onedit with Google Apps Script

I am adding timestamp in col a if colb is edited on 3 sheets.

I have the trigger working onEdit and the information will be updated automatically. It works fine for one, but for 3 im getting an error and cannot run the script.

Is there a simpler way to look for edit in column b in three sheets than what I am doing?

function onEdit() {
   var s = SpreadsheetApp.getActiveSheet();
   if (s.getName() == "Igualdad Op Hom Mu") {
      var r = s.getActiveCell();
      if (r.getColumn() == 2) {
         var nextCell = r.offset(0, -1);
         if (nextCell.getValue() === '')
            nextCell.setValue(new Date());
         var s2 = SpreadsheetApp.getActiveSheet();
         if (s2.getName() == "Incl Per Disc") {
            var r2 = s2.getActiveCell();
            if (r2.getColumn() == 2) {
               var nextCell2 = r2.offset(0, -1);
               if (nextCell2.getValue() === '')
                  nextCell2.setValue(new Date());
               var s3 = SpreadsheetApp.getActiveSheet();
               if (s3.getName() == "Empr y Col") {
                  var r3 = s3.getActiveCell();
                  if (r3.getColumn() == 2) {
                     var nextCell3 = r3.offset(0, -1);
                     if (nextCell3.getValue() === '')
                        nextCell3.setValue(new Date());
                  }
               }
            }
         }
      }
   }
}

Upvotes: 0

Views: 606

Answers (1)

Cooper
Cooper

Reputation: 64082

Try this:

function onEdit(e) {
  const sh=e.range.getSheet();
  const shts=['Igualdad Op Hom Mu','Incl Per Disc','Empr y Col']
  if( shts.indexOf(sh.getName())!=-1  && e.range.columnStart==2) { 
    const ts=Utilities.formatDate(new Date,Session.getScriptTimeZone(),"yyyy/MM/dd HH:mm:ss");//time stamp
    const rg=e.range.offset(0,-1);//next cell to the left
    if(rg.getValue()=='') {
      rg.setValue(ts);
    }
  }
}

This function requires the onedit trigger event object so you cannot run this script from the script editor unless you supply the event object.

Upvotes: 2

Related Questions