Google Apps Scripts: Resizing sheet rows automatically

Walden Systems Geeks Corner News Google Apps Scripts: Resizing sheet rows automatically 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 rows.

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 Google Apps Script code base. If you tried using Google Sheets you know that the default size of a row is fairly small and is set to 21 pixels. When I build my sheet from a database or another datasource, I often need to resize the rows and 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 one or more rows, comes in handy ‘autoResizeRows()’. Unlike the function 'autoResizeColumn()’ to resize a single column there is no function to resize a single row. The multiple resize row function is flexible and more extensible to get our job done quickly. Let's start with our function signature that takes three parameters: strRow, numOfRows and sheet. 


function resizeRows(strRow, numOfRows, sheet)
The ‘numOfRows’ parameter will control how many rows to resize from the starting row ‘strRow’. If you need to resize a single row then ‘numOfRows’ will simply be one (1) or any number that you require. Putting it all together: 

function resizeRows(strRow, numOfRows, sheet) {
  sheet.autoResizeRows(strRow, numOfRows);
};

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...');

  resizeRows(1, 1, firstSheet);
  resizeRows(3, 2, firstSheet);
};