Offset value invalid; output does not match API documentation

Bastiaan_Bijl · ‎Dec 04, 2019

Hello,

I am trying to build a query in Power Query/Power BI to load an Airtable table into the data model. I have found some working code which uses the offset parameter returned by the API to deal with the pagination.

However, the offset value returned does not match the API documentation and gives an error when I try to parse it.

According to the API docs, the offset should look like this:

"offset": "reco1XxXxXxXxXxXxXx"

However, what I get is this:

"offset":"itrZXxXxXxXxXxXxXxXx/rec5XxXXXxXxXxXxXx"

When I parse the value into the URL, using &offset=itrZXxXxXxXxXxXxXxXx/rec5XxXXXxXxXxXxXx, I get the following error:

{"error":{"type":"LIST_RECORDS_ITERATOR_NOT_AVAILABLE"}}

When I try passing either of the values (before and after the /), then the error is:

{"error":{"type":"INVALID_OFFSET_VALUE","message":"The value of offset itrZXxXxXxXxXxXxXxXx is invalid"}}

Am I missing something here?

Thanks!

cor · ‎Dec 05, 2019

I agree - something dropbox can learn from. In their API if you get an offset and delete one of the records you got, it changes the reference point so you end up with a mess.
a=1 b=2 c=3 d=4 e=5
if in the first call you get
a=1 b=2 c=3 with offset =4
you delete b
then the reference becomes a=1 c=2 d=3 e=4
if you now call the offset =4 you get e and d is missed from the process :sob:
[badly explained but I am sure you get what I mean]

Bill_French · ‎Dec 05, 2019

Yes, I’ve experienced this mess in other platforms (Asana has some similar issues). The Firebase folks provide a methodology that allows you to completely control all aspects of the pagination/offset process.

Bastiaan_Bijl · ‎Dec 06, 2019

Thanks guys for all your input.

Somehow, magically, the problem is solved: where using the offset value with the itr/rec combination didn’t work yesterday, it now does. When I tested it in Postman this morning, it worked. I then tried refreshing my Excel query (without changing anything in the code) and suddenly the whole table was loaded in stead of just the first 100 rows plus an error message.

Magically, the errors I got yesterday have disappeared.

It seems as if they were in the middle of pushing changes to the API to include an additional iteration value or parameter of the offset. Do you get the same type of value back now as well when executing a GET request on a table?

For what it’s worth: please see the actual JSON with the offset value below, as returned in Postman.

            "Form_submission_date": "2019-01-02T14:12:07.000Z",
            "Due date": "2019-01-09",
            "Weeks left": -47,
            "Quarter": "2018-Q4"
        },
        "createdTime": "2019-01-02T14:12:07.000Z"
    }
],
"offset": "itrPG9P2HkVYYEqgs/rec5UQ10c6QgjA88e"

}

I am also in touch with Airtable Support, and will ask them specifically for this itr addition to the value.

If they answer, I’ll update here as well.

Thanks!

Bill_French · ‎Dec 06, 2019

It’s not magic. :winking_face: Something in your code likely changed. Glad to see you are over that hurdle.

Bastiaan_Bijl · ‎Dec 06, 2019

Nope, nothing changed. I am querying the API in the most basic way via Postman and what wasn’t working yesterday, is working now.

Will keep you posted when I receive a response from Airtable support on this issue.

Arman_Azhkhanov · ‎Dec 09, 2019

Can you please provide final version of M code?

Bastiaan_Bijl · ‎Dec 09, 2019

Sure! Please find it below:

let
     URL            = "https://api.airtable.com/v0"         , //API URL excluding slash
     AppID          = "YOUR_BASE_ID"                        , //Base ID Airtable
     TableName      = "YOUR_TABLE_NAME"                     , //URL-encoded table name
     ApiKeyName     = "api_key"                             , //The name of the API key query parameter

     Pagination = List.Skip( List.Generate( () => [Page_Key = "init", Counter=0],                                                                                                   // Start Value
   	                     each  [Page_Key] <> null,                                                                                                                                 // Condition under which the next execution will happen
                         each  [Page_Key = try if [Counter]<1    
			                                   then "" 
			                                   else  [WebCall][Value][offset] 
			                                   otherwise null,                                                                                                                   // determine the LastKey for the next execution
		  	                     WebCall  = try if [Counter]<1    
			                                   then  Json.Document(Web.Contents( URL & "/" & AppID & "/" & TableName, [ApiKeyName = ApiKeyName] ))
			                                   else  Json.Document(Web.Contents( URL & "/" & AppID & "/" & TableName & "?offset=" & [WebCall][Value][offset], [ApiKeyName = ApiKeyName] )),    // retrieve results per call
			                     Counter = [Counter]+1                                                                                                                           // internal counter
                                ],
   		                  each [WebCall]
                        ),
                      1
                   ),
     #"Json2Table" =  Table.RenameColumns(Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{"Column1", "stepA.1"}}),
     #"Expanded to stepA.2" = Table.ExpandRecordColumn(#"Json2Table", "stepA.1", {"Value"}, {"stepA.2"}),
     #"Expanded to stepA.3" = Table.ExpandRecordColumn(#"Expanded to stepA.2", "stepA.2", {"records"}, {"stepA.3"}),
     #"Rows from stepA.3" = Table.RenameColumns(Table.ExpandListColumn(#"Expanded to stepA.3", "stepA.3"),{{"stepA.3", "stepB.1"}}),
     Source = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"fields"}, {"Src"})
in
     Source

Bill_French · ‎Dec 09, 2019

@Bastiaan_Bijl,

Thanks for sharing the latest version of your code.

Given this new information, it appears the problem was exactly where I initially pinpointed when you first published your code five days ago -

The two versions are shown here - latest on the left and the failing version on the right.

The change was to move the ampersand from the embedded offset URL and where it belongs to concatenate the offset with the URL endpoint.

Ergo… nothing magically started working and something in your code changed to make it appear like there was some magic happening. :winking_face:

Bastiaan_Bijl · ‎Dec 10, 2019

Hi Bill,

Thanks for your reply, but I’m afraid I have to respectfully disagree. Please allow me to explain why.

First of all, the ampersand you are referring to in your response to my original code is not embedded. The ampersand is used to concatenate the query string, embedded in quotes and the offset parameter that is defined earlier:

In my latest version of the code, the only thing I have done is parameterize the URL creation so that the user can insert the specifics at the top of the code rather. Furthermore, I have added 2 spaces left and right of the ampersand to improve the legibility of the code:

As you can see, essentially there are no differences between how the query URL is constructed.In fact, if I run the first code now, it works.

Secondly, as I mentioned before, the issues with the errors I got on the iteration did not only occur in Excel/PowerQuery. I also tried pasting the query URL directly into my browser and in Postman, and got the same error.

This leads me to conclude that something went wrong on the API side. They most likely implemented a change in the API to include the itr value to the offset, but did not implement it correctly until a day later.

Cheers,

Bastiaan

Bill_French · ‎Dec 10, 2019

Yeah, it’s possible the ampersand played no part in the five days of wondering why your earlier version was unable to run, but, there are some significant changes shown and likely more than a few tests along the way to the latest version.

And that’s my point - you didn’t share the complete picture until now. Without a complete picture of the logic, it’s almost impossible to assess what’s truly happening.

Breaking changes occur in APIs all the time. But when they occur we tend to see a number of people all reporting similar issues. The odds of an API error induced by a developer are astronomically high compared with a breaking change introduced by the vendor and factorially astronomical when no other users report any API issues.

As such, I conclude the exact opposite; it’s extremely likely something in your code probably changed and it started working.

Show of hands - how many API developers have experienced unexplained “sudden” success with an unfamiliar API? :winking_face:

No worries though - it’s great you have a working model now.