3

I'm trying to upload an Excel Spreadsheet containing student names their geolocations, and their "top skills" from an online class into the Google Javascript API.

Eventually, I want each location to have a popup box associated with it, displaying the student name and their top skills.

From what I can tell according to the API, I have to use Data Arrays like this to plot the markers:

var locations = [
  ['Bondi Beach', -33.890542, 151.274856, 4],
  ['Coogee Beach', -33.923036, 151.259052, 5],
  ['Cronulla Beach', -34.028249, 151.157507, 3],
  ['Manly Beach', -33.80010128657071, 151.28747820854187, 2],
  ['Maroubra Beach', -33.950198, 151.259302, 1]
];

Then I could add these locations like this:

for (i = 0; i < locations.length; i++) {  
      marker = new google.maps.Marker({
        position: new google.maps.LatLng(locations[i][1], locations[i][2]),
        map: map
      });
    }

A couple of questions:

  1. Is there a speedier way of uploading the Excel Spreadsheet Data into a Javascript format without manually writing each location?
  2. How do I associate those specific locations with a student name and list of skills?
pnuts
  • 58,317
  • 11
  • 87
  • 139
Parseltongue
  • 11,157
  • 30
  • 95
  • 160

3 Answers3

3

You could use a json to do this. If you save an excel file as a .csv there are online converters that can change it to a json. In the past I've done this on a project:

http://burdsgis.coffeecup.com/BluePlaques/bpWords.html

The json for the above looks like this:

var dec_markers = [

{
    "NewNumber": "1",
    "Title": "97",
    "Location": "Unknown",
    "Unveiler": "Unknown",
    "Date": "Unknown",
    "Sponsor": "Unknown",
    "TomEast": "-1.55167",
    "TomNorth": "53.7917",
    "Title2": "97",
    "TomURL": "http://farm1.staticflickr.com/76/198148805_85d6ff5b44_m.jpg",
    "TomLink": "http://www.flickr.com/photos/44067831@N00/198148805/in/set-1439239/"
},

etc...

You can then call the json in your map.js:

        //For loop to run through marker data
    for (id in dec_markers) {

        var photo = '<a href="' + dec_markers[id].TomLink + '" target="_blank" title="' + dec_markers[id].Title +' by Tom.Smith, on Flickr"><img src="' + dec_markers[id].TomURL + '" alt="' + dec_markers[id].Title2 + '"></a>';

        var info =  '<div><h1>' + dec_markers[id].Title + '</h1><p><b>Date Unveiled: </b>'  + dec_markers[id].Date + "<br><b>Sponsor: </b>" + dec_markers[id].Sponsor + '</p>' + photo + '</div>';

        var latlng = new google.maps.LatLng(dec_markers[id].TomNorth,dec_markers[id].TomEast);

        addMarker(latlng,dec_markers[id].Title,info);

        mc.addMarker(marker);
        }

I was scraping images from a Flickr album to populate the pop up boxes (using the Flickr API).

Google csv to json for a converter. I'd also suggest using infobubble rather than infowindow as the former is more versatile. For the clustering effect you can use MarkerClustererPlus for Google Maps V3

Oliver Burdekin
  • 1,098
  • 2
  • 17
  • 36
  • @Parseltongue Feel free to contact me if you have any trouble getting it working and links for the scripts / converter can be found in the references section in the above page (BluePlaques page) – Oliver Burdekin Jul 03 '13 at 20:50
  • I'll definitely be contacting you at some point-- what's best for you? I don't have a lot of javascript experience, so I anticipate some hurdles. Thanks so much! – Parseltongue Jul 08 '13 at 13:55
  • 1
    @Parseltongue, check my profile for my email address / link to contact. No PM option on stack exchange and it looks like there never will be: http://meta.stackexchange.com/questions/431/any-way-to-send-a-personal-message-to-another-user I hope it's going alright. – Oliver Burdekin Jul 11 '13 at 21:14
1

You can load information directly from Excel data file with Alasql and XLSX.js libraries.

For example, if you have Excel file with four columns with the following headers in row number 1 and all data in next rows:

  • Student
  • Latitude
  • Longitude
  • Mark

You can load it and process to Google Maps with the following code:

<script src="alasql.min.js"></script>
<script src="xlsx.core.min.js"></script>
<script>
    alasql('SELECT * FROM XLSX("students.xlsx",{headers:true})',[], function(data){
        for (var i = 0; i < data.length; i++) {  
            marker = new google.maps.Marker({
                 position: new google.maps.LatLng(data[i].Latitude, data[i].Longitude),
                 map: map
            });
        }
    });
agershun
  • 4,077
  • 38
  • 41
  • 1
    Thanks Alasql looks awesome! You can even use it with Tabletop.js to get data out of Google Drive spreadsheets: https://github.com/agershun/alasql#alasql-and-google-spreadsheets-with-tabletop – Matthew Lock Feb 24 '15 at 03:00
0

From a point of view to 'give a data to script', 'uploading' may not required. You just "copy" a simple text to clipboard of client PC. Then,some security-loose browser can peek it as follows:

"clipboardData.getData('text');"

Or,If you do not want such a dangerous process, You can make a visible pane like QA box of this page:

<span id=dp style="position:absolute;top:10px;left:8px;"></span>
$('#dp').html('<txtarea id=dt rows=20 cols=48>');

Then,paste some visible and clear data onto it, finally you can process it by function "$('#dt').text()"; At end,please $('#dp').html('') to hide it.

tcltk-d
  • 60
  • 1