How to resize columns from minimum width to auto resize

Question

I have a spreadsheet with a number of sheets. The first sheet has X used columns and the remaining sheets have Y used columns. What I want to do is resize the columns between a given width and the auto-resize width if it is bigger.

So far I have the following code which auto-resizes the columns.

function resizeColumns() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  var lastColumnMain = sheets[0].getLastColumn();
  var lastColumnUniversities = sheets[1].getLastColumn();

  for (var i = 0; i < sheets.length; i++) {
    var range;
    if (i == 0) {
      sheets[i].autoResizeColumns(1, lastColumnMain);
      range = sheets[i].getRange(1, 1, 1, lastColumnMain);
    } else {
      sheets[i].autoResizeColumns(1, lastColumnUniversities);
      range = sheets[i].getRange(1, 1, 1, lastColumnUniversities);
    }
    // Change width if less than 110 pixels
  }
}

I thought about using a range to somehow get the column width and then change from there if they were too small but I'm lacking the vision to see exactly how to do so.

Answer 1

I was looking for the answer to this one as well, and the following script is what I am currently using. First I created a dummy sheet with a header row exactly the same as the report I ran and set the columns to the widths I wanted in each report . Then ran the following to get the widths of each column paired with it's index and push the values to an array , and set them in the row above the header as well.

    function colWidth() {
       var WidthArry = [];
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Setup'); //.getSheets()[0];
       var numColumns = 21; // sheet.getMaxColumns(); 
       var columnPosition, width;
       var i = 0;
         while (i < numColumns) {
           columnPosition = i + 1;
            Logger.log(columnPosition);
          width = sheet.getColumnWidth(columnPosition);
          //push position and width to array
           WidthArry.push([columnPosition, width]);
            //set width value in cell above header col see image
          sheet.getRange(1, columnPosition).setValue(width);
          Logger.log(columnPosition);
          i = i + 1;
        }
         Logger.log(WidthArry.toString());
        return WidthArry.toString();
         }//end function

Which returns the following as well as setting the value in the 1st cell in each column of my header. set width value in cell above header cell Logger.log([1,218,2,100,3,100,4,40,5,502,6,100,7,76,8,100,9,72,10,100,11,75,12,100,13,71,14,70,15,100,16,100,17,100,18,26,19,20,20,105,21,15, []])

Because this report does not change as far as column widths, I just use each pair in a function call to reset the columns as soon as the data is parsed to the sheet.

    function SetWidth(e){
         var ss = SpreadsheetApp.getActiveSpreadsheet();
         var NwSht;
           Logger.log(e);
        if(!e){  return ss.toast("no event values");
          }else{
           NwSht = e;
            }
          var sheet = ss.getSheetByName(NwSht);
              sheet.setColumnWidth(1, 218);
              sheet.setColumnWidth(2, 100);
              sheet.setColumnWidth(3, 100);
              sheet.setColumnWidth(4, 40);
              sheet.setColumnWidth(5, 502);
              sheet.setColumnWidth(6, 100);
              sheet.setColumnWidth(7, 76);
              sheet.setColumnWidth(8, 100);
              sheet.setColumnWidth(9, 72);
              sheet.setColumnWidth(10, 100);
              sheet.setColumnWidth(11, 75);
              sheet.setColumnWidth(12, 100);
              sheet.setColumnWidth(13, 71);
              sheet.setColumnWidth(14, 70);
              sheet.setColumnWidth(15, 100);
              sheet.setColumnWidth(16, 100);
              sheet.setColumnWidth(17, 100); 
              sheet.setColumnWidth(18, 26);
              sheet.setColumnWidth(19, 20);
              sheet.setColumnWidth(20, 105);
              sheet.setColumnWidth(21, 15);
           //setup toast to notify the task completed
          ss.toast("Column Widths Resized");
        }//end function

I am positive there is a more elegant way to do this, but I thought I would share for anyone else who was looking for a way to get and set column widths.