Provides access to the Airtable API
knitr::opts_chunk$set(echo = TRUE)
devtools::install_github("bergant/airtabler")
After you've created and configured the schema of an Airtable base from the graphical interface, your Airtable base will provide its own API to create, read, update, and destroy records. - airtable.com/api
Generate the airtable API key from your Airtable account page.
airtabler functions will read the API key from
environment variable AIRTABLE_API_KEY
. To start R session with the
initialized environvent variable create an .Renviron
file in your home directory
with a line like this:
AIRTABLE_API_KEY=your_api_key_here
To check where your home is, type path.expand("~")
in your R console.
Create airtable base object:
library(airtabler) TravelBucketList <- airtable( base = "appIS8u9n73hzwE7R", tables = c("Destinations", "Hotels", "Travel Partners") )
Note that you should replace the Airtable base identifiers and record_id
s when running the examples.
Use select function to get all records:
hotels <- TravelBucketList$Hotels$select() knitr::kable(hotels[, c("id","Name", "Stars", "Price/night")], format = "markdown")
Filter records with formula (see formula field reference ).
hotels <- TravelBucketList$Hotels$select(filterByFormula = " ({Avg Review} > 8.5)" ) knitr::kable(hotels[, c("id","Name", "Stars", "Avg Review", "Price/night")], format = "markdown")
Sort data with sort parameter:
hotels <- TravelBucketList$Hotels$select(sort = list( list(field="Avg Review", direction = "desc"), list(field="Price/night", direction = "asc") )) knitr::kable(hotels[, c("id","Name", "Stars", "Avg Review", "Price/night")], format = "markdown")
Define page size with pageSize
:
hotels <- TravelBucketList$Hotels$select(pageSize = 3) nrow(hotels)
Continue at offset, returned by previous select:
hotels <- TravelBucketList$Hotels$select(offset = get_offset(hotels)) nrow(hotels)
To fetch all rows (even > 100 records) use select_all
. The select_all
function will handle the offset and return the result as a single object.
hotels <- TravelBucketList$Hotels$select_all() nrow(hotels)
Other optional arguments:
Add the record_id
argument to get the details of a record:
radisson <- TravelBucketList$Hotels$select(record_id = "recgKO7K15YyWEsdb") str(radisson$fields, max.level = 1)
Insert a new record with insert
function (API returns all record data - including new record ID):
record_data <- list( Name = "New hotel", `Price/night` = 200, Stars = "****", Amenities = c("Hiking", "Gym"), Notes = "Just a sample record.\nWith extra line in notes." ) new_hotel <- TravelBucketList$Hotels$insert(record_data) cat("Inserted a record with ID=", new_hotel$id, sep = "")
Update the price of the new hotel (API returns all record data):
new_hotel <- TravelBucketList$Hotels$update( record_id = new_hotel$id, record_data = list( `Price/night` = 120, Notes = "Check out the price!!!" ) ) cat("Updated a record with ID=", new_hotel$id, ". ", "New price: ", new_hotel$fields$`Price/night`, sep = "")
TravelBucketList$Hotels$delete(new_hotel$id)
Standard Airtable API does not accept a table of records.
Functions insert
and update
accept a data.frame and
execute transactions (call Airtable API) row by row.
Insert records with a data frame:
two_records <- data.frame( Name = c("Sample1", "Sample2"), `Price/night` = c(150, 180), Stars = c("***", "****"), Amenities = I(list(c("Wifi", "Pool"), c("Spa", "Laundry"))), Notes = c("Foo", "Bar"), check.names = FALSE, stringsAsFactors = FALSE ) new_records <- TravelBucketList$Hotels$insert(two_records)
Update records with a data frame:
# change records record_ids <- sapply(new_records, function(x) x$id) two_records$`Price/night` <- two_records$`Price/night` + 5 two_records$Stars <- "*****" updated <- TravelBucketList$Hotels$update( record_id = record_ids, record_data = two_records)
Delete multiple records:
# delete new records record_ids <- sapply(new_records, function(x) x$id) deleted <- TravelBucketList$Hotels$delete(record_ids)
While having all airtable base tables and functions in one object is handy in interactive mode, it is recommended to use primitive functions for adding, reading, updating and deleting when programming R packages:
travel_base <- "appIS8u9n73hzwE7R" # read data hotels <- air_select(travel_base, "Hotels") # get one record radisson <- air_select(travel_base, "Hotels", record_id = "recgKO7K15YyWEsdb") # create inserted <- air_insert(travel_base, "Hotels", record_data) # update updated <- air_update(travel_base, "Hotels", record_id = inserted$id, record_data) # delete deleted <- air_delete(travel_base, "Hotels", record_id = inserted$id)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.