Skip to content

Displaying data from a google sheet onto an html table

Lofton Gentry

As the title says, I’m having some issues properly displaying data from a google spreadsheet onto a table in HTML. For context, what my program is supposed to do is: when the button ‘New Alert’ is pressed, a form pops up that allows for user input. After inputting all the information that the user wants to add, they then press save. The alert is then saved to a google sheet, and the data from that google sheet is displayed on the HTML table.

The point I got to before I was stumped was when a user presses save, the alert they created is then saved to the google sheet. I added two additional methods (which I will highlight below), however I believe one of those two is what is stopping the program from executing properly. When I press ‘F12’ and view the console to check and see what errors are occurring, the two I get are: 1) Uncaught SyntaxError: Unexpected token ‘{‘ and 2) Uncaught ReferenceError: init is not defined at onload (userCodeAppPanel:1:18618). I’ve looked over the program a couple times, and I don’t think there is a floating open-bracket, so I believe there is something else wrong with it currently. Shown below is the code:

Web-Portal.gs

//TODO: Adding comments explaining method
function doGet(e){
  let template = HtmlService.createTemplateFromFile('Index')
  template.data = getData()
  return template.evaluate()
}

//TODO: Adding comments explaining method
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

//TODO: Adding comments explaining method
function getData(){
  let codeData = locations.getDataRange().getDisplayValues();
  codeData.toObject();
  let portLoc = Array.from(new Set(codeData.map(el => el.PORT_LCC)));
  let railLoc = Array.from(new Set(codeData.map(el => el.RAIL_LCC)));
  let weatherLoc = Array.from(new Set(codeData.map(el => el.WEATHER_LCC)))
  let out = {
    portOpLoc: portLoc,
    railOpLoc: railLoc,
    weatherLoc: weatherLoc
  }
  return JSON.stringify(out);
}

//TODO: Add comments explaining method
function saveAlerts(data){
  let sheetData = alerts.getDataRange().getDisplayValues()
  let keys = sheetData[0]
  data.forEach(el => {
    let temp = []
    keys.forEach(key => {
      temp.push(el[key])
    })
    alerts.appendRow(temp)
  })
}

//TODO: Add comments explaining method
function getAlerts(){
    let data = alerts.getDataRange().getDisplayValues()
    data.toObject()
    return data
}

//TODO: Adding comments explaining method
Array.prototype.toObject = function(){
  var keys = this.shift();
  for(var i = 0; i < this.length; i++){
    var temp = {};
    for(var j = 0; j < keys.length; j++){
      temp[keys[j]] = this[i][j];
    }
    this[i] = temp;
  }
}

JS.html

<script>   
    function init(){
      updateAlertLocation()
      getAlerts()
    }
    
    //TODO: Add comments explaining method
    function updateAlertLocation(){
      let select = document.getElementById('alertSelect')
      let list = document.getElementById('codeList')
      let codes = null

      while(list.firstChild){
        list.removeChild(list.firstChild)
      } 
      if(select.value === 'Port/Vessel Operations'){
        codes = data.portOpLoc
      }else if(select.value === 'Rail Operations' || select.value === 'Rail Disruption'){
        codes = data.railOpLoc
      }else if(select.value === 'Weather'){
        codes = data.weatherLoc
      }else{
        return
      }

      codes.forEach(code => {
        let option = document.createElement('option')
        option.value = code
        list.appendChild(option)
      })
      return
    }

    //TODO: Add comments explaining method
    function saveAlert(){
      let out = []
      //need commas between key/value pairs
      let controls = {
        alertSelect: document.getElementById("alertSelect"),
        alertLocation: document.getElementById('alertLocation'),
        alertSubject: document.getElementById('alertSubject'),
        alertBody: document.getElementById('alertBody'),
        alertDate: document.getElementById('alertDate'),
      }
      if(validateInput(controls)){
        let modalSpinner = document.getElementById('modalSpinner')
        modalSpinner.hidden = false

        let alertDateFinal = controls.alertDate.value.replace('-','/')
        alertDate.value === '' ? new Date(alertDateFinal).toLocaleDateString() : new Date(alertDateFinal)
        //multi word keys need to be enclosed in quotes
        let record = {
          'Alert Type': controls.alertSelect.value,
          'Alert Location': controls.alertLocation.value,
          Subject: controls.alertSubject.value,
          Details: controls.alertBody.value,
          Date: alertDateFinal
        }
        out.push(record)
        google.script.run.withSuccessHandler(clearForm).saveAlerts(out)
      } else{
        window.alert('Please Check Input')
      }
    }

    /*
    TODO: Add comments explaining method
    Confusion here
    */
    getAlerts(){
      let activeAlertSpinner = document.getElementById('activeAlertSpinner')
      activeAlertSpinner.hidden = false
      google.script.run.withSuccessHandler(updateAlertList).getAlerts()
    }
    
    //TODO: Add comments explaining method    
    function updateAlertList(data){
      let activeAlertSpinner = document.getElementById('activeAlertSpinner')
      activeAlertSpinner.hidden = true
      let cols = ['Alert Type', 'Alert Location', 'Alert Subject', 'Alert Details', 'Alert Date']
      let table = document.getElementById('alertDateTable')
      while(table.firstChild){
        table.removeChild(table.firstChild)
      }
      data.forEach(row => {
        let tr = document.createElement('tr')
        for(let col of cols){
          let td = document.createElement('td')
          td.textContent = row[col]
          tr.appendChild(td)
        }
        table.appendChild(tr)
      })
    }
    
    //TODO: Add comments explaining method
    function clearForm(){
      let modal = bootstrap.Modal.getOrCreateInstance(document.getElementById('newDateModal'))
      let modalSpinner = document.getElementById('modalSpinner')
      modalSpinner.hidden = true
      let controls = {
        alertSelect: document.getElementById("alertSelect"),
        alertLocation: document.getElementById('alertLocation'),
        alertSubject: document.getElementById('alertSubject'),
        alertBody: document.getElementById('alertBody'),
        alertDate: document.getElementById('alertDate'),
      }
      controls.alertSelect.value = ''
      controls.alertLocation.value = ''
      controls.alertSubject.value = ''
      controls.alertBody.value = ''
      controls.alertDate.value = ''
      modal.hide()
      getAlerts()
    }

    //TODO: Add comments explaining method
    function validateInput(controls){
      return (
        controls.alertSelect.value != '' &&
        controls.alertLocation.value != '' &&
        controls.alertSubject.value != '' &&
        controls.alertDate.value != ''
      )
    }
</script>

Index.html

The Methods getAlerts() and updateAlertList(data) are the methods I believe to be causing the error

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <!--Inserting bootstraps-->
  <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/font/bootstrap-icons.css">
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"
    integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous">
  </script>

  <!--Inserting styling and javascript code from 'CSS' and 'JS' class -->
  <script>
    let data = JSON.parse(<?= data ?>)
  </script>
  <?!= include('CSS') ?>
  <?!= include('JS') ?>
  
</head>

<!--Inserts 'ONE' logo in top right corner -->
<div style="margin-top: 10px;">
  <a class="navbar-brand">
    <img src="https://drive.google.com/uc?export=download&id=14Fx0en1-Hkmt7STi-asxPXrn7np-egfp" height="35" width="70" alt="">
  </a>
</div>

<!--Inserts nav bar that is used for styling purposes-->
<nav class="navbar navbar-expand-lg navbar-dark">
  <ul class="navbar-nav">
    <li class="nav-item">
      <a class="nav-link active" id="yardText"></a>
    </li>
  </ul>
</nav>

<body onload="init()">
  <div class="container-fluid">

    <!--Creates the button that opens the form that allows user input-->
    <div class="row mb-3 mt-3">
      <button type="button" class="btn save-btn btn-dark" data-bs-toggle="modal" data-bs-target="#newDateModal">New Alert</button>
    </div>

    <div class="modal fade" id="newDateModal" data-bs-backdrop="static" tabindex="-1">
      <div class="modal-dialog modal-dialog-scrollable">
        <div class="modal-content">

          <!--Header for the pop up that appears-->
          <div class="modal-header">
            <h5 class="modal-title" id="modalLabel">Create New Alert</h5>
            <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
          </div>

          <!--Body of the pop-up menu-->
          <div class="modal-body">
            <!--First option that allows user input (only allows for three options to be selected)-->
            <div class="modalDiv mb-3">
              <h5>Alert Type</h5>
              <select class="form-select" id="alertSelect" onchange="updateAlertLocation()">
                  <option value="Port/Vessel Operations">Port/Vessel Operations</option>
                  <option value="Rail Operations">Rail Operations</option>
                  <option value="Rail Disruption">Rail Disruption</option>
                  <option value="Weather">Weather</option>
                </select>
            </div>
            
            <!--Depending on user input on the alert type, users selects the address of the location being selected-->
            <div class="modalDive mb-3">
              <h5>Alert Location</h5>
              <div>
                <input type="text" list="codeList" class="form-control" id="alertLocation">
                <datalist id="codeList"></datalist>
              </div>
            </div>

            <!--Subject for the alert-->
            <div class="modalDiv mb-3">
              <h5>Alert Subject</h5>
              <div>
                <input type="text" class="form-control" id="alertSubject">
              </div>
            </div>

            <!--Details for data about the alert-->
            <div class="modalDiv mb-3">
              <h5>Alert Details</h5>
              <div>
                <input type="text" class="form-control" id="alertBody">
              </div>
            </div>

            <!--User selects date of the alert-->
            <div class="modalDiv mb-3">
              <h5>Alert Date</h5>
              <div>
                <input type="date" class="form-control" id="alertDate">
              </div>
            </div>
          </div>

          <!--Footer for the pop up that appears. Contains save button that saves the alert to the spreadsheet when pressed.-->
          <div class="modal-footer">
            <div class="spinner border spinner-border-sm float-start spinColor" id="modalSpinner" hidden="true"></div>
            <button type="button" class="btn btn-color-grey" data-bs-dismiss="modal">Cancel</button>
            <button type="button" class="btn btn-dark btn-color-magenta" onclick="saveAlert()">Save</button>
          </div>
        </div>
      </div>
    </div>

    <!--Creates table on web app that allows user to see what alerts have been added-->
    <div class="row justify-content-left mb-3mt-3">
      <div class="col-6">
        <div class="card">
          <div class="card-header cont-card-header">
            All Active Alerts 
            <div class="spinner-border spinner-border-sm float-end spinColol" id="activeAlertSpinner" hidden="true"></div>
          </div>
          <div class="tableDiv">
            <table class="table table-sm">
              <thead style="position: sticky; top: 0; z-index: 1, background:#FFFFFF">
                <tr>
                  <th scope="col">Alert Type</th>
                  <th scope="col">Alert Location</th>
                  <th scope="col">Alert Subject</th>
                  <th scope="col">Alert Details</th>
                  <th scope="col">Alert Date</th>
                </tr>
              </thead>
              <tbody id="alertDateTable">
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>
  </div>
</body>

</html>

Constants.gs

//Spreadsheet ID for 
const locations = SpreadsheetApp.openById('1_0zMVU8JHpasH8WbLsSB-w--3HvFNUVwHws33O2b0cs').getSheetByName('Locations');
const alerts = SpreadsheetApp.openById('1_0zMVU8JHpasH8WbLsSB-w--3HvFNUVwHws33O2b0cs').getSheetByName('Alerts');
const keys = ['Alert Type', 'Alert Location', 'Alert Subject', 'Alert Body', 'Alert Details'];

I can provide further context as necessary, and can remove or add portions of code as deemed necessary. I’ve never asked a question regarding a web app before, so I was unsure of what I should and should not be including. Thank you in advance.

Advertisement

Answer

I found the issue. It was in the JS.html file. It was because I didn’t put function in front of the getAlerts() method. I put function in front and it worked. I have never felt more like an idiot in my entire life, but after doing that, everything worked. Let this be a lesson to anyone who finds this answer to make sure your methods are all properly declared.

Lofton Gentry
User contributions licensed under: CC BY-SA
2 People found this is helpful