Displaying data from a google sheet onto an html table-CodePudding

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 onl oad (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 >
    <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 >
  <ul >
    <li >
      <a  id="yardText"></a>
    </li>
  </ul>
</nav>

<body onl oad="init()">
  <div >

    <!--Creates the button that opens the form that allows user input-->
    <div >
      <button type="button"  data-bs-toggle="modal" data-bs-target="#newDateModal">New Alert</button>
    </div>

    <div  id="newDateModal" data-bs-backdrop="static" tabindex="-1">
      <div >
        <div >

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

          <!--Body of the pop-up menu-->
          <div >
            <!--First option that allows user input (only allows for three options to be selected)-->
            <div >
              <h5>Alert Type</h5>
              <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 >
              <h5>Alert Location</h5>
              <div>
                <input type="text" list="codeList"  id="alertLocation">
                <datalist id="codeList"></datalist>
              </div>
            </div>

            <!--Subject for the alert-->
            <div >
              <h5>Alert Subject</h5>
              <div>
                <input type="text"  id="alertSubject">
              </div>
            </div>

            <!--Details for data about the alert-->
            <div >
              <h5>Alert Details</h5>
              <div>
                <input type="text"  id="alertBody">
              </div>
            </div>

            <!--User selects date of the alert-->
            <div >
              <h5>Alert Date</h5>
              <div>
                <input type="date"  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 >
            <div  id="modalSpinner" hidden="true"></div>
            <button type="button"  data-bs-dismiss="modal">Cancel</button>
            <button type="button"  onclick="saveAlert()">Save</button>
          </div>
        </div>
      </div>
    </div>

    <!--Creates table on web app that allows user to see what alerts have been added-->
    <div >
      <div >
        <div >
          <div >
            All Active Alerts 
            <div  id="activeAlertSpinner" hidden="true"></div>
          </div>
          <div >
            <table >
              <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.

CodePudding user response：

If you wish to use templated html then this html file should be quite helpful

HTML:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <p>Your can write an introduction here</p>
  <p>The following table is a summary of the current <?= lr ?> matches
  <div id="tabledata">
       <? var vs = getMyData(); ?>
       <table>
         <? vs.forEach((r,i)=>{ ?>
           <tr>
           <? r.forEach((c,j)=>{ ?>
             <? if(i == 0) { ?>
            <th style="padding:2px 5px;font-weight:bold;border:1px solid black;"><?= c ?> </th>           
           <? } else { ?>
             <td style="padding:2px 5px;border:1px solid black;"><?= vs[i][j] ?> </td>
           <? } ?>
         <?  }); ?>
           </tr>
         <? }); ?>
       </table>
     </div>
     <p>Your can write an ending here</p>
</body>
</html>

All you need to do now is to write a function that returns a 2 dimensional array like the ones that getValues() returns and name that function getMyData.

GS:

function showMyDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createTemplateFromFile('filename').evaluate(),"Dialog Title")
}

CodePudding user response：

this piece of code is invalid

JS.html line 69

getAlerts() {
  let activeAlertSpinner = document.getElementById('activeAlertSpinner')
  activeAlertSpinner.hidden = false
  google.script.run.withSuccessHandler(updateAlertList).getAlerts()
}

function getAlerts() is already defined before, and now I don't understand what are you trying to do here.

If you want to just call getAlerts() just use

getAlerts()

Or just define that function properly, but with other name

function someFunction() {
  let activeAlertSpinner = document.getElementById('activeAlertSpinner')
  activeAlertSpinner.hidden = false
  google.script.run.withSuccessHandler(updateAlertList).getAlerts()
}

CodePudding user response：

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.