Help

Offset value invalid; output does not match API documentation

Topic Labels: API
12353 34
cancel
Showing results for 
Search instead for 
Did you mean: 
Bastiaan_Bijl
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

34 Replies 34

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]

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

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

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.

Can you please provide final version of M code?

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

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

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

image

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:

image

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:

original code BPI

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:

new code PBI

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

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.

Well, if you scroll back up in the post, the entire code I used 5 days ago was there. When testing this morning, I actually copied it from the post because I did not save it anywhere else. :slightly_smiling_face:

I guess we will never know, so let’s agree to disagree on this point. :winking_face: After all, it is indeed nice to have a working model now.

One last thing: I still get the itrXXXXXXXXXXXX back as part of my offset value. Do you get that as well now?

I definitely want to agree that we disagree. :winking_face:

We version every change when testing new API code. This allows us to know where we may have created issues and more important where APIs are actually failing.

Sure, it’s a little more effort, but the analytics we get from this process accelerate development in the long run and put us in a position where we never feel like we will never know why things failed. This also provides us with clear ammunition to assert claims of another API failing to perform, an important development aspect that allows us to bill for time spent on failures beyond our control.

It’s difficult to say; when stuff works reliably we don’t go looking at offset values that have likely been occurring properly for years. I do know that in many API unit tests recently I have seen only record IDs, not iteration ids. But we must also realize unit tests are designed to produce responses in very narrow ranges of data.

But without question, of the 80+ Airtable API integration processes currently running under our apps every minute, we have seen zero failures from Airtable over the recent past (9+ months) with one exception - an Airtable outage that occurred about two weeks ago. The API impact was very brief (like 2 minutes) while the UI was impacted for a few hours I think.

aaron_altamura
6 - Interface Innovator
6 - Interface Innovator

Hey Guys,

Just wanted to say thank you all for this post!

I am getting the same response ‘itrXXXXXX/recXXXXXX’ for the offset and was unsure how to incorporate with the curl. This had been bugging me for a number of days now!

I used your query to better understand how to properly query for the next page with python’s api requests library: ‘offset’: ‘itrXXXXXX/recXXXXXX’

Glad to be amongst some fellow geeks :grinning_face_with_big_eyes:

Shahbaz_Haidar
4 - Data Explorer
4 - Data Explorer

In case anyone is still getting the issue, here is what happened with me.
I was getting same error and was making changes here and there but nothing worked. Then I realized, because of iterative offset value, my url was getting elongated every time as it was not replacing old offset but appending in it.
If you are still getting this error, just print your URL just before hitting API and see if it is OK.

candle
4 - Data Explorer
4 - Data Explorer

In case this helps anyone coming across this issues now...

I was having this issue when running the query in postman, and it ultimately came down to quotation marks.

I thought I had to pass the offset surrounded by quotes, e.g.,

&offset="itrXXXXXX/recXXXXXX"

I kept getting the error.

When I removed the quotes and passed,

&offset=itrXXXXXX/recXXXXXX

I got a proper response and no error.