Description: Do you need to create and maintain dozens of placemarks? Want a clean, attractive balloon design without editing any HTML?
This tutorial shows you how to enter data in a spreadsheet and then generate a set of placemarks for that data in Google Earth. When you want to update your placemarks, you simply update the spreadsheet and generate a new KML file. This tutorial provides a sample spreadsheet and instructions for entering your own placemark data.
Confused? Need help? Post your questions regarding this tutorial by replying to this thread...
I like this idea, However, I would like to change the template used to one of the other templates in the Using Better Balloons tutorial. I am having trouble with word wrap of a long paragraph. It is limited to the Table size of the generated Ballon, the rest of the text on each line gets cut off instead of wrapping at the proper spot.
> Description: > Do you need to create and maintain dozens of placemarks? Want a clean, > attractive balloon design without editing any HTML?
> This tutorial shows you how to enter data in a spreadsheet and then > generate a set of placemarks for that data in Google Earth. When you > want to update your placemarks, you simply update the spreadsheet and > generate a new KML file. This tutorial provides a sample spreadsheet > and instructions for entering your own placemark data.
> Confused? Need help? Post your questions regarding this tutorial by > replying to this thread...
Had a play with this earlier today, good work and a nice development of the EditGrid experiments Valery has done. I like all the work to develop the formatting of the pop up balloons. However, I didn't like the 9 steps to publish process, using editgrid you can write an XSL file and a URL pointing at that will boot up GE and plot all the files. Any reason this couldn't be done in Google Spread Sheets?
> I like this idea, However, I would like to change the template used to > one of the other templates in the Using Better Balloons tutorial. I > am having trouble with word wrap of a long paragraph. It is limited > to the Table size of the generated Ballon, the rest of the text on > each line gets cut off instead of wrapping at the proper spot.
> > Description: > > Do you need to create and maintain dozens of placemarks? Want a clean, > > attractive balloon design without editing any HTML?
> > This tutorial shows you how to enter data in a spreadsheet and then > > generate a set of placemarks for that data in Google Earth. When you > > want to update your placemarks, you simply update the spreadsheet and > > generate a new KML file. This tutorial provides a sample spreadsheet > > and instructions for entering your own placemark data.
> > Confused? Need help? Post your questions regarding this tutorial by > > replying to this thread...
Re: other templates in the Using Better Balloons tutorial - This is on the way, stay tuned!
Re: Balloon Width - I have added a "Balloon Width" cell to the "Templates" sheet, so you can now specify the width of the HTML table that wraps all the content. Try to keep it no wider than 550-600, otherwise users with small monitors will be swamped. Grab a fresh copy of the template by clicking on the "start template" link again on the tutorial's page, and tell me what you think.
Re: Text wrapping - Regarding the text wrapping problem... I tried a few long paragraphs without problems. Is the text actually running off the edge of the balloon? Reply with the HTML of your balloon, and I'll see if I can figure out the problem.
Finer control/creating your own templates: If you want to dive deeper, check out the kml(!) sheet. See the formula in the K6 cell and follow the lead. You can make any HTML layout you want.
We're planning to produce more versatile templates for power users... and also creating some tutorials explaining how to build your own. Specifically, how to create robust formulas that don't break when rows are deleted on the Placemarks sheet, how to create folders for groups of placemarks, conditional styling/content, etc.
If you want to get an idea of what is possible, see the Global Heritage Fund, Global Awareness layer we just released. Almost this entire layer, including the additional KML downloads from the main downloads, was created using Google Spreadsheets.
> I like this idea, However, I would like to change the template used to > one of the other templates in the Using Better Balloons tutorial. I > am having trouble with word wrap of a long paragraph. It is limited > to the Table size of the generated Ballon, the rest of the text on > each line gets cut off instead of wrapping at the proper spot.
> > Description: > > Do you need to create and maintain dozens of placemarks? Want a clean, > > attractive balloon design without editing any HTML?
> > This tutorial shows you how to enter data in a spreadsheet and then > > generate a set of placemarks for that data in Google Earth. When you > > want to update your placemarks, you simply update the spreadsheet and > > generate a new KML file. This tutorial provides a sample spreadsheet > > and instructions for entering your own placemark data.
> > Confused? Need help? Post your questions regarding this tutorial by > > replying to this thread...
Glad you like it! Valery did some great work and was the inspiration for this, and many other implementations. We've got some more general/ versatile templates in development that will make it fairly easy to generate more than just simple placemarks.
For this first version we wanted to create a system using the existing functionality of Google Spreadsheets. We are aware of the multitude of steps required to publish and load the KML, and are working with the Spreadsheets team to see how this could be streamlined.
> Had a play with this earlier today, good work and a nice development > of the EditGrid experiments Valery has done. I like all the work to > develop the formatting of the pop up balloons. However, I didn't like > the 9 steps to publish process, using editgrid you can write an XSL > file and a URL pointing at that will boot up GE and plot all the > files. Any reason this couldn't be done in Google Spread Sheets?
> Rich Treves
> On Jun 27, 12:42 pm, mba wrote:
> > I like this idea, However, I would like to change the template used to > > one of the other templates in the Using Better Balloons tutorial. I > > am having trouble with word wrap of a long paragraph. It is limited > > to the Table size of the generated Ballon, the rest of the text on > > each line gets cut off instead of wrapping at the proper spot.
> > > Description: > > > Do you need to create and maintain dozens of placemarks? Want a clean, > > > attractive balloon design without editing any HTML?
> > > This tutorial shows you how to enter data in a spreadsheet and then > > > generate a set of placemarks for that data in Google Earth. When you > > > want to update your placemarks, you simply update the spreadsheet and > > > generate a new KML file. This tutorial provides a sample spreadsheet > > > and instructions for entering your own placemark data.
> > > Confused? Need help? Post your questions regarding this tutorial by > > > replying to this thread...
Spreadsheets are a neat way of generating KML files, I've used them sucessfully with a course of 100 students collating their data together. I look forward to seeing later templates and glad to see you working closely with the spreadsheets team, I used Editgrid and their servers let me down, students had trouble uploading their data.
I've also developed a proof of concept mashup between Moodle (moodle.org, a virtual learning environment - think CMS for courses) and GE, it does a similar task to the spreadsheets using their database functionality. It led to a more advanced development;
> Glad you like it! Valery did some great work and was the inspiration > for this, and many other implementations. We've got some more general/ > versatile templates in development that will make it fairly easy to > generate more than just simple placemarks.
> For this first version we wanted to create a system using the existing > functionality of Google Spreadsheets. We are aware of the multitude of > steps required to publish and load the KML, and are working with the > Spreadsheets team to see how this could be streamlined.
> Thanks for the feedback!
> - Sean > Google Earth Outreach Team
> On Jun 27, 9:19 am, "[email address]" wrote:
> > Had a play with this earlier today, good work and a nice development > > of the EditGrid experiments Valery has done. I like all the work to > > develop the formatting of the pop up balloons. However, I didn't like > > the 9 steps to publish process, using editgrid you can write an XSL > > file and a URL pointing at that will boot up GE and plot all the > > files. Any reason this couldn't be done in Google Spread Sheets?
> > Rich Treves
> > On Jun 27, 12:42 pm, mba wrote:
> > > I like this idea, However, I would like to change the template used to > > > one of the other templates in the Using Better Balloons tutorial. I > > > am having trouble with word wrap of a long paragraph. It is limited > > > to the Table size of the generated Ballon, the rest of the text on > > > each line gets cut off instead of wrapping at the proper spot.
> > > > Description: > > > > Do you need to create and maintain dozens of placemarks? Want a clean, > > > > attractive balloon design without editing any HTML?
> > > > This tutorial shows you how to enter data in a spreadsheet and then > > > > generate a set of placemarks for that data in Google Earth. When you > > > > want to update your placemarks, you simply update the spreadsheet and > > > > generate a new KML file. This tutorial provides a sample spreadsheet > > > > and instructions for entering your own placemark data.
> > > > Confused? Need help? Post your questions regarding this tutorial by > > > > replying to this thread...
That is really cool! I would personally like to see a lot more geo/KML plugins developed for open-source CMS's... would be a great way to not only annotate your lessons, content, etc. but also to manage large KML projects too.
> Spreadsheets are a neat way of generating KML files, I've used them > sucessfully with a course of 100 students collating their data > together. I look forward to seeing later templates and glad to see > you working closely with the spreadsheets team, I used Editgrid and > their servers let me down, students had trouble uploading their data.
> I've also developed a proof of concept mashup between Moodle > (moodle.org, a virtual learning environment - think CMS for courses) > and GE, it does a similar task to the spreadsheets using their > database functionality. It led to a more advanced development;
> you might find it interesting, Moodle has 7.7 million plus users > worldwide and is growing rapidly.
> Rich Treves
> On Jun 28, 12:44 am, sensei wrote:
> > Glad you like it! Valery did some great work and was the inspiration > > for this, and many other implementations. We've got some more general/ > > versatile templates in development that will make it fairly easy to > > generate more than just simple placemarks.
> > For this first version we wanted to create a system using the existing > > functionality of Google Spreadsheets. We are aware of the multitude of > > steps required to publish and load the KML, and are working with the > > Spreadsheets team to see how this could be streamlined.
> > Thanks for the feedback!
> > - Sean > > Google Earth Outreach Team
> > On Jun 27, 9:19 am, "[email address]" wrote:
> > > Had a play with this earlier today, good work and a nice development > > > of the EditGrid experiments Valery has done. I like all the work to > > > develop the formatting of the pop up balloons. However, I didn't like > > > the 9 steps to publish process, using editgrid you can write an XSL > > > file and a URL pointing at that will boot up GE and plot all the > > > files. Any reason this couldn't be done in Google Spread Sheets?
> > > Rich Treves
> > > On Jun 27, 12:42 pm, mba wrote:
> > > > I like this idea, However, I would like to change the template used to > > > > one of the other templates in the Using Better Balloons tutorial. I > > > > am having trouble with word wrap of a long paragraph. It is limited > > > > to the Table size of the generated Ballon, the rest of the text on > > > > each line gets cut off instead of wrapping at the proper spot.
> > > > > Description: > > > > > Do you need to create and maintain dozens of placemarks? Want a clean, > > > > > attractive balloon design without editing any HTML?
> > > > > This tutorial shows you how to enter data in a spreadsheet and then > > > > > generate a set of placemarks for that data in Google Earth. When you > > > > > want to update your placemarks, you simply update the spreadsheet and > > > > > generate a new KML file. This tutorial provides a sample spreadsheet > > > > > and instructions for entering your own placemark data.
> > > > > Confused? Need help? Post your questions regarding this tutorial by > > > > > replying to this thread...
I'm looking to use an online spreadsheet for a class of 100 students collecting data in October. I used EditGrid last year and it fell over when lots of users tried to access it at once. I've looked at Google Spreadsheets to replace EditGrid, the templates you've produced are very useful but I found the update of the sheet was a few seconds, I think they'll get confused if I ask them to input their data with that sort of delay. Thought you'd like to know and also pass on my suggestion for helping with the issue, at the moment when you paste a value into a cell nothing changes, then a red 'updating' signal appears in the top left corner. The red updating signal is useful but it needs an orange 'processing' signal before it. This will mean that a user will see a change when they hit return so they won't worry that their edit hasn't been registered.
I think this is a really cool idea. I'm pretty new to this, and I don't have a lot of database experience, so this is a way for me to get started. I'd like someday to really unleash the power of this tool using database queries, but that is another topic for another day. Right now, one problem that I am having is formatting numerical data...I've imported some numbers into the spreadsheet, which have two decimal places. I can modify the format on the Placemarks tab, using the format command...but of course, in my !kml tab, these formats are not recognized, and I still have the two decimal places there. Is there a way in the macros to specify formatting? Or do I need to round and format my numbers before I bring them into google spreadsheets?
> Description: > Do you need to create and maintain dozens of placemarks? Want a clean, > attractive balloon design without editing any HTML?
> This tutorial shows you how to enter data in a spreadsheet and then > generate a set of placemarks for that data in Google Earth. When you > want to update your placemarks, you simply update the spreadsheet and > generate a new KML file. This tutorial provides a sample spreadsheet > and instructions for entering your own placemark data.
> Confused? Need help? Post your questions regarding this tutorial by > replying to this thread...
It's great to hear you've found this tutorial helpful, and thanks for the feedback.
The formulas on the kml! sheet by default use the unformatted values of the cells on the Placemarks sheet. So even though you might format the cell to round to the nearest integer, the actual value behind the scenes still has two decimal places. If you'd like to format the numbers in the resulting KML just like in the Placemarks sheet, you could modify the appropriate formula in the kml! sheet to use Round:
> I think this is a really cool idea. I'm pretty new to this, and I > don't have a lot of database experience, so this is a way for me to > get started. I'd like someday to really unleash the power of this > tool using database queries, but that is another topic for another > day. Right now, one problem that I am having is formatting numerical > data...I've imported some numbers into the spreadsheet, which have two > decimal places. I can modify the format on the Placemarks tab, using > the format command...but of course, in my !kml tab, these formats are > not recognized, and I still have the two decimal places there. Is > there a way in the macros to specify formatting? Or do I need to > round and format my numbers before I bring them into google > spreadsheets?
> > Description: > > Do you need to create and maintain dozens of placemarks? Want a clean, > > attractive balloon design without editing any HTML?
> > This tutorial shows you how to enter data in a spreadsheet and then > > generate a set of placemarks for that data in Google Earth. When you > > want to update your placemarks, you simply update the spreadsheet and > > generate a new KML file. This tutorial provides a sample spreadsheet > > and instructions for entering your own placemark data.
> > Confused? Need help? Post your questions regarding this tutorial by > > replying to this thread...
Now I'm VERY new to using this method for generating KML files but I think it's going to work better for us in the long run. Only problem: How to I add more than 100 entries? I have searched all through the file but I can't figure this out.
> It's great to hear you've found this tutorial helpful, and thanks for > the feedback.
> The formulas on the kml! sheet by default use the unformatted values > of the cells on the Placemarks sheet. So even though you might format > the cell to round to the nearest integer, the actual value behind the > scenes still has two decimal places. If you'd like to format the > numbers in the resulting KML just like in the Placemarks sheet, you > could modify the appropriate formula in the kml! sheet to use Round:
> As you've suggested, it would also work to import data that's already > been formatted the way you want it.
> Hope that helps, > Steve
> On Aug 17, 1:10 am, "[email address]" wrote:
> > I think this is a really cool idea. I'm pretty new to this, and I > > don't have a lot of database experience, so this is a way for me to > > get started. I'd like someday to really unleash the power of this > > tool using database queries, but that is another topic for another > > day. Right now, one problem that I am having is formatting numerical > > data...I've imported some numbers into the spreadsheet, which have two > > decimal places. I can modify the format on the Placemarks tab, using > > the format command...but of course, in my !kml tab, these formats are > > not recognized, and I still have the two decimal places there. Is > > there a way in the macros to specify formatting? Or do I need to > > round and format my numbers before I bring them into google > > spreadsheets?
> > > Description: > > > Do you need to create and maintain dozens of placemarks? Want a clean, > > > attractive balloon design without editing any HTML?
> > > This tutorial shows you how to enter data in a spreadsheet and then > > > generate a set of placemarks for that data in Google Earth. When you > > > want to update your placemarks, you simply update the spreadsheet and > > > generate a new KML file. This tutorial provides a sample spreadsheet > > > and instructions for entering your own placemark data.
> > > Confused? Need help? Post your questions regarding this tutorial by > > > replying to this thread...- Hide quoted text -
this page also helps how to found the Lat/Lon, Google Earth icons and something about the MIME Types and how to integrate the KML file into Google Maps.
Since Denmark is a small country and language, there has not been much content in our own language for absolute beginners regarding creating content for Google Earth, I found that my translation of your Google spreadsheet would be a step in that directions: I hope some K12 teachers might can use it in their class for minor projects.
This is really great but I have some data that we do not want to post on the web and thus would like to edit this spreadsheet locally and point google earth to my local location. I tried to export this tutorial worksheet as .xls but excel cannot open it on either mac or pc. Does someone have an excel version of this spreadsheet that we can use locally?
By way of followup, I was able to export the file .ods format and then use openoffice to save it as .xls. However, now I am not clear how to export this worksheet from excel so that it can be properly formatted as a .kml file.
> This is really great but I have some data that we do not want to post > on the web and thus would like to edit this spreadsheet locally and > point google earth to my local location. I tried to export this > tutorial worksheet as .xls but excel cannot open it on either mac or > pc. Does someone have an excel version of this spreadsheet that we can > use locally?
I have the same problem. I don't want to publish my data. It seems
that the only way I can get the kml is to publish it, quickly grab the
kml file, and then unpublish it. Though I appreciate the tutorial,
this method doesn't jive with my security model : (
> By way of followup, I was able to export the file .ods format and then
> use openoffice to save it as .xls. However, now I am not clear how to
> export this worksheet from excel so that it can be properly formatted
> as a .kml file.
> Can someone provide this information??
> Many thanks,
> Brian
> On Oct 24, 8:53 am, bkurzius wrote:
> > This is really great but I have some data that we do not want to post
> > on the web and thus would like to edit this spreadsheet locally and
> > point google earth to my local location. I tried to export this
> > tutorial worksheet as .xls but excel cannot open it on either mac or
> > pc. Does someone have an excel version of this spreadsheet that we can
> > use locally?
It seems to be possible to do all of this offline.
A spreadsheet (Google Docs or otherwise) is being used here to corral
data, so to speak, and set variables in an easy to understand format
(rather than a huge text file or by using some scripting language). I
didn't play with any of the formulas, but they seem pretty
straightforward - some hold kml element values and others data and
everything is tied together in the "kml!" with concatenate and
safeguards against empty data (noblank formula).
If you look at the formulas used you could recreate the same
spreadsheet offline.
Then - the only cell being published is cell "A1" - so copy that cell
to a text editor (I used Typepad) - and save as a kml instead of txt -
open in Google Earth and voila - you have the same file kml only not
as a network link.
Easy!
One thing to be aware of is online storage of photos - if you do not
want to access the internet at all (except of course to run Google
Earth - and even then it isn't 100% necessary with cached data, but I
digress) - you can use the kml you created and make a kmz with photos
embedded.
I hope that this isn't too confusing - it's really pretty
straightforward - just my writing that is confusing!
> I have the same problem. I don't want to publish my data. It seems
> that the only way I can get the kml is to publish it, quickly grab the
> kml file, and then unpublish it. Though I appreciate the tutorial,
> this method doesn't jive with my security model : (
> On Oct 24, 12:03 am, bkurzius wrote:
> > By way of followup, I was able to export the file .ods format and then
> > use openoffice to save it as .xls. However, now I am not clear how to
> > export this worksheet from excel so that it can be properly formatted
> > as a .kml file.
> > Can someone provide this information??
> > Many thanks,
> > Brian
> > On Oct 24, 8:53 am, bkurzius wrote:
> > > This is really great but I have some data that we do not want to post
> > > on the web and thus would like to edit this spreadsheet locally and
> > > point google earth to my local location. I tried to export this
> > > tutorial worksheet as .xls but excel cannot open it on either mac or
> > > pc. Does someone have an excel version of this spreadsheet that we can
> > > use locally?
Go to the kml sheet,
export as text,
grab the bit that starts with <kml>.... and ends with ...</kml> in
your favourite text editor,
save as .kml
should open in GE
> I have the same problem. I don't want to publish my data. It seems
> that the only way I can get the kml is to publish it, quickly grab the
> kml file, and then unpublish it. Though I appreciate the tutorial,
> this method doesn't jive with my security model : (
> On Oct 24, 12:03 am, bkurzius wrote:> By way of followup, I was able to export the file .ods format and then
> > use openoffice to save it as .xls. However, now I am not clear how to
> > export this worksheet from excel so that it can be properly formatted
> > as a .kml file.
> > Can someone provide this information??
> > Many thanks,
> > Brian
> > On Oct 24, 8:53 am, bkurzius wrote:
> > > This is really great but I have some data that we do not want to post
> > > on the web and thus would like to edit this spreadsheet locally and
> > > point google earth to my local location. I tried to export this
> > > tutorial worksheet as .xls but excel cannot open it on either mac or
> > > pc. Does someone have an excel version of this spreadsheet that we can
> > > use locally?
Thanks Soren, Im from Spain but I understood most of it. Very
helpfull. I´m trying to replace image with SWF flash file with no
success. Also I would like to use different image markers for diferent
locations. Do you know how to do that?
> this page also helps how to found the Lat/Lon, Google Earth icons and
> something about the MIME Types and how to integrate the KML file into
> Google Maps.
> Since Denmark is a small country and language, there has not been much
> content in our own language for absolute beginners regarding creating
> content for Google Earth, I found that my translation of your Google
> spreadsheet would be a step in that directions: I hope some K12
> teachers might can use it in their class for minor projects.
I decided to go back and recreate this work -- albeit in a much more
simplfied version. The version as an excel file just allows you to
enter a placemark name, description and coordinates -- but of course
you can modify this to add other options. After you are done editing,
it uses a macro to save the .kml file (after appending the header and
footer data) to your C drive (if you want to change anything --like
the filename or the default save location-- you can always edit the
macro).
> By way of followup, I was able to export the file .ods format and then
> use openoffice to save it as .xls. However, now I am not clear how to
> export this worksheet from excel so that it can be properly formatted
> as a .kml file.
> Can someone provide this information??
> Many thanks,
> Brian
> On Oct 24, 8:53 am, bkurzius wrote:
> > This is really great but I have some data that we do not want to post
> > on the web and thus would like to edit this spreadsheet locally and
> > point google earth to my local location. I tried to export this
> > tutorial worksheet as .xls but excel cannot open it on either mac or
> > pc. Does someone have an excel version of this spreadsheet that we can
> > use locally?
I'm making my own template in Excel too - don't want to work online -
and was wondering why the Google Docs template uses the INDIRECT
function to read info from other cells instead of just a regular cell
reference.
I did realize one function that is different between the two
spreadsheet programs - CONCATENATE. You can't have cell ranges
concatenate in Excel where you can in Google Docs.
The reason we use INDIRECT() so much is to provide some robustness
against users deleting rows.
If you use a direct cell reference to another sheet, say info about a
placemark, and the user decides to "delete" the placemark by deleting
the entire row (instead of highlighting those cells and deleting),
then the real cell references on the kml sheet will break (you'll get
lots of #REF! errors).
Using INDIRECT() gets around that. You can specify a text string that,
when combined with ROW() - any offset from the kml sheet to the
placemarkdata sheet, is resistant to deletions.
If you checked out our new spreadsheet tool (Spreadsheet Mapper 2.0:
http://earth.google.com/outreach/tutorial_mapper.html) you'll notice
that we use INDIRECT() extensively in there too. You'll also notice in
the error checking column on PlacemarkData, we've taken great lengths
to make the formulas sort-proof as well. Instead of using true cell
references (which is sorted will screw up, error checking the original
row, not the new one), we use ROW() so the formula is exactly the same
in every row, and can be sorted without causing problems.
Make sense? Let me know if you have any more questions.
> I'm making my own template in Excel too - don't want to work online -
> and was wondering why the Google Docs template uses the INDIRECT
> function to read info from other cells instead of just a regular cell
> reference.
> I did realize one function that is different between the two
> spreadsheet programs - CONCATENATE. You can't have cell ranges
> concatenate in Excel where you can in Google Docs.
> I'm making my own template in Excel too - don't want to work online -
> and was wondering why the Google Docs template uses the INDIRECT
> function to read info from other cells instead of just a regular cell
> reference.
> I did realize one function that is different between the two
> spreadsheet programs - CONCATENATE. You can't have cell ranges
> concatenate in Excel where you can in Google Docs.
|
