City geocoding + indicates how are they periodically added to google sheet?
So I have a google sheet that uses this importJSON script to add a list of cities and states to a google sheet. Rows are added periodically as cities are added by users on our site (which are reflected in our JSON file).
I am currently using this hacky code to add lon, lat for each row (C5 and D5 refer to the city and state columns respectively.
={
CONCATENATE(ImportJSON(CONCATENATE(
"http://maps.googleapis.com/maps/api/geocode/json?address=",CONCATENATE(C5,",",D5),"&sensor=false"),"/results/geometry/location/lng","noHeaders"
)),
CONCATENATE(ImportJSON(CONCATENATE(
"http://maps.googleapis.com/maps/api/geocode/json?address=",CONCATENATE(C5,",",D5),"&sensor=false"),"/results/geometry/location/lat","noHeaders"
))
}
Every time the sheet is reloaded, each geocode is recalculated unnecessarily - and the more rows need to be geocoded at once, the more errors occur - what can I do to make the row only geocoded if the city / state cells are changed for that row?
source to share
I do not see it. When I created the sample, my table is only updated when C5 (or D5) changes. I am actually just using one field for the address, though ...
={
CONCATENATE(ImportJSON(CONCATENATE(
"http://maps.googleapis.com/maps/api/geocode/json?address=",C5,"&sensor=false"),"/results/geometry/location/lng","noHeaders"
)),
CONCATENATE(ImportJSON(CONCATENATE(
"http://maps.googleapis.com/maps/api/geocode/json?address=",C5,"&sensor=false"),"/results/geometry/location/lat","noHeaders"
))
}
I used the ImportJSON function here ... https://gist.github.com/paulgambill/cacd19da95a1421d3164/
source to share