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

Question

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!

Nikko J. · Accepted Answer · 2022-01-07 20:28:13Z

2

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

edited Jan 7, 2022 at 20:28

answered Jan 7, 2022 at 19:34

Nikko J.

5,5531 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aleptian Over a year ago

Hello @Nikko J. Thank you for the response. Would there also be an alternate way of making the answer display as a pop-up? I have also edited my post with the HTML code. I apologize for the bother.

Nikko J. Over a year ago

@aleptian - See my updated answer.

aleptian Over a year ago

This works perfectly. Thank you so much :D Did i properly mark your answer as correct? Im new to stack overflow so I'm not sure. Thanks again !!

J Ive · Accepted Answer · 2022-01-07 20:05:57Z

1

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);
}

edited Jan 7, 2022 at 20:05

answered Jan 7, 2022 at 18:37

J Ive

112 bronze badges

5 Comments

aleptian Over a year ago

@J Ive thank you for your response. Your solution worked like a charm. While I am going to use your solution, I was wondering if there would be any way for it to be in the form of the HTML Sidebar. I feel like it will be more user-friendly, especially considering that I will not be using this expense tracker. Thank you once again.

J Ive Over a year ago

I edited my answer and added another method that I think is closer to what you are hoping for.

aleptian Over a year ago

@J Ive thank you for your response again. I really do hate to be a hassle, but while this method does work, it doesn't really do what i was hoping for. Preferrably, I would like for the user to open the HTML Sidebar, input the clothing brand in the input field, and when the Submit button is clicked, they would get a popup with the answer or something similar. I apologize once again.

J Ive Over a year ago

Borrowing from what @Nikko J was able to do I came up with another version. This works entirely within the HTML sidebar. When the user submits the form it reloads the sidebar and populates it with the result of perCent(). Check the most recent edit.. This was fun to work on, by the way. Hope it works for you!

aleptian Over a year ago

@J Ive both your answers are completely amazing. Thank you and I will mark both as working answers. Much appreciated !

Collectives™ on Stack Overflow

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

2 Answers 2

Example:

References

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Example:

References

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related