bergant/airtabler

R interface to the Airtable API

airtabler

Provides access to the Airtable API

Install

devtools::install_github("bergant/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 key

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.

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

id	Name	Stars	Price/night
reccPOcMQaYt1tthb	Heritage Christchurch Hotel (Christchurch, New Zealand)	****	176
receHGZJ22WyUxocl	Urikana Boutique Hotel (Teresopolis, Brazil)	*****	146
recgKO7K15YyWEsdb	Radisson Blu Hotel Marseille Vieux Port (Marseilles, France)	****	170
recjJJ4TX38sUwzfj	Hotel Berg (Keflavík, Iceland)	***	136
recjUU2GT28yVvw7l	Sheraton Nha Trang (Nha Trang, Vietnam)	*****	136
reckPH6G384y3suac	Grand Residences Riviera Cancun (Puerto Morelos, Mexico)	*****	278
reclG7Bd2g5Dtiw4J	Grand Budapest Hotel (Zubrowka)	*****	156

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

id	Name	Stars	Avg Review	Price/night
reccPOcMQaYt1tthb	Heritage Christchurch Hotel (Christchurch, New Zealand)	****	8.8	176
receHGZJ22WyUxocl	Urikana Boutique Hotel (Teresopolis, Brazil)	*****	9.0	146
recjJJ4TX38sUwzfj	Hotel Berg (Keflavík, Iceland)	***	9.2	136
recjUU2GT28yVvw7l	Sheraton Nha Trang (Nha Trang, Vietnam)	*****	8.8	136
reckPH6G384y3suac	Grand Residences Riviera Cancun (Puerto Morelos, Mexico)	*****	9.1	278
reclG7Bd2g5Dtiw4J	Grand Budapest Hotel (Zubrowka)	*****	9.0	156

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

id	Name	Stars	Avg Review	Price/night
recjJJ4TX38sUwzfj	Hotel Berg (Keflavík, Iceland)	***	9.2	136
reckPH6G384y3suac	Grand Residences Riviera Cancun (Puerto Morelos, Mexico)	*****	9.1	278
receHGZJ22WyUxocl	Urikana Boutique Hotel (Teresopolis, Brazil)	*****	9.0	146
reclG7Bd2g5Dtiw4J	Grand Budapest Hotel (Zubrowka)	*****	9.0	156
recjUU2GT28yVvw7l	Sheraton Nha Trang (Nha Trang, Vietnam)	*****	8.8	136
reccPOcMQaYt1tthb	Heritage Christchurch Hotel (Christchurch, New Zealand)	****	8.8	176
recgKO7K15YyWEsdb	Radisson Blu Hotel Marseille Vieux Port (Marseilles, France)	****	8.2	170

Using page size and offset

Define page size with pageSize:

hotels <- TravelBucketList$Hotels$select(pageSize = 3)
nrow(hotels)

## [1] 3

Continue at offset, returned by previous select:

hotels <- TravelBucketList$Hotels$select(offset = get_offset(hotels))
nrow(hotels)

## [1] 4

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)

## [1] 7

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)

## List of 9
##  $ Listing URL: chr "https://www.booking.com/hotel/fr/radisson-sas-marseille-vieux-port.html"
##  $ Name       : chr "Radisson Blu Hotel Marseille Vieux Port (Marseilles, France)"
##  $ Price/night: int 170
##  $ Amenities  : chr [1:4] "Pool" "Gym" "Restaurant" "Wifi"
##  $ Notes      : chr "Rooms with African or Provencál decor."
##  $ Country    : chr "recmSV4PR9ZCWyrk8"
##  $ Pictures   :'data.frame':	4 obs. of  6 variables:
##  $ Stars      : chr "****"
##  $ Avg Review : num 8.2

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

## Inserted a record with ID=recGtWMprUr7f2EvT

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

## Updated a record with ID=recGtWMprUr7f2EvT. New price: 120

Delete a record

TravelBucketList$Hotels$delete(new_hotel$id)

## $deleted
## [1] TRUE
## 
## $id
## [1] "recGtWMprUr7f2EvT"

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)

airtabler's Issues

Metadata API

Airtable now has a metadata API: https://airtable.com/api/meta . It would be great to wrap these endpoints to look up bases and base schemas.

Advanced filtering

Is there a way to do more advanced filtering when retrieving records? For example, I've got a column in my Airtable that can link to multiple records. I'd like to be able to filter for records where "foo" is in the list of records in that column. It works fine with a standard search if there's only one value in that field, but if there's more than one value, the filter fails with a HTTP 422 error: INVALID_FILTER_BY_FORMULA.

Here's an example that doesn't work (modified slightly to resemble the README queries):

x <- air_select(travel_base, "Hotels", filterByFormula = "({Groups} contains 'Family')" )

where Groups is the name of the field in my table and Family is one of the possible link values in that field.

Is there a way to do it that I'm missing?

Upcoming deprecation of Airtable API Keys

Hello @bergant - first I wanted to say thank you for this excellent package of functions! I use this package all the time for accessing my Airtable data via a Shiny application.

My question/issue is related to the recent communications from Airtable related to the deprecation of Airtable API Keys, which apparently are meant to be replaced with authentication tokens. I imagine this change will affect the implementation of the airtabler package functions. Are there updates planned before February 2024 when the API Keys will cease working?

Cheers,
Courtney

JSON object in air_insert missing records property

The airtable api examples show fields for created records are stored in an array inside a records property. Slight change of the air_insert function achieves expected JSON structure.

Expected JSON

'{
  "records": [
    {
      "fields": {
        "Name": "replacement",
        "value": 100,
        "Results": "Positive",
        "Some Linked Record": ["someID1234"]
      }
    }
  ]
}'

Change to R code in air_insert

  fields <- list(fields = record_data)
  records <- list(records = list(fields))

spaces in table names

For insert, update, and delete quick change to allow for spaces in table names:

request_url <- sprintf("%s/%s/%s/%s", air_url, base, utils::URLencode(table_name), record_id)

This will allow for table names with spaces such as "Example Table"

Propose an 'air_select_all()' function for use in programs

As the title says; although it is now possible to 'select_all' through the table object there isn't a single function to replicate that for less interactive style coding practise.

Get Request Filters by Field Name

Is there currently a way to use a GET request with field criteria? Like in the example you provide, can one select just hotels with 4 stars to be retrieved?

Here is the documentation that says it is possible.

fields. array of strings. optional
Only data for fields whose names are in this list will be included in the records. If you don't need every field, you can use this parameter to reduce the amount of data transferred.
https://airtable.com/api/docs#curl/table:articles:list

Filter by formula, sort and more

Airtable API update:
http://blog.airtable.com/post/138484080527/the-right-sort-of-api-updates

switch rbind to bind_rows

When reading a table with a dataframe column, I encounter errors due to your use of rbind instead of bind_rows when the column is a dataframe or list and has a missing/unassigned value in airtable. As a result, it is not possible to select that data.

Offset example?

Hi there,

Thanks for the package, it's awesome! I'm using it to pull some data from an Airtable that has more than 100 records, and it appears from your documentation that I need to make additional queries using an offset argument, and I think I'm interpreting correctly that offset = the record ID of the final record on the previous 100 records. However, that isn't working for me (I get "object not found") and I don't know what else to try. I was wondering if you would consider adding some clarifying information (or an example) to your documentation? I'd love to see how you go about fetching all records of something in a workflow when there are more than 100 records.

.bind_df - same column count, different columns

I ran into what I suspect is a rare corner case where .bind_df as used by select_all() will fail if two of the clusters of 100 rows have the same number of columns but different actual columns. In my case, there is a field that is only used in the first 100 rows and a different column that is used only in the subsequent rows. Because the number of colons matches, normal "rbind" is used but it fails.

Airtable API Deprecation

"API keys are being deprecated in favor of the two new API Authentication methods. Both of these methods offer more granular control of resources and scopes and enable both developers and end-users to extend Airtable while ensuring the highest grade of security." See article here.

As this is a major change to the Airtable API, the API Key deprecation period will last for 12 months and end on Feb 1, 2024.

After that date, API Keys will no longer be able to access the Airtable API.
Related to this, webhooks created by API Keys in Enterprise bases will also expire at the end of that period.
We recommend that all users migrate to Personal Access Tokens for individual use and OAuth for third-party integrations moving forward.

Issue to install with shinyapps.io

Hello,
I ve detected an error to install this package airtabler on shinyapps.io.
There is an error on the website which has been created with no explication and on the IDE Rstudio I've this issue :
Error detecting locale: Error in read.table(file = file, header = header, sep = sep, quote = quote, : ligne finale incomplète trouvée par readTableHeader dans 'raw'
(Using default: en_US)
I don't use a read.table in the code of the app.

Below is the step of the installation

rsconnect::deployApp(server="shinyapps.io")
_```
Preparing to deploy application...DONE
Uploading bundle for application: 6062680...Detecting system locale ... DONE
Deploying bundle: 5786004 for application: 6062680 ...
Waiting for task: 1133451758
building: Parsing manifest
building: Building image: 6750348
building: Installing system dependencies
building: Fetching packages
building: Installing packages
building: Installing files
building: Pushing image: 6750348
deploying: Starting instances
rollforward: Activating new instances
terminating: Stopping old instances
Application successfully deployed to https://testcrossjoin.shinyapps.io/shinydebut/
Warning message:
Error detecting locale: Error in read.table(file = file, header = header, sep = sep, quote = quote, : ligne finale incomplète trouvée par readTableHeader dans 'raw'
(Using default: en_US)

Below is the code

rsconnect::setAccountInfo(name='testcrossjoin', token='17441EA4805B244407DA5BF878E8E21D', secret='TLc/tLIMGQ6yyIceAY5k2xpPbUwLwo/b9egKT0CM')
library(shiny)
devtools::install_github("bergant/airtabler")
library(airtabler)

# Define UI for application that draws a histogram
ui <- fluidPage(
  titlePanel("censusVis"),
  
  sidebarLayout(
    sidebarPanel(
      helpText("Create demographic maps with 
               information from the 2010 US Census."),
      
      selectInput("var", 
                  label = "Choose a variable to display",
                  choices = c("Percent White", 
                              "Percent Black",
                              "Percent Hispanic", 
                              "Percent Asian"),
                  selected = "Percent White"),
      
      sliderInput("range", 
                  label = "Range of interest:",
                  min = 0, max = 100, value = c(0, 100))
    ),
    
    mainPanel(
      textOutput("selected_var1"),
      textOutput("selected_var2")
    )
  )
)
# Define server logic required to draw a histogram
server <- function(input, output) {
  
  output$selected_var1 <- renderText({ 
    paste("You have selected", input$var)
  })
  output$selected_var2 <- renderText({ 
    paste("You have selected", input$range)
  })
  
}

# Run the application 
shinyApp(ui = ui, server = server)

data.table dependency?

select_all() is returning Error in .rowNamesDF<-(x, value = value) : duplicate ‘row.names’ are not allowed - any idea how to fix this? seems to be an issue with how data.table works, and can be easily fixed by ", row.names=NULL" argument where relevant

https://stackoverflow.com/questions/8854046/duplicate-row-names-are-not-allowed-error

Roadmap and CRAN release?

I'm just curious about the roadmap for this. Are you planning to release to CRAN eventually? If not, is this because of time constraints or some other reasons?

Thanks for your great work!