I have a column in google sheets that has a range of dates. I want to insert one blank row after dates that happen to be Sundays (I only care about the day).

The pseudo-code that I was thinking of is the following:

1. Loop through the dates column
2. Format the date so that we only grab the day in full abbreviation (i.e., Sunday)
3. If the formatted date is equal to the string "Sunday" then insert a row below that date.

Here is my current script:

function addRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getActiveSheet();
  const range = sheet.getDataRange();
  const data = range.getValues();

  for (let i = 1; i < data.length; i  ) {
    const sheetDate = data[i][0];
    const day = Utilities.formatDate(sheetDate, ss.getSpreadsheetTimeZone(), "EEEE");
    if (day === "Sunday") {
      sheet.insertRowsAfter(i, 1); // I know this shouldn't be i, it should be the index of the row with the value "Sunday"
    }
  }
}

I think the problem is with the first argument of the insertRowsAfter() function. What should this argument be?

CodePudding user response：

It seems that every insertion of new rows after a row position, your data range adjusts, thus, you also need to adjust the row position for the next succeeding rows that contains Sunday.

Suggestion

Perhaps you can try this tweaked script below:

function addRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getActiveSheet();
  const range = sheet.getDataRange();
  const data = range.getValues();
  var count = 0;

  for (let i = 1; i < data.length; i  ) {
    var row = i 1; //Get the sheet row number of every data from "data" variable
    const sheetDate = data[i][0];
    const day = Utilities.formatDate(sheetDate, ss.getSpreadsheetTimeZone(), "EEEE");
    if (day == "Sunday") {
      count  = 1; //Count how many times "Sunday" was found
      var curRow = row count; //Adjust the current row #
      curRow -= 1; //Subtract 1 row from the current row to match the sheet
      sheet.insertRowsAfter(curRow,1)
    }
  }
}

Sample Test

• Test Sheet:

• After running the tweaked script: