Using getTime in a For loop in google sheets script

Question

I am trying to run a 'for' loop that looks like this:

function runMultipleDates() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //9/26/2018 is 1569477600000 and 2/28/2019 is 1551337200000; 9/21/2018 is 1537509600000
   
  for (var date1 = 1537596000000 /*9/21/2018*/; date1 < 1569477600000 /*9/26/2018*/; date1 +=86400000) {
       //runEverything();
   var date2 = new Date();
       date2 = date2.setTime(date1);
   ss.getSheetByName('Time Range').getRange("A3").setValue(date2);

 };
  
};

My goal is to run a function called "runEverything()" that reference a date located in cell A3 of a sheet called 'Time Range'. As long as the date in cell A3 is less that 9/26/2018, the for loop should run my function 'runEverything' and then set a new date in A3. When I do my test loop, the setValue(date2) returns a numeric value rather than a date in that cell (A3). Can someone point out how I can return the date format rather than the numeric value of date? if there is a more elegant way to achieve this, I am all ears!

Thank you for your insight!

When you want to put the value to the cell "C3" as the date object, please modify `ss.getSheetByName('Time Range').getRange("A3").setValue(date1);` to `ss.getSheetByName('Time Range').getRange("A3").setValue(new Date(date1));`. But I cannot understand about `As long as the date in cell A3 is less that 9/26/2018, the for loop should run my function 'runEverything' and then set a new date in A3.`. In your script, the values of `date1` is put to a cell "A3". So in your script, the result is that `9/25/2018` is put to the cell "A3". Can I ask you about your goal? — Tanaike, Nov 21 '19 at 00:40
I tried a new script and updated it in the question. Can you look at my question again and see if it will help clarify what I meant more? The value in cell A3 is still the numeric value of "1569391200000" rather a date value like 9/23/2018. — Thien Pham, Nov 21 '19 at 00:43
Thank you for replying. For example, how about modifying `setValue(date2)` to `setValue(new Date(date2))`? But in this case, `1569391200000` is `9/25/2019`. Can I ask you about the logic for retrieving `9/23/2018`? — Tanaike, Nov 21 '19 at 00:49
Tanaike, thank you for suggesting the setValue(new Date(date2)). That worked. What I wanted was to run my function based on a date located in cell A3 up several days after that. So I needed cell A3 to increase one day at a time. The final code for me was this: runEverything(); var date2 = new Date(); date2 = date2.setTime(date1+86400000); ss.getSheetByName('Time Range').getRange("A3").setValue(new Date(date2)); }; Do you have other suggestions? — Thien Pham, Nov 21 '19 at 01:36
Tanaike, would there be away to write the same script without having to go through the numerical representation of date (like "1538719200000") and rather using normal dates 10/5/2018 — Thien Pham, Nov 21 '19 at 02:11
Thank you for replying. For your replying, I proposed a sample script. Could you please confirm it? If I misunderstood your question and that was not the result you want, I apologize. — Tanaike, Nov 21 '19 at 02:47

score 2 · Answer 1 · answered Nov 21 '19 at 02:46

2

You want to use the date string instead of the unix time

If my understanding is correct, how about the following script? Please think of this as just one of several possible answers.

Sample script:

function runMultipleDates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var start = new Date("2018-09-29"); // <--- 1538200800000
  var end = new Date("2018-10-05"); // <--- 1538719200000
  var step = 1; // 1 day (86400000)

  for (var date1 = start; date1 <= end; date1.setDate(date1.getDate() + step)) {
    runEverything();
    var date2 = new Date(date1.getTime());
    date2.setDate(date2.getDate() + 1);
    ss.getSheetByName('Time Range').getRange("A3").setValue(date2);
  };
}

This script brings the same result with the script of your answer.
When the script is run, 10/06/2018 is put to the cell "A3".

References:

answered Nov 21 '19 at 02:46

Tanaike

181,128
11
97
165

Tanaike, thanks for the help again! This is far better as I don't have to convert the unix epoch time every time I change the time frame. I made one adjustment to move the runEverything() after the "setValue(date2) and it ran smoothly. One question I have is why **var date2 = new Date(date1.getTime())** rather than **var date2 = new Date(date1)** ? If you care to explain, I'd appreciate very much. Additionally, on another related issue, [link](https://stackoverflow.com/questions/58983029/exceeded-maximum-execution-time-google-sheets-script-work-around) , Would you be so kind to take a look? – Thien Pham Nov 21 '19 at 20:24
@Thien Pham Thank you for replying. I think that both patterns of `var date2 = new Date(date1.getTime())` and `var date2 = new Date(date1)` can be used. In this case, I used this for copying the date object of `date1` to `date2`. I apologize for low readability of my sample script. This is due to my poor skill. If this answer doesn't resolve your issue, please tell me. I would like to modify it. – Tanaike Nov 21 '19 at 22:09

score 0 · Answer 2 · answered Nov 21 '19 at 01:49

Tanaike suggested using new Date(date2) in the setValue() and it worked. My final script is something like this:

function runMultipleDates() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
   
  for (var date1 = 1538200800000 ; date1 <= 1538719200000; date1 +=86400000) {
   runEverything();
   var date2 = new Date();
       date2 = date2.setTime(date1+86400000);
   ss.getSheetByName('Time Range').getRange("A3").setValue(new Date(date2));

 };

Using getTime in a For loop in google sheets script

2 Answers2

Sample script:

References: