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]);
            last = columnRoom[i][0]
        } else if (last == columnRoom[i][0]) {
            var currentSheet = SpreadsheetApp.getActiveSpreadsheet()

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?


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.