I'm working on a pothole map web app in Google Apps Script. The map gets its data from a Google spreadsheet (from manual surveying), which is then transformed via hits to the Geolocation and Google Maps Roads APIs. There are 101 markers on the map (one marker per row of data, which represents either a pothole or a cluster of potholes). This map, for some odd reasons, takes about 20 seconds to load initially. Unacceptable!

This app makes use of the following APIs:

  • jQuery
  • Google Maps Roads
  • Google Geolocation
  • async.js
  • Mustache.js

I profiled my code , and from what it seems, most of the CPU time is from methods that don't appear to be business ones. I analyzed the code profile two ways:

Self time

There were at least three heavy function calls in this respect, although nothing that seems to be too bad. enter image description here

Total time

There was no business logic right at the top of this list, but plenty right below it. I present thus: enter image description here

I then identify the lines of code where these bottlenecks appear:


/* Adds marker to map.
 * Parameters : 
 *  • potholeData  : a PotholeData (or PotholeDataFromCoords) object
 *  • snappedToRoad: boolean
 *  • callback     : the function to call next (optional)
 * Returns : 
 *  • the marker that was added to the map, or null if arguments invalid, or callback() if it was provided
function addPotholeMarker(potholeData, snappedToRoad, callback) {
  // make sure that callback is either falsy or a function
  if ((callback) && (typeof callback !== 'function')) throw new TypeError('callback specified but not a function. Thrown from addPotholeMarker()'); 
  // make sure potholeState is either falsy or contains iconURL string
  if ((!potholeData.potholeState) || ((potholeData.potholeState) && (potholeData.potholeState.iconURL === undefined))) throw new Error('invalid potholeData');
  // let's make sure to snap this to road if it isn't already...  
  var coords = new GPSCoordinates(potholeData.lat, potholeData.lng);
  if (!snappedToRoad) 
    var potholeMarker = 'a garbage return value';
    getRoadCoordinates(coords).done(function(response) {
        var coords = response.snappedPoints[0].location;
        potholeData.lat = coords.latitude;
        potholeData.lng = coords.longitude;
        return (potholeMarker = addPotholeMarker(potholeData, true, callback));
   /*     potholeMarker = addPotholeMarker(potholeData, true);
        return potholeMarker;*/
    if (callback) return callback(null);
    //return potholeMarker;
  var marker = new google.maps.Marker({
    position: coords,
    title: coords.toString(),
    map: map,
    opacity: 0.5,
    icon: ((potholeData.potholeState.iconURL !== undefined) ? potholeData.potholeState.iconURL : PURPLE_MARKER)

  // make marker have effect when mouseout,mouseover
  marker.addListener('mouseover', function(mouseEvent) {
  marker.addListener('mouseout', function(mouseEvent) {


  var infoWindow = createInfoWindow(potholeData);
  // save infoWindow for later reference
  // on click of marker, show infoWindow
  marker.addListener('click', function(mouseEvent) { 
    infoWindow.open(map, marker);
  // add this to potholeMarkers
  if (callback) return callback(null, marker);
  return marker;


/* snaps potholes stored in potholeCoordinates to road
 * Parameters: 
 *  • potholeCollection : (Object<Array<PotholeData> >) the Collection of PotholeData to use
 *  • callback          : the function to call next
// TODO: refactor the body of this so as to use potholeCollection (preferrably instead of potholeCoordinates)
function snapPotholeCoordsToRoad(potholeCollection, callback)
    var DEBUG = false;
    // guarantee that callback is function
    if ((callback) && (typeof(callback) !== 'function')) throw new TypeError('callback is something, but not a function. Thrown from snapPotholeCoordsToRoad().');
    // for each element of potholeCollection
    if (DEBUG) console.log('potholeCollection === ' + JSON.stringify(potholeCollection, null, '\t'));
    var keys = [];
    for (var key in potholeCollection)
        if (typeof potholeCollection[key] !== 'function') keys.push(key);
    for (var key in potholeCollection)
        (function itr(k, m) { 
            if (typeof potholeCollection[k] === 'function') return;
            if (m === potholeCollection[k].length) return;
            if (DEBUG) console.log('potholeCollection.' + k + '[' + m + '] == ' + JSON.stringify(potholeCollection[k][m], null, '\t'));
            // if element (PotholeData) not snapped to road
            if (!potholeCollection[k][m].isSnappedToRoad())
                // get road coordinates for element
                // replace element's coordinates with those road coordinates
                .done(function(newCoords) { 
                    if (DEBUG) console.log('potholeCollection.' + k + '[' + m + '] == ' + JSON.stringify(potholeCollection[k][m], null, '\t'));
                    if ((k === keys[keys.length - 1]) && (m === potholeCollection[k].length - 1) && (callback)) return callback(null, null, potholeCollection);
                    itr(k, m+1);
                if ((k === keys[keys.length - 1]) && (m === potholeCollection[k].length - 1) && (callback)) return callback(null, null,  potholeCollection);
                itr(k, m+1);
        })(key, 0);


/* put all potholes on the map 
 * Parameters:
 *  • callback         : the function to call next
 *  • unsnappedPotholes: (<Object<Array<PotholeData> > ) collection of potholes with coordinates that have not been snapped to road
 *  • snappedPotholes  : (<Object<Array<PotholeData> > ) collection of potholes with coordinates that are snapped to road
function addPotholeMarkers(unsnappedPotholes, snappedPotholes, callback)
    var DEBUG = false;
    // guarantee that callback is function
    if ((callback) && (typeof(callback) !== 'function')) throw new TypeError('callback is something, but not a function. Thrown from addPotholeMarkers().');
    // add all the markers for them to the map
        function(cb) { 
            async.eachOf(unsnappedPotholes, function(value, key) {
                // TODO: refactor this
                async.eachOf(value, function (v, k) { 
                    if (DEBUG) console.log('dealing with unsnappedPotholes');
                    //console.assert(v.potholeState.toString() != '[object Object]', 'toString() override missing');
                    //v = revivePotholeState(v); // unnecessary, because addPotholeMarker() invokes getPotholeStateFor() to force the potholeState to be in the statesMap
                    addPotholeMarker(v, false);
        }, function(cb) {
            async.eachOf(snappedPotholes, function(value, key) { 
                async.eachSeries(value, function(pothole, fn) { 
                    if (DEBUG) console.log('pothole == ' + JSON.stringify(pothole, null, '\t'));
        }], function(err, results) {
            console.log('trying to center map');

            console.log('Map recentered');
            if (callback) { 


The driver function

google.script.run.withSuccessHandler(function(data) { 
        async.waterfall([//fetchServerPotholeData,  // for some reason, this function is not receiving data
            function(callback) { 
                fetchServerPotholeData(data, callback);
            ], function(err, result) { 
                if (!err) { 
                    console.log('Everything successfully done. Enjoy your map!'); 


I'm trying to fix these bottlenecks but am not sure how.


I'm adding a link to the GitHub project itself that I created the other day, since this question's been crickets since I asked it.

Here is the HTML code to the app (maybe the script is wasting a lot of time loading the necessary <script> tags):

<!DOCTYPE html>
    <base target="_top">
        async defer></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/mustache.js/0.7.0/mustache.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/async.min.js"></script>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <!--<script src="https://google-developers.appspot.com/_static/js/jquery-bundle.js"></script>-->
    <script src="//code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <?!= include('mathFunctions'); ?>
    <?!=// include('Pothole'); ?>
    <?!= include('JavaScript'); ?>
    <?!= include('stylesheet'); ?>
    <div id="toggleHidden" class="floatingMenu">&mdash;</div>
    <div id="legend" class="floatingMenu">
        <span class="center row title">Legend</span>
        <!-- the different icons and states of potholes -->

    <div id="map">


  • \$\begingroup\$ The problem seems to be with addPotholeMarker, which is invoked at most 101 * 2 == 202 times. Google Maps API documentation advises against dropping a bunch of markers all at once: developers.google.com/maps/documentation/javascript/… , at least fro adding animation (which I am not trying to do). Perhaps I could remedy that by writing a wrapper function(potholeData, snappedToRoad, someTime, callback) { setTimeout(addPotholeMarker(potholeData, snappedToRoad, callback), someTime) } ? \$\endgroup\$ Feb 16, 2018 at 20:47
  • \$\begingroup\$ Looks like my wrapper-function idea failed miserably. The data shows no signs of loading at all.... \$\endgroup\$ Feb 20, 2018 at 0:54
  • 1
    \$\begingroup\$ It appears that some rows of the spreadsheet have coordinates filled in - why not have those completed for all rows and utilize that data instead of querying each time the map loads? \$\endgroup\$ Feb 20, 2018 at 23:43
  • \$\begingroup\$ I manually filled those in via manually using Google Maps to get the appropriate GPS coordinates for the potholes on the map. It was tedious just for those few. Doing it manually for all 101 points of interest is thus out of the question. Could I offload that task to the "server" (Google Script) part of the app, and get any sort of performance boost? \$\endgroup\$ Feb 21, 2018 at 7:14

1 Answer 1


Storing coordinates in Google Apps Script, instead of fetching each time on client-side

As was alluded to in your comment, yes the fetching of GPS coordinates could be handled on the server side.

The function getPotholesFromDataStore (or a different method) can utilize the Geocoder service. Then use the Sheet class to write the values back in the sheet.

function getPotholesFromDataStore()
    //doesn't change, so use const
    const POTHOLE_SPREADSHEET = 'https://docs.google.com/spreadsheets/d/1gxDeZUSykyEtL4B7WUYLeKqkDJpuc1uF02Jp_p2lfOg/edit?usp=sharing';

    //new variable for using spreadsheet for reading and then writing later
    var spreadsheet = SpreadsheetApp.openByUrl(POTHOLE_SPREADSHEET);
    //since we opened the spreadsheet above, substitute that here
    var dataStore = spreadsheet.getDataRange().getValues();
    //use this for writing later - take the first sheet (i.e. Sheet 1)
    var sheet = spreadsheet.getSheets()[0];

    //make a geocoder object and set the boundaries for Indianapolis - adjust accordingly
    var boundGeocoder = Maps.newGeocoder()
        // The latitudes and longitudes of southwest and northeast corners of Indianapolis, respectively     
        .setBounds(39.635765, -86.466493, 40.042790, -85.915804);

    for (var j = 1; j < dataStore.length; j++)
        // check for latitude,longitude of the pothole on the current row, using the PotholeDataHelper
        var latLngPresent = PotholeData.isValidCoord(dataStore[j][helperA.argIndices[0]], true) && 
                    PotholeData.isValidCoord(dataStore[j][helperA.argIndices[1]], false);

        if (dataStore[j][3]) { //address available  
            var response = boundGeocoder.geocode(dataStore[j][3]);
            if (response.results.length) {
                var result = response.results[0]; //take the first result
                var rng = sheet.getRange(j+1, 2, 1, 2);
                var values = rng.getValues();
                var valuesToSet = [[result.geometry.location.lat, result.geometry.location.lng]];



Since you have 5 js libraries, I would question whether jQuery is really necessary (and suggest you look at youmightnotneedjQuery.com if you haven't yet). The Fetch API or one of the three suggested on YMNNJQ could perhaps replace the jQuery AJAX code (i.e. $.ajax()).

I see places in the jQuery code like $('#legend div:first').append(). Alternatives to using that jQuery pseudo selector would be to add an id attribute to that first <div> element and using that with document.getElementById(), or select it with document.querySelector() and then use appendChild() to add the HTML.

const and let

I would definitely recommend replacing var with let and const where appropriate in the .gs files and unless browser compatibility is an issue, use it in the regular javascript files as well.

Notice in the example above, I used const POTHOLE_SPREADSHEET - this is because that value is a constant and should not be re-assigned.

const POTHOLE_SPREADSHEET = 'https://docs.google.com/spreadsheets/d/1gxDeZUSykyEtL4B7WUYLeKqkDJpuc1uF02Jp_p2lfOg/edit?usp=sharing';
  • \$\begingroup\$ let is not allowed by Google Apps Script, for some odd reason, but const is.... \$\endgroup\$ Mar 8, 2018 at 0:14
  • 1
    \$\begingroup\$ doh! go figure... \$\endgroup\$ Mar 8, 2018 at 0:14
  • \$\begingroup\$ Yup, Google Apps Script is stuck in the past; /* the only reason I'm using it is because of its built-in easy-access to many things Google, for free */ \$\endgroup\$ Mar 8, 2018 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.