Google-sheets – How to delete all rows containing given text that could be in any column within the row

I've built two solutions, the difference is whether it removes all empty cells in the selected range or only the ones where all cells in a row (in the selection) is empty.

Both solutions require an additional sheet "ScriptSheet", which you can hide.
Both solutions shift the remaining cells up, including the ones below the selections.

You can try both here, they can be found in a custom menu called "Scripts". Make a copy of the spreadsheet.

Solution 1

This script only removes cells if an entire row of cells in the selection is empty.

function removeEmptyRowsInSelection(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
  var sheetLastRow = sheet.getLastRow();
  var range = sheet.getActiveRange();
  var rangeFirstRow = range.getRow();
  var rangeLastRow = range.getLastRow();
  var rangeFirstCol = range.getColumn();
  var rangeLastCol = range.getLastColumn();
  var rangeCols = rangeLastCol - rangeFirstCol + 1;
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();
  var scriptSheetMaxRows = scriptSheet.getMaxRows();
  var scriptSheetMaxCols = scriptSheet.getMaxColumns();
  var insertRows = maxRows - scriptSheetMaxRows;
  var insertCols = maxCols - scriptSheetMaxCols;
  var currScriptRow = 1;
  
  scriptSheet.clear();
  
  if (insertRows > 0){
    scriptSheet.insertRows(1, insertRows);
  }
  
  if (insertCols > 0){
    scriptSheet.insertCols(1, insertCols);
  }
  
  
  for(var i = rangeFirstRow; i <= rangeLastRow; i++){

    var currRange = sheet.getRange(i, rangeFirstCol, 1, rangeCols);
    var currValue = currRange.getValues().join();
    var emptyValue = '';
    for (j = rangeCols; j > 1; j--){
      emptyValue = emptyValue + ',';
    }
    if(currValue != emptyValue){
      currRange.copyTo(scriptSheet.getRange(currScriptRow, 1));
      currScriptRow++;
      var test;
    } 
  }
  
  
  
  for(var currRow = rangeLastRow + 1; currRow <= sheetLastRow; currRow++, currScriptRow++){
    sheet.getRange(currRow, rangeFirstCol, 1, rangeCols).copyTo(scriptSheet.getRange(currScriptRow, 1));
  }
  
  var clearRange = sheet.getRange(rangeFirstRow, rangeFirstCol, maxRows, rangeCols);
  var scriptSheetRange = scriptSheet.getRange(1, 1, scriptSheet.getLastRow(), scriptSheet.getLastColumn());
  var pasteRange = sheet.getRange(rangeFirstRow, rangeFirstCol);
  
  clearRange.clear();
  scriptSheetRange.copyTo(pasteRange);
}

This function is much slower than the other one. It went through about 1000 lines in the first of three columns before canceled by the six minute limitation.

Solution 2

This solution removes all empty cells in the selection.

There is two functions in this one, one two find the first empty cell in a column. This function (as perhaps the rest as well) is probably possible to write more efficient.

function removeEmptyCellsInSelection(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
  var sheetLastRow = sheet.getLastRow();
  var range = sheet.getActiveRange();
  var rangeFirstRow = range.getRow();
  var rangeLastRow = range.getLastRow();
  var rangeFirstCol = range.getColumn();
  var rangeLastCol = range.getLastColumn();
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();
  var scriptSheetMaxRows = scriptSheet.getMaxRows();
  var scriptSheetMaxCols = scriptSheet.getMaxColumns();
  var insertRows = maxRows - scriptSheetMaxRows;
  var insertCols = maxCols - scriptSheetMaxCols;
  var currRow;
  var pasteCol = 1;
  
  scriptSheet.clear();
  
  if (insertRows > 0){
    scriptSheet.insertRows(1, insertRows);
  }
  
  if (insertCols > 0){
    scriptSheet.insertCols(1, insertCols);
  }
  
  
  
  for(var i = rangeFirstCol; i <= rangeLastCol; i++, pasteCol++){
    currRow = rangeFirstRow;
    for(var j = rangeFirstRow; j <= rangeLastRow; j++){
      var currValue = sheet.getRange(j, i).getValue();
      if(currValue != ''){
        var scriptSheetEmptyRow = getColumnLastRow(pasteCol, scriptSheet);
        sheet.getRange(j, i).copyTo(scriptSheet.getRange(scriptSheetEmptyRow, pasteCol));
      }
      currRow++;
    }
    var scriptSheetRow = getColumnLastRow(pasteCol, scriptSheet);
    for(; currRow <= sheetLastRow; currRow++){
      sheet.getRange(currRow, i).copyTo(scriptSheet.getRange(scriptSheetRow, pasteCol));
      scriptSheetRow++;
    }
  }
  
  var clearRange = sheet.getRange(rangeFirstRow, rangeFirstCol, maxRows, rangeLastCol - rangeFirstCol + 1);
  var scriptSheetRange = scriptSheet.getRange(1, 1, scriptSheet.getLastRow(), scriptSheet.getLastColumn());
  var pasteRange = sheet.getRange(rangeFirstRow, rangeFirstCol);
  
  clearRange.clear();
  scriptSheetRange.copyTo(pasteRange);
}

function getColumnLastRow(column, sheet){
  var row = 0;
  do{
    row++;
    var value = sheet.getRange(row, column).getValue();
  }while(value !== '')
    return row;
}

This solution is obviously faster the the previous one. It went through 1555 rows, 3 columns, in 02 minutes and 40 seconds.

I hope they can help!

The following script implements your second approach: convert all formula cells to the text displaying the formula. It does so in all sheets in the current spreadsheet.

The idea is simple: get all values (getValues) and formulas (getFormulas). If a cell contains a formula, then the new value is the formula text prepended by the apostrophe ' (which isn't displayed; it's just an indicator that the cell content should be treated as plain text).

After exporting the sheets as CSV (one by one, using the menu), you can restore the original state of the spreadsheet either by using revision history, or by running the same script again. (If the script runs again, a cell having text content =A1+1 will again become a formula =A1+1)

function formulasAsText() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var k = 0; k < sheets.length; k++) {
    var range = sheets[k].getDataRange();
    var values = range.getValues();
    var formulas = range.getFormulas();
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[0].length; j++) {
        values[i][j] = formulas[i][j] ? "'" + formulas[i][j] : values[i][j];
      }
    }
    range.setValues(values);
  }
}