Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Python script to retrieve schema from an Airtable base

Topic Labels: API
8250 1
cancel
Showing results for 
Search instead for 
Did you mean: 
JerryD
4 - Data Explorer
4 - Data Explorer

A couple of members have been kind enough to post code snippets to retrieve a base’s schema. I’ve build a small python script that can automate the process.

Anyone with Javascript experience should be able to update this without too much trouble to a pure Javascript solution. The code is basically just a ‘proof of concept’ with no error checking to get people started. I hope it’s helpful.

The (uncommented) source code follows; an additional post will show sample output of two schema formats (if the forum accepts it).

Many thanks to Chester_McLaughlin and Kai_Curry for sharing their original Javascript code.

jd

P.S. I couldn’t include everything (with download links to installation files) because (as a new user) I’m limited to only two links and the code syntax seems to confuse the forum. Apologies for the omission.

from Chester_McLaughlin:

I found a half-way solution that will help in some use-cases while waiting for the metadata API. Visit https://airtable.com/api and select a base. Then open your browsers console (Developer Tools) and run the following (I tried simply running JSON.stringify(window.application), however there are circular references so I had to manually reconstruct the properties I wanted). var myapp = { id:window.application.id, name:window.application.name, tables:[] }; for (let table of window.applicatio…

from Kai_Curry:

After logging in to the Airtable website I’ve been pasting the following into the browser console of the API documentation page. I then paste the result into a new json text file. I haven’t bothered with automating or finding a better way. console.log(JSON.stringify(_.mapValues(application.tablesById, table => _.set( _.omit(table, ['sampleRows']), 'columns', _.map(table.columns, item => _.set(item, 'foreignTable', _.get(item, 'foreignTable.id'))) ))))

  • Retrieving schema from an Airtable base (jd: 2018-01-25) get_airtable_base_schema.py

from selenium import webdriver
from pprint import pprint
from sys import version_info
from time import sleep

  • NOTE: it’s a security risk to put login info in a plain text file. take appropriate precautions to protect/secure your data.

AIRTABLE_APP_ID = 'app012345abcdef’
AIRTABLE_USER_ID = 'userID@example.com’
AIRTABLE_PASSWORD = ‘mySecretPW’

WAIT_SECS_FOR_PAGE_LOAD = 15 # set higher/lower as needed for a slow webpage load

(from Chester_McLaughlin)
js_script_1 = ‘’'
var myAT = {
id:window.application.id,
name:window.application.name,
tables:[]
};

for (let table of window.application.tables){
var mytable = {
id:table.id,
isEmpty:table.isEmpty,
name:table.name,
nameForUrl:table.nameForUrl,
primaryColumnName:table.primaryColumnName
, columns:[]
};

for (let column of table.columns){
	var mycolumn = {
		id:column.id,
		name:column.name,
        type:column.type
        , 			typeOptions:column.typeOptions
	};
	mytable.columns.push(mycolumn);
}
myAT.tables.push(mytable);

}

jQuery(‘link[rel=stylesheet]’).remove();
jQuery(“body”).html(JSON.stringify(myAT));
// console.log(myAT); // (not used here)
return (myAT);
’’’

(from Kai_Curry)
js_script_2 = ‘’‘
return (JSON.stringify( _.mapValues(application.tablesById,
table => .set(.omit(table, [‘sampleRows’]),
‘columns’,
_.map(table.columns,
item => _.set(item, ‘foreignTable’,
_.get(item, ‘foreignTable.id’)))
))))
’’’

print( “\nUsing: Python v” + f"{version_info.major}.{version_info.minor}.{version_info.micro} / ", end=’’ )
print( “Selenium v” + webdriver.version )

opts = webdriver.FirefoxOptions()
opts.set_headless( False )
driver = webdriver.Firefox( firefox_options = opts )
sess_id = driver.session_id

print( “WebDriver version: " + driver.capabilities[‘browserName’] +
” v" + driver.capabilities[‘browserVersion’] + “\n” )

driver.get( ‘https://airtable.com/’ + AIRTABLE_APP_ID + ‘/api/docs’ )

userID_field = driver.find_element_by_name(‘email’)
passwd_field = driver.find_element_by_name(‘password’)

userID_field.send_keys( AIRTABLE_USER_ID )
passwd_field.send_keys( AIRTABLE_PASSWORD )
passwd_field.submit()

print( “Pausing for Airtable API page to load…” )
sleep( WAIT_SECS_FOR_PAGE_LOAD )

schema_dict_1 = driver.execute_script( js_script_1 )
schema_dict_2 = driver.execute_script( js_script_2 )

print("\n=== Script results 1: ======================\n")
pprint( schema_dict_1 )

print("\n=== Script results 2: ======================\n")
pprint( schema_dict_2 )

driver.quit()

print( “\n=== Done ==================================\n” )


Sample output:

Using: Python v3.6.1 / Selenium v3.8.1
WebDriver version: firefox v58.0

Pausing for Airtable API page to load…

=== Script results 1: ======================

{‘id’: ‘app12345abcdef’,
‘name’: ‘My Company Base’,
‘tables’: [{‘columns’: [{‘id’: ‘fld123ibhW2LaIUUw’,
‘name’: ‘Customer Contacts’,
‘type’: ‘foreignKey’,
‘typeOptions’: {‘foreignTableId’: ‘tbl123kgvyAlTQR1y’,
‘relationship’: ‘many’,
‘symmetricColumnId’: ‘fld123cXqlKxj9f3v’}},
{‘id’: ‘fld123L0FEDKLWOaD’,
‘name’: ‘Status’,
‘type’: ‘select’,
‘typeOptions’: {‘choiceOrder’: [‘sel123HgkyaBwuqo1’,
‘sel123YQCGLHH3eip’],
‘choices’: {‘sel123YQCGLHH3eip’: {‘color’: ‘cyan’,
‘id’: ‘sel123YQCGLHH3eip’,
‘name’: 'Inactive '
‘account’},
‘disableColors’: True}},
(…etc…)

         'id': 'tbl12393h7R4RQAVx',
         'isEmpty': False,
         'name': 'Customers',
         'nameForUrl': 'customers',
         'primaryColumnName': 'Key'},

         (...etc...)

=== Script results 2: ======================

(’{“tbl12393h7R4RQAVx”:{“id”:“tbl12393h7R4RQAVx”,“name”:“Customers”,“columns”:[{“id”:“fld123epFkGqDGCaC”,“name”:“Key”,“type”:“text”,“typeOptions”:null,"$$hashKey":“object:255”},{“id”:“fld123ibhW2LaIUUw”,“name”:“Customer '
'Contacts”,“type”:“foreignKey”,“typeOptions”:{“foreignTableId”:“tbl123kgvyAlTQR1y”,“symmetricColumnId”:“fld123cXqlKxj9f3v”,“relationship”:“many”},“foreignTable”:“tbl123kgvyAlTQR1y”,"$$hashKey":“object:257”},

(…etc…)

== Done ==================================

1 Reply 1
Kai_Curry
5 - Automation Enthusiast
5 - Automation Enthusiast

Inspired by this I made a version with node https://github.com/cape-io/airtable-schema