Google Maps data - retrieve data when pagination is necessary

[Anonymous]

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 location data.

our code works when we receive less than 20 items, for more item pagination is necessary but we are not able to go on, having no experience on that.

help is well appreciated

Roberto

let
    Source = (location as text, radius as text, category as text, optional pagetoken as text) =>
let    
    VAR1 = (location, radius, category, pagetoken,mapskey)  
 =>
let
    result = if pagetoken= null or "" then 
        Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/nearbysearch/json?location="	&location&"&radius="&radius&"&keyword="&category&"&key=mapskey"))
else
Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/nearbysearch/json?key=mapskey&pagetoken=pagetoken"))
,
    pagetoken= try result[pagetoken] otherwise null,    
    results = result[results],
    record = [results=results, pagetoken=pagetoken]
in record,
resultSet = List.Generate(
                    () => VAR1 (location, radius, category),
                    each _[pagetoken] <> null,
                    each VAR1(_[pagetoken]),
                    each [pagetoken= [pagetoken], results = [results]]
                    )
in VAR1 (location, radius, category, pagetoken)
in 
   Source

[Anonymous]

Hi @Anonymous ,

You can refer to following links to know how to handle pagination rest api:

Helper Functions

How to get paginated data from API in Power BI

How To Do Pagination In Power Query

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, we have followed your suggestions and looked at some examples. Our code still doesn't work.

The code we wrote is 

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

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

,

        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(_[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

We are able to collect the 20 records that are in the first page, but we are not able to retrieve the next pages items (20 per page).

Basically we problem is in the pages after the first, that's to say in the generation of resultset. The error message is

"Expression.Error: The import location matches no exports. Did you miss a module reference?" - "Expression.Error: The name 'location' wasn't recognized. Make sure it's spelled correctly."

BR

Roberto

[Anonymous]

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]

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