0
267
ViewsInsert 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?
·
Juan Pablo Isaza
1 answers
Answer question0
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:
·
Juan Pablo Isaza
Report
Answer question