CODEWITHSUNDEEP
google-apps-script
Jose Ricardo
Jose Ricardo

Reputation: 13

Google Apps Scripts - Looping Dates

In my google sheets I have three cells:

A1 => with the Year ex: 2018
A2 => with the Month ex: 08
A3 => with the number of total months to loop ex: 12

My goal is to save in the same sheet the 12 sequential dates with End of Month, ex:

31.08.2018 // 30.09.2018 // etc.

How to solve this?

Upvotes: 1

Views: 165

Answers (2)

idfurw
idfurw

Reputation: 5852

Formula:

=ArrayFormula(EOMONTH(A1&"/"&A2,SEQUENCE(A3,1,0,1)))

Google Apps Script:

//const [y, m, c] = sheet.getRange('A1:A3').getValues().flat();
const y = 2018;
const m = 08;
const c = 12;

const values = [...Array(c).keys()].map(n=>[new Date(y, m + n, 0)]);
console.log(values);
// sheet.getRange(4, 8, values.length, values[0].length).setValues(values);

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15318

Try in B1 :

=arrayformula(if(row(A1:A)<=$A$3,EOMONTH(date(A1,A2+row(A1:A),0),0),))

Upvotes: 0

Related Questions