Excel web lookup of data value (Google elevation from Lat/Long parameter input)
$30-100 USD
In Bearbeitung
Veröffentlicht vor etwa 13 Jahren
$30-100 USD
Bezahlt bei Lieferung
Excel spreadsheet has Lat / Long columns. Need altitude / elevation value looked up for each position. Google elevation API provides this easily:
<[login to view URL]>
Example:
<[login to view URL],150.4473|-34.4962,150.4473&sensor=false>
I need a script, macro or (ideally) an Excel .iqy file that populates the elevation values for each position in the worksheet. I need to be able to run this against several different Excel files.
<[login to view URL]> shows what I need, as I want to use the Lat / Long cell reference values in the spreadsheet as the URL parameters. However the Excel Parameters buttons seem greyed out on my spreadsheet, so I can't edit the parameters in this window. See second Worksheet in attached file, showing example data correctly obtained from Google. But this is using a static position URL, instead of drawing the position parameters from the spreadsheet itself.
Google URL allows multiple positions with pipe separation between pairs, so example Google URL above shows two positions. It may be good to lookup five positions at a time to limit number of requests or speed up responses. URL limited to 255 characters though, (we need about 20 chars per position)
Google reply has request status value (E.g. 'Ok') and elevation value. We need both values in spreadsheet.
## Deliverables
DELIVERABLES: Project requires...
1. The sample spreadsheet returned with the values for Google status and Google altitude populated from the Lat / Long values in that spreadsheet for each row.
2. An Excel .iqy query file, or macro, or script that will enable altitude lookups to be easily obtained in future for any other spreadsheet - e.g. by just changing column references in the query to specify any new Lat / Long source data column etc.
3. Query lookup designed to retrieve *multiple values* for one request URL E.g. Obtain elevation value for five positions, in one URL string - instead of five single position lookup URL's.