0
484
VistasHow to delete cells in a range of a column in google app script using getLastRow()
I've been looking through stackoverflow for some help on how to script in google sheets a function that would allow me to remove empty cells in a range and shift them over (to the left) when completed. I've only seen questions regarding removing empty rows or columns instead of cells in a range.
This is what the data looks like I'm trying to work with:
I need to delete cells A1-A15 and shift over the other columns.
What's currently working is the following code, however it will delete/shift over the column even if there is data in the column (A) that I'm having the function review (ex. data in cell A6).
I want to ensure that this function doesn't go off if there is data in column A before the last and 2nd to last data entry as per the example.:
function blankRemoval(){
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1');//need to call sheet by name as I have multiple tabs
var lastRow = sheet.getLastRow()
var cellsBlank = sheet.getRange(1,1).isBlank();
if(cellsBlank) {
sheet.getRange(1,1,lastRow-3,1).deleteCells(SpreadsheetApp.Dimension.COLUMNS); // shifts cells left
}//end of if
}//end of functionSo with this in mind, I thought I could make the variable cellsBlank to use .getRange(1,1,lastRow-3,1) as well so that it only looks through the range to the last row, and stops 3 from the last row. However when I use this code, it doesn't delete the cells if there is data in them or not (the function runs without errors but nothing occurs).
I've tried a few different items to use on this but I'm running into a wall as I'm not a proficient programmer. My guess is that it needs to iterate, or I'm doing something wrong with the range.
Also to note, there will always be 2 data entries in column A like my example, with data being one cell apart. This is a entry of sorts to help with historical review. I want to retain these two cells where they are, but shift the empty data above
2 Respuestas
Responde la pregunta0
Probably it should be this:
function blankRemoval(){
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1');
var col_A = sheet.getRange(1,1,sheet.getLastRow()-3,1);
if (col_A.getValues().flat().filter(String).length == 0) { // <-- if the col A is empty
col_A.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
}
}
.getValues() get a 2d array with values
.flat() convert the 2d array into an 1d array
.filter(String) remove from the array all empty cells
.length get a length of the array
This way, if the range contains empty cells only the array will have no elements (array.length = 0)
Before:
After:
Update
It turned out that some cells in column 'A' could contain spaces. Here is modified function that removes spaces from the range:
function blankRemoval(){
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1');
var col_A = sheet.getRange(1,1,sheet.getLastRow()-3,1);
var contents = col_A.getValues().flat().join('').replace(/\s+/g,'');
if (contents == '') col_A.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
}
0
This seems to be the best function to use for the question I posed thanks to @Yuri. The clearContent helps remove any entries that would prevent the deleteCells action to go
function blankRemoval(){
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1');
var col_A = sheet.getRange(1,1,sheet.getLastRow()-3,1);
if (col_A.getValues().flat().filter(String).length == 0) {
col_A.clearContent();} //<---checks if cell range is empty, and if so performs a clear content (this seemed to fix issues with any ghost entries)
if (col_A.getValues().flat().filter(String).length == 0) { // <-- if the col A is empty
col_A.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
}
}