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!

Bill_French · ‎Dec 04, 2019

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.

Bastiaan_Bijl · ‎Dec 05, 2019

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"

Bill_French · ‎Dec 05, 2019

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=”.

Bastiaan_Bijl · ‎Dec 05, 2019

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"

Bill_French · ‎Dec 05, 2019

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

cor · ‎Dec 05, 2019

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

Bill_French · ‎Dec 05, 2019

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.

cor · ‎Dec 05, 2019

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

Bill_French · ‎Dec 05, 2019

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.

Bill_French · ‎Dec 05, 2019

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.