1

Goal: My end goal is to add the number of rows that is equal to the number of days in a month. For that I believe we should be using this method: insertRows(rowIndex, numRows).

What I don't understand: However, I don't know how to calculate the number of days there are in a month of when the script was ran.

What I was able to find: There were other questions on Stack Overflow that regards a similar issue, however most of them has to do with calculating the number of days between two dates (example question: Google Apps Script: Number of days between two dates after subtracting full months). In my case, I'm not using two dates, rather wanting to calculate the number of days depending on what the current month is.

Hope you can help.

Solana_Station
  • 187
  • 1
  • 14

1 Answers1

2

I believe your goal as follows.

  • You want to retrieve the number of days in a month and want to insert the rows using the retrieved number.
  • You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const year = 2021; // Please set the year.
  const month = 2; // Please set the month.

  const days = (new Date(year, month, 0)).getDate();
  console.log(days)  // You can check the result.

  // If you want to insert the rows from 1st row, you can use the following script.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  sheet.insertRows(1, days);
}
  • If you want to set the start row for inserting the rows, please modify 1 of sheet.insertRows(1, days);.

References:

Added:

About your following replying,

Is there a way to do this dynamically without having to hardcode??

When you want to retrieve the number of days from this year and this month, how about the following script?

Sample script:

const date = new Date();
const days = (new Date(date.getFullYear(), date.getMonth() + 1, 0)).getDate();
console.log(days)
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hi Tanaike@-san, many thanks for answering very quickly. Is there a way to do this dynamically without having to hardcode?? – Solana_Station Feb 14 '21 at 07:39
  • @John Thank you for replying. I apologize for the inconvenience. From your replying, although I'm not sure whether I could correctly understand about `Is there a way to do this dynamically without having to hardcode??`, I added a sample script in my answer. Could you please confirm it? If I misunderstood your replying, I apologize. At that time, can I ask you about the detail of your goal? By this, I would like to modify the answer. – Tanaike Feb 14 '21 at 07:49
  • Neat trick! TIL that the 'zero-ith' day (ie. `new Date(year, month, 0)`) gives you the last day of the month! Took me a minute to find it in the docs: "Similarly, if any parameter underflows, it "borrows" from the higher positions. For example, new Date(2020, 5, 0) will return May 31st, 2020." - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/Date – Jo-el Jan 09 '23 at 22:21