0

I want to create a simple guidance tool in sheets using the sidebar function. Based on the column they select in the sheet I would like the html file to change to match the column. So that the sidebar message changes as they move columns.

along side this I have created simple HTML templates with a different messages i.e, but I simply cant work out the best way to find the active column the user selects and then based on the column reference it will pick up the correct HTML file.

So say they hit a cell that is in column D the sidebar will load html4 file to display message associated with column D.

I'm sure this is fairly simple to do, but I'm no expert!

     var ui = SpreadsheetApp.getUi();
  ui.createMenu("Risk Menu")
  .addItem("Add New Risk", "sideBar")
  .addToUi()
}


function sideBar() {

  var html= HtmlService.createHtmlOutputFromFile("sideBar").setTitle("Risk Guidance")
  var ui = SpreadsheetApp.getUi();
  ui.showSidebar(html);

}

function sideBar2() {
  var ui = SpreadsheetApp.getUi();
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Risk Register');
  var Cell = sh.getActiveCell();
  var Column = Cell.getColumn();
}
Tom
  • 87
  • 1
  • 9
  • There's no select trigger. So I don't think this is possible easily. But you can poll from html side https://stackoverflow.com/a/30634581 (not recommended) – TheMaster Apr 17 '19 at 15:26

1 Answers1

0

Executing functions with just a click on a check box

You could put a row of checkboxes along the top just below the headers that allows user to just click on the checkbox and use that to launch the sidebar with guidance info.

Here's a project I did when learning how to use checkboxes. I allows me to open and close the sidebar dialog with just a click of a checkbox. It also has the feature of being able create onEdit trigger programmatically. It's quite a bit of code but your welcome to it. If you freeze the line just below the checkboxes then these controls will always be easy to reach.

Codes.js:

function onOpen() {
  menu();
}

function doGet(e) {
  return HtmlService.createTemplateFromFile('example1').evaluate();
}

function menu() {
  SpreadsheetApp.getUi().createMenu('My Menu')
  .addItem('Insert CheckBoxes', 'insertCheckboxes')
  .addItem('Create OnEdit Trigger', 'createOnEditTrigger')
  .addItem('Open Dialog', 'openTheDialog')
  .addItem('Close Dialog', 'closeDialog')
  .addItem('Open Sidebar', 'openTheSidebar')
  .addItem('Display Project Triggers', 'displayProjectTriggers')
  .addItem('Delete Trigger', 'deleteOnEditTrigger')
  .addSubMenu(SpreadsheetApp.getUi().createMenu('Utilities')
             .addItem('Named Range', 'jjeSUS1.createNamedRange')
             .addItem('Skip Header Select', 'jjeSUS1.selectColumnsSkipHeader'))
  .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");
    }
  }
  if(e.range.rowStart==4 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      sortByCol(3);
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==5 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      sortByCol(4,5);
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==6 && e.range.columnStart==1) {
    if(e.value=='C') {
      sortByCol(3);
    }
  }
  if(e.range.rowStart==6 && e.range.columnStart==1) {
    if(e.value=='D,E') {
      sortByCol(4,5);
    }
  }
  if(e.range.rowStart==7 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      closeSideBar();
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==8 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      closeDialog();
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
}

function sortByCol(col,col2) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getRange(1,3,getColumnHeight(col,sh,ss),3);
  if(col2) {
    rg.sort([{column: col,ascending:true},{column: col2,ascending:true}]);
  }else{
    rg.sort({column: col, ascending:true});
  }
}

function createOnEditTrigger() {
  ScriptApp.newTrigger('onCheckOpenSideBar').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  displayProjectTriggers();
}

function insertCheckboxes() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  sh.getActiveRange().insertCheckboxes();
}

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

function displayProjectTriggers() {
  var tA=ScriptApp.getProjectTriggers();
  var html="<style>th,td{border:1px solid black;padding:2px;margin:2px;}</style><table><tr><th>Handler Function</th><th>Trigger Type</th><th>Unique ID</th></tr>";
  for(var i=0;i<tA.length;i++) {
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>', tA[i].getHandlerFunction(),tA[i].getEventType(),tA[i].getUniqueId());
  }
  html+='</table>';
  var userInterface=HtmlService.createHtmlOutput(html).setWidth(800);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Project Triggers');
}

function closeSideBar() {
  var userInterface=HtmlService.createHtmlOutputFromFile('dummy');
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

function closeDialog() {
  var userInterface=HtmlService.createHtmlOutputFromFile('dummy');
  SpreadsheetApp.getUi().showModelessDialog(userInterface,'Closing');
}

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>

dummy.html: (self closing html for closing other dialogs or sidebars)

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <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>
    <script>
    $(function(){
      google.script.host.close();
    });
    </script>
  </head>
  <body>
  </body>
</html>

I posted images of all of the sheets because I'm not sure if parts of the project utilizes those sheets so it might help you to figure what some sections are doing.

enter image description here

enter image description here

enter image description here

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks for taking the time to post this Cooper, managed to replicate your work and get it working. Gives me something to work with adapting to my requirements. – Tom Apr 18 '19 at 14:17
  • Yeh. I prefer examples over explanations. – Cooper Apr 18 '19 at 16:23