I spent a couple of hours trying to figure out why my script takes so long to process. I ended up realizing the culprit was autoResizeColumns(). The more rows on a sheet the longer it takes. It takes about a minute for about 6K rows. In my case I had to call it twice on two different sheets, both about 6K rows.
Is there really no other option? Doing this action manually on a sheet using the mouse takes significantly less time. So my question to you is what methods are you using to resize columns automatically?
Using the Advanced Sheets Service[1] you can build a batch request[2] in Google Apps Script for your Sheet which auto resizes the columns for you.
After enabling the Advanced Sheets Service from the Resources > Advanced Google services... menu option and clicking the On switch, you can build a batch request as a JSON object.
The structure of your batch request will have to look something like this:
{
"requests": [
{
"autoResizeDimensions": {
"dimensions": {
"sheetId": "your-sheet-id",
"dimension": "COLUMNS",
"startIndex": X,
"endIndex": Y
}
}
}
]
}
Where X and Y are integers representing the first and last columns you wish to have auto-resized. You can find more information about it in the 'Automatically resize a column' section[3] of the 'Row and Column Operations' documentation[4].
NB: The startIndex and endIndex values are array-like and so start at 0. If you wish to update the first 1000 columns you must use a startIndex of 0 and and endIndex of 999.
function resizeColumns() {
// You can still use the SpreadsheetApp methods to get the Spreadsheet
// and Sheet IDs which you need for the batch request
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getSheetByName("Name-of-Sheet-to-resize").getSheetId();
// Define the range of columns
var X = 0;
var Y = 999;
// Build the batch resource
var resource = {
"requests": [
{
"autoResizeDimensions": {
"dimensions": {
"sheetId": sheetId,
"dimension": "COLUMNS",
"startIndex": X,
"endIndex": Y
}
}
}
]
}
// Make the batch request
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}