Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 17, 2018 10:53 AM
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:
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:
“[PartialAddressToYourBase]”
"[NameOfYourTable]"
"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).
Jun 30, 2020 10:07 AM
Hello, This information has been very helpful for linking airtable to Excel, I was able to get it to work properly, unfortunately some of the record names are strings generated by Airtable for the linked record fields instead of the name displayed in airtable or displayed when you export a csv of the table. Is there a way to translate these generated record strings into the names that are displayed in airtable?
Thanks
Jul 02, 2020 11:15 AM
Hello Simon, please send a screen shot. If I understand you correctly, your solution is to create another field to extract the data from your linked record field in a more friendly fashion.
EX:
Lets assume you have an INVOICES_tbl (that shows the total amount owed to each client per invoice) and a CLIENTS_tbl (with contact info of each client, and a field for the clients name called Full_Name).
Jul 03, 2020 11:57 AM
Yep, this solved the problem, thank you very much for you hasty response.
Jul 06, 2020 07:31 AM
you may be having the same issue other’s have had.
Jul 06, 2020 07:43 AM
I suspect this is the error:
Should be as follows:
Oct 29, 2020 02:17 PM
Full disclosure, I’m a noob to power query and Airtable. This gives me the fields but not the ids. All linked fields use ids, which I need to then reference. How do I add a column with the id in it so I can look up all linked fields?
Oct 29, 2020 02:19 PM
I can get a list of JUST the ids if i change {“fields”} out for {“id”} on the #“Source” line. I want to be able to do both, but I don’t know how to make it multiple columns.