Google Apps Script that removes/deletes any empty rows-CodePudding

Essentially I am looking for a script that keeps my single Google Sheet (titled, Main) free of any empty rows. If I have a row that contains data, and after some time, all of the data in that row is cleared, I want the script to notice and immediately delete that row. Any help/scripts appreciated.

I am managing the data in the sheet from an external app, and what happens is that I have the ability to "delete" a row (from the app). But what it actually does is just clear all the data from that row, it doesn't actually delete the row itself. So what I'm left with is a sheet that has scattered rows of data. For example, after a while, maybe there's data in row 3, and row 12, and row 125, and row 356, and what happens is the space between those rows (that contain data) are empty rows, when instead, using a script, it could keep checking for empty rows, and delete them as they become empty.

Edit - working solution:

function deleteEmptyRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Main");
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  var row = sheet.getLastRow();

  while (row > 2) {
    var rec = data.pop();
    if (rec.join('').length === 0) {
      sheet.deleteRow(row);
    }
    row--;
  }

  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();

  if (maxRows - lastRow != 0) {
    sheet.deleteRows(lastRow   1, maxRows - lastRow);
  }
}

CodePudding user response：

Have a look at Trigger. You will find the onEdit() trigger particularly useful.

Every time an edit happens in the associated Spreadsheet, the JavaScript function onEdit() will be run and it will get an event object passed to it. You can use the event object to detect which kind of event happend e.g. a row was deleted. Filter the kind of event you want to react to and then do whatever you need to do using the APIs Google App Script provides. For a list of available changeTypes (i.e. row deleted, column inserted etc.) of a event object and its properties for Google Sheets see the documentation.

CodePudding user response：

Delete Empty Lines in Sheet Main every five minutes

function delMts() {
  const ss = SpreadsheetApp.getActive();
  ss.toast('', "Removing Empties", 5);
  const sh = ss.getSheetByName("Main");
  const vs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues().filter(r => r.every(c => c != ''));
  sh.deleteRows(vs.length   1,sh.getMaxRows() - vs.length);
  sh.getRange(1,1,vs.length,vs[0].length).setValues(vs);
}

//run this once
function createTriggerfordleMts() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "delMts").length == 0) {
    ScriptApp.newTrigger("delMts").timeBased().everyMinutes(5).create();
  }
}

CodePudding user response：

This script grabs the sheet, iterates from the bottom up (starting from the last data containing row) and checks for empty rows, if any are found, it deletes them, it then checks for remaining empty rows beyond the last data containing row and deletes them all at once, if they are empty. Also, this script is setup with a corresponding onChange trigger so that the script runs anytime the sheet is edited, catching any newly cleared rows.

function deleteEmptyRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Main");
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  var row = sheet.getLastRow();

  while (row > 2) {
    var rec = data.pop();
    if (rec.join('').length === 0) {
      sheet.deleteRow(row);
    }
    row--;
  }

  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();

  if (maxRows - lastRow != 0) {
    sheet.deleteRows(lastRow   1, maxRows - lastRow);
  }
}