StumbleUpon.Com Recommend to StumbleUpon

Fill a customized Google Map with points from spreadsheet file

(Filename: GMapBySpr.htm)

This is one of the "might be fun" pages which exist here at Flat-Earth-Academy.com. It isn't "basic stuff you should know".

Thumbnail for page about putting points on Google map from a spreadsheet

I hope you are an "old hand" at using Google maps? Just too much fun, apart from anything else.

You may already know that you can "draw" on Google maps. But did you know how easy it can be?

You can put pins... with associated information... on the map "by hand". You could compile a map of your favorite pubs, for instance.

But if you put that information into a spreadsheet or database (I offer many tutorials about using the database which is part of the free Libre/ Open Office)) first, you can bring all the joys of computing to managing your list, AND "send" the contents of the list to a Google map! Easily!. Here's how...

My Google Maps

I created a Google map with some pins in it. It looks like what you see below....

Google.com/maps map with user designated pins in it

... and you can visit a "live version" of that. With the live version, you can do the usual things... zoom and pan being the most magical, as far as I am concerned.

I'll leave it to you to "get in" to the "Your Places" part of Google Maps. (You need a Google account... if you have a Gmail presence, that's sufficient- it brings "an account" with it.)

Once there you use the "Maps" tab, and then use "Create Map".

So far, so obvious.

Now you need to know about "layers". Imagine that you have an ink-on-paper map on your desk. That's equivalent to the "base map" (note the styles you can have!).

Now imagine a thin sheet of clear plastic, laid over the base map. That's a "layer". Imagine drawing on it with marker pens. Google lets you do the equivalent.

You can have multiple layers. And uses of your map can switch individual layers on and off. All Just Too Much Fun!

ANYWAY... this is supposed to be about creating pins on a Google map using just a spreadsheet.

The spreadsheet I used to create my sample map looked like...


You put column headings in the first row. (You don't need to highlight it, as I did. I was using OpenOffice Calc, by the way.)

The column headings can be whatever you like. Even gibberish would work. You can have many columns, not just the obvious two we will come to in a moment.

One column... it doesn't have to be the second one... has to have location data. But! This doesn't have to be lat/longs! You can use US zip codes, UK postal codes, names of significant buildings. Street names. Basically, anything Google can look up. (But if you use lat/longs, it will be one less thing to go wrong, won't it?

Moving on

A moment ago, if you were following along, in "our Places/ Maps" you'd created a new map. You should see something like...

Google maps import dialog

Click on "import". That opens into an invitation to select a file from your computer. Do that. (Don't worry, things that aren't so completely obvious are coming! While DOING what we've done so far isn't hard, perhaps knowing that you CAN do the job with so little trouble was not obvious?)

After a moment of watching a progress bar, you should see a dialog asking which columns should be used to tell Google where to put the "pins" on your map. Note that you can select multiple columns. (You can have lat in one column, long in another. If you give raw numbers in these columns, Google will treat the first column's numbers as the latitude numbers. If you did that and also, say, used postal codes, Google figures it out... but why make things hard for it? (It will even flag entries that are merely problematic, but let you proceed if you wish. (As a test, I "accidentally" reversed some lat/ long numbers.))

Then it asks you to choose a column to title our markers. Don't lose too much sleep about this choice, I am pretty sure you can change the setting you are making. In my table, you can see I had short names for the places I was marking, and descriptions. For the "Title", I chose the short version. Note: In every case, Google maps pays no attention to the names you gave the columns, though it will, later, refer to the data from the columns by the names you assigned.

Even though Google only asks columns for location and title, fear not, all of what's in your spreadsheet will move across to be part of your map, if only internally.

If something is unusable, the process will complain. I put a negative number into the column for latitude, which makes no sense. (Negative numbers for longitude are fine.)

You can edit the table held with the map... but... and here's an important point: Your changes will not be written back to the spreadsheet it was initially filled from. (And likewise, changes to the spreadsheet after you've used it to fill the map's table will not automatically appear in the map.) Deal with it!

So much for the easy stuff!

That's all you need to get what I promised... a Google map, with pins on it, placed and labeled according to stuff from a spreadsheet.

What remains are a few pointers about refining your map, and letting the world see it... if you wish. You don't have to make it available, if it is something private.

On the map we have so far all the pins are the same. On my demo, different pins have different colors

Let's say you were making that map of favorite pubs I spoke of. If you had a column with your rating of the pub, let's say you rated them "A", "B" and "C". (And "D" and "E", if you want to bother to record pubs you didn't like.), you could then have the color of the pin reflect your rating of the pub. You do it like this...

The dialog we used earlier now looks like...


See the little "paint roller" beside "Uniform style"?

If you click on that, you get...


Change the "Uniform Style" to "Style by data column", and, presto- different pins for different values. (If I start on all the clever stuff you can do, I may never finish this. Suppose the column was "price of a night out"... you could tell the map to make a red pin for any that cost more than £100, blue for £50-99, green for £10-49... etc. With boundaries chosen by you. Etc!!)

While we're here, notice that we can change the "labels". What are the "labels"... they're what the pins are marked with, on the map.

What about the other stuff in your table? Yes, users of your map can see that, too. All they (or you!) have to do is to click on the pin, and a little "info box" pops up.

So! We have pins! We have different pins for different categories of points. What if you don't like the colors?

Edit pin color- Google Map

See the "tipping paint bucket" icon to the right of each pin category? Click it. You can now change the color of the pins for that category, AND the icon used. (There's one that may be someone kicking a football, but also looks remarkably like some of my friends trying to walk after a visit to the pub.)


We've seen the following before...


I've repeated it here, to save you scrolling back. See the two columns of three dots? Each presents a small menu when clicked. Click the lower one to delete a layer. You will have to do that if the data you want to display has changed. Update the external spreadsheet which you used to create the layer, delete the old layer, import a new layer, with the new data. There's no way that I know of to export one of the internal- to- this- Google- map table "behind" a layer. So use the external spreadsheet as the "ultimate authority" for the points on a layer, and re- import it from time to time, as necessary.

There is, on the same menu, an "open data table" option. Click that to make "quick and dirty" repairs, updates to date in the table. But it can ALSO be used to change which column is used as the source of the titles of the points displayed. The "title" for a point is used at the top of the info box which pops up when you click on a point on the map.

The following is a detail from the first graphic I gave you.


In fact, you'd never see a page looking like this. You would either have the four point icons with the numbers beside them (the count of pointers of that type) or the panel I've made yellow with a list of all the pointers, grouped by type.

In my early trials, an annoying discrepancy turned up... When I was looking at my own map, with full editor privileges, if I hovered my mouse over a point icon (in the list) or label there, the corresponding point, on the map, was highlighted. That didn't work for people merely viewing the map I'd created. In either case, double clicking on a label in the list centered the map on that point.

Sharing: There are, as you would imagine various ways to use what you have created.

Go back in this tutorial two images, to the one with "Uniform style" and "No labels" on it. Near the top, see "Add Layer/ Share/ Preview"? Click on that. Don't worry... it only takes you to a "Your sharing choices" menu.

Google My Maps sharing dialog

At the bottom: "Invite people". Would you leave your mouth open while chewing your food? The internet is new. Manners are still being decided. Me? I'd rather watch someone chew with their mouth open than be subjected to having my email address given to third parties without my consent. Because that's what's going on when you fall for one of these "tell your friends about..." links, and enter your (perhaps former) friend's email address.

If you want to restrict access to this page just to specific people (and for each of them, you can say they can just view, or view and edit), you are going to have to "invite" them, I suspect. But ask them first if it is alright to put their eddress into this record.

If you want people to see your map, just send them, yourself, by whatever means is convenient, the link provided to you at the top of this page under "Link to share". Turn it into a bit.ly, if you want to share the link in a human friendly form.

But just giving them the link is not enough. You have to make settings to the page to make it visible to others. And you can control the level of access they have. That's the use of the "Change" link, to the left of "Anyone who has link can view."

You can make the map visible to anyone on the internet. You can make it available just to people who have the link. You can make it visible to a select list of people.


Sticking to the idea of a map listing good pubs... what if some of them have webpages? And you want users of your map to be able to go to the pub's webpage easily?

No problemo, as someone who shouldn't have been going to pubs was wont to say.

Put the pub's URL into one of the columns, with the "http://" bit, and it will give rise to a clickable event.

As we have seen, we don't need separate layers for, say "Grade A" pubs, "Grade B" pubs, etc. We can "do that" another way....

.... Unless. Unless you want to be selective. Unless you want to do what would be called "filtering" in some contexts.

Suppose you only wanted the "important" ones, say the "A" pubs and "E" pubs, but not the great unwashed in the middle? Then you would use a separate layer (and separate spreadsheet, to populate the layer) for each. Once you had your multiple layers, you could play with the tick-boxes in front of the layer name to turn that layer on or off.

Found it interesting?

You're still reading! Thank you.

If it's because you are still looking for an answer to someting, feel free to write, ask. (See just a bit further down the page.)

Otherwise, I assume you found the page at least a little interesting. I've spent hours putting it together. It would be nice if it reached an audience. Please do Facebook likes, mention it in forums, etc? (There are links at the top.)

Search across all my sites with the Google search button at the top of the page the link will take you to.

Search just this site without using forms,
Or... again to search just this site, use...

Powered by FreeFind

Site search Web search

The search engine merely looks for the words you type, so....
  *!  Spell them properly   !*
  Don't bother with "How do I get rich?" That will merely return pages with "how", "do", "I", "get" and "rich".

I have other sites. My Google custom search button will include things from them....
   One of my SheepdogGuides pages.
   My site at Arunet.

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

--Click here to visit editor's freeware, shareware page.--

This page's editor, Tom Boyd, will be pleased if you get in touch by email.

Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in Google+ button code. Sigh.

-- Page ends --