0
180
VistasBatch request removing empty rows and columns
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 here and tried to suit it to my need.
I have modified it like below, but I do something wrong with function arguments (and probably something else).
function clear(){
const sheetName = "Parser"; // Please set the sheet name.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sh = ss.getSheetByName(sheetName);
const sheetId = sh.getSheetValues;
var values = sh.getRange('B1:B').getValues();
var requests = values.reduce((ar, value) => {
var maxColumns = sh.getMaxColumns();
var lastColumn = sh.getLastColumn();
var maxRows = sh.getMaxRows();
var lastRow = sh.getLastRow();
if (lastRow == 0 && lastColumn == 0 && maxRows > 1 && maxColumns > 1) {
ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "ROWS", startIndex: 1}}});
ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "COLUMNS", startIndex: 1}}});
}
Logger.log(ar);
return ar;
}, []);
if (requests.length > 0) {
Sheets.Spreadsheets.batchUpdate({requests: requests}, id);
}
};
Begging for help!
From:
to:
·
Juan Pablo Isaza
1 Respuestas
Responde la pregunta0
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!
Clean() Result:
·
Juan Pablo Isaza
Denunciar
Responde la pregunta