Offset value invalid; output does not match API documentation
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:
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!
Page 1 / 2
Hmmm, I have never encountered this in a paginated offset. I have no idea what an “itr” object ID even is.
Have you tried parsing just the “/rec…” portion and using that in the offset URL?
Hmmm, I have never encountered this in a paginated offset. I have no idea what an “itr” object ID even is.
Have you tried parsing just the “/rec…” portion and using that in the offset URL?
Hi Bill,
Yes, I have tried that (without the slash) and got the following error:
{"error":{"type":"INVALID_OFFSET_VALUE","message":"The value of offset rec5XxXxXxXxXxXxXxXx is invalid"}}
Strange, right?
Bastiaan
Hi Bill,
Yes, I have tried that (without the slash) and got the following error:
{"error":{"type":"INVALID_OFFSET_VALUE","message":"The value of offset rec5XxXxXxXxXxXxXxXx is invalid"}}
Strange, right?
Bastiaan
Yes, strange indeed.
There’s a reason your offsets are getting a complex itr…/rec… ID - we need someone to explain why and perhaps we need to see the query that is able to produce this off result.
Is it this, perhaps?
Iteration may timeout due to client inactivity or server restarts. In that case, the client will receive a 422 response with error message LIST_RECORDS_ITERATOR_NOT_AVAILABLE . It may then restart iteration from the beginning.
Still, the “itr” record needs some more explanation.
Is it this, perhaps?
Iteration may timeout due to client inactivity or server restarts. In that case, the client will receive a 422 response with error message LIST_RECORDS_ITERATOR_NOT_AVAILABLE . It may then restart iteration from the beginning.
Still, the “itr” record needs some more explanation.
Hmmm, itr... - iteration - related? Perhaps.
Hmmm, itr... - iteration - related? Perhaps.
Yep, that was my thought
Tested with another table in the same base, and I get the same weird combination of itr/rec back.
I guess that you guys do not have this issue when querying the API now?
I have sent a ticket to Airtable support about this issue as well. If they respond, I’ll let you guys know.
Tested with another table in the same base, and I get the same weird combination of itr/rec back.
I guess that you guys do not have this issue when querying the API now?
I have sent a ticket to Airtable support about this issue as well. If they respond, I’ll let you guys know.
Correct - I (for one) have never seen this before. I have no knowledge of an “itr” object either, so I’m curious.
But, do we connect to and query Airtable the same way you do? Can’t say since we cannot see your code. Feel free to share more insights.
Correct - I (for one) have never seen this before. I have no knowledge of an “itr” object either, so I’m curious.
But, do we connect to and query Airtable the same way you do? Can’t say since we cannot see your code. Feel free to share more insights.
I have two ways of querying: via Power Query in Excel and directly in my browser. I use the base URL of the API, with the API key parsed as a parameter in the URL. Both methods essentially use the same curl method, and give the same offset value back.
I have two ways of querying: via Power Query in Excel and directly in my browser. I use the base URL of the API, with the API key parsed as a parameter in the URL. Both methods essentially use the same curl method, and give the same offset value back.
Explain to me the reason there is an embedded ampersand in this line.
XXXXX&offset="&pWebCall]lValue]e
The ampersand (&) looks out of place, but I’m unfamiliar with this scripting environment. It seems that the ampersand indicated here will be included in the offset parameter, no?
To know if this is the case, you could publish here the request header made by this cURL call.
Explain to me the reason there is an embedded ampersand in this line.
XXXXX&offset="&[WebCall][Value][
The ampersand (&) looks out of place, but I’m unfamiliar with this scripting environment. It seems that the ampersand indicated here will be included in the offset parameter, no?
To know if this is the case, you could publish here the request header made by this cURL call.
To be honest, I don’t know. I guess this is just how the M language from Power query works. I don;t think it is relevant though in this case (see below).
I was unable to review the request headers in Power Query, so I tried in Postman. I get the same weird value for offset. Please see the headers below:
To be honest, I don’t know. I guess this is just how the M language from Power query works. I don;t think it is relevant though in this case (see below).
I was unable to review the request headers in Power Query, so I tried in Postman. I get the same weird value for offset. Please see the headers below:
Okay. In Postman you should be able to see the details of the entire API request process as well as the response and details of response including the HTTP response code. Can you share all of this detail?
Of particular concern is the URL that looks like you’re constructing the offset parameter with an embedded ampersand after the “&offset=”.
Okay. In Postman you should be able to see the details of the entire API request process as well as the response and details of response including the HTTP response code. Can you share all of this detail?
Of particular concern is the URL that looks like you’re constructing the offset parameter with an embedded ampersand after the “&offset=”.
Sure, please find the complete response below.
Regarding the ampersand: I am using the raw API URL as you can see below, so the code with the ampersand in it is not used now. On the bottom of the complete response, you can find the offset value that is returned.
GET https://api.airtable.com/v0/appXXXXXXXXXXXXX/TABLENAME?pageSize=10
Regarding the ampersand: I am using the raw API URL as you can see below, so the code with the ampersand in it is not used now. On the bottom of the complete response, you can find the offset value that is returned.
GET https://api.airtable.com/v0/appXXXXXXXXXXXXX/TABLENAME?pageSize=10
That is the interpretation of the offset returned. I want to see the actual JSON data returned.
In this first API request, you are getting ten records and they are contained in an array like this:
{
"records": >
{
"id": "recMbhCEaJ61b7VVU",
"fields": {
"Droplet Name": "Translate Demo (3)",
"Status": "Passed",
"Droplet Script": "// set the base to Airdrop Target Test\nset base to appbyqzUGhcPWGoJn\n\n// select the table\nset table to Translate Content\n\n// replace the spanish field..."
},
"createdTime": "2019-09-28T22:41:46.000Z"
},
{
"id": "reclk2Q5bLVUeePBs",
"fields": {
"Droplet Name": "Translate Demo (4) - Count to Target Record",
"Status": "Passed",
"Droplet Script": "// set the base to Airdrop Demo\nset base to appAvzbF1dJ9OkgMn\n\n// set the source and target tables\nset source table to Facets\nset target table to Data..."
},
"createdTime": "2019-09-28T23:00:21.000Z"
}
],
"offset": "reclk2Q5bLVUeePBs"
}
I would like to see the actual JSON data returned, and I’m pretty sure Airtable support will want to see the same thing.
If the actual JSON element contains the “itr…/rec…” value then you’ve encountered something that only Airtable support could advise you on because there is not a single reference anywhere on the web or in the forum or in the API docs that reference an object that begins with “itr”.
Have you tried referencing that entire value as an offset parameter in subsequent calls?
As Bill mentioned above, I think the call should look like
I think the issue is in your PowerBI code But you are in amazing hands with @Bill.French
LOL! Careful - I like to set expectations VERY low and look like a hero later.
This is interesting though and to be honest, when offsets are working - who really looks at the offset URLs, right? This ITR thing is probably normal, although, the example I posted above came right from AT’s own API docs.
Pretty sure this is true because I have never had an issue with offset-based processes, but I’m a little old-school too.
LOL! Careful - I like to set expectations VERY low and look like a hero later.
This is interesting though and to be honest, when offsets are working - who really looks at the offset URLs, right? This ITR thing is probably normal, although, the example I posted above came right from AT’s own API docs.
Pretty sure this is true because I have never had an issue with offset-based processes, but I’m a little old-school too.
I have a hunch that the ITR object is an iteration object that’s cached because the API knows there’s a high likelihood you’re going to come looking for the next 100 records (or whatever) and this ID tells it where the data already exists in short term transient memory.
It’s also possible these ITR IDs are only returned for queries into larger data sets.
I have a hunch that the ITR object is an iteration object that’s cached because the API knows there’s a high likelihood you’re going to come looking for the next 100 records (or whatever) and this ID tells it where the data already exists in short term transient memory.
It’s also possible these ITR IDs are only returned for queries into larger data sets.
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
>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.
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.
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!
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.