0

Is it possible to get the Ui of the active sheet using a trigger which is activated by another sheet?

I want to showSidebar() in a sheet called Tech Dashboard whenever a form is submitted to the sheet called ICU Request Form.

The Tech Dashboard is always going to be the active sheet and my sidebar just shows a notification onFormSubmit

function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle(SIDEBAR_TITLE);
  SpreadsheetApp.getUi().showSidebar(ui);
}

I'm aware you can't invoke the Ui from a sheet other than the active one so i was hoping for a workaround.

Aidan Wilson
  • 51
  • 1
  • 10
  • 1
    [Edit] to clarify terms- sheet means tab. spreadsheet means the file. – TheMaster Aug 23 '19 at 07:05
  • Can you clarify what you mean by this? When one user submits the form, you want it to trigger a UI event for another user who has the sheet open? Or do you want one user to submit the form, and then be taken to the sheet which then opens the sidebar? – sinaraheneba Aug 23 '19 at 07:12
  • Hi @sinaraheneba. I have the sheet called 'Tech Dashboard' on display in my office. When an external user submits a form, I want the sidebar to open on my screen. Hope this clarifies, sorry for the confusion. – Aidan Wilson Aug 23 '19 at 14:06

1 Answers1

1

showSidebar() will show a side bar in all sheets of a spreadsheet, no matter from which sheet within a spreadsheet it is called.

However, referring to your comment in your last question, showSidebar() cannot be triggered onFormSubmit, because SpreadsheetApp.getUi() cannot be accessed from this context.

You can trigger showSidebar() only with the installable triggers which require a user action - that is onOpen, onEdit and OnChange.

As a workaround, e.g. paste a notification text into a cell of the desired spreadsheet triggered by the installable trigger onFormSubmit:

function calledOnFormSubmit() { 
SpreadsheetApp.openById(YOUR SPREADSHEET ID).getSheetByName('Tech Dashboard').getRange('A1').setValue('a form was submitted');
}
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • This won't allow the script to activate my .HTML file. This is the only reason for this code. It has a notification sounds and a message to display. Is there any way to use a timed trigger to check the contents of the form sheet and then getUi if there is a change, for example, see if the maxRows has increased from the last check? – Aidan Wilson Aug 23 '19 at 14:13
  • Timed trigger does not work either. As stated above, only trigger detecting human-made actions (open, edit, change) can access showSidebar(). https://stackoverflow.com/questions/26690333/google-apps-script-time-driven-trigger-not-executing-any-spreadsheetapp-getui – ziganotschka Aug 23 '19 at 17:01
  • Ah that's a pain! Back to the drawing board then. Thanks for your help. – Aidan Wilson Aug 23 '19 at 21:21