Delete column if all cells after row 1 are blank-CodePudding

I have a sheet with a bunch of headings but in some sheets the cells after the header row are blank. I want to delete the column if all cells are blank except row 1 (header).

I have cobbled together the code below, however, it only retains the last array. Where am I going wrong!!

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getActiveSheet();
  var currentSheetRange = currentSheet.getDataRange();
  var currentSheetVals = currentSheetRange.getValues();
  var currentSheetLC = currentSheet.getLastColumn();
  var currentSheetLR = currentSheet.getLastRow();
  var finalData = [];
  let finalDataVal
  var c;
  var r;

  for (r = 1; r < currentSheetLR; r  ) {
    for (c = 0; c < currentSheetLC; c  ) {
      if (currentSheetVals[r][c] != "") {
        finalData.push(currentSheetVals[r][c]);
      }
    }
  }

  Logger.log(finalData);

  var finalDataLength = finalData.length;
  var finalDataColLength = finalData[0].length;
  currentSheet.getRange(5, 1, finalData.length, finalData[0].length).setValues(finalData);

}

Thanks in advance for anyone who has a solution!

CodePudding user response：

To delete all empty columns (headers excluded):

Retrieve the data range without headers
Loop through each of the columns and use join() to verify either all column entries are empty
Use deleteColumn() to delete empty columns
Loop backwards to make sure that the deletion of columns does not mess up your column positions

Sample:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getActiveSheet();
  var currentSheetLC = currentSheet.getLastColumn();
  var currentSheetLR = currentSheet.getLastRow();
  var SheetRangeWithoutHeaders = currentSheet.getRange(2, 1, currentSheetLR- 1, currentSheetLC);
  var currentSheetVals = SheetRangeWithoutHeaders.getValues();
  var c;
    
  for (c = currentSheetLC; c > 0; c--) {
    var columnData = currentSheetVals.map(row=>row[c-1]);
    console.log("columnData: "   columnData);
    if(columnData.join("") ==""){
      console.log("Column is empty");
      currentSheet.deleteColumn(c);
    }
  }
}

References: