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 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
Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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'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