Troubleshooting the “Data Not Defined” Error in Google Apps Script

by

Google Apps Script is a versatile tool for automating tasks within Google Workspace. However, encountering errors like “data not defined” can be frustrating. In this blog post, we’ll troubleshoot a common issue in a script that displays Google Sheets data in a dialog box.

Understanding the Error

The error message “data not defined” typically indicates that a variable or property is being accessed that hasn’t been defined in the current scope. Let’s examine the provided script to identify the cause of the error.

The Provided Script

Here’s the script that is causing the “data not defined” error:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Option').addItem('PopUp', 'showDialog').addToUi();
}

function showDialog() {
  var htmltemp = HtmlService.createHtmlOutputFromFile('Sheet');
  var ss = SpreadsheetApp.openById("1-ld");
  var asheet = ss.getSheetByName("Sheet1");
  var rng = asheet.getDataRange();
  var values = rng.getValues();
  htmltemp.data = values;

  var html = HtmlService.createTemplateFromFile('Sheet').evaluate().setHeight(800).setWidth(1200);
  SpreadsheetApp.getUi().showModalDialog(html, 'My Data');
}

function doGet() {
  var htmltemp = HtmlService.createHtmlOutputFromFile('Sheet');
  htmltemp.setTitle("My Google sheet Data");
  return htmltemp;
}

Identifying the Problem

The error occurs because the htmltemp object, created using HtmlService.createHtmlOutputFromFile(), does not have a data property by default. Attempting to assign values to htmltemp.data results in the “data not defined” error.

Solution: Using a Template to Pass Data

To pass data from your script to an HTML file, use a template. Templates in Google Apps Script allow you to pass variables and process them within your HTML file.

Here’s how to modify your script to use a template:

  1. Modify showDialog to Use a Template:javascriptCopy codefunction showDialog() { var ss = SpreadsheetApp.openById("1-l"); var asheet = ss.getSheetByName("Sheet1"); var rng = asheet.getDataRange(); var values = rng.getValues(); var template = HtmlService.createTemplateFromFile('Sheet'); template.data = values; // Pass the data to the template var html = template.evaluate().setHeight(800).setWidth(1200); SpreadsheetApp.getUi().showModalDialog(html, 'My Data'); }
  2. Update Your HTML File: In your HTML file (Sheet.html), use the <?!= ?> syntax to render the data passed from the script. Here’s an example of how to display the data:htmlCopy code<!DOCTYPE html> <html> <head> <base target="_top"> </head> <body> <h1>Sheet Data</h1> <table border="1"> <? for (var i = 0; i < data.length; i++) { ?> <tr> <? for (var j = 0; j < data[i].length; j++) { ?> <td><?= data[i][j] ?></td> <? } ?> </tr> <? } ?> </table> </body> </html>

Complete Script

Here is the complete script with the modifications:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Option').addItem('PopUp', 'showDialog').addToUi();
}

function showDialog() {
  var ss = SpreadsheetApp.openById("1-");
  var asheet = ss.getSheetByName("Sheet1");
  var rng = asheet.getDataRange();
  var values = rng.getValues();
  
  var template = HtmlService.createTemplateFromFile('Sheet');
  template.data = values; // Pass the data to the template

  var html = template.evaluate().setHeight(800).setWidth(1200);
  SpreadsheetApp.getUi().showModalDialog(html, 'My Data');
}

function doGet() {
  var htmltemp = HtmlService.createHtmlOutputFromFile('Sheet');
  htmltemp.setTitle("My Google sheet Data");
  return htmltemp;
}

And the Sheet.html:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <h1>Sheet Data</h1>
  <table border="1">
    <? for (var i = 0; i < data.length; i++) { ?>
      <tr>
        <? for (var j = 0; j < data[i].length; j++) { ?>
          <td><?= data[i][j] ?></td>
        <? } ?>
      </tr>
    <? } ?>
  </table>
</body>
</html>

Conclusion

By using templates, you can effectively pass data from your Google Apps Script to an HTML file and avoid the “data not defined” error. This approach ensures a smooth integration of your script and HTML, making it easier to create dynamic and interactive applications.