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

    • 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