Help

Re: Airtable link to Excel via Power Query

3215 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthew_Billio1
7 - App Architect
7 - App Architect

Here is a way to automatically pull information from airtable into Excel via power-Query using a bit of coding.

If you are looking to just copy and paste the code you need without much explanation, ignore the details :slightly_smiling_face:

Details

SECTION 1- CONNECTING EXCEL TO POWERQUERY-

The first issue a user may run into while trying to get data to automatically feed into Excel is creating an authenticated connection to Airtable from Excel’s powerQuery. The following code will be used in a later step, but it is given here as a preview of the code that is chiefly responsible for the data connection to airtable’s api.

Source = Json.Document(Web.Contents(“https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]”, [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]]))

You will be replacing “[PartialAddressToYourBase]”, “[NameOfYourTable]”, and the “keyXXXXXXXXXXXXXX” with your particular information at a later step.

Now even if a query were created with its sole source of data being the above code, the output would be a very useless and unfriendly format; additionally, airtable’s api only allows users to get data via paginated requests… so unless the query looped through all the pages of data, the resulting datasheet in excel would only contain the first few hundred records of data.

SECTION 2- PAGINATING THROUGH AIRTABLE’s DATA

In order to pull ALL of the Base’s data into Excel, the query needs to make calls to Airtables api repeatedly until it reaches the end of the records. So, instead of just setting the query source as using the above code, a query should instead loop through all of the records in the Airtable Base as follows:

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

SECTION 3- FORMATTING THE DATA

Again, as stated at the beginning, the above code will return data in a pretty nasty format. So, the rest of the code is necessary to reformat it in a record by record fashion:

#"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 Records" = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"fields"}, {"Src"}),

Now you have an idea of what the code is doing, proceed to put it all together in a how-to-fashion.

STEP 1.
Copy and paste into Excel’s advanced power query editor the following code.

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/[PartialAddressToYourBase]/[NameOfYourTable]", [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]]))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]])),// 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"})
in
     #"Source"

STEP 2
Replace the following strings with your particular information:

  1. [PartialAddressToYourBase]

  2. "[NameOfYourTable]"

  3. "keyXXXXXXXXXXXXXX"

For example,

Json.Document(Web.Contents(“https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]”, [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]]))

Becomes

Json.Document(Web.Contents(“https://api.airtable.com/v0/tb2rvf9QCSxi2QJJ3/Books”, [Headers=[Authorization=“Bearer keyDB8lDEPgMun”]]))

STEP 3
After copying, pasting, and replacing hit “done” in excels Advanced Query Editor. Upon attempting to run the query, a yellow bar will appear at the top of the Power Query application. Click “authenticate” or something like that. A dialog box will pop up asking to authenticate the query. I used regular web authentication at Airtable’s Base level (ex www.airtable.com/v0/tbl21OZVeYPLuPQl4 instead of www.airtable.com).

FINAL STEPS
This part is up to you. Anyone familiar with power query will then be able to run with this process from this point forward.

CREDITS

Apologies for not remembering those who helped me (although I visited many websites, read the airtable api, and had to dig into the powerquery help to figure this out, there was one website that especially helped and I will try to find it and update this post later).

36 Replies 36
Jon_Thomas1
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Hi.

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

airtable.JPG

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

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:

airtable2.JPG

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:

  • image.png

To this code:

  • image.png

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 (&)

Mikey_McLaughli
4 - Data Explorer
4 - Data Explorer

thanks @Matthew_Billiodeaux1 this worked like a charm; really appreciate saving me from re-inventing this :grinning_face_with_smiling_eyes:

Bharat_Venugopa
4 - Data Explorer
4 - Data Explorer

Hi Guys,

Thanks for the original post and the replies. This helped me link AirTable data into MSExcel using PowerQuery. However the source table has 400 rows and only 100 are being downloaded into excel and it looks like a preview. Can one of you help me with any code updates.

Regards,

Bharat

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/appfSBJGSgkE7a6tF/COVID-19%20Tracker?view=Treatments%20and%20Vaccines”, [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]]))
else Json.Document(Web.Contents(“https://api.airtable.com/v0/appfSBJGSgkE7a6tF/COVID-19%20Tracker?view=Treatments%20and%20Vaccines?of...”&[WebCall][Value][offset] , [Headers=[Authorization=“Bearer keyXXXXXXXXXXXXXX”]])),// 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”, {“Developer / Researcher”, “Treatment vs. Vaccine”, “Product Category”, “Product Description”, “Stage of Development”, “Anticipated Next Steps”, “Clinical Trials for COVID-19”, “Funder”, “FDA-Approved Indications”, “Sources”, “Date Last Updated”, “CMS2”, “Record Update”, “Clinical Trials for Other Diseases (T only) / Related Use or Platform (V only)”, “Published Results”}, {“Src.Developer / Researcher”, “Src.Treatment vs. Vaccine”, “Src.Product Category”, “Src.Product Description”, “Src.Stage of Development”, “Src.Anticipated Next Steps”, “Src.Clinical Trials for COVID-19”, “Src.Funder”, “Src.FDA-Approved Indications”, “Src.Sources”, “Src.Date Last Updated”, “Src.CMS2”, “Src.Record Update”, “Src.Clinical Trials for Other Diseases (T only) / Related Use or Platform (V onl”, “Src.Published Results”}),
#“Filtered Rows” = Table.SelectRows(#“Expanded Src”, each true)
in
#“Filtered Rows”