Checking checkbox does not open sidebar window

Question

As a follow up to Trigger function by checking checkbox, I have a sheet with checkboxes in the first column. I would like to be able to open a ui sidebar by checking a box? Right now I have a function popupRow that creates the sidebar and starts with:

function popupRow() {

      Logger.log('popupRow1');
  var id = 'vvvvvvvvvVYpeOmRn6lVk-xxxxxxxx'
 var sheet =    SpreadsheetApp.openById(id).getSheetByName('mysheet');
 var headerRow = sheet.getFrozenRows();

      Logger.log('popupRow2');
  ....

Following what I saw in the previous question I tried:

function onEdit(e){
 if (e.range.columnStart == 1 && e.range.columnEnd == 1 && e.range.rowStart <= 2000) {

    Logger.log('ONEDIT');
    popupRow();

The sidebar does not open and the logs show:

[19-03-02 10:30:23:034 EST] ONEDIT
[19-03-02 10:30:23:034 EST] popupRow1

Execution does not get to the line containing:

      Logger.log('popupRow2');

Why not? How can I get this working?

You're using unauthorized functions. Review the restrictions associated with simple triggers. You can view these restrictions in documentation, and view the errors your script is throwing in Stackdriver — tehhowch, Mar 02 '19 at 16:00
Possible duplicate of [Google Script - OnEdit problems](https://stackoverflow.com/questions/22333232/google-script-onedit-problems) — Rubén, Mar 03 '19 at 00:37

Cooper · Accepted Answer · 2019-03-02T23:42:19.503

Open Sidebar and/or Dialog with Checkbox or Menu

Codes.gs:

function onOpen() {
  menu();
}

function menu() {
  SpreadsheetApp.getUi().createMenu('My Menu')
  .addItem('Insert CheckBoxes', 'insertCheckboxes')
  .addItem('Create OnEdit Trigger', 'createOnEditTrigger')
  .addItem('Open Dialog', 'openTheDialog')
  .addItem('Open Sidebar', 'openTheSidebar')
  .addItem('Delete Trigger', 'deleteOnEditTrigger')
  .addToUi();
}

function openTheDialog() {
  var userInterface=HtmlService.createTemplateFromFile('example1').evaluate();
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Example 1 HTML');
}

function openTheSidebar() {
  var userInterface=HtmlService.createTemplateFromFile('example1').evaluate();
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

function getColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var lastrow=sh.getLastRow();
  if(lastrow==0)return 0;
  var rg=sh.getRange(1,col,lastrow,1);
  var vA=rg.getValues();
  while(vA.length>0 && vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getRowColumn() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveCell();
  var rObj={row:rg.getColumn() ,column:rg.getRow()};
  return rObj;
}

function getCellA1() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveCell();
  var rObj={A1:rg.getA1Notation()};
  return rObj;
}

function onCheckOpenSideBar(e) {
  if(e.range.getSheet().getName()!='Sheet1')return;
  if(e.range.rowStart==2 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      openTheSidebar();
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==3 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      openTheDialog();
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
}

function createOnEditTrigger() {
  ScriptApp.newTrigger('onCheckOpenSideBar').forSpreadsheet('Spreadsheet ID').onEdit().create();
}

function insertCheckboxes() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  sh.getRange(2,1,2).insertCheckboxes();
}

function deleteOnEditTrigger() {
  var tA=ScriptApp.getProjectTriggers();
  for(var i=0;i<tA.length;i++) {
    if(tA[i].getHandlerFunction()=='onCheckOpenSideBar') {
      ScriptApp.deleteTrigger(tA[i]);
    }
  }
}

Script.html:

<script>
  function getRowCol(){
    $('#rc').css('background-color','#ffff00');
    google.script.run
    .withSuccessHandler(function(rObj){
      $('#rc').val(rObj.row + ',' + rObj.column);
      $('#rc').css('background-color','#ffffff');
    })
    .getRowColumn();
  }
  function getCellA1(){
    $('#A1').css('background-color','#ffff00');
    google.script.run
    .withSuccessHandler(function(rObj){
      $('#A1').val(rObj.A1);
      $('#A1').css('background-color','#ffffff');
    })
    .getCellA1();
  }
  function getColumnHeight(){
    $('#colheight').css('background-color','#ffff00');
    google.script.run
    .withSuccessHandler(function(h){
      $('#colheight').val(h);
      $('#colheight').css('background-color','#ffffff');
    })
    .getColumnHeight();
  }
</script>

css.html:

<style>
body {background-color:#ffffff;}
input{padding:2px;margin:2px;}
</style>

res.html:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

example1.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('res') ?>
    <?!= include('css') ?>
  </head>
  <body>
   <input id="A1" type="text" placeholder="A1Notation" size="6" /><input type="button" value="Cell A1" onClick="getCellA1();" />
   <br /><input id="rc" type="text" placeholder="Row , Column" size="6" /><input type="button" value="Row,Column " onClick="getRowCol();" />
   <br /><input id="colheight" type="text" placeholder="Column Height" size="6" /><input type="button" value="Column Height" onClick="getColumnHeight();" />
   <?!= include('script') ?>
  </body>
</html>

Thanks, there is a lot of interesting code here. I haven't got it working yet though (just added onCheckOpenSideBar and openTheSidebar) — user1592380, Mar 05 '19 at 17:54

Checking checkbox does not open sidebar window

1 Answers1

Open Sidebar and/or Dialog with Checkbox or Menu