Search code examples
google-apps-scriptgoogle-sheetscustom-function

Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 1)

I have a project with two scripts, that is bound to a Google spreadsheet. The first script opens the UI

    var ui = SpreadsheetApp.getUi();
var userProperties = PropertiesService.getUserProperties();

const TOKEN_OLD = 'api.token_old';

function onOpen(){

  ui.createMenu('CREDENZIALI')
  .addItem('Imposta TOKEN', 'setToken')
  .addToUi();
}

function setToken(){
  var scriptValue = ui.prompt('Per favore inserisci il token.' , ui.ButtonSet.OK);
  userProperties.setProperty(TOKEN_OLD, scriptValue.getResponseText());
}

And the second one does the business logic and implements a custom function that is meant to be used into the spreadsheet.

function getFromApi(book_id,dato) {...}

Obviously, this API is behind authentication and I don't want to hardcode a token into the script, so I've created the first UI in order to let the end-user manage it at runtime.

The UI works flawlessly but when I try to invoke the custom function, I get this error: enter image description here

I am the owner of the files. What am I missing ?

Solution

  • Try to add SpreadsheetApp.getUi(); inside onOpen() and setToken() separately.

    The following script works as standalone:

    var userProperties = PropertiesService.getUserProperties();

const TOKEN_OLD = 'api.token_old';

function onOpen(){
  
  SpreadsheetApp.getUi()
  .createMenu('CREDENZIALI')
  .addItem('Imposta TOKEN', 'setToken')
  .addToUi();
}

function setToken(){
  var ui = SpreadsheetApp.getUi();
  var scriptValue = ui.prompt('Per favore inserisci il token.' , ui.ButtonSet.OK);
  userProperties.setProperty(TOKEN_OLD, scriptValue.getResponseText());
}

    However, you can not use a custom formula to call this function. See related articles: