0
290
VistasHow do I loop adding the month to each date until the condition is met? google apps script
How do I loop adding the month to each date until the condition is met?
Explanation : I want to add 3 months to each date every time until the annual column (column D) will be greater than the today's date ( column E).
Example : If we took the first line of the column Annual " 01/02/2021" and we add at first 3 months the results will be 01/05/2021 then we add second time 3 months 01/08/2021 ..etc until it will be 01/05/2022 ( Greater than the today's date).
In the column "H" I've write the results I want, but whats I've got is in the column "F" by Red.
Here's my code :
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
for (var i = 2; i < 12; i++) {
var today_date = ss.getRange(i, 5).getValue();
var today_date_format = Utilities.formatDate(new Date(today_date), "Europe/Paris", 'MMMM dd, yyyy 12:00:00 Z');
var Date_2021 = ss.getRange(i, 4).getValue();
var Date_2021_Format = Utilities.formatDate(new Date(Date_2021), "Europe/Paris", 'MMMM dd, yyyy 12:00:00 Z');
var Date_2021_constructed = new Date(Date_2021_Format);
var a3date = Date_2021_constructed.getMonth();
Date_2021_constructed.setMonth((a3date + 3) % 12);
do {
Date_2021_constructed = Date_2021_constructed.setMonth((a3date + 3) % 12);
} while (Date_2021_constructed > today_date_format);
ss.getRange(i, 6).setValue(Date_2021_constructed)
}
}Here's the screen of my data :
1 Respuestas
Responde la pregunta0
Thanks for the example and the image that helps a lot:
I'm going to suggest a quick fix and see if it works. It looks like you may be posting the value of the date in millisecond in column f. So try this:
ss.getRange(i, 6).setValue(new Date(Date_2021_constructed)).setNumberFormat("dd/MM/yyyy");
If this doesn't work I'll take a closer look and it would be helpful to have a table of values from your spreadsheet so that I can test.
If the above solution does not work then try this:
function myFunction() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1');
const vs = sh.getRange(2,1,sh.getLastRow() - 1,5).getValues();
vs.forEach((r,i) => {
let td = new Date(r[4]);
let tdv = td.valueOf();
let ad = new Date(r[3]);
let m = r[2];
let n = 1;
do{
var fd = new Date(ad.getFullYear(),ad.getMonth() + (n++ * m), ad.getDate());
var fdv = fd.valueOf();
}while(fdv<tdv);
sh.getRange(i + 2,6).setValue(fd).setNumberFormat("dd/MM/yyyy");
});
}