Help

Re: Python script to retrieve schema from an Airtable base

1109 0
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