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

0
votes

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.

2
google-apps-scriptgoogle-sheetsgoogle-apps-script-editor
Ppl can easily get your library code from the bound script: Logger.log(RandomNetwork.goToLastBlock) – TheMaster
Stack Snippets should be used only for executable HTML/CSS/JavaScript code. In very rarely case it could be use on Google Apps Script client-side code but "never" with server-side code (you should create an api to your server side code for that which is something too convuluted for most of the Google Apps Script questions. – Rubén
@TheMaster wow. What's the solution for security? – kiki
Sorry @Ruben didn't get what you've said) – kiki
@kiki What exactly is your security issue? Do you need to prevent people from seeing the code or do you need to prevent them from modifying the code? If you don't want people to see the code, is there a particular reason? Most "secrets" can be hidden by using the properties service. – Diego

2 Answers

1
votes

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.

0
votes

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.