App scripts

Functions to create menu and load html

function loadMainForm() {
  const htmlserv = HtmlService.createTemplateFromFile('main');
  const html = htmlserv.evaluate();
  const ui = SpreadsheetApp.getUi();
  ui.showModalDialog(html,"reWard Patient Management");
}

function createMenu(){
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Enter Information');
  menu.addItem("Open Form", "loadMainForm");
  menu.addToUi();
}

function onOpen(){
  createMenu();
  
}

Function to create web-app to embed in google sites

//Triggered when the page is navigated to, serves up HTML
/* function doGet(){
  var template = HtmlService.createTemplateFromFile('main'); 
  return template.evaluate()
      .setTitle('Example App')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
 */

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('WebAppBoot');
}

Load Tabs to main page

function loadTabHTML(tabs) {
  const htmlserv = HtmlService.createTemplateFromFile(tabs);
  return htmlserv.evaluate().getContent();
}

function loadSearchTab(){
  return loadTabHTML("search");
}

function loadNewPatientTab(){
  return loadTabHTML("newPatient");
}

function loadVitalsTab(){
  return loadTabHTML("vitals");
}
function loadFollowUpCommentsTab(){
  return loadTabHTML("followUpcomments");
}

UUID generation, CURD functions

/*function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('WebAppBoot');
}
*/

function uuid() {
  var uuid_array = [];
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA");
  var getLastRow = dataSheet.getLastRow();
  if(getLastRow > 1) {
    var uuid_values = dataSheet.getRange(2, 1, getLastRow - 1, 1).getValues(); 
    for(i = 0; i < uuid_values.length; i++)
    {
      uuid_array.push(uuid_values[i][0]);
    }
    var x_count = 0;
    do {
    var y = 'false';
    var uuid_value = Utilities.getUuid(); 

    if(uuid_array.indexOf(uuid_value) == -1.0)
    {
      y = 'true';
      Logger.log(uuid_value);
      return uuid_value;   
    } 
    x_count++;
    } while (y == 'false' && x_count < 5);
  } else {
    return Utilities.getUuid();
  }
}

function UpdateRecord(record_id, firstname, lastname, street, city, state, zip, email) {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA"); 
  var getLastRow = dataSheet.getLastRow();
  var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 8).getValues();
  for(i = 0; i < table_values.length; i++)
  {
    if(table_values[i][0] == record_id)
    {
      dataSheet.getRange(i+2, 2).setValue(firstname);
      dataSheet.getRange(i+2, 3).setValue(lastname);
      dataSheet.getRange(i+2, 4).setValue(street);
      dataSheet.getRange(i+2, 5).setValue(city);
      dataSheet.getRange(i+2, 6).setValue(state);
      dataSheet.getRange(i+2, 7).setValue(zip);
      dataSheet.getRange(i+2, 8).setValue(email);
    }
    
  }
  return 'SUCCESS';
}

function DeleteRecord(record_id)
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA"); 
  var getLastRow = dataSheet.getLastRow();
  var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 8).getValues();
  for(i = 0; i < table_values.length; i++)
  {
    if(table_values[i][0] == record_id)
    {
      var rowNumber = i+2;
      dataSheet.getRange('A' + rowNumber +':I' + rowNumber).clearContent();
      
    }   
  }
  return 'SUCCESS';
}

function AddRecord(firstname, lastname, street, city, state, zip, email) {
  var uniqueID = uuid();
  var found_record = false;
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA");
  var getLastRow = dataSheet.getLastRow();
  for(i = 2; i < getLastRow; i++)
  {
    if(dataSheet.getRange(i, 1).getValue() == '')
    {
      dataSheet.getRange('A' + i + ':I' + i).setValues([[uniqueID, firstname, lastname, street, city, state, zip, email, new Date()]]);
      found_record = true;
      break;
    }
  }
  if(found_record == false)
  { 
    dataSheet.appendRow([uniqueID, firstname, lastname, street, city, state, zip, email, new Date()]);
  }
  return 'SUCCESS';
  
}

function searchRecords(firstname, lastname, street, city, state, zip, email) 
{

  var returnRows = [];
  var allRecords = getRecords();

  allRecords.forEach(function(value, index) {

    var evalRows = [];
    if(firstname != '')
    {
      if(value[1].toUpperCase() == firstname.toUpperCase()) {
        evalRows.push('true');
      } else {
        evalRows.push('false');
      }
    }
    else
    {
       evalRows.push('true');
    }

    if(lastname != '')
    {
       if(value[2].toUpperCase() == lastname.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(street != '')
    {
       if(value[3].toUpperCase() == street.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(city != '')
    {
       if(value[4].toUpperCase() == city.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(state != '')
    {
       if(value[5].toUpperCase() == state.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(zip != '')
    {
       if(value[6] == zip) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(email != '')
    {
       if(value[7].toUpperCase() == email.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(evalRows.indexOf("false") == -1)
    {
      returnRows.push(value);    
    }

  });

  return returnRows;
}

function getRecords() { 
  var return_Array = [];
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA"); 
  var getLastRow = dataSheet.getLastRow();
  for(i = 2; i <= getLastRow; i++)
  {
    if(dataSheet.getRange(i, 1).getValue() != '')
    {
      return_Array.push([dataSheet.getRange(i, 1).getValue(), 
      dataSheet.getRange(i, 2).getValue(),
      dataSheet.getRange(i, 3).getValue(),
      dataSheet.getRange(i, 4).getValue(),
      dataSheet.getRange(i, 5).getValue(),
      dataSheet.getRange(i, 6).getValue(), 
      dataSheet.getRange(i, 7).getValue(), 
      dataSheet.getRange(i, 8).getValue()]);
    }
  }  
  return return_Array;  
}

HTML

Main UI

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap CSS -->
    <link href="<https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css>" rel="stylesheet" integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">
    <style>
      .nav-link {
        cursor:pointer;
      }
      </style>
    <title>reWard</title>
  </head>
  <body>

    <div class = "container">
      <ul class="nav nav-tabs">
          <li class="nav-item">
            <div class="nav-link" id="search-link" >Search</div>
          </li>
          <li class="nav-item">
              <div class="nav-link active" id = "newPatient">New patient</div>
          </li>
          <li class="nav-item ">
              <div class="nav-link" id = "vitals">Vitals</div>
          </li>
          <li class="nav-item">
            <div class="nav-link" id = "followUpComments">Follow-up Comments</div>
          </li>
        </ul>
        <div id = "app"> yo k ho </div>

    </div>

    <!-- Optional JavaScript; choose one of the two! -->

    <!-- Option 1: Bootstrap Bundle with Popper -->
    <script src="<https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/js/bootstrap.bundle.min.js>" integrity="sha384-JEW9xMcG8R+pH31jmWH6WWP0WintQrMb4s7ZOdauHnUtxwoG2vI5DkLtS3qm9Ekf" crossorigin="anonymous"></script>

    <!-- Option 2: Separate Popper and Bootstrap JS -->
    <!--
    <script src="<https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.1/dist/umd/popper.min.js>" integrity="sha384-SR1sx49pcuLnqZUnnPwx6FCym0wLsk5JZuNx2bPPENzswTNFaQU1RDvt3wT4gWFG" crossorigin="anonymous"></script>
    <script src="<https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/js/bootstrap.min.js>" integrity="sha384-j0CNLUeiqtyaRmlzUHCPZ+Gy5fQu0dQ6eZ/xAww941Ai1SxSY+0EQqNXNE6DZiVc" crossorigin="anonymous"></script>
    -->
  <script>
    function loadSearchTab(){
     google.script.run.withSuccessHandler(function(html){
        document.getElementById("app").innerHTML=html;
      }).loadSearchTab();
    }
    
    document.getElementById("search-link").addEventListener("click",loadSearchTab);
  </script>
  </body>

</html>

CSS

<style>
.hidden {
    display: none;
}

.form-group {
    margin: 2px 0px;
}

#submitButton {
    margin: 4px 0px;
}

body {
    margin-left: 50px;
}

.message {
   padding: 2px;
   width: 50%;    
}

.message > * {  
   display: inline-block;
}

.message .title {
    font-weight: 700;
    font-size: 1.1em;     
}

.success.message {
    border: 1px solid #5c9a18;
    background: #e4ffe4;
    color: #2a8e2a;
}

.error.message {
    background: #f9cece;
    border: 1px solid #7d2929;
}

.error.message .title {
    color: #863030;
}

button.clear {
    background: -moz-linear-gradient(top, #dd6e39, #d17636);
    background: -ms-linear-gradient(top, #dd6e39, #d17636);
    background: -o-linear-gradient(top, #dd6e39, #d17636);
    background: -webkit-linear-gradient(top, #dd6e39, #d17636);
    background: linear-gradient(top, #dd6e39, #d17636);
    border: 1px solid transparent;
    color: #fff;
    text-shadow: 0 1px rgba(0, 0, 0, .1);
}

button.clear:hover {
    background: -moz-linear-gradient(top, #ca602e, #bd6527);
    background: -ms-linear-gradient(top, #ca602e, #bd6527);
    background: -o-linear-gradient(top, #ca602e, #bd6527);
    background: -webkit-linear-gradient(top, #ca602e, #bd6527);
    background: linear-gradient(top, #ca602e, #bd6527);
    border: 1px solid transparent;
    color: #fff;
    text-shadow: 0 1px rgba(0, 0, 0, .1);
}
</style>

Sub-pages

<h1>Vitals</h1>