CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apps-script-editor
kiki
kiki

Reputation: 199

Cannot activate a sheet from a standalone script (Google Apps script)

In order to protect the code (so people can't see it) I manage my Spreadsheet from a standalone script.

All functions work besides activating the sheet or cell (these functions work withing bound script, the calculations are correct, a cell gets activated).

I tried to activate the sheet from standalone script and then added it as a library and called from bound script of my file. None works.

I need to activate the sheet and the cell on spreadsheet opening.

Here's my code (bound to spreadsheet):

function onOpen() {      
    RandomNetwork.goToLastBlock()
}

Here's the code from a library (standalone script):

//goes to the last block on the sheet
function goToLastBlock() {  

    var file = SpreadsheetApp.openById("1kTOcxVv7RgIvp-BVvbZyBbeE92HiAKm8hbFURh19Enc")

    var sheets = file.getSheets()

    for (var i in sheets) {

        var sheetID = sheets[i].getSheetId()

        if (sheetID === 908402362) {

            var lr = sheets[i].getLastRow()
            var cell = sheets[i].getRange(lr, 1)

            sheets[i].activate()
            sheets[i].setCurrentCell(cell)

            return
        }
    }
  }

When I run it from the bound script it finds this spreadsheet and the sheet, calculates the last row correctly. But when I run from the script it doesn't do anything.

How can I activate the sheet and the cell from a standalone script? I'll appreciate any suggestions.

Upvotes: 1

Views: 1765

Answers (2)

Alan Wells
Alan Wells

Reputation: 31300

Every time that you change the code in the library, you need to publish a new version, and you must also change the version number in the bound script for the library.

As a test, I put the following code in a stand alone script:

//goes to the last block on the sheet
function goToLastBlock() {  

    var file = SpreadsheetApp.getActive();

    var sheets = file.getSheets()
    var theSheet = sheets[0];

    var lr = theSheet.getLastRow()
    var cell = theSheet.getRange(lr, 1)

    theSheet.activate()
    theSheet.setCurrentCell(cell)

    return theSheet.getSheetName();
}

Then, from a script bound to the sheet, I added the library (stand alone) to the bound script and ran the code from the bound script:

function onOpen() {      
    RandomNetwork.goToLastBlock()
}

The code activated the column A cell in the last row.

I published the library as a Web App to execute as "User Accessing the Web App" and "anyone within the organization"

It worked for me with the sample code provided and setting it up as described.

Upvotes: 1

Diego
Diego

Reputation: 9571

You cannot activate the sheet and cell from a standalone script.

Only container-bound scripts can make sheets & ranges active. The particular methods you're using are not explicitly mentioned in the container-bound description, but that's the reality.

Libraries have more nuanced scoping, which you can read more about here. As indicated in the table (in the link), libraries get access to the container. Essentially, because your container-bound script uses the library, the standalone script is copied–if you will–into the container-bound script and therefore gets access to those privileged methods.

Upvotes: 0

Related Questions