CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jon Bowles
Jon Bowles

Reputation: 129

You can't delete all the rows on the sheet Google Sheets Script

I am trying to remove empty cells from an entire workbook using this code:

//Remove All Empty Rows in the Entire Workbook
function removeEmptyRows() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]
var maxRows = sheet.getMaxRows(); 
var lastRow = sheet.getLastRow();
if (maxRows-lastRow != 0){
  sheet.deleteRows(lastRow+1, maxRows-lastRow);
    }
  }
}

from this post: Deleting ALL empty rows in a Google Spreadsheet

Problem is I keep getting this error: "Exception: You can't delete all the rows on the sheet."

The workbook has multiple sheets all have data in the top 20 rows as a minimum.

The need for this script comes from a bigger problem of not being able to run an import script because of this error: "Exception: This action would increase the number of cells in the workbook above the limit of 5000000 cells."

Any help would be gratefully received!! Thanks

Upvotes: 2

Views: 1824

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14502

If you need just to clean everything it can be done much faster and shorter this way:

function removeAllRows() {
  SpreadsheetApp.getActive().getSheets().forEach(s => s.clear());
}

You can use clearContents() instead of clear() if you need to keep formatting.

Just in case, if you need to remove all empty rows (and keep not empty ones), probably it makes sense don't use the deleteRows() since it's slow. You can get values from a sheet as a 2D array, remove 'rows' from the 2D array, and set its values back on the sheet. It can be done this way:

function removeEmptyRows() {

  // function to remove all empty lines from a 2D array
  const get_not_empty_rows = (data) => data.filter(row => row.filter(String) != '');

  // function to get a range on the given sheet by the size of a given 2D array
  const get_new_data_range = (s, data) => s.getRange(1, 1, data.length, data[0].length);

  var sheets = SpreadsheetApp.getActive().getSheets();

  for (let sheet of sheets) {                          // for every sheet
    let all_rows = sheet.getDataRange().getValues();   // get 2D array
    let not_empty_rows = get_not_empty_rows(all_rows); // remove empty lines
    if (not_empty_rows.length > 0) {                   // if there are lines
      sheet.getDataRange().clearContent();             // clean the sheet
      get_new_data_range(sheet, not_empty_rows).setValues(not_empty_rows); // set the values
    }
  }
  
}

But you need to decide will you remove formatting on the sheets or not. With deleteRows() formatting of the rest rows remains intact.

Upvotes: 1

mshcruz
mshcruz

Reputation: 1987

Your function seems to do what you want, and I could only get that error if there was a sheet completely empty. One way to handle that is to add a condition to your IF to see if there's anything in the sheet:

function removeEmptyRows() {
  var ss = SpreadsheetApp.getActive();
  var allsheets = ss.getSheets();
  for (var s in allsheets) {
    var sheet = allsheets[s]
    var maxRows = sheet.getMaxRows();
    var lastRow = sheet.getLastRow();
    if (lastRow > 0 && maxRows - lastRow != 0) {
      sheet.deleteRows(lastRow + 1, maxRows - lastRow);
    }
  }
}

Upvotes: 1

Related Questions