Yes, but it will require a little work. Unfortunately the form feature
isn't customizable enough at this point to do what you are thinking
(easily). You will only be able to add placemarks via the form, not
edit existing ones. And the form will have to be specific to a single
template, since you can only have one form per spreadsheet. Also,
because you can't choose where the data from the form gets injected
into the spreadsheet, you will have to pull in values from the sheet
form data is collected on into the PlacemarkData sheet.
Once you get a copy, you will need to go to the Share tab, and choose
the "to fill out a form" option, and then "start editing your form" to
reactivate the form I create *you can close the pop-up window that
appears). Though the spreadsheet copies the form from my original, it
won't start sharing it with the world until you ask it to.
How to do it, step-by-step, with your own spreadsheet:
When you first create "questions" for the forms, the answers are
automatically get added to the first sheet (in order left to right) in
your spreadsheet. That means you have to:
1. "Add [a new] Sheet", give it a good name (e.g. "Form") move it to
the far left so it is first in line.
2. Then create the form (from the "Share" tab) and write all the
questions to collected the needed information (placemark name,
coordinates, content for "unique variables"
3. Then you would have to pull this information into the
'PlacemarkData' sheet using spreadsheet formulas. You could use direct
cell references (e.g., =Form!B2), but this makes you very suceptible
to broken cell references (#REF! errors). That's because if you delete
a row in the "Form" sheet, the formula on the PlacemarkData sheet
pointing to a cell in that row doesn't know where to look any more.
Instead, use the INDIRECT() function. Example: if you first question
is "Placemark Name?", the first answer will appear on the "Form"
sheet, in cell B2. This needs to get pulled into cell D11. So use
this: '=INDIRECT("Form!B" & ROW()- 9)'. This will know that you are in
row 11, and need to subtract 9 to get to "2". the INDIRECT will then
get called on the text ("Form!B2"), and will be resistant to deletions
on the Form sheet that will cause #REF! errors.
4. Create those INDIRECT formulas all the way across row 11 on the
PlacemarkData sheet, pulling the data for coordinates, template #, and
the "unique" variables for Template 4 (Title, Photo URL, etc.).
5. Copy those formulas down to the bottom of the sheet so that
anything added to the Form sheet will get pulled into the
PlacemarkData sheet.
Remember that *you* will still have to re-publish the sheet to see
changes. If other people that have access to the form fill it out,
they won't immediately see changes. also remember that you can't edit
existing placemarks, and because you can only have one form per
spreadsheet, you are pretty much locked into creating a form for a
single template.
When the spreadsheet Form tool gets a little more customizable, we'll
look into integrating it into the Spreadsheet Mapper (3.0?). For now,
being limited to only one template, not being able to edit existing
placemarks via the form and not being able to dump the form data
directly into the PlacemarkData sheet are reasons enough to hold off.
>><<
2) I'm not sure if I understand your question here. You are asking if
you have data in an Excel file, or another Google Spreadsheet, if you
can inject that data into the Spreadsheet Mapper? If so, here are some
options for you:
A. Easy - Copy/paste from Excel, other G Spreadsheets:
You can just copy the cells directly. I sometimes use Notepad, or a
basic text editor as an intermediary between Excel and other programs.
Paste from Excel into Notepad usually gives you a basic tab-delimited
format, which pastes nicely into Google Spreadsheets.
The limitation of the method is that G Spreadsheet can only accept so
much data pasted in at a time. This means that if you have a lot of
content, you sometimes have to paste in the data section by section.
If that is too much of a pain, try the following idea:
B. Harder - Import into a new spreadsheet, and then use the
importData() function (good if you have a LOT of data)
Click File > Import in google spreadsheets, and open up that Excel
file. Then Publish the document. Assuming that the columns from Excel
are all in the right/same order as what the Spreadsheet Mapper needs,
you can import a range of cells from this new spreadsheet into a
Spreadsheet Mapper. Example, say you imported data into the
spreadsheet, and it has 100 placemarks with all the columns in the
right order (folder name, placemark name, latitude, longitutde, etc.).
Use the "More publishing options" under the 'Publish' tab (appears
after you choose 'Publish Now'). Choose the file format "TXT", choose
the sheet the data is on, and then enter the range of cells you want
to import, if you don't want everything (e.g. A1:H50). Remember that
the Spreadsheet Mapper can only do 400 placemarks as of now. Click the
generate URL button. You should get a URL that looks something like
this: http://spreadsheets.google.com/pub?key=p-zIWuNNsnGK7AXVng_utTw&output....
Now go to cell D11 on your PlacemarkData sheet (in a Spreadsheet
Mapper spreadsheet), and enter that URL into a formula using
importData, e.g. =importData("//spreadsheets.google.com/pub?key=p-
zIWuNNsnGK7AXVng_utTw&output=txt&gid=0&range=A1:H50"). This will make
Spreadsheet pull in the data from the published data spreadsheet into
your Spreadsheet Mapper.
One last thing. The ImportData function will always pull in data from
the other spreadsheet. You want the actual values in those cells on
PlacemarkData, not formulas that pull data from the other spreadsheet.
So highlight the entire range, copy it, then click the paste button
(to the left of "Format" button under the 'Edit' tab), and choose
"Paste Values Only".
That should do it for you. Obviously it's easier to just copy and
paste the data directly from another spreadsheet of Excel... but if
you have a LOT of data (400 placemarks with lots of text in each
balloon) then method A could be very tedious.
You might note that on the 'start here' sheet, at the bottom, steps
very similar to method B are described. This is a way to upgrade to
newer versions of the spreadsheet by porting over your data (if you
don't want to use method A). We auto-generate the =importData("url")
formula for you, and all you have to do is paste it into the new
spreadsheet, and then to the "paste values only" thing. Keep in mind
that this does pull in templates from other spreadsheets, just the
placemark data. Working on that.
1) WOOOONDERFUUULLL!!!
This method works very well, thank you very much!
2)
> You are asking if you have data in an Excel file, or another Google Spreadsheet, if you
> can inject that data into the Spreadsheet Mapper?
Yes,
> If so, here are some
> options for you:
> A. Easy - Copy/paste from Excel, other G Spreadsheets:
> You can just copy the cells directly. I sometimes use Notepad, or a
> basic text editor as an intermediary between Excel and other programs.
> Paste from Excel into Notepad usually gives you a basic tab-delimited
> format, which pastes nicely into Google Spreadsheets.
OK, this works
> The limitation of the method is that G Spreadsheet can only accept so
> much data pasted in at a time.
how much exactly?
> This means that if you have a lot of
> content, you sometimes have to paste in the data section by section.
> If that is too much of a pain, try the following idea:
> B. Harder - Import into a new spreadsheet, and then use the
> importData() function (good if you have a LOT of data)
I've tryed a lot of time, but this doesn't work for me:
The problem is that, when I put the import data formula in cell D11,
it imports all the data in column D,
and when I copy data and paste values only,
nothing happens ...
> That should do it for you. Obviously it's easier to just copy and
> paste the data directly from another spreadsheet of Excel...
infact ;-)
I'm going to do so ...
> Let me know if this helps at all!
My principal problem was 1), and your solution is great and works,
for problem 2), solution A is good enough,
Excellent tool! and great post that was my first question answered
perfectly :)
My second is:
Can this spreadsheet be extended to store more than 400 points? or was
there some kind of inbuilt limitation from google maps.
thanks,
NoobSchoolBus
> Yes, but it will require a little work. Unfortunately the form feature
> isn't customizable enough at this point to do what you are thinking
> (easily). You will only be able to add placemarks via the form, not
> edit existing ones. And the form will have to be specific to a single
> template, since you can only have one form per spreadsheet. Also,
> because you can't choose where the data from the form gets injected
> into the spreadsheet, you will have to pull in values from the sheet
> form data is collected on into the PlacemarkData sheet.
> Once you get a copy, you will need to go to the Share tab, and choose
> the "to fill out a form" option, and then "start editing your form" to
> reactivate the form I create *you can close the pop-up window that
> appears). Though the spreadsheet copies the form from my original, it
> won't start sharing it with the world until you ask it to.
> How to do it, step-by-step, with your own spreadsheet:
> When you first create "questions" for the forms, the answers are
> automatically get added to the first sheet (in order left to right) in
> your spreadsheet. That means you have to:
> 1. "Add [a new] Sheet", give it a good name (e.g. "Form") move it to
> the far left so it is first in line.
> 2. Then create the form (from the "Share" tab) and write all the
> questions to collected the needed information (placemark name,
> coordinates, content for "unique variables"
> 3. Then you would have to pull this information into the
> 'PlacemarkData' sheet using spreadsheet formulas. You could use direct
> cell references (e.g., =Form!B2), but this makes you very suceptible
> to broken cell references (#REF! errors). That's because if you delete
> a row in the "Form" sheet, the formula on the PlacemarkData sheet
> pointing to a cell in that row doesn't know where to look any more.
> Instead, use the INDIRECT() function. Example: if you first question
> is "Placemark Name?", the first answer will appear on the "Form"
> sheet, in cell B2. This needs to get pulled into cell D11. So use
> this: '=INDIRECT("Form!B" & ROW()- 9)'. This will know that you are in
> row 11, and need to subtract 9 to get to "2". the INDIRECT will then
> get called on the text ("Form!B2"), and will be resistant to deletions
> on the Form sheet that will cause #REF! errors.
> 4. Create those INDIRECT formulas all the way across row 11 on the
> PlacemarkData sheet, pulling the data for coordinates, template #, and
> the "unique" variables for Template 4 (Title, Photo URL, etc.).
> 5. Copy those formulas down to the bottom of the sheet so that
> anything added to the Form sheet will get pulled into the
> PlacemarkData sheet.
> Remember that *you* will still have to re-publish the sheet to see
> changes. If other people that have access to the form fill it out,
> they won't immediately see changes. also remember that you can't edit
> existing placemarks, and because you can only have one form per
> spreadsheet, you are pretty much locked into creating a form for a
> single template.
> When the spreadsheet Form tool gets a little more customizable, we'll
> look into integrating it into the Spreadsheet Mapper (3.0?). For now,
> being limited to only one template, not being able to edit existing
> placemarks via the form and not being able to dump the form data
> directly into the PlacemarkData sheet are reasons enough to hold off.
> >><<
> 2) I'm not sure if I understand your question here. You are asking if
> you have data in an Excel file, or another Google Spreadsheet, if you
> can inject that data into the Spreadsheet Mapper? If so, here are some
> options for you:
> A.Easy- Copy/paste from Excel, other GSpreadsheets:
> You can just copy the cells directly. I sometimes use Notepad, or a
> basic text editor as an intermediary between Excel and other programs.
> Paste from Excel into Notepad usually gives you a basic tab-delimited
> format, which pastes nicely into GoogleSpreadsheets.
> The limitation of the method is that G Spreadsheet can only accept so
> much data pasted in at a time. This means that if you have a lot of
> content, you sometimes have to paste in the data section by section.
> If that is too much of a pain, try the following idea:
> B. Harder - Import into a new spreadsheet, and then use the
> importData() function (good if you have a LOT of data)
> Click File > Import in googlespreadsheets, and open up that Excel
> file. Then Publish the document. Assuming that the columns from Excel
> are all in the right/same order as what the Spreadsheet Mapper needs,
> you can import a range of cells from this new spreadsheet into a
> Spreadsheet Mapper. Example, say you imported data into the
> spreadsheet, and it has 100 placemarks with all the columns in the
> right order (folder name, placemark name, latitude, longitutde, etc.).
> Use the "More publishing options" under the 'Publish' tab (appears
> after you choose 'Publish Now'). Choose the file format "TXT", choose
> the sheet the data is on, and then enter the range of cells you want
> to import, if you don't want everything (e.g. A1:H50). Remember that
> the Spreadsheet Mapper can only do 400 placemarks as of now. Click the
> generate URL button. You should get a URL that looks something like
> this:http://spreadsheets.google.com/pub?key=p-zIWuNNsnGK7AXVng_utTw&output....
> Now go to cell D11 on your PlacemarkData sheet (in a Spreadsheet
> Mapper spreadsheet), and enter that URL into a formula using
> importData, e.g. =importData("//spreadsheets.google.com/pub?key=p-
> zIWuNNsnGK7AXVng_utTw&output=txt&gid=0&range=A1:H50"). This will make
> Spreadsheet pull in the data from the published data spreadsheet into
> your Spreadsheet Mapper.
> One last thing. The ImportData function will always pull in data from
> the other spreadsheet. You want the actual values in those cells on
> PlacemarkData, not formulas that pull data from the other spreadsheet.
> So highlight the entire range, copy it, then click the paste button
> (to the left of "Format" button under the 'Edit' tab), and choose
> "Paste Values Only".
> That should do it for you. Obviously it's easier to just copy and
> paste the data directly from another spreadsheet of Excel... but if
> you have a LOT of data (400 placemarks with lots of text in each
> balloon) then method A could be very tedious.
> You might note that on the 'start here' sheet, at the bottom, steps
> very similar to method B are described. This is a way to upgrade to
> newer versions of the spreadsheet by porting over your data (if you
> don't want to use method A). We auto-generate the =importData("url")
> formula for you, and all you have to do is paste it into the new
> spreadsheet, and then to the "paste values only" thing. Keep in mind
> that this does pull in templates from otherspreadsheets, just the
> placemark data. Working on that.
> Let me know if this helps at all!
> Sean
> GoogleEarthOutreach Team
> On Feb 13, 9:30 am, Jonathan_the_seagull wrote:
> > Wonderfultutorial, thanks!
> > Is it possible :
> > 1) to connect the PlacemarkData sheet to a form (http://
> > googledocs.blogspot.com/2008/02/stop-sharing-spreadsheets-start.html)
> > 2) to import in the PlacemarkData sheet an excel file or copy/paste
> > another google spreadsheet ?
> > Thank you :-)
> > On 13 Feb, 09:51, sensei wrote:
> > > Thistutorialis now up on the GoogleEarthOutreach site, and is now
> > > called: "Spreadsheet Mapper 2.0"
What does it take to add an extra space for either a "static variable"
or a "unique variable?" into one of the existing templates? For
example, Template #5 has room for 3 links on its right side. What if I
want 4 links? Or 8 links, etc...? I have tried editing the spreadsheet
in various ways in order to achieve this and have had no success
whatsoever. I basically render my placemark balloon inoperable. What
is the secret to doing this? Can you tell me the steps necessary or
point me to a tutorial to achieve this?
I am having a problem with the Spreadsheet Mapper 2.0 tutorial. I have
followed the various steps without any problem until I reached the
section on View templates in Google Earth. In this section I could
copy the Network Link KML cell (which on my installation is cell C80,
not C78 as indicated in the tutorial). I then move to Google Earth (I
am running version
Google Earth 4.2.0205.5730), select My Places and right-click. The
menu which appears does not have a 'Paste' option, except
occasionally. When it did have a Paste option, I used it and got an
error message from Google Earth. The next time I got the Paste option,
I tried again, but nothing at all happened.
Are there any tests I could try to see what is going wrong, or is
there something in the sequence of actions I have taken which might
explain this? I am puzzled by the discrepancy in the cell numbers, for
example.
Can I use this to add my Picasa slideshows instead of single images?
I've tried copying the data in as I did before but it does not work.
Any suggestions?
This is a great solution and I'm glad to see the beginnings of
spreadsheet -> KML support. The big question remains when is GE going
to support linked tables directly (both directions)?
Correct me if I'm wrong, but while I can post a lot of locations in
Spreadsheet to be updated in the KML to GE, I can't edit the placemark
in GE and have it update the spreadsheet automatically. Rather, I have
to go in and edit locations numerically.
Those of us who have used GIS software such as ArcMap know that the
user moves constantly between graphical and table view of datasets and
it's a really powerful combination (for sorting, auto-renaming,
incrementing items, and group changes). In GE you have the list of
placemarks on the left (the Places panel) but no table view.
GoogleSpreadsheet seems like the natural place for KML support. It
seems like this might be in the works since it seems like an obvious
application of spreadsheets.
Thanks
Isn't it possible to just have the kml to refresh on time basis and
to set the publishing option of the spreadsheet mapper to
"Automatically re-publish when changes are made" ?
then niew entries in the form would appear automatically inGE..
> This is a great solution and I'm glad to see the beginnings of
> spreadsheet -> KML support. The big question remains when is GE going
> to support linked tables directly (both directions)?
> Correct me if I'm wrong, but while I can post a lot of locations in
> Spreadsheet to be updated in the KML to GE, I can't edit the placemark
> in GE and have it update the spreadsheet automatically. Rather, I have
> to go in and edit locations numerically.
> Those of us who have used GIS software such as ArcMap know that the
> user moves constantly between graphical and table view of datasets and
> it's a really powerful combination (for sorting, auto-renaming,
> incrementing items, and group changes). In GE you have the list of
> placemarks on the left (the Places panel) but no table view.
> GoogleSpreadsheet seems like the natural place for KML support. It
> seems like this might be in the works since it seems like an obvious
> application of spreadsheets.
> Thanks
> Isn't it possible to just have the kml to refresh on time basis and
> to set the publishing option of the spreadsheet mapper to
> "Automatically re-publish when changes are made" ?
> then niew entries in the form would appear automatically inGE..
> Pwak
> On Apr 14, 12:18 am, nico.t wrote:
> > This is a great solution and I'm glad to see the beginnings of
> > spreadsheet -> KML support. The big question remains when is GE going
> > to support linked tables directly (both directions)?
> > Correct me if I'm wrong, but while I can post a lot of locations in
> > Spreadsheet to be updated in the KML to GE, I can't edit the placemark
> > in GE and have it update the spreadsheet automatically. Rather, I have
> > to go in and edit locations numerically.
> > Those of us who have used GIS software such as ArcMap know that the
> > user moves constantly between graphical and table view of datasets and
> > it's a really powerful combination (for sorting, auto-renaming,
> > incrementing items, and group changes). In GE you have the list of
> > placemarks on the left (the Places panel) but no table view.
> > GoogleSpreadsheet seems like the natural place for KML support. It
> > seems like this might be in the works since it seems like an obvious
> > application of spreadsheets.
> > Thanks
You probably just have to wait a bit and try again. This spreadsheet
is very large and takes a long time to load - so often you'll time out
before you get it. I tried this morning and couldn't get it, then just
tried again and had no problem. Hana hou.
