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")
}