I have an app that uses parent and child records with IsPartOf turned on. When I delete a parent record, it only clears the records in Google Sheets, leaving the sheet with many large gaps over time, which gets unsightly and annoying for my OCD
So I write a Google Apps Script that triggers once a week to delete all the blank rows in each table, to clean them up. If you’re interested, here’s the code:
function deleteBlankRows() {
var SS = SpreadsheetApp.getActiveSpreadsheet();
// Get sheets
var sheets = SS.getSheets();
// Loop through sheets. Delete blank rows in each sheet.
for (var s=0; s < sheets.length; s++) {
var currentSheet = sheets[s];
var sheetName = sheets[s].getName();
var searchDataRange = currentSheet.getRange(1,1,currentSheet.getMaxRows(),currentSheet.getMaxColumns()); // get the ENTIRE sheet. not just where the data is.
var searchValues = searchDataRange.getValues();
var numRows = searchValues.length;
var numCols = searchDataRange.getNumColumns();
var rowsToDel = [];
var delRow = -1;
var prevDelRow = -2;
var rowClear = false;
// Loop through Rows in this sheet
for (var r=0; r < numRows; r++) {
// Loop through columns in this row
for (var c=0; c < numCols; c++) {
if (searchValues[r][c].toString().trim() === "") {
rowClear = true;
} else {
rowClear = false;
break;
}
}
// If row is clear, add it to rowsToDel
if (rowClear) {
if (prevDelRow === r-1) {
rowsToDel[delRow][1] = parseInt(rowsToDel[delRow][1]) + 1;
} else {
rowsToDel.push([[r+1],[1]]);
delRow += 1;
}
prevDelRow = r;
}
}
Logger.log("numRows: " + numRows);
Logger.log("rowsToDel.length: " + rowsToDel.length);
// Delete blank rows in this sheet, if we have rows to delete.
if (rowsToDel.length>0) {
// We need to make sure we don't delete all rows in the sheet. Sheets must have at least one row.
if (numRows === rowsToDel[0][1]) {
// This means the number of rows in the sheet (numRows) equals the number of rows to be deleted in the first set of rows to delete (rowsToDel[0][1]).
// Delete all but the first row.
if (numRows > 1) {
currentSheet.deleteRows(2,numRows-1);
}
} else {
// Go through each set of rows to delete them.
var rowsToDeleteLen = rowsToDel.length;
for (var rowDel = rowsToDeleteLen-1; rowDel >= 0; rowDel--) {
currentSheet.deleteRows(rowsToDel[rowDel][0],rowsToDel[rowDel][1]);
}
}
}
}
}
To make this run once a week, you need to open the spreadsheet containing your data. Go to Tools > Script Editor in the menu and add the code I pasted above. I named the file “deleteBlankRows”. Now you need to set the trigger. From the script editor menu, select Edit > Current project’s triggers. In the lower right corner, click the blue button, Add Trigger. On the pop-up form, select Time Driven for the Event Source. The other options are pretty clear.
This has been running flawlessly for a few weeks now for me. I hope it help someone else too!
You can add this code to the top of the script file.
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Clean up')
.addItem('Delete blank rows', 'deleteBlankRows')
.addToUi();
}
It would have driven me crazy if I could not clean up the empty records!!
I don’t know why you can’t open “Current project triggers”. It should be available under the Edit menu in the Scripts editor, not the menu for the sheet itself.
after copy and paste below script and save the script, refresh the google sheet, you will see the menu in google sheet:
Click “Authorize and Install Trigger”, for initialization. The trigger will automatic installed and triggered every Sunday at 09:00 and a pop-up will appeared on the google sheet for successful installation.
Sometime you also want to manually delete the blank rows (if you don’t want to wait for Sunday to come. Click “Delete Blank Row Manually”.
Sometime you just want to remove the trigger.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Delete Blank Rows')
.addItem('Authorize and Install Trigger', 'configure')
.addItem('Delete Blank Row Manually','deleteBlankRows' )
.addItem('Remove Trigger', 'reset')
.addToUi();
}
function deleteBlankRows() {
var SS = SpreadsheetApp.getActiveSpreadsheet();
// Get sheets
var sheets = SS.getSheets();
// Loop through sheets. Delete blank rows in each sheet.
for (var s=0; s < sheets.length; s++) {
var currentSheet = sheets[s];
var sheetName = sheets[s].getName();
var searchDataRange = currentSheet.getRange(1,1,currentSheet.getMaxRows(),currentSheet.getMaxColumns()); // get the ENTIRE sheet. not just where the data is.
var searchValues = searchDataRange.getValues();
var numRows = searchValues.length;
var numCols = searchDataRange.getNumColumns();
var rowsToDel = [];
var delRow = -1;
var prevDelRow = -2;
var rowClear = false;
// Loop through Rows in this sheet
for (var r=0; r < numRows; r++) {
// Loop through columns in this row
for (var c=0; c < numCols; c++) {
if (searchValues[r][c].toString().trim() === "") {
rowClear = true;
} else {
rowClear = false;
break;
}
}
// If row is clear, add it to rowsToDel
if (rowClear) {
if (prevDelRow === r-1) {
rowsToDel[delRow][1] = parseInt(rowsToDel[delRow][1]) + 1;
} else {
rowsToDel.push([[r+1],[1]]);
delRow += 1;
}
prevDelRow = r;
}
}
//Logger.log("numRows: " + numRows);
//Logger.log("rowsToDel.length: " + rowsToDel.length);
// Delete blank rows in this sheet, if we have rows to delete.
if (rowsToDel.length>0) {
// We need to make sure we don't delete all rows in the sheet. Sheets must have at least one row.
if (numRows === rowsToDel[0][1]) {
// This means the number of rows in the sheet (numRows) equals the number of rows to be deleted in the first set of rows to delete (rowsToDel[0][1]).
// Delete all but the first row.
if (numRows > 1) {
currentSheet.deleteRows(2,numRows-1);
}
} else {
// Go through each set of rows to delete them.
var rowsToDeleteLen = rowsToDel.length;
for (var rowDel = rowsToDeleteLen-1; rowDel >= 0; rowDel--) {
currentSheet.deleteRows(rowsToDel[rowDel][0],rowsToDel[rowDel][1]);
}
}
}
}
}
function configure() {
reset();
ScriptApp.newTrigger('deleteBlankRows')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.SUNDAY)
.atHour(9)
.create();
Browser.msgBox("Initialized", "Blank Row will be deleted with trigger every Sunday at 09:00", Browser.Buttons.OK)
}
function reset() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
}
auto> /**> * A special function that runs when the spreadsheet is first> * opened or reloaded. onOpen() is used to add custom menu> * items to the spreadsheet.> */> function onOpen() {> var ui = SpreadsheetApp.getUi();> ui.createMenu('Clean up')> .addItem('Delete blank rows', 'deleteBlankRows')> .addToUi();> }> >
Does anyone get the error message " Exception: You can’t delete all the rows on the sheet." when running this? It works but I always get this error when running it manually. After looking it up, it appears the error may stem from the ‘for’ loop.
I am on an iPhone so I can’t check script, but are you trying to execute this on a sheet that has no content below a header row? I didn’t see any error checking so you might be trying to delete ALL rows in your sheet which is not allowed (must have at least one row). Add a few rows to your sheet and see if it runs. Just something to check.
I’m not 100% sure, but I think you would get that error when the sheet has no data in it. The problem is that the script wants to delete all the rows in that case, but at least one row is always required.
Hey Greg and Mike, thanks for the feedback. I have about 10 sheets in the whole workbook and they all have hundreds of rows of data including header columns so I’m not sure why I’m getting this error.
If you don’t want to loop through each rows and rely mostly on build in function, you can try following script. Note that this script is only for one sheet. You can modify it to cover all sheets.
function RemoveEmptyRows() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).createFilter();
var criteria = SpreadsheetApp.newFilterCriteria()
.whenCellEmpty()
.build();
/* Use following code if you don't have key columns. It will filter empty for all columns.
var maxColumn = sheet.getMaxColumns();
for(var i = 1; i <= maxColumn; i++){
sheet.getFilter().setColumnFilterCriteria(i, criteria);
}*/
var keyColumns = [1,3]; // !!!CHANGE THIS ARRAY FOR KEY COLUMNS
for(var i = 0; i < keyColumns.length; i++){
sheet.getFilter().setColumnFilterCriteria(keyColumns[i], criteria);
}
spreadsheet.getRange('1:1').activate();
sheet.insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1); // insert an empty row after row 1. So we know the row 2 is empty
spreadsheet.getRange('2:2').activate();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
sheet.deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
sheet.getFilter().remove();
};
The scripts proposed under this post will quickly run out of Google’s execution limits provided you have a lot of columns and rows in your gSheet. The best and the quickest way to handle the issue is using Google’s Visualization Query. For example; I use below gs code to return some user data from the gSheet. With a gSheet of 30-35 columns and over 50K rows, the script’s return runtime is approx. 4-5 secs. You can alter the code below to remove empty rows as well. In terms of script execution time, it’ll be much more quicker.
GAS CODE
function verifyUserCredentials(spreadsheetID, sheetName, queryColumnLetterStart, queryColumnLetterEnd, queryColumnLetterSearch, query) {
// SQL like query
myQuery = "SELECT * WHERE " + queryColumnLetterSearch + " = '" + query + "'";
// the query URL
var qvizURL = 'https://docs.google.com/spreadsheets/d/' + spreadsheetID + '/gviz/tq?tqx=out:json&headers=1&sheet=' + sheetName + '&range=' + queryColumnLetterStart + ":" + queryColumnLetterEnd + '&tq=' + encodeURIComponent(myQuery);
// fetch the data
var ret = UrlFetchApp.fetch(qvizURL, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getContentText();
// remove some crap from the return string
var response = JSON.parse(ret.replace("/*O_o*/", "").replace("google.visualization.Query.setResponse(", "").slice(0, -2));
if (typeof response.table.rows[0] != 'undefined') {
var data = response.table.rows[0].c[1].v;
return [true, data];
} else {
return [false,""];
}
}
