Script for cleaning empty lines in a Google Sheet is taking too long and times out

I am needing to run a simple script for Google Sheets to delete rows with empty cells. The spreadsheet I`m working on has 6000+ lines, and my script is taking so long that it times out (more than 6 minutes). Can someone help optimizing my script so that it would run faster? Thanks, Francisco.

I tried this code. It searches for empty cells through the sheet, column 2, and deletes the row when finds it. But it is incredibly slow!!

function Macro3() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Database'), true);
    var sheet = spreadsheet.getActiveSheet();

    var i = 1
    while (i < (sheet.getMaxRows())){
        if (sheet.getRange(i,2).isBlank()){
            sheet.deleteRow(i)
        }
        else {
            i++
        }
    spreadsheet.getRange('A1').activate();
    }
};

You're getting into an infinite loop, and you're also using the wrong function.

I see you are using getMaxRows(). That's going to count EVERY row in your worksheet, which will include far more rows than are actually relevant for your purposes, and therefore find an endless number of rows to delete (since the sheet is really long, and almost entirely blank beyond row ~6,000).

Instead, use getLastRow(), which will find the last row that actually has content. (read more here)

You can further simplify your branching logic by counting backwards. And you should! Because, for example, if your original sheet is 6,000 rows long, and even if only ONE row is deleted, your version of the code will be in an endless loop since the 6000th row is always blank (notice that your code doesn't increment when it deletes a blank row, so you'll iterate forever trying to count up to row 6000).

So do something like this:

function Macro3() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Database'), true);
    var sheet = spreadsheet.getActiveSheet();
    var i = sheet.getLastRow();

    while (i > 0){
        if (sheet.getRange(i, 2).isBlank()){
            sheet.deleteRow(i);
        }
        i--;
        spreadsheet.getRange('A1').activate();
    }
};

Finally, I don't understand why you'd need to do spreadsheet.getRange('A1').activate(); for every iteration. I suggest you try removing that.

I believe your goal is as follows.

• You want to reduce the process cost of your script.

Modification points:

• I'm worried that when getRange, isBlank, deleteRow, and activate() are used in the loop, the process cost becomes high. This might be the reason for your current issue.

In your situation, how about the following modification patterns?

Pattern 1:

In this pattern, the rows are deleted by filtering the retrieved values. In this case, when specific text styles and cell formats are used, this script might not be suitable.

function Macro3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Database");
  var range = sheet.getDataRange();
  var values = range.getValues().filter(([, b]) => b.toString());
  range.clearContent().offset(0, 0, values.length, values[0].length).setValues(values);
}

Pattern 2:

In this pattern, the rows are deleted by Sheets API. So, please enable Sheets API at Advanced Google services. In this case, even when the specific text styles and cell format are used, this script can be used because the rows are deleted.

function Macro3() {
  var sheetName = "Database";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetId = ss.getSheetByName(sheetName).getSheetId();
  var { values } = Sheets.Spreadsheets.Values.get(ssId, `'${sheetName}'!B1:B`);
  var requests = values.reduce((r, [b], i) => {
    if (!b) {
      r.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i + 1, dimension: "ROWS" } } });
    }
    return r;
  }, []).reverse();
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

References:

• filter()
• Method: spreadsheets.values.get
• Method: spreadsheets.batchUpdate