Google Apps Scripts:

Walden Systems Geeks Corner News GOOGLE APP SCRIPT Rutherford NJ New Jersey NYC New York City North Bergen County

Working with google sheets one quickly realizes there are many good reasons to automate many tasks. Even simple tasks, like resizing columns and rows can take 4 to 5 mouse clicks plus a couple of keyboard clicks, to get accomplished. This is more than tedious, annoying and time consuming. In this blog we will go over a simple yet very useful function to automate the task of resizing columns.

I often work with data sets that are populated from a database or another spreadsheet to analyze big chunks of data. Once I have my spreadsheet built out from my dataset, I automate many of the tasks from within my Apps Script code base. If you tried using Google Sheets you know that the default size of a column is fairly small and is set to 100 pixels. When I build my sheet from a database or another datasource, I often need to resize the columns in order to fit the actual data length. It is hard to work visually with a cell that displays my data partially and cuts off the rest.


Here a nifty function that will resize all or a couple of columns, comes in handy ‘autoResizeColumns()’. There is also a function that resizes a single column at a time called 'autoResizeColumn()’. But, I prefer to use the multiple column resize function, because it is more extensible and can handle resizing single columns also. Let's start with our function signature that takes three parameters: strtCol, numOfCols and sheet.


function resizeColumns(strCol, numOfCols, sheet) 

The ‘numOfCols’ parameter will control how many columns to resize from the starting column ‘strCol’. If you need to resize a single column then ‘numOfCols’ will simply be one (1) or any number that you require. This is the reason why I choose not to use the function that only resizes one column at a time.

Putting it all together:


function resizeColumns(strCol, numOfCols, sheet) {
  sheet.autoResizeColumns(strCol, numOfCols);
};

function myMainBlog() {
  const sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var firstSheet = sSheet.getSheets()[0];

  firstSheet.getRange('A1').setValue('Now is the time for all good men to come to the aid of the party...');
  firstSheet.getRange('B2').setValue('Now is the time for all good men to come to the aid of the party...');
  firstSheet.getRange('C3').setValue('Now is the time for all good men to come to the aid of the party...');
  firstSheet.getRange('D4').setValue('Now is the time for all good men to come to the aid of the party...');
  resizeColumns(1, 1, firstSheet);
  resizeColumns(3, 2, firstSheet);
};