Need Help with REST APi Pagination (Google Places API)

[TonyGu]

Was wondering if anyone could help me figure out a easy way to ETL the Google Places API.

Background info, 

 - Each request is about 20 results in a page 

 - For the first, you would use

https://maps.googleapis.com/maps/api/place/textsearch/json?query=test&key="Your API Key"

. This would return page 1 data as well as the next page token (if applicable)

 - To get the second page, we would have to use the page token from pervious step as shown here

https://maps.googleapis.com/maps/api/place/textsearch/json?query=test&key="Your API Key"&pageToken="Next Page Token"

 this would return page 2 data as well as the next page token (if applicable)

My goal is to take the next page taken from page 2 and replace that to generate page 3. Then next page token from page 3 and replace that to generate page 4 and so on. The API does not tell me how many datapoints there are, however, on the last page, the next page token will no longer be an available row. As such, I want to stop the loop on that error. 

The API Key stays consistent, so no need to call a function to generate that. This is where I have gotten so far 

let
PageKey = () =>
    let 
    Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/textsearch/json?query= test&key=XXXXXX")),
    next_page_token = Source[next_page_token]
    in 
next_page_token, 

NextPage = () =>
    let
    Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/textsearch/json?query= test&key=XXXXXX"& #"PageKey"() &""))
    in
    Source,
Source = NextPage() 
in 
Source

I tried creating this list generate function which failes 

let
    Source = List.Generate(
   () => [x = PageToken(), y = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/textsearch/json?query= test&key=XXXXXX&pageToken="& Text.From([x]) & ""))]
,
        each  [x]="",
        each [y]
    )
in
    Source

Where

let
    Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/textsearch/json?query=Test&key=XXXXXX")),
    next_page_token = Source[next_page_token]
in
    next_page_token

[lbendlin]

List.Generate is the right approach.  Please also read about RelativePath and Query attributes.

Please understand that we cannot assist more without access to that API (and you don't want to give us your API key).

[TonyGu]

I don't want to post it here as people who may be interested in helping may use it for testing purposes, however, I don't want others to abuse the key. If I could PM the key, that would be more helpful. 

I have built API ETLs with relative path in the past for endpoints where the docs required using headers and relative path rather than having the whole thing in single hyperlink. I could also use relative path here, but not sure whether it would make a difference and the call doesn't require me to use relative path. By Query Attributes you mean Parameters?? 

I do feel like I'm getting a  bit closer to where I want to be. I've posted the updated PQ Code with comments as to what I am doing. Would appreciate your comment or whether you would be willing to help if I were to DM you my request key?

//This Code is just to generate the list of Access tokens. Once I get this working, I will include the datacalls in the List Generate function. Get nextPage token is my function to genrate the page token. it'll execute the datacall, but only extract the pagetoken value as shown below.



let
    GetNextPageToken = (nextPageToken) =>
        let
            apiUrl = "https://maps.googleapis.com/maps/api/place/textsearch/json?query=test&key=XXXXXX" & 
                     (if nextPageToken <> null then "&pageToken=" & nextPageToken else ""),
            source = Json.Document(Web.Contents(apiUrl)),
            nextToken = if Record.HasFields(source, "next_page_token") then source[next_page_token] else null
        in
            nextToken,



//I will start with initialToken where I am keeping the pageToken blank. i.e. "https://maps.googleapis.com/maps/api/place/textsearch/json?query=test&key=XXXXXX&pageToken=" this would generate the first page and I would extract the nextpagetoken from this page, and use it to generate token for second page. Then use nextpagetoken from second page to generate token for third page and so on in the alltokens step.



    initialToken = GetNextPageToken(""),
     allTokens = List.Generate(
        () => initialToken,
        each _ <> null,
        each GetNextPageToken(_)
    )
in
    allTokens

Issue with above code is I am perpetually generating the first page. It's not looping through the getNextPageToken function for some reason

[lbendlin]

Please study the documentation. RelativePath and Query are not required for Power BI Desktop, but they become mandatory when you want to refresh in the service.

Web.Contents - PowerQuery M | Microsoft Learn