Offset value invalid; output does not match API documentation

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

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.

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.

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.

This is the Power Query/M code I use:

let
     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("https://api.airtable.com/v0/appdID/Table_Name?api_key=keyXXXXXXXXXXXXX"))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/appdID/Table_Name?api_key=keyXXXXXXXXXXXXX&offset="&[WebCall][Value][offset])),// 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"}),
     #"Expanded Src" = Table.ExpandRecordColumn(Source, "Src", {"COLUMN NAMES"})
in
     #"Expanded Src"

let
     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("https://api.airtable.com/v0/appdID/Table_Name?api_key=keyXXXXXXXXXXXXX"))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/appdID/Table_Name?api_key=keyXXXXXXXXXXXXX&offset="&[WebCall][Value][offset])),// 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"}),
     #"Expanded Src" = Table.ExpandRecordColumn(Source, "Src", {"COLUMN NAMES"})
in
     #"Expanded Src"

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:

Request Headers:

* User-Agent:"PostmanRuntime/7.18.0"
* Accept:"*/*"
* Cache-Control:"no-cache"
* Postman-Token:"41159c05-9d68-4e24-96bd-7593d07d88d0"
* Host:"api.airtable.com"
* Accept-Encoding:"gzip, deflate"
* Cookie:"brw=brwV3wlPUVzc6jiKF"
* Connection:"keep-alive"

Request Headers:

* User-Agent:"PostmanRuntime/7.18.0"
* Accept:"*/*"
* Cache-Control:"no-cache"
* Postman-Token:"41159c05-9d68-4e24-96bd-7593d07d88d0"
* Host:"api.airtable.com"
* Accept-Encoding:"gzip, deflate"
* Cookie:"brw=brwV3wlPUVzc6jiKF"
* Connection:"keep-alive"

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

* ▶Network

▶Request Headers

Authorization: Bearer APIKEY

Accept: application/json

User-Agent: PostmanRuntime/7.20.1

Cache-Control: no-cache

Postman-Token: a5a7217d-90ab-4308-b214-374a2fc0032b

Host: api.airtable.com

Accept-Encoding: gzip, deflate

Cookie: brw=brwV3wlPUVzc6jiKF

Connection: keep-alive

▶Response Headers

access-control-allow-headers: authorization,content-length,content-type,user-agent,x-airtable-application-id,x-airtable-user-agent,x-api-version,x-requested-with

access-control-allow-methods: DELETE,GET,OPTIONS,PATCH,POST,PUT

access-control-allow-origin: *

content-encoding: gzip

Content-Type: application/json; charset=utf-8

Date: Thu, 05 Dec 2019 14:40:49 GMT

etag: W/"17ba-mWgb6ve+SWehC3a8yq53uCtnGX4"

Server: Tengine

Set-Cookie: brw=brwV3wlPUVzc6jiKF; path=/; expires=Sat, 05 Dec 2020 14:40:48 GMT; domain=.airtable.com; secure; httponly

Strict-Transport-Security: max-age=31536000; includeSubDomains; preload

Vary: Accept-Encoding

x-airtable-has-non-empty-changes-payload: false

Content-Length: 1621

Connection: keep-alive

* ▶Response Body

1. ▶records: [10]

2. offset: "itryXXXXXXXXXXXX/rec0XXXXXXXXXXXX"

▶GET https://api.airtable.com/v0/appXXXXXXXXXXXXX/TABLENAME?pageSize=10

* ▶Network

▶Request Headers

Authorization: Bearer APIKEY

Accept: application/json

User-Agent: PostmanRuntime/7.20.1

Cache-Control: no-cache

Postman-Token: a5a7217d-90ab-4308-b214-374a2fc0032b

Host: api.airtable.com

Accept-Encoding: gzip, deflate

Cookie: brw=brwV3wlPUVzc6jiKF

Connection: keep-alive

▶Response Headers

access-control-allow-headers: authorization,content-length,content-type,user-agent,x-airtable-application-id,x-airtable-user-agent,x-api-version,x-requested-with

access-control-allow-methods: DELETE,GET,OPTIONS,PATCH,POST,PUT

access-control-allow-origin: *

content-encoding: gzip

Content-Type: application/json; charset=utf-8

Date: Thu, 05 Dec 2019 14:40:49 GMT

etag: W/"17ba-mWgb6ve+SWehC3a8yq53uCtnGX4"

Server: Tengine

Set-Cookie: brw=brwV3wlPUVzc6jiKF; path=/; expires=Sat, 05 Dec 2020 14:40:48 GMT; domain=.airtable.com; secure; httponly

Strict-Transport-Security: max-age=31536000; includeSubDomains; preload

Vary: Accept-Encoding

x-airtable-has-non-empty-changes-payload: false

Content-Length: 1621

Connection: keep-alive

* ▶Response Body

1. ▶records: [10]

2. offset: "itryXXXXXXXXXXXX/rec0XXXXXXXXXXXX"

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

https://api.airtable.com/v0/appXXXXXXXXXXXXX/TABLENAME?pageSize=10&offset=itrZXxXxXxXxXxXxXxXx/rec5XxXXXxXxXxXxXx

I think he tried that and it fails, hence the reason this message thread even exists.

But, we still have yet to see an actual HTTP response code to determine why it fails.

Cool -
For what its worth, I just ran a quick test using integromat with this url and got a successful response:

https://api.airtable.com/v0/app2DNScxk9ZeXXXX/Table%201?offset=itrD04LchFeBrVQcg/recTQAhuId9pXXXX

I think the issue is in your PowerBI code :slightly_smiling_face: 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.

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 :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.

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!

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

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?