Reputation: 81

How can I execute an Apps Script Function using a built in HTML sidebar browser?

Currently, I am making an expense tracker on Google Sheets. Here is a preview:

I currently have a function that checks how much money (in percentage) is spent on a particular brand.

Like this:

Here is the code block for it:

function perCent(categ){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  var total = 0;
  var sum = 0;
  values.forEach(function(row){
    total+=row[1];
    if (row[7]==categ){sum+=row[1]}
  })
  return (sum/total)*100 + "%"  + " of your income has been spent on " + categ;
}

However, for this function to work, the user must manually type in a cell =perCent(categ), which is not user friendly.

For example:

To increase user friendliness, I introduced an HTML sidebar, as such:

Here is the code block for the sidebar:


function onOpen() {
  SpreadsheetApp.getUi() 
      .createMenu('Main Menu')
      .addItem('My sidebar 1', 'showSidebar')
      .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setTitle('Clothing Main Menu');
  SpreadsheetApp.getUi()
      .showSidebar(html);
}

Here is the HTML code block:


<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1 class = "align ">Welcome to the clothing expense custom menu bar!</h1>
    <h2 class = "align">Here are the custom functions you can use:</h2>
    <p class = "align"> Per Cent Function</p>

    <form>

    <input class = "margin" type = "text" placeholder="Enter brand name">
    <div>
    <button type = "submit" class="align">Submit
    </button>
    </div>


    </form>



  </body> 



  <style>

    body{
      background-color: black; 
      color: red; 
    }

    
  
    .align{
      text-align: center; 
    }

    .margin{
      margin-bottom: 10px; 
    }

   
</html>

Is there any way I can make the perCent function run by clicking on the Submit button?

Thanks!

Upvotes: 1

Answers (2)

Nikko J.

Reputation: 5533

You need add google.script.run in the script section of your html. This will run the function in your Code.gs. To prompt the value calculated by perCent() function, you can use UI.alert().

Example:

Sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <script>
    function runFunc(){
      var brand = document.getElementById("brand").value;
      google.script.run.perCent(brand);
    }
  </script>
  <body>
    <h1 class = "align ">Welcome to the clothing expense custom menu bar!</h1>
    <h2 class = "align">Here are the custom functions you can use:</h2>
    <p class = "align"> Per Cent Function</p>
    <form onsubmit="runFunc()">
      <input class = "margin" id="brand" type = "text" placeholder="Enter brand name">
      <div>
        <button type="submit" class="align">Submit</button>
      </div>
    </form>
  </body> 
  <style>
    body{
      background-color: black; 
      color: red; 
    }
    .align{
      text-align: center; 
    }
    .margin{
      margin-bottom: 10px; 
    }
</html>

Code.gs

function perCent(categ){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  var total = 0;
  var sum = 0;
  values.forEach(function(row){
    total+=row[1];
    if (row[7]==categ){sum+=row[1]}
  })
  var val = (sum/total)*100 + "%"  + " of your income has been spent on " + categ; 
  var ui = SpreadsheetApp.getUi();
  ui.alert(val)
}

function onOpen() {
  SpreadsheetApp.getUi() 
      .createMenu('Main Menu')
      .addItem('My sidebar 1', 'showSidebar')
      .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setTitle('Clothing Main Menu');
  SpreadsheetApp.getUi()
      .showSidebar(html);
}

Output:

References

Upvotes: 2

J Ive

Reputation: 11

I was able to get this to work as a UI popup. I'm sure it's possible as a sidebar but this is a quick way to accomplish what you are looking to do:

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Clothing Main Menu')
      .addItem('Calculate % spend per brand', 'showPrompt')
      .addToUi();
}

function showPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Check percentage of clothing budget',
      'Please enter a clothing brand:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    ui.alert('Results: ' + perCent(text) + '.');
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('Please input a clothing brand');
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
    ui.alert('You closed the dialog.');
  }
}

When you insert this apps script snippet a menu called "Main Menu" will be added and the only option will be called "Calculate % spend per brand". If the user clicks that they will be prompted to enter a brand name. When they do so and then hit OK the perCent function results will be displayed to them.

Edit: You can get this to work as a sidebar, but as far as I can tell I cannot get a form field value inside of an html sidebar to be processed through an apps script function. This is as close as I could get, it pops up the input box same as last time but this time the results are displayed in the sidebar. It can obviously be styled and expanded upon but this is pretty close to what you were hoping for:

    function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Clothing Main Menu')
      .addItem('Calculate % spend per brand', 'showPrompt')
      .addToUi();

}

function showPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Check percentage of clothing budget',
      'Please enter a clothing brand:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    var html = HtmlService.createHtmlOutput("<h1> Results: "+ perCent(text) + "</h1>");
    SpreadsheetApp.getUi().showSidebar(html);
    
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('Please input a clothing brand');
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
    ui.alert('You closed the dialog.');
  }
}

Edit 2:

Here is a version that works entirely within the HTML sidebar:

    function perCent(categ){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  var total = 0;
  var sum = 0;
  values.forEach(function(row){
    total+=row[1];
    if (row[7]==categ){sum+=row[1]}
  })
  var rtrnStr = (sum/total)*100 + "%"  + " of your income has been spent on " + categ;

  var html2 = HtmlService.createHtmlOutput("<h1> Results: "+ rtrnStr + "</h1>");
    SpreadsheetApp.getUi().showSidebar(html2);


}

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Clothing Main Menu')
      .addItem('My sidebar 1', 'showSidebar')
      .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutput('<script> function processSub(){ var result = document.getElementById("brand").value; google.script.run.perCent(result);}</script> <form id="myForm" onsubmit="event.preventDefault(); processSub();"> <input type="text" label="Clothing Brand" id="brand"><br><input type="submit"></form> ')
      .setTitle('Clothing Main Menu');
  SpreadsheetApp.getUi()
      .showSidebar(html);
}

Upvotes: 1

How can I execute an Apps Script Function using a built in HTML sidebar browser?

Answers (2)

Example:

References

Related Questions