1

Having had my code fixed by @Tanaike here How to run a function when specific range is edited, I have copied my code to the live sheet and now face some issues.

Is it possible for my trigger to run onFormSubit from a specific sheet (called 'ICU Request Form') and activate the sidebar in the Ui on the active sheet (which will ALWAYS be 'Tech Dashboard').

function createTrigger(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ICU Request Form")
   ScriptApp.newTrigger('installableOnEdit')
      .forSpreadsheet(ss)
      .onFormSubmit()
      .create()
}

function installableOnEdit(e){
  var range = e.range;
  var sheetName = e.source.getActiveSheet().getSheetName();
  if ( 
    sheetName == "ICU Request Form" &&
    range.rowStart >= 2 &&
    range.columnStart >=1 &&
    range.rowStart <= 19 &&
    range.columnStart <= 9 &&
    !e.oldValue && e.value
  ) {
    showSidebar();
     }
}
    var SIDEBAR_TITLE = 'Dashboard Notification!';


function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}


function onInstall(e) {
  onOpen(e);
}


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

Here is a copy of the live page so any edits won't affect the program. The Script project is called 'Notifications'. https://docs.google.com/spreadsheets/d/1hxLMHoxjp4KHKXRTzwdeXJSGhEz_kOChDrB9NVDSZ1E/edit?usp=sharing

Aidan Wilson
  • 51
  • 1
  • 10
  • Possible duplicate of [e.range.getA1Notation() unable to track changes caused by formula update](https://stackoverflow.com/questions/48828919/e-range-geta1notation-unable-to-track-changes-caused-by-formula-update) – TheMaster Aug 16 '19 at 12:57
  • I took your trigger, with my working `onEdit` code, then installed the trigger according to [These instructions](https://developers.google.com/apps-script/guides/triggers/installable) on "Managing triggers manually", and it worked fine. So the trigger seems OK? – Christofer Weber Aug 16 '19 at 14:19
  • Thanks for your comment @TheMaster. Having looked through that question, it says installable triggers won't run on script executions, can they run on a formula (filter)? Also, the answer given by the other user states about a value to check, is it possible that the value is a word, not numerical? – Aidan Wilson Aug 16 '19 at 14:19
  • Thanks Christofer, you're right, I think the trigger itself works but it's just doesn't recognize the edits when they are made. I'm sure it's due to the edits being made by function or script. – Aidan Wilson Aug 16 '19 at 14:25
  • Where range do you want to use? In the current script, the range is `A2:E10` on the sheet of `Tech Dashboard`. And in your current situation, a lot of projects are used. In order to test with the simple situation, when only one project is used, what result will you obtain? – Tanaike Aug 16 '19 at 23:38
  • The range I want to use is `F2:G19` even when I have the correct range then function won't run `onEdit`. It does run manually when just that project is in use, but will not trigger automatically. – Aidan Wilson Aug 17 '19 at 06:01
  • Your spreadsheet is shared as "View only" - users can't access your script editor or Project. – Tedinoz Aug 18 '19 at 04:20
  • Sounds like you know this already, but just to confirm [Script executions and API requests do not cause triggers to run.](https://developers.google.com/apps-script/guides/triggers/). Applies to both Simple and Installable triggers. – Tedinoz Aug 18 '19 at 04:24
  • Some basic troubleshooting: Insert `Logger.log(JSON.stringify(e));` as the first line of the onEdit function. Trigger the function and then switch to the Script editor; the View the log, copy/paste the results into your question, also view the Execution transcript, copy/paste the results into your question. – Tedinoz Aug 18 '19 at 04:34
  • `Having looked through that question, it says installable triggers won't run on script executions, can they run on a formula (filter)?` No. You need a actual human to do the edit. – TheMaster Aug 19 '19 at 06:41
  • Thanks everyone for your comments. In light of the fact that installable triggers wont run on script execution, i have edited my question. I would be grateful if you could take another look for me. – Aidan Wilson Aug 19 '19 at 10:09
  • onFormSubmit() is a trigger that is fired when a Google Form is submitted. A sheet is not a Google Form, even if it is called "ICU Request Form". Your best bet will be to use a time-driven trigger. – ziganotschka Aug 19 '19 at 15:28
  • Could you give me a hint as to how i can get that to work? I want the function to run everytime a new line of data is added to the form responses sheet called "ICU Request Form". I had attempted a time trigger but i'm getting it wrong. – Aidan Wilson Aug 19 '19 at 15:52
  • Is your sheet `ICU Request Form` the destination for your Google Form and is automatically populated by Google Form? In this case you could use onFormSubmit(). – ziganotschka Aug 20 '19 at 14:53
  • Is there a work around to allow `showSidebar(ui)` to run on the active sheet and not the sheet that receives the form data. My form submits to "ICU Request Form" however i want the sidebar activated in "Tech Dashboard" `onFormSubmit` – Aidan Wilson Aug 22 '19 at 21:35

0 Answers0