This guide will walk you through the process of using Google Fusion Tables to turn a spreadsheet into a map.
Before we begin, make sure you are logged into your Google account. (Note to Lens staff: Our work accounts don't seem to allow Google Fusion Tables, so log in as a separate account for now.)
Read on for a detailed explanation of the steps.
CSV (comma-separated values) files are the same as Excel files (.xlsx and .xls), for all intents and purposes. You can open either in Excel. The key difference is that CSV files aren't specific to any single program, so they can be used by any program.
If you already have a CSV file, you can skip this step. Don't have any data at all? Go to this sample Google spreadsheet to follow along.
Google Sheets
While looking at the spreadsheet that you want to save, click on "File" -> "Download as" -> "Comma-separated values (.csv, current sheet)."
The downloaded file will likely be saved to your "Downloads" folder, if you're working on a Mac. You can find it by opening the Finder app and clicking on "Downloads" on the list to the left.
Excel file (.xlsx or .xls)
In Excel, go to "File" -> "Save as." A popup will open. Type in the filename that you want, but don't save it yet. Find the "Format" dropdown and select the .csv format. Then click "Save."
In a new tab or window, open Google Drive (http://drive.google.com). (Lens staff: Make sure you are logged in under an account other than your work account. You can check by looking in the upper right-hand corner.)
Under the "New" button, click "More" and then click on "Google Fusion Tables."
Don't see it yet? Click on "Connect more apps" instead. In the popup, search for "Google Fusion Tables" and then click on the "Connect" button to add the application to your account. You should now be able to open Fusion Tables by again clicking "New" -> "More" -> "Google Fusion Tables."
Now that you've started a new project, you need to import your data. Click on "Choose file" and navigate to the .csv file you created in step 1, possible in your "Downloads" folder. If your data came from Google Sheets, you will see that it is also possible to import data directly from there.
Two new options will appear: "Separator character" and "Character encoding." You can safely ignore these for the majority of your projects. Click "Next" to load the next step.
Fusion Tables will try to identify the row that has your column headings. Make sure that they are shown in the first row. If not, adjust the dropdown menu to let Google know where the headings are located. Click "Next."
For the last import step, you can fill in or change details about your project. These are all optional, but they might be helpful for viewers who aren't as familiar with your data as you are. You can also change these later if you aren't sure about what to do. Click "Finish" once you are done.
Now you have a Fusion Table! There are still a few steps left before you see a map though.
Find the column in your data that contains location information. This can either be addresses or latitude/longitude coordinates. Hover over that column's header and click on the triangle dropdown icon that appears on the right side of the header's cell. Select "Change..." in the dropdown menu.
You are now editing this column. Change the "Type" to "Location" to let Google know that this column contains data that can be mapped. Click "Save."
Now look for the two tabs above your spreadsheet labeled "Rows 1" and "Cards 1." Click on the small red plus sign icon to the right of the last tab, then select "Add map."
A new map tab will be created. If your data contained addresses, then a popup will appear letting you know that Google has begun geocoding your data. If you have latitude/longitude coordinates, they will be mapped almost instantly.
If you had addresses in your data, click "Pause geocoding" in the popup that opened. We need to give Google a little bit more information so that it can return the best mapping results possible. Click on "Add location hint," then fill in helpful information about where your data appears, such as the city and state.
In the case of the sample data, there are only addresses and no city or state information. Google will do its best to guess which city and state those addresses are in, but this will lead to errors. In the text box, enter "New Orleans, LA" or wherever your data might appear.
Click on "Resume geocoding" and let Google process your data. Once it is finished, you will see your mapped data. The locations will never be perfect, so always take your map with a grain of salt.
One quick way to check on the integrity of your results is to zoom out as far as you can and look for points that are shown outside of the location you expected. For the sample data, you would want to look out for any points shown outside of New Orleans.
If you spot any of these points, you can correct them. Click on the point in the map to open a popup that tells you more information. Using that information, find the point's row back in the spreadsheet (click on the "Rows 1" tab above the map to open the spreadsheet again). Click on the row, and then click on the pencil icon that opens up on top of the row (the left icon of a set of three icons) to edit this row's data.
In the popup that opens, click on the "Edit geocode..." link. You can now search for this point again and select the correct location. Type in as much information as you can for a point, including city, state and ZIP code if you have them.
Find the red marker that is located at the correct spot and click on it. Then click "Use this location." Click "Save" and then open the map again ("Map 1" tab at the top) to see your updated data. Repeat this process for any other points that weren't geocoded correctly.
If you have been using the sample data, then your finished product should look something like this: https://www.google.com/fusiontables/data?docid=1F93P9nzQku8CD-A0Xhe5Xiv1G_WdR92aOR_mlv-c#rows:id=1
Now that you have a finished product, you can share the map with others and even embed it in a story.
While viewing the map, click on the "Map 1" tab and then click on "Publish..." in the dropdown menu that appears. A popup will open that has a URL that links to a full-screen view, an HTML iframe code snippet or the full HTML and JavaScript for the map.