0

I'm new to Google Sheets and Apps Script. I have a sheet with two URLS. In cell F1 is http://testurl1.com and in cell G1 is http://testurl2.com.

I would like to have a button or link or something in cell D1 that when I click it will open both of these links. I can do this manually with Alt-Enter but haven't been able to translate that to code.

I have been able to open both these urls from a menu item, but when I try calling the code from a cell it says

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

But the code works from a menu item. Weird. The code I'm currently trying to use is below but I am open to any suggestions! 

function callOthers() {
  myFunction()
   Utilities.sleep(1500);
  myFunction2() 
}

function myFunction() {    
  var sheet = SpreadsheetApp.getActiveSheet();  
  var selection = sheet.getRange("F1").getValue(); 
    var html = "<script>window.open('" + selection + "');google.script.host.close();</script>";  
    var userInterface = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');  
}

function myFunction2() {  
    var sheet = SpreadsheetApp.getActiveSheet();  
   var selection2 = sheet.getRange("G1").getValue(); 
   var html2 = "<script>window.open('" + selection2 + "');google.script.host.close();</script>";  
   var userInterface2 = HtmlService.createHtmlOutput(html2);  
     SpreadsheetApp.getUi().showModalDialog(userInterface2, 'Open Tab');  
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Nathan Heuton
  • 1
  • 2
    That depends on how you use your `myFunction` and `myFunction2`. There is a difference between *custom functions* and normal functions. All functions "live" in a script project, not in the cell. `getUI()` method requires authorization from the user and thus cannot be run in the context of custom functions (which is excatly what error says). But you can run them using "buttons" (via drawings / images) or menu items – Oleg Valter is with Ukraine Jun 03 '20 at 19:15
  • Is there an easier way to open two links at once without code? Or a better way to code it? Thank you! – Nathan Heuton Jun 03 '20 at 19:23
  • 1
    yes, that's called a user :) But, seriously, I think that's the best you can do given the task. And even that is not a [recommended](https://developer.mozilla.org/en-US/docs/Web/API/Window/open#Usability_issues) course of action, however convenient it might seem.Users should have a say in how to open links (ofc, it might be totally ok for your purposes) – Oleg Valter is with Ukraine Jun 03 '20 at 19:32
  • 1
    Thank you Oleg for the info! I went with the button option and it is working. – Nathan Heuton Jun 03 '20 at 19:45
  • Hi @OlegValter Could you post your commenr as an answer so it can be upvoted and accepted for better documentation purpose? – Raserhin Jun 04 '20 at 10:07
  • Hi Raserhin, thank you for suggesting - the question core is probably a duplicate - I'll check and suggest community to link fhem if any, otherwise will surely add an answer) – Oleg Valter is with Ukraine Jun 04 '20 at 10:11
  • Apparently, other Q&As are concerned with trigger context - so I decided to make a dedicated answer. If someone sees this and thinks that [other](https://stackoverflow.com/questions/56008432/cannot-call-spreadsheetapp-getui-from-this-context-but-not-using-getui-on-tim) threads (also [this](https://stackoverflow.com/questions/33353333/cannot-call-formapp-getui-from-bound-google-forms-script) and [this](https://stackoverflow.com/questions/14703413/trigger-google-apps-script-in-spreedsheet-from-context-menu)) are supersets of this - feel free to flag / vote as duplicate to reduce fragmentation – Oleg Valter is with Ukraine Jun 04 '20 at 11:39

1 Answers1

1

Problem

Custom function is blocked from being run when used in a cell

Explanation

There are three main ways of making a bound script function available in the Spreadsheet UI:

  1. As a custom function that can be used like a formula
  2. As a menu item that will run the function on click
  3. As a "button" created via image or drawing that will run the function on click

All three have different execution context and limitations on what they can and cannot access, the most restrictive being the first. Custom functions execution context is bound to the cell it is called in, so you cannot do anything that affects the UI as a whole, which getUi() allows to do.

Additionally, since showModalDialog is a method that requires authorization on behalf of the user, even if the getUi() method was available, you could not show the dialog due to the fact that custom functions never ask users to authorize access to personal data.

Solution

If you want to interact with UI, you should either create a menu or a button as mentioned before.

Please note that users will have to give your script their permission for the following scope:

https://www.googleapis.com/auth/script.container.ui

References

  1. Custom functions guide
  2. showModalDialog method reference
  3. getUi() method reference
Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59