Google Apps Scripts:
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);
};