CODEWITHSUNDEEP
google-apps-script
Dan Howard
Dan Howard

Reputation: 316

Google Apps Script: Get an array of dates between two dates

I'm building a time sheet spreadsheet. I'd like to include a function that allows the user to enter a period of Leave, by entering the start date and end date, which then automatically marks out the dates in between. I have the following, which enters the value against the relevant Start and End date, but not the dates in between. Is there away to build an array of the dates that I can then loop though?

function updateLeave() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Front Page");
 var startDate = ss.getRange("C15").getValue();
 var endDate = ss.getRange("C16").getValue();
 var type = ss.getRange("C17").getValue(); 
 var creditTime = ss.getRange("C18").getValue();

 var allSheets = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = allSheets.getSheets();
 sheets.forEach (function (sheet) {

 var columnB = sheet.getRange(2, 2, 70, 1); 
 var bValues = columnB.getValues(); 
 var today = startDate; 
 for (var i = 0; i < bValues.length; i++) { 
 var fDate = new Date(bValues[i][0]);
 if (fDate.getDate() == today.getDate() && 
    fDate.getMonth() == today.getMonth() && 
    fDate.getFullYear() == today.getFullYear()) { 
    sheet.getRange(i + 2, 10, 1, 1).setValue(type);
    sheet.getRange(i + 2, 9, 1, 1).setValue(creditTime);  
}
}
})

sheets.forEach (function (sheet) {

var columnB = sheet.getRange(2, 2, 70, 1); 
var bValues = columnB.getValues(); 
var today = endDate; 
for (var i = 0; i < bValues.length; i++) { 
var fDate = new Date(bValues[i][0]);
if (fDate.getDate() == today.getDate() && 
    fDate.getMonth() == today.getMonth() && 
    fDate.getFullYear() == today.getFullYear()) { 
    sheet.getRange(i + 2, 10, 1, 1).setValue(type);
    sheet.getRange(i + 2, 9, 1, 1).setValue(creditTime);  
}
}

})
}

Upvotes: 2

Views: 5553

Answers (1)

Bardy
Bardy

Reputation: 2170

How about something like this:

var DAY_MILLIS = 24 * 60 * 60 * 1000;

function main() {
  var a = new Date('2016/01/01');
  var b = new Date('2016/01/03');
  var dates = createDateSpan(a, b);
}

function createDateSpan(startDate, endDate) {
  if (startDate.getTime() > endDate.getTime()) {
    throw Error('Start is later than end');
  }

  var dates = [];

  var curDate = new Date(startDate.getTime());
  while (!dateCompare(curDate, endDate)) {
    dates.push(curDate);
    curDate = new Date(curDate.getTime() + DAY_MILLIS);
  }
  dates.push(endDate);
  return dates;
}

function dateCompare(a, b) {
  return a.getFullYear() === b.getFullYear() &&
    a.getMonth() === b.getMonth() &&
    a.getDate() === b.getDate();
}

The main function shows an example of use

Upvotes: 4

Related Questions