Map a Google Sheet

[Kylie]

You have information in a Google Sheet that you want to see in a map. No problem! Publish your Google Sheet as a CSV and add it to the map in ArcGIS Online.

Part 1: Publish your Google Sheet as a CSV

(*If you click on a video, you will see it larger.)

1. Open Google Sheets and open your CSV. It might be one that you created, or one that you saved to your sheets.
   Your CSV needs to be properly formatted:
   • Start the table in the top left corner of the sheet.
   • You can't have any non-numeric characters in the latitude and longitude columns. 
   • Watch for random words or sentences above or below the main content.
   • Ensure latitude and longitude values are always in the correct columns - we see some values flipped in some rows of some sheets.
   • The type of your cell (for example, numeric) needs to match the contents in it. In particular, be careful of degree symbols or letters like N and W in numeric fields.
2. Click File > Share > Publish to web.
3. In the link tab, make sure the first drop-down is set to Entire Document and in the second, choose Comma-separated values (.csv).
4. Click Publish.
5. Click OK if asked if you are sure you want to publish.
6. Copy the link that is shown by pressing Control-C on your keyboard.
7. If you want changes to your Google Sheet to automatically appear in the map, verify that Published content & settings > Automatically republish when changes are made is enabled.

Part 2: Add your published CSV to the map

1. Log in to ArcGIS Online and open Map Viewer.
2. Click Add > Add layer from URL.
3. Paste in your URL (click Control-V on your keyboard) and click Next.
4. Choose Reference the file from the URL and add it to the map and click Next.
   By referencing the file, changes you make to your Google Sheet will appear in the map.
5. In the Fields dialog, accept the defaults and click Next.
6. In the Location settings dialog, make sure the fields with latitude and longitude in your spreadsheet were correctly identified. If not, correct them.
7. Click Add to map.

Your data appears in the map! The information from your spreadsheet is available in the pop-ups. Now you can customize the symbology and pop-up, making a great map of your data.

Want to try it out?

Check out the great storymap my coworker Tom put together!

[SaraJL]

@Kylie I just have a quick question! Is it possible to know if adding columns to a Google Sheet linked to the map will update in both the attribute table and the pop-up? I was playing around with this today and noticed that it updates all of the information in the attribute table - but if anyone adds columns to the spreadsheet, it doesn't seem to reflect in the pop-up. 

Example Map

Thank you!

[Kylie]

Good question! I see the same behavior, and it makes sense to me that while ArcGIS Online can generate an initial pop-up, it doesn't change it when the underlying spreadsheet has columns added. 

Why, you might ask? Well, when you first add the data, a pop-up of a list of fields and values is created for you. But you can customize it, removing fields, adding text or images, taking out the list as it was created, and other ways as well. This is stored as part of the map.

When you change the spreadsheet, the map doesn't know how you might want that new information included in your pop-up. So ArcGIS Online waits for you to update the pop-up, telling the map how to use the new info in the pop-up. 

The attribute table, on the other hand, shows the columns of the data, so the map knows there is a new column and how to include it.

(This is not unique to CSV data in ArcGIS Online. It is the same behavior if you create a layer directly in ArcGIS Online and later add a field - the pop-up needs to be manually updated to include that new field.)

[WKorevaar]

It only works with latitude etc not with adresses or postcodes. Thats a pitty.

[Kylie]

Sorry for my slow reply! Yes, it is latitude/longitude only if you want to reference the file from the URL and have changes you make in your spreadsheet appear in your map. This is due to the "cost" of plotting addresses. 

However, you can take a spreadsheet with addresses in it and created a hosted feature layer based off of it. In Part 2, step 4, you'd want to choose Create a hosted feature layer and add it to the map instead of referencing the file as in the steps above. This way, the addresses are plotted on the map once instead of having to plot them every time the map is used.

In fact, if you create a hosted feature layer from your CSV, your CSV can contain addresses, place names, postal codes, and countries. (It could even include other coordinates like MGRS or USNG.)

[WKorevaar]

Thanks. But now there is no link between the spreadsheet and the layer as was shown in the original post, you always have to use the 'csv as hosted layer' for changes etc. and not the original spreadsheet (which you can 'share')

[Kylie]

Correct - that's the trade-off since using the linked spreadsheet doesn't give a way for the located addresses to be stored (you don't want to look them up every time the map is accessed as would be necessary if linking a spreadsheet with just addresses). 

You can share the hosted feature layer and have changes be made there. 

What are you doing? I might have a recommendation, or there might be a more elegant solution.

[WKorevaar]

Hi

No not a direct problem. It just would be a lot easier for my students
-already using spreadsheets for adresses- to update their lists 'on the
fly'insteda of uploading them for every change/ delete etc.
Not only for the above, but also nice if it would work for a spreadsheet
that's used by multiple users (shared).

[NileshShingade]

I have added the Google sheet in arcgis online map. it looks good there. but it dosent come in the Web app which i have created using the same map(Web app Builder). what need to do any suggession?

Thanks

[Kylie]

Hi @NileshShingade - I'd need some more info to help you out. Could you share the map and the app so I can see them?