Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Google Maps data - retrieve data when pagination is necessary

Hi, we are quite newbie and need help. We are trying to create a table with data coming from a google maps query. we enter in a function lat, long, radius and keyword to receive a json with locatio...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI Anonymous , 

I think this issue should more related to your formula. pippo function require 4 parameters but you only input one in list.generate function.
I try to modify your formula to let parameter works in your query formula:

let
    pippo = (location as text, radius as text, category as text, pageToken) =>

    let
        url=if pageToken = null then
        "https://maps.googleapis.com/maps/api/place/nearbysearch/json?key=OUR KEY&location="&location&"&keyword=bar&radius=2500"
        else 
        "https://maps.googleapis.com/maps/api/place/nearbysearch/json?key=OUR KEY&pageToken="&pageToken,
        source =Json.Document(Web.Contents(url)),

        nextPageToken = try source[next_page_token] otherwise "pippo",
        results = source[results],
        record = [results = results , nextPageToken = nextPageToken ]
    in
        record,


resultSet = List.Generate(
                    () => pippo (location, radius, category, pageToken),
                   
                    each _[nextPageToken] <> null,
                    each pippo(location,radius,category,_[nextPageToken]),
                    each [nextPageToken= [nextPageToken], results = [results]]
                    ),

   lastPageToken = List.Last(Table.FromRecords(resultSet)[nextPageToken]),
   lastResultSet = pippo(lastPageToken)[results], 
   fullResultSet = Table.Combine({Table.FromRecords(List.Combine(Table.FromRecords(resultSet)[results])), Table.FromRecords(lastResultSet)})
     
     
in
    fullResultSet

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

thanks for your suggestions, we have followed them and now we have the following query:

 

let    Func = (Location, Radius, Keyword, Token) =>

    let        Source =

            if Token = null  then                Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/nearbysearch/json?key=OUR_KEY&location=" & Location & "&keyword=" & Keyword & "&radius=" & Number.ToText(Radius)))

            else                Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/nearbysearch/json?key=OUR_KEY&pagetoken=" & Token)),

        Results = try Source[results] otherwise {},

        NextPageToken = try Source[next_page_token] otherwise null,

        Items = [Results = Results, NextPageToken = NextPageToken]

    in        Items,

    ResultSet = List.Generate(

                    () => Func("45.538474,10.220309", 1200, "bar", null),

                    each [Results] <> {} and [NextPageToken] <> null,

                    each Function.InvokeAfter(() => Func(null, null, null, _[NextPageToken]), #duration(0,0,0,1)),

                    each [Results = [Results], NextPageToken = [NextPageToken]]

                    ),

    LastPageToken = List.Last(Table.FromRecords(ResultSet)[NextPageToken]),

    LastResultSet = Function.InvokeAfter(() => Func(null, null, null, LastPageToken)[Results], #duration(0,0,0,1)),

    FullResultSet = Table.Combine({Table.FromRecords(List.Combine(Table.FromRecords(ResultSet)[Results])), Table.FromRecords(LastResultSet)})

in    FullResultSet

 

We have some problems:

  • Our query returns 40 records (Google API returns 20 records per page up to a total of 60 records), we are not able to collect the records in the last page;
  • If we change the radius value, for example 1500 meters (or any other number) instead of 1200 meters, our query returns 20 records, not 40 anymore. This is strange and we can’t understand why.

Can you help us? Thanks so much in advance!

Regards,

Elena Anonymous