Google Script for deleting blank or unused columns

Question

I have searched and searched but can't find anything that works. I am trying to eliminate any empty columns that remain in the spreadsheet when creating a Google Form. It would be helpful because I have a formula for the sum of a string comparison in the sheet, but I don't want it to include comparisons of empty cells. (i.e. I compare the entries in from H:X in every individual row to H2:X2 and have it count how many are the same.) I know I could adjust the formula, but I am looking to build a template for fellow colleagues so they don't have to worry about altering anything. If I could get the empty columns at the end to disappear automatically, I could just change the sum formula to extend all the way to column CZ (just to be sure it goes far enough), without it calculating blanks in its comparison.

Any help would be great! Thanks!

I'm not sure I understand the question fully. If you have a Google form, all columns are populated from the fields in the form. You can make a field mandatory to avoid it being blank. Can you add more specific information to your question, with an example, perhaps ? What is the output you are expecting? Have you written a script (since you've tagged it with google-apps-script)? If so, can you post the code that you already have Since you're new to SO, I suggest you take a look at the SO checklist meta.stackexchange.com/questions/156810/… — Srik

Trey Trey · Accepted Answer · 2016-01-14T04:39:19

i use these scripts in the script editor of my google sheets for cleaning them up. stumbled across them somewhere... perhaps they will help.

//Remove All Empty Columns in the Entire Workbook
function removeEmptyColumns() {
  var ss = SpreadsheetApp.getActive();
  var allsheets = ss.getSheets();
  for (var s in allsheets){
    var sheet=allsheets[s]
    var maxColumns = sheet.getMaxColumns(); 
    var lastColumn = sheet.getLastColumn();
    if (maxColumns-lastColumn != 0){
      sheet.deleteColumns(lastColumn+1, maxColumns-lastColumn);
    }
  }
}

//Remove All Empty Rows in the Entire Workbook
function removeEmptyRows() {
  var ss = SpreadsheetApp.getActive();
  var allsheets = ss.getSheets();
  for (var s in allsheets){
    var sheet=allsheets[s]
    var maxRows = sheet.getMaxRows(); 
    var lastRow = sheet.getLastRow();
    if (maxRows-lastRow != 0){
      sheet.deleteRows(lastRow+1, maxRows-lastRow);
    }
  }
}

Google Script for deleting blank or unused columns

3 Answers