Setting Google Spreadsheet cell values from array, but all values are the same

Question

I am using Google Apps Script to try to get an array of dates and paste them into the cells on a Google Spreadsheet.

I am able to get the data into an array, and when I use the logger, I can see that the dates are incrementing, as is the array.length.

When I use .setValues to paste the contents of the array into the spreadsheet, I get the same value in every cell. (oldestDate in the code is just another date variable set earlier on. It is created using a new Date(date string) constructor, so is a valid date.

  var today = new Date();
  var testDate = new Date(oldestDate);
  Logger.log(oldestDate);
  Logger.log(testDate);
  Logger.log("Loop Starts");
  var storage = [];
  var cols=0
  while (Date.parse(testDate) < Date.parse(today))
  {
    storage.push(testDate);
    Logger.log(testDate); //This is giving the same output as vvvvv
    Logger.log(storage[cols]); //This is giving the same output as ^^^^^
    Logger.log("Storage Length: "+storage.length); //This is incrementing
    testDate.setDate(testDate.getDate() +1);
    cols++;
  }

  var expensesDump = expenses.getSheetByName("Dump");
  expensesDump.getRange(1, 1, 1, cols).setValues([storage]); //This is populating every cell with "27/05/2013"

score 1 · Accepted Answer · edited May 23 '17 at 11:43

When you push an object (in this case, a date object) into an array, you are pushing a reference to that object:

Do objects pushed into an array in javascript deep or shallow copy?

The second answer in that link suggests that if a new object is assigned to the variable that is referenced by push(), then it is "disconnected" from previous invocations of push(). Therefore a solution might be (inside the while loop):

testDate = new Date(testDate);
testDate.setDate(testDate.getDate()+1);

Setting Google Spreadsheet cell values from array, but all values are the same

1 Answers1