Question

0

67

Views

Insert blank row after the date that has Sunday as the day google app scripts

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:

Loop through the dates column
Format the date so that we only grab the day in full abbreviation (i.e., Sunday)
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?

7 months ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-03T03:59:45.985Z

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: