Get list of dates from StartDate to EndDate and put it in other Sheet

Question

I like to have script that lists the date from start to enddate and put it in sheet2. When a new end and startdate gets added, the script adds dates automatic. Format for the date dd-mm-yy

I hope you can help, I've searched many script that i could not use. Thanks.

Sheet1

A         B

startDate endDate

5-4-13    12-4-13

19-4-13   26-4-13

Result in Sheet2

A

Dates

5-4-13
6-4-13
7-4-13
8-4-13
9-4-13
10-4-13
11-4-13
12-4-13
19-4-13
20-4-13
21-4-13
22-4-13
23-4-13
24-4-13
25-4-13
26-4-13 In column A

Thanks everybody for their replies. I already had googled a lot, and I have pasted something together dat almost works. The script does not print the last day and sometimes it starts with the day before the start date. Could it have someting to do with the date difficulties and setValue ?

  function getDates() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var export = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var startRow = 2;  // First row of data to process
var numRows = sheet.getLastRow()-1;   // Number of rows to process
// Fetch the range of cells A:B
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();

for (var i=0; i < data.length; i++) {

var row = data[i];
var startDate = row[0]   //get start date
var endDate = row[1];    //get the end Date
endDate.setHours(0);

var e = Utilities.formatDate(endDate,'GMT+0100','d-M-yyyy');
//Logger.log(endDate+" "+e);      
//Logger.log(date+" ");

startDate.setHours(0);

for (var k=0; k<9; k++) {  

var date=new Date(startDate.getYear(),startDate.getMonth(),startDate.getDate() + k);

//Logger.log(d+" "+date);
var d = Utilities.formatDate(date,"GMT+0100","d-M-yyyy");

var last_row = export.getLastRow();
export.insertRowAfter(last_row);      
var target_range = export.getRange("A"+(last_row + 1));
target_range.setValue(d);

  if (e == d) break;
}
}
}

I've found a solution, but I do not think it is the best solution. I hope there is a better solution. so I have 11 hours added to the GMT time. And the output is OK

I have changed; var e = Utilities.formatDate(endDate,'GMT+0100','d-M-yyyy'); to; var e = Utilities.formatDate(endDate,'GMT+1200','d-M-yyyy');

and; var d = Utilities.formatDate(date,"GMT+0100","d-M-yyyy"); to; var d = Utilities.formatDate(date,"GMT+1200","d-M-yyyy");

Answer 1

Try this. You may want to play with the date formats on the way out.

function genDates() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get the dates from sheet 1
  var dates = ss.getSheetByName('Sheet1')
    .getDataRange()
    .getValues()
    .reduce(function(p,c) {
      var startDate = new Date (c[0]);
      var endDate = new Date (c[1]);
      while (startDate.getTime() <= endDate.getTime() ) {
        p.push ( [new Date(startDate)] );
        startDate.setDate( startDate.getDate() +1);
      }
      return p;
    }, []);

  // write the expanded dates to sheet 2
  ss.getSheetByName ('Sheet2')
    .clear()
    .getRange(1,1,dates.length,dates[0].length)
    .setValues (dates);

}

Answer 2

Look up the sample provided by google. Something like google drive-new file-script-spreadsheet sample..then google "date addition google spreadsheet" ... in bed on phone... bad thumb typer...

Answer 3

https://developers.google.com/apps-script/articles

Better? I googled it for you....

Answer 4

I drafted a solution that works for me, although the way Google Sheets formats dates can be a little tricky. You can, however, set a timezone for the whole sheet, which could solve part of your problem. Note that appendRow() is recommend by Google over setValue() and that this code doesn't insert new rows, if required. You can, of course, easily add that in. I did this using an onEdit() trigger instead of a menu function. Seemed sensible given your requirements.

// assumes columns are already set to display dates in dd-mm-yy format
ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setSpreadsheetTimeZone("Etc/GMT-1"); // set spreadsheet timezone to +1:00 (see http://joda-time.sourceforge.net/timezones.html for other values)
sheet1 = ss.getSheetByName("Sheet1");
sheet2 = ss.getSheetByName("Sheet2");

Date.prototype.isEqualTo = function(date) {
  return date.getDate() === this.getDate() && date.getMonth() === this.getMonth() && date.getYear() === this.getYear();
};

function updateValues(start, end) {
  var dateList = makeDateList(start, end);
  dateList.forEach( function(date) {
    sheet2.appendRow(date);
  });
}

function makeDateList(start, end) {
  var newDate = new Date(start.getYear(), start.getMonth(), start.getDate());
  var dateList = [];
  do {
    var date = newDate.getDate();
    var month = newDate.getMonth();
    var year = newDate.getYear();
    var dateString = [date, month, year].join("-");
    dateList.push([dateString]);
    newDate = new Date(year, month, (date + 1));
  } while (newDate.isEqualTo(end) === false);
  return dateList;
}

function onEdit(e) {
  if (ss.getActiveSheet().getName() === "Sheet1") {
    var range = e.range; // you could also rewrite this function to accept ranges of dates instead of just one cell at a time
    var row = range.getRow();
    var column = range.getColumn();
    var adjacentRange = sheet1.getRange(row, 1);
    if (column === 2 && e.range.isBlank() === false && adjacentRange.isBlank() === false) {
      var start = adjacentRange.getValue();
      var end = e.range.getValue();
      updateValues(start, end);
    }
  }
}