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:

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

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"

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"

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"

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