How do you pass variables between functions in Google Apps Script when using menu items?

Question

I've been able to successfully build a custom menu with the onOpen() code below.

My desired outcome is to use my function setSheet() to set a specific sheet once a user has clicked on a menu item to select the given sheet.

The problem is that the setSheet function doesn't actually do anything, because I can't figure out how to pass the menu item 'name' to the other function.

I'm not sure what I'm doing wrong in this case? I would appreciate any help you could provide. Thanks!

Here's the working code I have so far:

function onOpen(e) {
 subMenus = [];
 ss = SpreadsheetApp.getActiveSpreadsheet();
 allsheets = ss.getSheets();
 for ( i = 0; i < allsheets.length; ++i){
   sheet = allsheets[i]
   subMenus.push({name:sheet.getName(), functionName: 'setSheet'})
  }
  SpreadsheetApp
  .getActiveSpreadsheet()
  .addMenu("Pick A Spreadsheet", subMenus)
}

function setSheet() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  name = ss.getss.getSheetByName()
  ss.setActiveSheet(name)
}

Additionally, I have made a workaround by using the code below, but I would like to save the user the need to input values into a prompt directly because they might not know the values of the sheet name.

function onOpen(e) {
  subMenus = [{name:"Go To Sheet", functionName: 'setSheet'}];
  SpreadsheetApp
   .getActiveSpreadsheet()
   .addMenu("Pick A Spreadsheet", subMenus)
}

function setSheet() {
  ss = SpreadsheetApp
  active = ss.getActiveSpreadsheet()
  ui = ss.getUi();
  response = ui.prompt("What Sheet Do You Need?")
  active.setActiveSheet(active.getSheetByName(response.getResponseText()))
}

Serge insas · Accepted Answer · 2017-08-02 20:53:08Z

Functions called from the menu can't have parameters, there is no real alternative than to - some way or another - hardcode the values or at least references to variables stored somewhere else (in properties service for example).

But anyway I find the sheet selection using menu a less than optimum solution, not very user friendly at the least.

That's why I suggest you give a try to this version that uses the sidebar with an html interface. You'll have to create 3 files in your project, 1 script file and 2 html files.

code is below in 3 separate items (quite long, I know... here is a link to a read only demo that you can copy to use)

below is a screen capture of the result.

code.gs

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu("Utilities")
  .addItem('authorize script', 'autorize')
  .addToUi();
  showSidebar();
}

function autorize(){
  showSidebar();
}

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


function createList(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var list = [];
  var sh, sheetName;
  for(var s=0;s<ss.getNumSheets();s++){
    sh = ss.getSheets()[s];
    sheetName = sh.getName();
    list.push(sheetName);
  }
  var message = ''; 
  var color;
  message+= '<table>';
  message+='<tr valign="top" cellpadding=5>'
  for(var n=0;n<list.length;n++){
      color="#000"
    //Logger.log('list[n] = '+list[n]+' is in '+alreadyCreated.indexOf(list[n])+' position');
    message+='<tr><td align="center">&nbsp'+(n+1)+'&nbsp</td><td>'+
      '<input type="button" id="button'+n+'" value="'+list[n]+'" style="color:'+color+';" onclick="selectSheet(\''+n+'\')"/></td></tr>';
  }
  message+='</table>';
  return message;
}

function showSheetGS(sheetNumber){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets()[sheetNumber].activate();
}

Sidebar.html

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
<body>
<!-- Below is the HTML code that defines the sidebar element structure. -->
<div class="sidebar branding-below">
<!-- The div-table class is used to make a group of divs behave like a table. -->
<h3> Listof available sheets,<br><span style="color:#0A0"> green color </span> if already selected</h3>
  <div id="sidebarList">
  <br><br><br>...please wait a second ...<br>
  <p>If nothing happens please authorize the script using the menu.</p> 
  </div>

<!-- Enter sidebar bottom-branding below. -->
  <div class="sidebar bottom">
  <img alt="Add-on logo" class="logo" width="25"
      src="http://insas.cluster006.ovh.net/serge/apps-script_2x.png">
  <span class="gray branding-text">sheet selector demo  V1.0<br>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp© SG 2017</span>
</body>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script>
  /**
   * Run initializations on sidebar load.
   */
populateEncodeLocal();

function populateEncodeLocal(){
   //console.log('populateEncode');
   google.script.run.withSuccessHandler(populateList).createList();
}

function selectSheet(sheetNumber){
  console.log(sheetNumber);
  google.script.run.withSuccessHandler(showSheet(sheetNumber)).showSheetGS(sheetNumber);
}
function showSheet(n){
  console.log("showSheet"+n);
  $('#button'+n).css('font-size','12pt').css('color','#0A0');
}

function populateList(data){
   //console.log(data);
   $('#sidebarList').html(data);
}
</script>

Stylesheet.html

<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">
<style>
html, body {
    max-width: 100%;
    overflow-x: hidden;
}
#sidebarList {
  height:600px;
  overflow:auto;
  }
table{
  display:block;
  background-color:#EEE;
  border:1px solid  #666666;
  border-spacing:2px;
  border-collapse:collapse;
  font-family:arial,sans;
  font-size:9pt;
  }
th {
  border-bottom: 0px solid #acacac;
  font-weight: normal;
  padding: 1px 1px 0;
  text-align: left;
}
td {
  border-bottom: 0px solid #ebebeb;
  padding: 1px 0;
}
input[type=button] {
    color:#08233e;
    font:11pt Helvetica,Arial,sans;
    padding:3px;
    background:#FFE;
    border:1px solid #AAA;
    -moz-border-radius:5px;
    -webkit-border-radius:5px;
    border-radius:5px;
    cursor:pointer;
    width:270px;
    white-space: normal;
}
input[type=button]:hover {
    border:1px solid #000;
}
label {
  font-weight: bold;
}
.branding-below {
  bottom: 3px;
  top: 0px;
  padding-left:0px;
}
.branding-text {
  left: 7px;
  position: relative;
  top: 3px;
}
.logo {
  vertical-align: middle;
}
</style>

Thank you @Serge, I thought that might be the case. I have not tried sidebars before, but I will walk through the code now. Thank you for the quick response!
I just added a link to the demo sheet you can copy to test without the code setup and copy/paste :)

Collectives™ on Stack Overflow

How do you pass variables between functions in Google Apps Script when using menu items?

1 Answer 1

code.gs

Sidebar.html

Stylesheet.html

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

code.gs

Sidebar.html

Stylesheet.html

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related