I am trying to insert 2 additional columns using app script to count the number of days from specific columns until todays date

Additional column at the end would be: Days from last activity (Column O), Days from opened (Column P) where the output should be the number of days (ie. 5), i know how to create the formula in sheets =days(date1, date1), but i can't figure it out via app script, it needs to be via app script because the data is pulled dynamically and when pulled it fills data at the end of the last row with the formula :( Obs: there are cases in which the row might not have some of the 2 values to compare to todays date, in this case i am trying to leave the cell empty.

Link of the sheet

Any help will be very much appreciated.

CodePudding user response：

Using array formulas in P1 and Q1 will allow one formula per column to produce the header and all results for each respective column. I've installed array formulas in P1 and Q1, which you can test with some incoming data.

P1:

=ArrayFormula({"Time from Opened";IF(L2:L="",,TODAY()-INT(L2:L))})

Q1:

=ArrayFormula({"Time from Last Activity";IF(J2:J="",,TODAY()-INT(J2:J))})

However, you have a bigger problem going on, involving your date formats.

I notice that your dates are coming into the sheet in all kinds of different formats (month-day, day-month, some strings, some actual dates) and that will cause a major problem.

I fixed the date formats for the short data set in Col J and Col L, so that you can see the formulas working correctly. But all dates in all columns are still rendered in non-uniform ways. You will need to address that or no formula or script will be able to work consistently on them.

Part of the problem may be that you are using "United States" as location, when you are actually located in Brazil. The US date format is month-day-year while I believe the Brazil date format standard is day-month-year. So if a date like 1/12/2021 is entered directly, it will be read as January 12, 2021. But if it comes in from another spreadsheet from Brazil, it will be read as December 1, 2021. That is a problem you'll need to address.

CodePudding user response：

function AddingontoEnd() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Your Sheet Name');
  const sr = 2; //data start row
  const vs = sh.getRange(sr,1,sh.getLastRow() - sr   1,sh.getLastColumn());
  vs.forEach(r => {
    r.splice(r.length,0,DiffInDays(new Date(r[9],new Date())),DiffInDays(new Date(r[11],new Date())));
  });
  sh.getRange(sr, 1,vs.length,vs[0].length).setValues(vs)
}

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';
  }
}