Airtable link to Excel via Power Query

Thanks for providing this info.

I was getting a “[null]” result in my query and wanted to share the fix if anyone else experiences the same.

I was using the AIRTABLE_BASEID found in the URL address bar in the browser. I had to go to the Airtable API page and use the BASE_ID shown in the connection string Authentication section. It was different than the one in the URL address bar when accessing the base.

Once I replaced that, the [null] record issue went away and I was able to see the list of records.

1 Like

Hi.

I tried your solution and I didn’t get the the table, only lines with Record

I’m doing something wrong, i just dont now what :slight_smile:

Hello @Adolfo_Ferreira,

That looks correct. Now you just need to use the GUI to build out the rest of the query (ie, expanding “record” into columns with the data you need). To do this, see my post earlier in this thread.

Feel free to reach out again if that doesn’t work!

Matt

Thanks for the help.

I’m sorry I didn’t saw the other post, now it’s ok, and I got all the columns but only 100 lines :frowning:

Sorry to hear that. I am not seeing anything obvious in your code that would indicate why you are only having 100 lines. Due to the fact that you are only getting 100 records, it seems like it is not paginating … if you feel comfortable, you can share the table with me, but I am really busy the next few days.

Until then, my suspicion is that something is wrong on the line that looks like this

 Json.Document(Web.Contents("https://api.airtable.com/v0/TTTTTTTTTTTTTTTTT/Parcels?view=EXPORT%20(Abstracts%20Report%20Wkly)&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer BBBBBBBBBBBB"]])),// retrieve results per call

Where the Ts are replaced with your table ID, the Bs are the bearer key.

As with @Adolfo_Ferreira, I’m also only getting 100 records back. I’ve tried several codes similar to this one, and keep getting similar error messages. For this query, I’m using this code:

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/[APP_ID]/[TABLE_ID]", [Headers=[Authorization="Bearer [KEY_ID]"]]))
		                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer [KEY_ID]"]])),// retrieve results per call
		                     Counter = [Counter]+1// internal counter
                             ],
		                  each [WebCall]
                     ),
                   1
                )

And from this, I’m getting a list where there’s no error for the first 100 records, but for the rest, I get this error message: “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6’ (404): Not Found”

@Adolfo_Ferreira and @Ivan_Larson

I tried using Adolfo’s code, and it looks legit. I see no reason why it is throwing errors except for the fact that I can’t see your API table names. When it tries paginating it throws the following error:

"Web.Contents failed to get contents from 'https://api.airtable.com/v0/[...redacted for privacy...]/[...redacted...]&offset=[...redacted...]' (404): Not Found"

And this error renders the next page of results as “Null”.

So, please look at @Jon_Thomas1’s post. I think this is the issue you are facing.

I would test it for you with the code you shared, but do not have easy access to your API table names. I probably will be away from this discussion board for about a day, but will reply ASAP.

Hello @Adolfo_Ferreira and @Ivan_Larson ,

I am back from my “siesta”. After reviewing code that was shared with me, I found the error. If you are not using a particular view, then you had to modify the code, deleting the VIEW from the http request. When deleting the portion of the code that pertains to a parcticular airtable view, be careful NOT to delete the question mark (?) in the code that denotes HTTP request parameters…

This is easy to miss (as seen by the fact that I missed it when reviewing your code).

So, when NOT using a particular view for your import from airtable into excel, the following code should be modified FROM this

...
 WebCall  = try if [Counter]<1    
			    then  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME", [Headers=[Authorization="Bearer key????????????"]]))
			    else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call

Into this format

...
 WebCall  = try if [Counter]<1    
			    then  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME", [Headers=[Authorization="Bearer key????????????"]]))
			    else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call

So, in short we went from this code:

To this code:

Notice how we dropped the " ?view=VIEW_NAME " entirely from the first shown line, but on the
second shown line, we dropped only " view=VIEW_NAME " and kept the question mark (?)
but DROPPED THE AMPERSAND (&)