CODEWITHSUNDEEP
javascriptdatetime
SRCOMSEC
SRCOMSEC

Reputation: 29

Using Arrays & Map Method to add days to an array of date

I am working on a script in which I am trying to use Arrays & Map Method to add numdays to an array in google Sheets.

The following image is an example of the data enter image description here

function new_date() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = ss.getMaxRows();
  
var data = ss.getRange(2, 5, lastRow).getValues();
  
// Logger.log(data)

var newData = data.map(adding_days);

ss.getRange(2, 5, lastRow).setValues(newData);
 
}

function adding_days(row){ 
  var millis_per_day = 1000 * 60 * 60 * 24;
  var days = 7 ; 
  
  var date = new Date(row[0]);
  var date2 = Utilities.formatDate(date, "GMT+05:30" , "MM-dd-yyyy");
  
  var numdays = days * millis_per_day
  
  return [ date2 + numdays ];  

}

When I run the code its only returning the data again in another format, I dont understand why its not being able to add the numdays to the data. I am new to using arrays in app script so I am very confused about whats going wrong here.

Thanks in advance!

Upvotes: 0

Views: 488

Answers (1)

Cooper
Cooper

Reputation: 64040

Adding years, months, days, hours, minutes, seconds and milliseconds to a date

In this particular case days:

function new_date() {
  const ss=SpreadsheetApp.getActive()
  const sh=ss.getActiveSheet();
  const v=sh.getRange(2,5,sh.getLastRow()-1,1).getValues();
  const ndt=v.map(function(r,i){
    let dt=new Date(r[0]);
    let days=7;
    return [new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()+days,dt.getHours(),dt.getMinutes(),dt.getMilliseconds())];
  });
  sh.getRange(2,5,ndt.length,ndt[0].length).setValues(ndt);
}

The problem with adding in milliseconds to the date value is that it does compensate for special situations like leap years and such. While this requires one very long line it now allows you to add numbers of days and hours and such directly to the date with simple integer math by adding it to the appropriate section of the date constructor.

Date Constructor

Upvotes: 2

Related Questions