I have the following problem with this function. It only deletes blank rows below but I want to delete the rows above.
function removeEmptyRows(){
var sh = SpreadsheetApp.getActive();
var sh1=sh.getSheetByName('name');
var range=sh1.getRange('A:A');
var maxRows = sh1.getMaxRows();
var lastRow = sh1.getLastRow();
sh1.deleteRows(lastRow+1, maxRows-lastRow);
}
I tried with the following function
function removeemptyrows(){
var ss=SpreadsheetApp.getActive();
var sh1=ss.getSheetByName('name');
var range=sh1.getRange('A:A');
var values = range.getValues();
for( var i = values.length-1; i >=0; i-- ) {
for( var j = 0; j < values[i].length; j++ )
if( values[i][j] === "" )
sh1.deleteRow(i+1)
}
}
but it deletes rows too slowly - one by one.
Here is my understanding:
When you use this script, please enable Sheets API at Advanced Google services.
function removeemptyrows() {
var sheetName = "name"; // Please set the sheet name.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
var values = sheet.getRange('A10:A').getValues();
var requests = values.reduce(function(ar, [e], i) {
if (!e) ar.push({deleteDimension:{range:{sheetId:sheetId,dimension:"ROWS",startIndex:(i + 9),endIndex:(i + 10)}}});
return ar;
}, []).reverse();
if (requests.length > 0) Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
}
'C6:C' + sheet.getLastRow()
and if (e)
were modified to 'A10:A'
and if (!e)
, respectively.