How to display an array to a HTML sidebar in Google apps script

Question

This is a big project for google-apps-script beginner, I have an inventory list in an array (columnValues) this needs to be searched through for what the user is focused on (ingredient). Right now it is just looking for exact matches but I'm going to go back and make it smarter (with some help), for now though I'm having trouble getting my array of matches (matches) to display in my sidebar. I'd like to eventually give the user a list of potential matches that is smarter (like searching poached chicken will result in chicken breast, chicken wings, and poached salmon). When clicked I'd like this to write values to some cells relative to the users focus (two cells over).

So should I make it a list or a table to display it in the sidebar based on what I want to do? How could I fix the function that makes the list? Any advice on making the search smarter would be welcome. Also any tips for a beginner would be welcome.

JSFiddle link : https://jsfiddle.net/amreinj/fk1a5to7/

GoogleDocs link : https://docs.google.com/spreadsheets/d/1Xnbvy4tg2jNhYqL8QKIPJ28AcJ0whygygKzrQ51cGvQ/edit?usp=sharing

I'm not using jQuery and this answer uses it, could use a translation: How to return an array (Google apps script) to a HTML sidebar?

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Costing Tools')
    .addItem('Search menu', 'showSidebar')
    .addToUi();
}

function showSidebar() { // Brings up side bar to do inventory search
  var html = HtmlService.createHtmlOutputFromFile('page')
    .setTitle('Inventory search')
    .setWidth(300);
  SpreadsheetApp.getUi()
    .showSidebar(html);
}

function onSearch() { //When the search button is pressed it loops through the inventory list and finds exact matches eventually I'd like it to be smart
  var ui = SpreadsheetApp.getUi();
  var ingredient = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getValue(); //user focus, they click on the item they want to search
  Logger.log('i ' + ingredient); //debug
  var isheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("INVENTORY");
  var columnValues = isheet.getRange("G:G").getValues(); //inventory list
  var i = columnValues.length,
    array, flag = false,
    matches = [];
  while (i--) { //walk through columnValues
    array = columnValues[i]; //get the ingredient in the li
    if (-1 !== array.indexOf(ingredient)) { //-1, string not found
      flag = true;
      matches.push(array);
    }
  }
  if (!flag) {
    ui.alert('No match found.');
  } else {
    Logger.log('m ' + matches); //debug
    function makeUL(arr) { //This function not working 
      var list = document.createElement('ul'); // Create the list element
      Logger.log(list); //debug
      for (var i = 0; i < arr.length; i++) { //loop through the array to make the list
        var item = document.createElement('li'); // Create the list item
        item.appendChild(document.createTextNode(arr[i])); // Set its contents
        Logger.log(item); //debug
        list.appendChild(item); // Add it to the list
      }
      Logger.log(list); //debug
      return list;
    }
    document.getElementById('foo').appendChild(makeUL(matches)); //this doesn't work
  }
}

page.html

<!DOCTYPE html>
<html>
  <head>
    <script >
    </script>
    <base target="_top">
  </head>
  <body>
    <p>Click on the ingredient you want to find in inventory and then click search.</p>
    <button onclick="google.script.run.onSearch()">Search</button>
    <br>
    <br>
    <div id="foo">Results</div>
  </body>
</html>

score 3 · Accepted Answer · edited May 23 '17 at 12:00

"How could I fix the function that makes the list?"

The place where you have written your function makeUL(arr) which is responsible for DOM manipulation and this line of code :

document.getElementById('foo').appendChild(makeUL(matches));

are totally at the wrong place. You are trying to manipulate HTML DOM elements in google app script code.gs file and google app script cannot manupulate dom elements directly. HTML's document and its APIs are not available in google app script. It is available in browser.

So what you should do?

You should return the list of matches from google app script to the client and write your makeUL function in page.html this is where your javascript will run.

page.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>Click on the ingredient you want to find in inventory and then click search.</p>
    <button onclick="onSearchClickHandler()">Search</button>
    <br>
    <br>
    <div id="foo">Results</div>
    <script>

    // search button click handler
    function onSearchClickHandler(e){
        google.script.run
           .withFailureHandler(function(err){
               // failure handler callback
               console.log(err)
           })
           .withSuccessHandler(function(arr){
                // arr is your match array returned by onSearch
                if(arr.length > 0){
                    var domList = makeUL(arr);
                    // document object will be available here
                    document.getElementById('foo').appendChild(domList);
                }
            })
           .onSearch()
    }

    function makeUL(arr) {  
        var list = document.createElement('ul'); // Create the list element
        console.log(list); //debug
        for (var i = 0; i < arr.length; i++) { //loop through the array to make the list
            var item = document.createElement('li'); // Create the list item
            item.appendChild(document.createTextNode(arr[i])); // Set its contents
            console.log(item); //debug
            list.appendChild(item); // Add it to the list
        }
        console.log(list); //debug
        return list;
    }
    </script>
  </body>
</html>

Also note that in makeUL function I have replaced Logger.log with console.log this is because Logger is available in google app script and console is available in browser window object.

Code.gs:

function onSearch() { //When the search button is pressed it loops through the inventory list and finds exact matches eventually I'd like it to be smart
  var ui = SpreadsheetApp.getUi();
  var ingredient = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getValue(); //user focus, they click on the item they want to search
  Logger.log('i ' + ingredient); //debug
  var isheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("INVENTORY");
  var columnValues = isheet.getRange("G:G").getValues(); //inventory list
  var i = columnValues.length,
    array, flag = false,
    matches = [];
  while (i--) { //walk through columnValues
    array = columnValues[i]; //get the ingredient in the li
    if (-1 !== array.indexOf(ingredient)) { //-1, string not found
      flag = true;
      matches.push(array);
    }
  }
  if (!flag) {
    ui.alert('No match found.');
  } 
  return matches;
}

I have not run the code myself but this is how it should work.

"So should I make it a list or a table to display it in the sidebar based on what I want to do?"

Totally depends on your user experience what you want to achieve; How you design your UI.

"Any advice on making the search smarter would be welcome."

From your use case, you can break down your ingredients in separate words and then match. If any of the keyword matches then add it to matches array. i.e in your ingredients list: chicken breast, chicken wings. When user search chicken, you can split ingredients with space character:

ingredientList = ["chicken breast", "chicken wings"];
ingredientWords = ingredientList[0].split(" ");

ingredientWords will have ["chicken", "breast"] and check if this array contains your search term "chicken" then you can add the item in your matches.

If you want more advanced matching scheme you can use Levenshtein distance algorithm, bitap algorithm. You can use fuzzy matching algorithms. You will find many string matching, string similarity algorithms. Using advance algorithms depends on your use case and what you want to achieve.

" Also any tips for a beginner would be welcome."

Go through google app script tutorial, see the documentation to know which APIs are available and what they do. Also google app script runs on google server so all those APIs and objects of javascript which you get in browser i.e window, document, console, alert etc. are not available on google app script. Code to manipulate HTML dom, should be on client i.e browser.

How to send strings and objects from google app script to your client code in document bound script see this answer.

You may also want to delete the results before each search, so you do not maintain previous results, something like this: `
Results

` and inside the `onSearchClickHandler()` add `document.getElementById('foo').innerHTML = "";` — ocordova, Aug 19 '16 at 14:13

How to display an array to a HTML sidebar in Google apps script

1 Answers1