functionsortinggoogle-sheetsgoogle-apps-script

Deleting all columns if a cell is blank


I try to have a script to delete a column if my first cell of this column is blank. The row 1 have sometimes something and sometimes is blank. I want a macro that, when the cell from row 1 is blank, the macro will delete the entire column and check for next one until they are no more columns with cell from row 1 blank (for example if A1 is blank then delete column A and go to next until all the columns have been tested).

I cannot figure out where I made an error on the code.

I tried a script I already have for deleting one row after another until they are no more rows with a specific blank cell (I tried to modify it myself to get it for columns too).

The script work but do nothing, I don't have any errors:

function delmonth() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('CALMON'), true);

    var sheet = SpreadsheetApp.getActiveSheet();
    var e = sheet.getRange('A' + sheet.getMaxColumns())
                 .getNextDataCell(SpreadsheetApp.Direction.NEXT)
                 .getColumn() ;

    for (k = 1; k <= e; k++) {
        if(sheet.getRange('A' + k).getValue() == '') {
            sheet.deleteColumn(k);
            k=1;
            e--;
            if(k==e) {
                break
            };
            SpreadsheetApp.flush();
        }
    }
}

Solution

  • It seems like your code is geared towards working with rows, but you want to delete columns based on wheather the first row is blank. The issue could be in how you're iterating over the columns and check the values.

    Try something like this:

    function delmonth() {
        // removed unnecessary code to determine the sheet
        var sheet = spreadsheet.getSheetByName('CALMON');
      
        // Get the last column in the sheet
        var lastColumn = sheet.getLastColumn();
    
        // Start from the last column and move to the first column
        for (var col = lastColumn; col >= 1; col--) {
    
            // Check if the first row cell is blank
            var cellValue = sheet.getRange(1, col).getValue();
            if (cellValue === '') {
    
                // Delete the column if the first row cell is blank
                sheet.deleteColumn(col);
            }
        }
    }