I am trying to import data from url into R which is in the form of JSON, then export it to an Excel file.
url: https://api.typeform.com/v1/form/JlCM2J?key=ecab3f590a2af4ca55468adc95686a043bbf6c9a
This is my R code
library(data.table)
library(httr)
library(rjson)
set_config(config(ssl_verifypeer = 0L))
var1=fread('https://api.typeform.com/v1/form/JlCM2J?key=ecab3f590a2af4ca55468adc95686a043bbf6c9a')
head(var1)
Output:
Empty data.table (0 rows) of 161 cols: {"http_status":200,stats":{"responses":{"showing":2,"total":2,"completed":1}},"questions":[{"id":"textfield_38991412,question":"What is your first name?,field_id":38991412},{"id":"statement_38991416,question":"Hi {{answer_38991412}},Thank you for taking this questionnaire.Your answers will help us build a great brand for you. One that is strong and memorable in your customers' minds. One that defines clearly what you are, what you stand for, and what makes you different.Let's get started!,field_id":38991416},{"id":"group_38991407...
Need:
I only need the responses section of this data to be exported as an Excel file.
Entire data can be viewed by pasting the above url in the jsonviewer.stack.hu site
The following is the more (IMO) idiomatic way to interface with REST APIs in modern R:
library(httr)
library(jsonlite)
library(dplyr)
res <- GET("https://api.typeform.com/v1/form/JlCM2J",
query=list(key="ecab3f590a2af4ca55468adc95686a043bbf6c9a"))
content(res, as="text") %>%
fromJSON(flatten=FALSE) -> out
glimpse(out$responses$answers)
## Observations: 2
## Variables: 26
## $ textfield_38991412 <chr> NA, "A"
## $ dropdown_38991418 <chr> NA, "Accounting"
## $ textarea_38991420 <chr> NA, "A"
## $ textfield_38991413 <chr> NA, "A"
## $ textarea_38991421 <chr> NA, "A"
## $ listimage_38991426_choice <chr> NA, "Company"
## $ textfield_38991414 <chr> NA, "A"
## $ website_38991435 <chr> NA, "http://A.com"
## $ textarea_38991422 <chr> NA, "A"
## $ listimage_38991427_choice <chr> NA, "Sincere"
## $ listimage_38991428_choice <chr> NA, "Male"
## $ list_38991436_choice <chr> NA, "17 or younger"
## $ list_38991437_choice <chr> NA, "Upper class"
## $ listimage_38991429_choice_49501105 <chr> NA, "Store"
## $ listimage_38991430_choice <chr> NA, "Product"
## $ textarea_38991423 <chr> NA, "A"
## $ listimage_38991431_choice <chr> NA, "Techy"
## $ listimage_38991432_choice_49501124 <chr> NA, "Fuchsia Rose"
## $ listimage_38991433_choice <chr> NA, "Classic"
## $ list_38991438_choice <chr> NA, "$3,000 or less"
## $ listimage_38991434_choice_49501140 <chr> NA, "Brand Design"
## $ textarea_38991424 <chr> NA, "A"
## $ textfield_38991415 <chr> NA, "A"
## $ dropdown_38991419 <chr> NA, "Afghanistan"
## $ email_38991439 <chr> NA, "A@a.com"
## $ textarea_38991425 <chr> NA, "A"
- Using
httr::GET() directly will enable easier management of extra parameters.
- Using
httr::content() to take the response and retrieve the raw text enables finer-grained processing (if needed)
- Using
jsonlite::fromJSON() directly provides far more granular control over individual JSON processing options.
However, there's an R package rtypeform which really simplifies everything (fun fact: it follows the idiom above under the covers):
library(rtypeform)
library(dplyr)
res <- get_results("JlCM2J")
glimpse(res$responses$answers)
## Observations: 2
## Variables: 26
## $ textfield_38991412 <chr> NA, "A"
## $ dropdown_38991418 <chr> NA, "Accounting"
## $ textarea_38991420 <chr> NA, "A"
## $ textfield_38991413 <chr> NA, "A"
## $ textarea_38991421 <chr> NA, "A"
## $ listimage_38991426_choice <chr> NA, "Company"
## $ textfield_38991414 <chr> NA, "A"
## $ website_38991435 <chr> NA, "http://A.com"
## $ textarea_38991422 <chr> NA, "A"
## $ listimage_38991427_choice <chr> NA, "Sincere"
## $ listimage_38991428_choice <chr> NA, "Male"
## $ list_38991436_choice <chr> NA, "17 or younger"
## $ list_38991437_choice <chr> NA, "Upper class"
## $ listimage_38991429_choice_49501105 <chr> NA, "Store"
## $ listimage_38991430_choice <chr> NA, "Product"
## $ textarea_38991423 <chr> NA, "A"
## $ listimage_38991431_choice <chr> NA, "Techy"
## $ listimage_38991432_choice_49501124 <chr> NA, "Fuchsia Rose"
## $ listimage_38991433_choice <chr> NA, "Classic"
## $ list_38991438_choice <chr> NA, "$3,000 or less"
## $ listimage_38991434_choice_49501140 <chr> NA, "Brand Design"
## $ textarea_38991424 <chr> NA, "A"
## $ textfield_38991415 <chr> NA, "A"
## $ dropdown_38991419 <chr> NA, "Afghanistan"
## $ email_38991439 <chr> NA, "A@a.com"
## $ textarea_38991425 <chr> NA, "A"
Either way, this must be your first time using R (or almost your first time) if you aren't used to using $ to access fields in lists. You should really spend some time learning R before trying to work with API data. Incorrect results and self-frustration are the only things you're going to get in return for cut-paste-and-praying your way through coding. You still need to get this into a CSV file (write.csv() for that).
Penultimately, if you're just going to end up using Excel, why are you programmatically retrieving the form responses? If you're not going to use R for the rest of the work, this seems like a needless step unless you're trying to script the download of the data vs make someone login to the site to fetch it.
Finally, invalidate and re-generate your API key immediately since you posted it in an open forum. I now know you have 2 forms: "Branding Questionnaire" and "Test Form" and will be able to monitor what you do in Typeform and retrieve any of your form data at-will. The rtypeform package will let you store your API key in the typeform_api environment variable (you can use the ~/.Renviron to hold this data) so you never have to expose it to the world again in your scripts.
