Fix Google Apps Script HTML Syntax Error in Mapping Project

HTML
Written by Shaheen Ullah

I’m pretty new to coding and running into a syntax error I can’t seem to resolve. Earlier, I actually managed to get my script working, but now I’m back to square one with this issue! My goal is to pull data from rows in a Google Sheet and display it on a map. However, I’m getting an error specifically on line 9, where I’m trying to return the HTML array.

Here’s the problem line:

codereturn html.evaluate().setTitle('Company Directory Map');

I’ve combed through online resources but still feel stuck. If anyone has suggestions, I’d be so grateful! Here’s my current code setup:

Error Code:

codefunction doGet(e) {
  var html = HtmlService.createTemplateFromFile('DirectoryHTML');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  html.ss = parseDirectory(ss.getSheetByName('Directory Address & Name').getRange('A2:R').getValues());
  return html.evaluate().setTitle('Company Directory Map');
}

function parseDirectory(values) {
  var locations = [];
  for (var i = 1; i < values.length; i++) {
    locations.push({
      LocationID: values[i][1],
      LocationName: values[i][4],
      Address: values[i][6],
      AddressDetail: values[i][7],
      City: values[i][8],
      State: values[i][9],
      Zipcode: values[i][10],
      ZipcodeExtension: values[i][11],
      mapURL: getMapUrl(values[i][6, 8, 9, 10, 11]),
      Phone: values[i][12],
      Fax: values[i][13],
      EnteralPhone: values[i][14],
      EnteralFax: values[i][15],
      Type: values[i][16],
      Notes: values[i][17]
    });
  }
  return locations;
}

function getMapUrl(city) {
  return Maps.newStaticMap().setSize(1200, 600).setCenter(41.37132419162449, -112.13662837438801).getMapUrl();
}

For context, here’s the HTML template I’m working with as well:

code<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <? = for (var i = 0; i < locations.length; i++) { ?>
      <div>
        <img src="<? locations[i].mapUrl ?>" />
        <h2><?= locations[i].LocationID ?></h2>
        <!-- <h4><?= locations[i].Address ?></h4> -->
      </div>
    <? } ?>
  </body>
</html>

Any advice on what might be causing this error, especially with the return statement or my HTML template syntax, would be a big help!

The syntax error in your script is likely due to some issues with JavaScript syntax and Google Apps Script. Let’s go through the code and address potential issues step-by-step to help resolve the error and improve your code.

Problem and Solution:

  1. Google Apps Script HTML Rendering Error: In the HTML code, you’re attempting to use <? = ... ?> syntax, which isn’t correct for Google Apps Script templating. Instead, use <?= ... ?> to embed JavaScript expressions within HTML files.
  2. Array Access Issue in getMapUrl: In getMapUrl, you’re trying to pass multiple indices to values[i][6,8,9,10,11]. This doesn’t work because values[i][6,8,9,10,11] is not valid JavaScript syntax for accessing multiple elements from an array. You may need to adjust how you handle multiple fields when constructing your map URL.
  3. Return Error in doGet Function: The error appears in return html.evaluate().setTitle('Company Directory Map');. This usually happens if the html.evaluate() is not correctly configured. Ensure that the parseDirectory function is returning the correct structure and that the HTML file references the variable name locations.

Corrected Code:

codefunction doGet(e) {
  var html = HtmlService.createTemplateFromFile('DirectoryHTML');  // Load HTML file
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Parse directory and assign it to the HTML template
  html.locations = parseDirectory(ss.getSheetByName('Directory Address & Name').getRange('A2:R').getValues());
  
  return html.evaluate().setTitle('Company Directory Map');  // Return evaluated template
}

function parseDirectory(values) {
  var locations = [];
  for (var i = 0; i < values.length; i++) {
    locations.push({
      LocationID: values[i][1],
      LocationName: values[i][4],
      Address: values[i][6],
      AddressDetail: values[i][7],
      City: values[i][8],
      State: values[i][9],
      Zipcode: values[i][10],
      ZipcodeExtension: values[i][11],
      mapURL: getMapUrl(values[i][8], values[i][9], values[i][10], values[i][11]),  // pass separate fields
      Phone: values[i][12],
      Fax: values[i][13],
      EnteralPhone: values[i][14],
      EnteralFax: values[i][15],
      Type: values[i][16],
      Notes: values[i][17]
    });
  }
  return locations;
}

function getMapUrl(city, state, zip, zipExt) {
  // Customize this based on the specific map URL you need
  return Maps.newStaticMap()
             .setSize(1200, 600)
             .setCenter(`${city}, ${state}, ${zip}-${zipExt}`)
             .getMapUrl();
}

Update your HTML code with the correct syntax for templating:

code<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <? for (var i = 0; i < locations.length; i++) { ?>
    <div>
      <img src="<?= locations[i].mapURL ?>" alt="Map Image" />
      <h2><?= locations[i].LocationID ?></h2>
      <h4><?= locations[i].LocationName ?></h4>
      <p><?= locations[i].Address ?>, <?= locations[i].City ?>, <?= locations[i].State ?> <?= locations[i].Zipcode ?></p>
      <!-- Add more fields as needed -->
    </div>
  <? } ?>
</body>
</html>

Explanation:

Additional Tips:

  1. Debugging Google Apps Script Errors:
    • Use Logger.log() to print values during execution, which helps debug the flow.
    • Check that your Google Sheet structure matches the code’s expected columns.
  2. Checking HTML Templates:
    • Google Apps Script templating can be tricky; ensure all server-side variables (like locations) are correctly assigned and accessible in the HTML file.

This setup should resolve the syntax error and provide a working example for reading from a Google Sheet and rendering data on a map within HTML.

Related blog posts

Load more
Know More About FSIBLOG
Useful Pages

2024 © FSIBLOG. All Rights Reserved.