CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulaspreadsheet
Leanne
Leanne

Reputation: 1

How to delete empty columns with header in google sheet?

Someone asked this before but not working How do I delete empty columns with the header in google sheet? I still have issues that it shows cannot delete the column but only can hide. How to make the code work?

Upvotes: 0

Views: 373

Answers (2)

Lod
Lod

Reputation: 769

@Cooper's the prefered solution.

I just found this other Filtering with Query formula method from this source that might help for result but without headers in blank columns:

= ArrayFormula(Query(transpose(Query(TRANSPOSE({
    Query({
        Sheet1!A1:MJ1;to_text(Query({
            if(Sheet1!A2:MJ<>"", 1, 0)
        }, "Select "&JOIN(",","Sum(Col"&column(Sheet1!A1:MJ1)&")")))
    },"Offset 1",1);
    Sheet1!A2:MJ
}), "Select * Where Col2<>'0'",1)),"Select * Offset 1", 1))

Upvotes: 0

Cooper
Cooper

Reputation: 64082

Delete All Empty Columns

function deleteAllEmptyColumns() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const cols = sh.getLastColumn();
  let d = 0;
  [...Array.from(new Array(cols).keys())].forEach((idx => {
    if (sh.getRange(1, idx + 1 - d, sh.getLastRow()).getValues().flat().filter(e => e).length == 0) {
      sh.deleteColumn(idx + 1 - d++);//delete counter increments here
    }
  }));
  sh.deleteColumns(sh.getLastColumn() + 1, sh.getMaxColumns() - sh.getLastColumn());

}

You must remember to keep track of the columns deleted to the left of getLastColumn().

Upvotes: 2

Related Questions