Re: Python script to retrieve schema from an Airtable base
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 ==================================
