Faster way to copy rows of data to different sheets by column value

Question

I'm trying to find a faster way to copy specific rows of a sheet to different sheets. Iterating through them as done in code below takes too much time it leads to timeout.

Some information about origin sheet:

• Has already a blocked header
• Has around 5000 rows
• Column A has a header "Project"
• Sheet is sorted by Column A

Goal is to copy range of all rows for each project from origin sheet to a blank sheet that is named from specific project - so all rows that has in column e.g. "ProjectA" in column A are in a sheet called "ProjectA".

Here is a code that is working, but it is using very slow iteration, so I'm waiting around 20 minutes or even get a timeout when I'm processing it:

 var sheet = SpreadsheetApp.getActiveSheet();
    var columnRoom = sheet.getRange("A:A").getValues();
    var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
    var header = rows[0];
    var completedRooms = []
    var last = columnRoom[1][0]
    for (var i = 1; i < columnRoom.length; i++) {
        if (!completedRooms.includes(columnRoom[i][0])) {
            if (columnRoom[i][0] === "") {
                var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("No Room");
            } else {
                var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnRoom[i][0]);
            }
            currentSheet.appendRow(header);
            currentSheet.appendRow(rows[i]);
            completedRooms.push(columnRoom[i][0])
            last = columnRoom[i][0]
        } else if (last == columnRoom[i][0]) {
            var currentSheet = SpreadsheetApp.getActiveSpreadsheet()
            currentSheet.appendRow(rows[i]);
        }
    }

Is there a way to do it faster? I'm thinking about appending specific rows to a range and use copyTo but I can't arrange it, maybe use map function?

Answer 1

Your for () loop is unnecessarily calling SpreadsheetApp.getActiveSpreadsheet() in every single iteration. You can define a varible outside of the loop for chaining:

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
for (let i = 1; i < columnRoom.length; i++) {
  // something...
  spreadsheet.insertSheet("No Room")
}