I have a spreadsheet with multiple sheets each sheet has the same layout with Date in column "A" and Time Elapsed in column "P" I need to calculate days between column A and today on all four sheets. If checkbox in column "N" is set to true then instead calculate column "O" from "A". So far I have been able to code this bit but, need help getting over the hurdle.

function getDateDifference(event) {
  var ss = event.source;
  var s = ss.getSheetByName('Alpha'); // I need this to run on all four pages
  var r = event.source.getActiveRange();
  var startDate = sheet.getRange(3,1).getValue(); 
  var endDate = new Date();
  var duration = (endDate - startDate)
  var r = event.source.getActiveRange();
  if (r.getColumn() == 14 && r.getValue() == true) {
    var endDate = sheet.getRange(3, 15);
    var duration = (endDate - startDate); 
    sheet.getRange(3, 16).setValue(duration); 
}
else {sheet.getRange(3, 16).setValue(duration);}
}    

Example sheet for reference https://docs.google.com/spreadsheets/d/1OKFoS17le-Y5SAOecoLE4EJxiKqKVjRLRHtMzwHNwxM/edit?usp=sharing

CodePudding user response：

Calculate Diff in Days for selected sheets

function calcdiffindays() {
  const ss = SpreadsheetApp.getActive();
  const incl = ['Alpha', 'Sheet2', 'Sheet3'];//Add the desired sheet names
  const dt = new Date();
  const dtv = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate());
  ss.getSheets().filter(sh => ~incl.indexOf(sh.getName())).forEach(s => {
    s.getRange(3, 1, s.getLastRow() - 2, s.getLastColumn()).getValues().forEach((r, i) => {
      if (r[13] == true) {
        let d = new Date(r[0]);
        let dv = new Date(d.getFullYear(), d.getMonth(), d.getDate());
        s.getRange(i   3, 15).setValue(DiffInDays(dtv, dv));
      }
    })
  })
}

function DiffInDays(Day1,Day2) {
  if(Day1 && Day2 && (Object.prototype.toString.call(Day1) === '[object Date]') && (Object.prototype.toString.call(Day2) === '[object Date]')) {
    var day=86400000;
    var t1=new Date(Day1).valueOf();
    var t2=new Date(Day2).valueOf();
    var d=Math.abs(t2-t1);
    var days=Math.floor(d/day); 
    //Logger.log(days);
    return days;
  } else {
    throw 'Invalid Inputs';
  }
}

Test Sheet:

You appeared to be attempting to use a trigger. However, you did not specify a trigger and there were no triggers defined in your spreadsheet. So I went without triggers.

An onEdit Version:

function onEdit(e) {
  const sh = e.range.getSheet();
  const incl = ['Alpha', 'Sheet2', 'Sheet3'];//Add the desired sheet names
  const idx = incl.indexOf(sh.getName())
  if (~idx && e.range.columnStart == 14 && e.range.rowStart > 2 && e.value == "TRUE") {
    const dt = new Date();
    const dtv = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate());
    let d = new Date(sh.getRange(e.range.rowStart, 1).getValue());
    let dv = new Date(d.getFullYear(), d.getMonth(), d.getDate());
    e.range.offset(0, 1).setValue(DiffInDays(dtv, dv));
  }
}

CodePudding user response：

To insert timestamps in column O when the checkboxes in column N are ticked, use a timestamping script such as yetAnotherTimestamp_ with these parameters:

    // [START modifiable parameters]
    {
      sheetName: /^(Alpha|Beta|Charlie|Delta)$/i,
      watchColumn: ['N'],
      stampColumn: ['O'],
      watchRowStart: 3,
      watchRowEnd:  Infinity,
      timestampFormat: 'yyyy-MM-dd',
      overwriteTimestamp: true,
      eraseTimestampOnDelete: true,
    },
    // [END modifiable parameters]

Then put this formula in cell P2:

=arrayformula( 
  { 
    "Days Elapsed"; 
    if( N3:N = "Yes", O3:O - A3:A, iferror(1/0) ) 
  } 
)

The formula will give the number of days between the timestamp and the form submission in the rows where a checkbox is ticked.