CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Atif Qayyum
Atif Qayyum

Reputation: 57

How to get Date & Time using onEdit(e) function for multipal Sub Sheets in Google Spread Sheet

A Google Sheet with Multiple Sub-Sheets for each user. Each user is doing the entry and I need to calculate performance based on data entry date/time. I use following onEdit(e) function it works fine for 1 Sheet... But when I make multiple copies of same script for other Sub-Sheets... It Stops Working.

function onEdit(e) {
  var s = e.source.getActiveSheet(),
      watchcols = [2],
      offsetcols = [-1],
      ind = watchcols.indexOf(e.range.columnStart)
  if (s.getName() !== "Sheet1" || ind === -1) return ;
  e.range.offset(0,offsetcols[ind])
  .setValue(!e.value ? null : Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:SS"))
}

Can someone please figure help me on this one?

Thank you in advance for reading my post and helping me out!

Upvotes: 0

Views: 630

Answers (3)

Kris
Kris

Reputation: 567

UPDATE - using Utilities.formatDate(date, time zone, format) with local time zone. Find your local time zone id from here

What about separating the function from onEdit():

function onEdit(e) {
  var s = e.source.getActiveSheet();
  var range = e.range;
  var col = range.getColumn();
    
   if (e.value){
      if (s.getName() === "AAA" && col === 4) { setDateTime(range, -3)};
      if (s.getName() === "BBB" && col === 6) { setDateTime(range, -5)};
      if (s.getName() === "CCC" && col === 10) { setDateTime(range, -6)};   
   }
}
    
    
function setDateTime(range, offsetcols){
  var date =  Utilities.formatDate(new Date(),"America/New_York", "MM/dd/yyyy HH:mm:ss");
  
  Logger.log(date);
       
  range.offset(0,offsetcols).setValue(date);
}

Upvotes: 1

Cooper
Cooper

Reputation: 64100

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  const shts = ['Sheet1',"add others"]
  const idx = shts.indexOf(sh.getName());
  const watchcols = [2];
  const offsetcols = [-1];
  const ind = watchcols.indexOf(e.range.columnStart);
  if (~idx && ~ind) {
    e.range.offset(0,offsetcols[ind]).setValue(!e.value ? null : Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:SS"))
  }
}

Upvotes: 0

idfurw
idfurw

Reputation: 5862

function onEdit(e) {
  var s = e.source.getActiveSheet(),
      watchcols = [2],
      offsetcols = [-1],
      ind = watchcols.indexOf(e.range.columnStart)
  if (ind === -1) return ;
  if (s.getName() === "Sheet1") {
    e.range.offset(0,offsetcols[ind])
    .setValue(!e.value ? null : Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:SS"))
  }

  else if (s.getName() === "Sheet2") {
  }

  else if (s.getName() === "Sheet3") {
  }
}

Upvotes: 1

Related Questions