GSheets: Script running very slowly and sometimes not performing task on all sheets

Question

My spreadsheet: Link

I've the following script which is supposed to enable me to control the columns to Hide/Unhide on specific sheets (A,B,C,D,E). There is a trigger on opening the sheet. If I run the script from the Script editor, it does perform the task, sheet after sheet very very slowly. However If I try on opening the spreadsheet, it starts with sheet A, then B, but does not complete it in B. Knowing that currently users are not inputting data, I wonder if this is the best approach (fearing to have at the end a spreadsheet which runs calculations and this script very slowly)

Could you please help with this little task ? I don't know much about writing scripts, this is merely few copy/paste from what I was able to find on the internet,

I appreciate your time!

Basically: I'm trying to control which week to show, I would show week 1 and hide the rest, then the following week, I would show Week 2 and hide the rest, etc...

Script

function hideColumnsOnSheet(sheetName) {
var b = SpreadsheetApp.getActive().getSheetByName(sheetName);
b.showColumns(1, b.getMaxColumns());
b.getRange('4:4')
    .getValues()[0]
    .forEach(function (r, i) {
        if (r && r == 'x') b.hideColumns(i + 1)
    });
}
function onOpen() {

hideColumnsOnSheet("A");
hideColumnsOnSheet("B");
hideColumnsOnSheet("C");
hideColumnsOnSheet("D");
hideColumnsOnSheet("E");
}

Simple triggers (such as function onOpen(e)) are limited in run time to 30 seconds: developers.google.com/apps-script/guides/triggers/#restrictions You should refactor the approach to first develop contiguous ranges of columns that should be hidden and then use whichever of the hideColumns() methods for those sets of ranges. Currently, you call hideColumns() once for individual columns, which, as you note, is slow. — tehhowch

Carr Carr · Accepted Answer · 2018-03-25T17:03:35

Reverse your original way to reduce the expensive API calls. Hide all columns at beginning, find the first and last column with space in row 4, and then show all columns in between, I've revised the method like below:

Update

Regrading to that all columns cannot be hid at beginning, we reverse the method back, but still reduce to only two hideColumns() API call.

function hideColumnsOnSheet(sheetName) {

    var b = SpreadsheetApp.getActive().getSheetByName(sheetName);
    b.showColumns(1, b.getMaxColumns());

    var visibleColumn = [];

    b.getRange('J4:4').getValues()[0].forEach(function (r, i) {
        if (r != 'x') {
            visibleColumn.push(i + 1);   
        }
    });

    var freezeColumnsNum = 9;
    var indexOfFirstShowColumn = parseInt(visibleColumn[0] + freezeColumnsNum);
    var indexOfLastShowColumn = parseInt(visibleColumn.pop() + freezeColumnsNum);

    if (indexOfFirstShowColumn != (freezeColumnsNum + 1)) {
        b.hideColumns(freezeColumnsNum + 1, (indexOfFirstShowColumn - 1) - freezeColumnsNum);
    }

    b.hideColumns(indexOfLastShowColumn + 1, (b.getMaxColumns() - 
   indexOfLastShowColumn));
}   

function onOpen(e) {

    hideColumnsOnSheet("A");
    hideColumnsOnSheet("B");
    hideColumnsOnSheet("C");
    hideColumnsOnSheet("D");
    hideColumnsOnSheet("E");
}

GSheets: Script running very slowly and sometimes not performing task on all sheets

1 Answers