Index(match) function in Google Apps Script / HTML Sidebar UI?

AlpacaPat
July 9, 2023
300 views
1 vote
2 Answers

I’m building a pseudo-app in Google Sheets to allow managers to tell me the status of an employee on their team. Please note: I’m very much a beginner at Google Apps Script, .css and .html, and have been cobbling bits of code together and testing every step of the way until I have something that works.

Currently, the sidebar UI requests an employee ID (input<>) and a status update (select<>,). Managers enter this information and click submit. That data is then appended to a log on a different workbook. All that works (so far).

In in the same workbook, I have a table named "Import" and a range named "Confirm" that has all the employee IDs and their names. ID is in Col1, Name is in Col3. This range can be as many as 8500 rows.

What I want to do is modify the script so that once the ID is entered into the sidebar, the associated name appears beneath it. This ensures that they can see if they’ve entered the correct ID or not. They can then proceed with entering the employee status.

As the range can be very large and is updated frequently, I would need to find the most efficient search-and-display function possible. I would also like to build in error messaging to show

Then, I want to collect the employee ID, the employee name, and the status and append it to the log.

I feel very close to the solution, using getRangeByName() and filtering, but I just don’t have the experience yet to make it work. Any suggestions?

(Due to company policy, I’m unable to post any existing code outside our private network, and I’m too novice to properly anonymize field names and URLs, etc.)

Answers

Here is an example of using HTML Service to get values from spreadsheet and place values to spreadsheet.

In my example I have a spreadsheet "Import" with the Id and names of all employees. I use an immediately invoked function expressions (function () {} )(); in my HTML script to get the values from the spreadsheet and store in browser memory. Then when ever the user inputs an Id in the sidebar, I lookup the employee name. I do something similar on the server when the submit button is pressed.

Notice in the spreadsheet screen shot the "New" status returned from the sidebar.

I use templated HTML to split the HTM and Javascript into seperate files. The same can be done with CSS.

Sheet Import

Code.gs

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function showTest() {
  let html = HtmlService.createTemplateFromFile("HTML_Test");
  SpreadsheetApp.getUi().showSidebar(html.evaluate());
}

function getData() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Import");
    // get columns A to C (cannot contain Date objects)
    let values = sheet.getRange(2,1,sheet.getLastRow()-1,3).getValues();
    return values
  }
  catch(err) {
    Logger.log(err);
  }
}

function setStatus(employee) {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Import");
    // get columns A to C
    let values = sheet.getRange(2,1,sheet.getLastRow(),3).getValues();
    let index = values.findIndex( row => row[0] ===  Number(employee.id));
    sheet.getRange(index+2,2).setValue(employee.status);
  }
  catch(err) {
    Logger.log(err)
  }
}

HTML_Test.html

<!DOCTYPE html>
<html>
  <head>
    <title>Employee status</title>
  </head>
  <body>
    <input id="emplyeeId" type="text" onchange="idOnChange(this.value)">
    <label for="emplyeeId">Employee Id</label><br>
    <input id="emplyeeName" type="text">
    <label for="emplyeeId">Employee Name</label><br>
    <select id="status">
      <option value="unknown"></option>
      <option value="New">New</option>
      <option value="Old">Old</option>
      <option value="Retired">Retired</option>
    </select>
    <br>
    <input id="submitButton" type="button" value="Submit" onclick="submitOnClick()">
    <?!= include("JS_Test"); ?>
  </body>
</html>

JS_Test.html

<script>
  let data = null;
  function idOnChange(id) {
    try {
      let name = data.find( row => row[0] === Number(id) );
      if( name ) {
        document.getElementById("emplyeeName").value = name[2];
      }
      else {
        throw "Id ["+id+"] not found";
      }
    }
    catch(err) {
      alert(err);
    }
  }
  function submitOnClick() {
    try {
      let id = document.getElementById("emplyeeId").value;
      if( id === "" ) throw "Incorrect Id";
      let status = document.getElementById("status").value;
      if( status === "" ) throw "Status not selected";
      google.script.run.setStatus({id:id,status:status});
    }
    catch(err) {
      alert(err);
    }
  }
  function storeData(values) {
    try {
      data = values;
    }
    catch(err) {
      alert(err);
    }
  }
  (function () {
    try {
      google.script.run.withSuccessHandler(storeData).getData();
    }
    catch(err) {
      alert(err);
    }
  }
  )();
</script>

References

As an alternative you could simply send the Id to the server and return the employee name

Code.gs

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function showTest() {
  let html = HtmlService.createTemplateFromFile("HTML_Test");
  SpreadsheetApp.getUi().showSidebar(html.evaluate());
}

function getEmployee(id) {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Import");
    // get columns A to C
    let values = sheet.getRange(2,1,sheet.getLastRow(),3).getValues();
    let employee = values.find( row => row[0] ===  Number(id));
    return employee;
  }
  catch(err) {
    Logger.log(err);
  }
}

function setStatus(employee) {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Import");
    // get columns A to C
    let values = sheet.getRange(2,1,sheet.getLastRow(),3).getValues();
    let index = values.findIndex( row => row[0] ===  Number(employee.id));
    sheet.getRange(index+2,2).setValue(employee.status);
  }
  catch(err) {
    Logger.log(err)
  }
}

HTML_Test.html

<!DOCTYPE html>
<html>
  <head>
    <title>Employee status</title>
  </head>
  <body>
    <input id="employeeId" type="text" onchange="idOnChange(this.value)">
    <label for="employeeId">Employee Id</label><br>
    <input id="employeeName" type="text">
    <label for="employeeId">Employee Name</label><br>
    <select id="status">
      <option value="unknown"></option>
      <option value="New">New</option>
      <option value="Old">Old</option>
      <option value="Retired">Retired</option>
    </select>
    <br>
    <input id="submitButton" type="button" value="Submit" onclick="submitOnClick()">
    <?!= include("JS_Test"); ?>
  </body>
</html>

JS_Test.html

<script>
  function idOnChange(id) {
    try {
      google.script.run.withSuccessHandler(showName).getEmployee(id);
    }
    catch(err) {
      alert(err);
    }
  }
  function showName(employee) {
    try {
      if( employee ) {
        document.getElementById("employeeName").value = employee[2];
      }
      else {
        throw "Incorrect Id";
      }
    }
    catch(err) {
      alert(err);
    }
  }
  function submitOnClick() {
    try {
      let id = document.getElementById("employeeId").value;
      if( id === "" ) throw "Incorrect Id";
      let status = document.getElementById("status").value;
      if( status === "" ) throw "Status not selected";
      google.script.run.setStatus({id:id,status:status});
    }
    catch(err) {
      alert(err);
    }
  }
</script>

Please signup or login to give your own answer.

Click here to cancel reply.