title	output
airtabler	html_document keep_md true

Provides access to the Airtable API

Install

devtools::install_github("ecohealthalliance/airtabler")

Setup

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

Get and store the API tokens

** As of November 2022 Airtable recommends using scoped tokens. The personal access tokens can be used interchangeably with the now superseded Airtable API Key.

Create appropriately scoped personal access tokens.

airtabler functions will read the API token from environment variable AIRTABLE_API_KEY. To start R session with the initialized environvent variable create an .Renviron file in your home directory.

usethis::edit_r_environ

In .Renviron add the following:

AIRTABLE_API_KEY=your_api_token_here

NOTE: Be sure the last line of your .Renviron file is an empty return line

To check where your home is, type path.expand("~") in your R console.

If you're frequently working across multiple bases, consider using gitcrypt and the dotenv package to securely manage multiple tokens.

Using the metadata API

In order to use the metadata API, a personal access token or OAuth integration must be used. User API keys are not supported. See the airtable guide for more information.

Usage

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_ids when running the examples.

Get records

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")

Using page size and offset

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:

• fields A list of fields to be returned (instead of all fields).
• view The name or ID of the view, defined on the table.
• maxRecord The maximum total number of records that will be returned.

Retrieve a record

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 record

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 a record

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 = "")

Delete a record

TravelBucketList$Hotels$delete(new_hotel$id)

Working with data frames

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)

Programming with airtabler

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)