Get all Airtable tables with API in R. #r #jsonlite #httr

get_airtable_tables.R

library(tidyverse)
library(httr)
library(jsonlite)

# API set up
api <- "https://api.airtable.com/v0/apphVdH23b9rXFd1p/"
api_key <- "keyL1Ja4L6pg3Uu9Q"    # fake api_key
tables <- c("products", "brands", "stores")

# GET request
urls <- paste0(api, tables)
resps <- lapply(urls, GET, query = list("api_key" = api_key))

# Parse JSON objects
contents <- list()
for (i in seq_along(resps)) { contents[[i]] <- content(resps[[i]], as = "text") }
jsons <- lapply(contents, fromJSON)

# Assign dataframes
df <- lapply(jsons, as.data.frame)
for (i in seq_along(df)) assign(tables[[i]], df[[i]])

# Unnest dataframes
unnest_dataframes <- function(x) {
  y <- do.call(data.frame, x)
  if ("data.frame" %in% sapply(y, class)) unnest_dataframes(y)
  y
}

for (i in seq_along(df)) assign(tables[[i]], unnest_dataframes(mget(tables)[[i]]))

# Wrangle columns names
wrangle_names <- function(df) {
  names(df) %>% 
    str_replace_all("records.", "") %>% 
    str_replace_all("fields.", "") %>% 
    tolower()
}

all_tables <- lapply(tables, get)
for (i in seq_along(tables)) { names(all_tables[[i]]) <- wrangle_names(all_tables[[i]]) }
for (i in seq_along(df)) assign(tables[[i]], all_tables[[i]])