Showing addresses in Glide map component

Project Showcase
1

Hi!

I don’t know if you had a problem with putting addresses in the map component, but for me, the location pins were showing everywhere but the place I was aiming for.

After figuring out how to make it work properly I wrote a very short story about it in Medium:

https://medium.com/@danliebeschutz/how-to-make-addresses-show-correctly-in-glide-app-map-component-d0dcd8dcfba4

This is me trying to put 4 addresses in Tel Aviv:

Before:
image

After:
image

Enjoy!

10 Likes
2

Thanks Dan!

1 Like
3

Hi Dan, that’s a clever way of generating the component!

Love the integrated Google Script as well, I also used a script to generate the distance and travel time here.

1 Like
4

This is an awesome tip. I just added this script to one of my municipal apps where citizens can report illegal parking issues during a snowstorm. It seems to handle blocks, intersections, and specific addresses without an issue (so far).

Thanks Dan!

2 Likes
5

This is pretty neat, dude! Congrats!

2 Likes
6

Excellent your article, Dan. The solution I have used is to write the address on the map of the mapbox website (https://docs.mapbox.com/search-playground/). It gives you the correct way to write the address. Although some addresses cannot be found, in my case it has only been 10% of my searches.

I will test your formula with the addresses that I could not find. Thanks Dan.

3 Likes
7

Wow! Great to hear it helped Tim! Thanks for the feedback :+1:

8

Thank you Mauricio! Please let me know here if it did help!

9

Thank you!

10

Hey guys/gals, Im brand new here, and I have a google sheet with all of my club members addresses. They are in separate columns, street, city, state and zip.

They are as the original poster mentioned showing up all over the globe too.

So my question is this. I paid for the premium app, how does a non-coder like myself get this to work properly?

11

Have you had the addresses as coordinates yet?

12

Coordinates are best and most reliable, but if you use addresses, you need to make sure the entire address is in one column. If your address consists of multiple columns and you can only point the map to one of those columns, then it’s expected to have erratic results. Either use a formula in the sheet or use a template column in the Glide data editor to join all the address columns into one complete address column.

1 Like
13

@StinsonOwner: The first task is to use a formula to build the address in a new cell with comma separated values, i.e. street, city, state, zip.

Once you have that, you will be able to convert the new address cell into lon/lat coordinates using the add-on Geocode for Awesome Table.

For more accurate map pins, coordinates are the best choice and the above method will get your there in no time.

14

@Dan_Liebeschutz thank you for this - it works perfectly!

I am having an issue with the script though and was wondering whether you have an idea how to solve it:

I use array formula to apply the formula to new rows. Every time a new row is added, the formula re-calculates the entire column’s coordinates which causes:

a. some down time before Glide refreshes and shows the pins on the map. This means that maps are not shown for a couple of minutes when a new row is added.
b. “Exception: Service invoked too many times for one day: geocode. (line 11).” - since my app is sending hundreds of requests each day, I reached a limit I wasn’t aware of.

I think it would be great if newly added rows would use geolocation for themselves only without recalculating the entire column. Any idea how to do that?

Thank in advance!

1 Like
15

This is best with a script, but also make sure your script is set to only get coordinates if the coordinates haven’t already been retrieved.

16

Thanks Jeff, I currently use a script that creates a custom formula, then apply it to the address column using array formula. The array formula recalculates the entire column every time a new row is added, which is the issue.

I don’t know how to write code (that’s why I’m here :wink:) so I asked a friend to help but he couldn’t figure it out :confused:

17

Maybe I will try this next week when I have time. The idea is the same as yours, but add in that after the calculation we copy the final value and paste it back into the cell so it won’t recalculate again every time a new formula is added.

1 Like
18

@ThinhDinh that would be appreciated, thank you!

1 Like
19

I’m working on app where I have already successfully managed to achieve the following:

  1. Get user to enter either post code or use current location.
  2. Get driver to do as User in 1 above.
  3. Show pick up point and drop off point. Here i show both coordinates and addresses for the same.
  4. Show distance to driver
  5. Show distance from pick up to drop off
    6 show price by mile
  6. Not use time as roads have different speed zones and traffic was another issue but have included the formula.

My template is now out of the production stage.

Now, addressing your problem.

  1. I experienced exactly what you are experiencing.
  2. I’m sure you are using getAdress and/or either geoode yandex or google. I prefer yandex as I miss can make more calls on it than I can on google.
  3. Get rid of the array formula if you are using a script as you would have to. Instead use a copydown script. That gets rid of the recalculation of the coordinates each time a new row is added.
  4. If you need to split the coordinates to get the address, use an arrayformula with iferror. For this you will not need a script. I use both text join and split to make sure both ends are tied.

Hope it woks for you as it has for me. When I showcase my app, you can it all in action.

Have a good day.

1 Like
20

That’s exactly the solution I am looking for - thanks @Wiz.Wazeer!

The part I’m having trouble with is #3 in your solution, I’m not sure how to create a copydown script. For the matter, I am using the script below to create a custom formula, which is implemented in a sheet called “Homes”. Column I contains the full address entered by the user, column J should return the coordinates using the custom formula.

function myFunction() {
  
}

function GEOCODE_GOOGLE(address) {
  if (address!=""){
    if (address.map) {
      return address.map(GEOCODE_GOOGLE)
    }
    else {
        var r = Maps.newGeocoder().geocode(address)
        for (var i = 0; i < r.results.length; i++) {
            var res = r.results[i]
            return res.geometry.location.lat + ", " + res.geometry.location.lng
        }
    }
  }
}
1 Like