How to fix: "Uncaught ReferenceError: loadRecords is not defined" with Google App Script

Question

I have followed a tutorial "Create HTML Form that Moves through RecordSet on Google Sheets" done by Code With Curt. https://www.youtube.com/watch?v=V9ptq7tZV50&t=152s

The project doesn't look that complicated. It is a simple CRUD app that I want to run in a modal dialog in google sheets, I am a newbie, I really tried to understand the code that I was copying from the video and not make any typos. The form shows up OK from the custom menu but it is not populating with the data from the sheet. The only error I can see is in the console which says "Uncaught ReferenceError: loadRecords is not defined" I have double checked the variable and function names but just can't see the error.

Any help would be appreciated.

Code.gs

function getList()
{
  var url = 'https://docs.google.com/spreadsheets/d/1QkSdtybPHA9IrWH2VPw44WtQ9dN_-9KjRVNOuCylMCk/edit#gid=0';
  var ss= SpreadsheetApp.openByUrl(url);
  //var ss = SpreadsheetApp.getActiveSpreadsheet();
  var recordSheet = ss.getSheetByName("WebInscriptions");
  var getLastRow = recordSheet.getLastRow();
  return recordSheet.getRange(2, 1, getLastRow -1, 9).getValues();
}

function startForm()
{
  var form = HtmlService.createHtmlOutputFromFile("Modal");
  SpreadsheetApp.getUi().showModalDialog(form, 'Manage New Submissions');
}

function addMenu()
{
  var ui = SpreadsheetApp.getUi()
  ui.createMenu('HR-Recruitment')
    .addItem('New Submissions','startForm')
    .addItem('Manage Recruits','startForm')
    .addToUi();
}

function onOpen(e)
{
  addMenu;
}

Modal.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

      <script>
        function loadRecords(record)
          {
            google.script.run.withSuccessHandler
              (function(ar)
                {
                  var record = document.getElementById("record").value;
                    //console.log (ar);
                    //console.log (record);

                  var recordCount = 0;

                  ar.forEach(function(item, index)
                      {
                        if(index == record - 1)
                            {
                              document.getElementById("inscriptionDate").value = item[0];
                              document.getElementById("firstName").value = item[1];
                              document.getElementById("lastName").value = item[2];
                              document.getElementById("gender").value = item[3];
                              document.getElementById("email").value = item[4];
                              document.getElementById("telNumWhatsApp").value = item[5];
                              document.getElementById("location").value = item[6];
                              document.getElementById("visaImageUpload").value = item[7];
                              document.getElementById("commentMessage").value = item[8];
                              document.getElementById("referrer").value = item[9];
                            }
                        recordCount ++;
                      });

                      console.log (recordCount);
                      document.getElementById("maxRecord").value = recordCount;
                }).getList(); 
          }

          function NextRecord() 
            {
              var record = document.getElementById("record").value;
              var maxRecord = document.getElementById("maxRecord").value;
              var nextRecord = Number record + 1;

              if(nextRecord <= maxRecord)
              {
                document.getElementById ("record").value  = nextRecord;
                loadRecords();
              }
            }

          function PreviousRecord() 
            {
              var record = document.getElementById("record").value;
              var previousRecord = Number record - 1;

              if(previousRecord >= 1)
              {
                document.getElementById ("record").value  = previousRecord;
                loadRecords();
              }
            }

        //loadRecords();

      </script>

  </head>
  <body>
    Inscription Date: <input type="text" id="inscriptionDate"/><br>
    First Name: <input type="text" id="firstName"/><br>
    Last Name: <input type="text" id="lastName"/><br>
    Gender: <input type="text" id="gender"/><br>
    Email: <input type="text" id="email"/><br>
    Telephone Number (WhatsApp): <input type="text" id="telNumWhatsApp"/><br>
    Location: <input type="text" id="location"/><br>
    VISA Image Upload: <input type="text" id="visaImageUpload"/><br>
    Comment or Message: <input type="text" id="commentMessage"/><br>
    Referrer: <input type="text" id="referrer"/><br>

    <input type="button" value = "PREVIOUS" onclick="PreviousRecord"/>
    <input type="text" value="1" id="record" size="2px"/>
    <input type="hidden" id="maxRecord"/>
    <input type="button" value = "NEXT" onclick="NextRecord"/>

  <script>loadRecords();</script>

  </body>
</html>

Google Sheet image

Try moving your script to the body `` – TheWizEd Feb 05 '22 at 17:57 — TheWizEd, Feb 05 '22 at 17:57

Rubén · Answer 1 · 2022-02-06T17:23:15.700

Regarding the specific error, the parenthesis are missing in two lines:

var nextRecord = Number record + 1;
var previousRecord = Number record - 1;

Correct syntax

var nextRecord = Number(record) + 1;
var previousRecord = Number(record) - 1;

As mentioned in the Yuri's answer, the video that you used looks to have some problems. From my point of view it's obsolete, one hint is that it's using the now called "Classic Editor" instead of the current default editor. It's weird that the comment with the code was removed, next time start with a more recent example and once you have learned how to debug and learned the differences between the "old" Google Apps Script and the new (i.e. old runtime based on Mozilla Rhino, and the new runtime Chrome V8), go to old tutorials / examples.

P.S. It might be possible that if you are using new editor that your project is using the new runtime, if you want to try the code as is in the video, try enabling the Rhino runtime, for details see https://developers.google.com/apps-script/guides/v8-runtime.

I have made the modification as suggested but still not working :0( Now in the console I see: Uncaught TypeError: Cannot read properties of null (reading 'forEach') ??? — Skip67, Feb 06 '22 at 07:24

score 1 · Accepted Answer · edited Feb 06 '22 at 17:02

Given that the youtube guy removed his code and doesn't answer on comments it's obviously that there is something terribly wrong with his code.

As far as I can tell the main problem was that you can't return an array from the function getList() into the HTML form. You need to convert it into a string with return JSON.stringify(array) and then (within HTML form) to convert it back into an array with var array = JSON.parse(array).

Basically, if you add the JSON.stringify and JSON.parse and add the brackets as @Rubén said, it should work.

Just in case, here is my a bit rewritten code:

Modal.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

    <script> 

      function NextRecord() {
        var record = document.getElementById("record").value;
        var maxRecord = document.getElementById("maxRecord").value;
        var nextRecord = +record + 1;
        if(nextRecord <= maxRecord) {
          document.getElementById ("record").value = nextRecord;
          google.script.run.withSuccessHandler(loadRecords).getList();
        }
      }

      function PreviousRecord() {
        var record = document.getElementById("record").value;
        var previousRecord = +record - 1;
        if(previousRecord >= 1) {
          document.getElementById ("record").value = previousRecord;
          google.script.run.withSuccessHandler(loadRecords).getList();
        }
      }

      function loadRecords(ar) {
        ar = JSON.parse(ar); // <--- here we parse the string back into an array
        var record = document.getElementById("record").value; 
        document.getElementById("maxRecord").value = ar.length;

        var item = ar[+record-1];
        document.getElementById("inscriptionDate").value = item[0];
        document.getElementById("firstName").value       = item[1];
        document.getElementById("lastName").value        = item[2];
        document.getElementById("gender").value          = item[3];
        document.getElementById("email").value           = item[4];
        document.getElementById("telNumWhatsApp").value  = item[5];
        document.getElementById("location").value        = item[6];
        document.getElementById("visaImageUpload").value = item[7];
        document.getElementById("commentMessage").value  = item[8];
        document.getElementById("referrer").value        = item[9];
      }

      google.script.run.withSuccessHandler(loadRecords).getList();

    </script>

  </head>

  <body>

    Inscription Date:            <input type="text" id="inscriptionDate"/><br>
    First Name:                  <input type="text" id="firstName"/><br>
    Last Name:                   <input type="text" id="lastName"/><br>
    Gender:                      <input type="text" id="gender"/><br>
    Email:                       <input type="text" id="email"/><br>
    Telephone Number (WhatsApp): <input type="text" id="telNumWhatsApp"/><br>
    Location:                    <input type="text" id="location"/><br>
    VISA Image Upload:           <input type="text" id="visaImageUpload"/><br>
    Comment or Message:          <input type="text" id="commentMessage"/><br>
    Referrer:                    <input type="text" id="referrer"/><br>

    <input type="button" value = "PREVIOUS" onClick="PreviousRecord()"/> // <-- don't forget the brackets here
    <input type="text"   value = "1" id = "record" size = "2px"/>
    <input type="hidden" value = ""  id = "maxRecord"/>
    <input type="button" value = "NEXT" onClick="NextRecord()"/> // <-- don't forget the brackets here

  </body>
</html>

Code.gs

function getList(){
  var url = 'https://docs.google.com/spreadsheets/d/1QkSdtybPHA9IrWH2VPw44WtQ9dN_-9KjRVNOuCylMCk/edit#gid=0';
  var ss= SpreadsheetApp.openByUrl(url);
  // var ss = SpreadsheetApp.getActiveSpreadsheet();
  var recordSheet = ss.getSheetByName("WebInscriptions");
  var lastRow = recordSheet.getLastRow();
  var list = recordSheet.getRange(2, 1, lastRow-1, 10).getValues();
  return JSON.stringify(list); // <--- here we return a string instead of the array
}

function startForm() {
  var form = HtmlService.createHtmlOutputFromFile("Modal.html");
  SpreadsheetApp.getUi().showModalDialog(form, 'Manage New Submissions');
}

function addMenu() {
  var ui = SpreadsheetApp.getUi()
  ui.createMenu('HR-Recruitment')
    .addItem('New Submissions','startForm')
    .addItem('Manage Recruits','startForm')
    .addToUi();
}

function onOpen(e) { addMenu() }

Wow my learning curve just spiked. Exactly what was needed. Much appreciated help from both @Rubén and Yuri. I noticed that it takes time for the data to update between toggles. Does that mean that there is a request to the server at each toggle. Is it possible to somehow store the array in local memory ? — Skip67, Feb 06 '22 at 16:59
Fair point. The script makes a call to the server every time you click on the 'PREVIOUS' or 'NEXT' button. It would be better if it be able to store the array in the local memory. But, as far as I know, it's impossible in GAS architecture. The script runs every time afresh without any old data in the memory. Probably you can make the answer from the server a bit faster if you will store the array as a hidden text within the HTML form (like the `record` variable). But I can be wrong. Frankly, I'm not a big expert in the web dev. ) — Yuri Khristich, Feb 06 '22 at 20:51
I found this video [link](https://www.youtube.com/watch?v=1CSPpsBGlQ0&list=PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx&index=2) where this guy talks about storing data in memory. I will try to understand how it works but thought you may also be interested. — Skip67, Feb 07 '22 at 20:04
Yep. Looks promising. Probably you can just add the global variable `ar` and take it every time instead of the calls to the server. As for the video, note: there could be the same problem with return an array. Probably it worked those days. But it looks like today we need to convert the array to a string and vice versa in such cases. — Yuri Khristich, Feb 07 '22 at 20:22
It's turned out that the problem was because of the dates. You can return from backend arrays, and objects. But if you have dates you should convert them into strings. Otherwise you will getting 'null'. — Yuri Khristich, Feb 08 '22 at 00:07

How to fix: "Uncaught ReferenceError: loadRecords is not defined" with Google App Script

2 Answers2