Wednesday, September 19, 2012

yogi_spreadsheetSheet_USA-for User anstan In Regard To Geocode Values

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #763   Sep 19, 2012     www.energyefficientbuild.com.

user anstan said: (http://productforums.google.com/forum/?zx=y7jq6icx61c4#!category-topic/docs/spreadsheets/npyEp-ZJSQ8)
Error formatting latitude and longitude coordinates 
If I use on my form this formula:
=ImportData("http://maps.google.com/maps/geo?output=csv&q="&D:D) 
where D contains addresses, it works but shows wrong data, maybe in a wrong format.
For example, New York coordinates ( 40.7143528 and -74.0059731) in my spreadsheet become 146393.16.00 and -105053.22.00
This happen also with new spreadsheet. What is the problem and how can I solve it? I'm using this script to array the "=ImportData" formula 

function createFormTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('onFormSubmit').forSpreadsheet(ss).onFormSubmit().create();
}

function onFormSubmit(e) {
e.range.offset(0, 4, 1, 1).setFormula('=ImportData("http://maps.google.com/maps/geo?output=csv&q="&D:D)');
}

Thanks in advance
(Chrome, Win7)

--------------------------------------------------------------
I don't have an answer for you ... but let me present the observation from a little play I had working on this with native formulas ...
1) I get what appear to be correct latitude and longitude values in degrees (decimal representation)
    with my spreadsheet setting to locale USA
2) using my spreadsheet locale as ITALY, I get the same results as you got in your spreadsheet
    with your locale setting of ITALY ... the resulting data  that seems to be in   degrees.minutes.seconds but nowhere near the values I get in 1)

so here we go ...

This is a spreadsheet with Spreadsheet setting ... USA . . . . and the same spreadsheet, same data, same formulas, with the only difference being the Spreadsheet setting ... ITALY

No comments:

Post a Comment