Batch request removing empty rows and columns-CodePudding

I need to create a script which deletes all empty rows and columns (with no value in any cell of the row/column) from indicated sheet starting from 1 column/row, at the same time - using batch update. I have found a script

to:

CodePudding user response：

Try:

function clean() {

  const sheetName = "Parser"
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)

  const cleanRows = sheet.getDataRange()
                         .getValues()
                         .filter(row => !row.every(cell => cell === ``))

  const cleanCols = cleanRows[0].map((_, index) => cleanRows.flatMap(row => row[index]))
                                .filter(col => !col.every(cell => cell === ``))

  const values = cleanCols[0].map((_, index) => cleanCols.flatMap(row => row[index]))

  sheet.getDataRange().clearContent()
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values)

  if (sheet.getLastRow() !== sheet.getMaxRows()) sheet.deleteRows(sheet.getLastRow() 1, sheet.getMaxRows()-sheet.getLastRow())
  if (sheet.getLastColumn() !== sheet.getMaxColumns()) sheet.deleteColumns(sheet.getLastColumn() 1, sheet.getMaxColumns()-sheet.getLastColumn())
      
}

This will filter out all empty rows, rotate the array, filter out all empty 'columns', then rotate the array back and update the sheet.

function clear() {
    
  const sheetName = "Parser"
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
    
  sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).clearContent()

}

Let me know if this works for you!

Try:

Clean() Result: