[{"id":3087,"date":"2017-03-22T17:34:12","date_gmt":"2017-03-22T22:34:12","guid":{"rendered":"https:\/\/digitalborn.org\/?p=3087"},"modified":"2024-01-15T10:34:23","modified_gmt":"2024-01-15T15:34:23","slug":"data-analysts-get-data-from-the-airtable-api","status":"publish","type":"post","link":"https:\/\/digitalborn.org\/data-analysts-get-data-from-the-airtable-api\/","title":{"rendered":"Get Data from the Airtable API the Easy Way"},"content":{"rendered":"\n

Airtable is part spreadsheet and part relational database management system. It makes a good, simple tool for analysts to organize basic data. While Airtable provides the option to export to CSV, let’s look at how to get the data in real time through the API.<\/p>\n\n\n\n

    \n
  1. Sign in to Airtable
    https:\/\/airtable.com\/login<\/a><\/li>\n<\/ol>\n\n\n\n
      \n
    1. If you haven’t already, go to your account settings<\/a> and click “Generate API key”\"generate<\/a><\/li>\n<\/ol>\n\n\n\n
      \n\n\n\n
        \n
      1. Go to the API hub for your bases
        https:\/\/airtable.com\/api<\/a> and select the base where you want to retrieve data.
        Note: use a base that already has some entries or add a few test entries<\/li>\n<\/ol>\n\n\n\n
        \n\n\n\n
          \n
        1. Scroll down to the Authentication section and note the base id
          $ <\/span>curl https:\/\/api.airtable.com\/v0\/[highlight]app0LWmcz9Bjd56KV[\/highlight]\/<\/span>Donations<\/span> \\ –<\/span>H<\/span> “Authorization: Bearer YOUR_API_KEY”<\/span><\/span><\/li>\n<\/ol>\n\n\n\n
          \n\n\n\n

          <\/p>\n\n\n\n

            \n
          1. Now we will use your API key and this base id to form our request<\/li>\n<\/ol>\n\n\n\n
            \n\n\n\n

            Examples<\/h4>\n\n\n\n

            In the below examples we will get five records from a view called “Main View” and a base called “Donations”.<\/p>\n\n\n\n

            Python<\/h4>\n\n\n\n

            import requests<\/code><\/p>\n\n\n\n

            headers = {
            ‘Authorization’: ‘Bearer yourapikey’,
            }<\/p>\n\n\n\n

            params = (
            (‘maxRecords’, ‘5’),
            (‘view’, ‘Main View’),
            )<\/p>\n\n\n\n

            r = requests.get(‘https:\/\/api.airtable.com\/v0\/app0LWmcz9Bjd56KV\/Donations’, headers=headers, params=params)
            from pprint import pprint
            pprint(r.json())<\/p>\n\n\n\n

            \n\n\n\n

            Excel<\/h4>\n\n\n\n
              \n
            1. Open Excel (comes default in Excel 2016 but otherwise you’ll need to download the Power Query Addon)<\/li>\n\n\n\n
            2. Click on Data -> New Query -> From Other Sources -> From Web\"\"<\/a><\/li>\n\n\n\n
            3. Select Advanced<\/li>\n\n\n\n
            4. Add the URL for the base to the URL field<\/li>\n\n\n\n
            5. Select Authorization for the Header and use “Bearer yourapikey”\"\"<\/a><\/li>\n\n\n\n
            6. Select OK<\/li>\n\n\n\n
            7. Convert Into Table
              \"\"<\/a><\/li>\n\n\n\n
            8. Click on arrows in column header to Expand to New Rows
              Note: repeat this step on any additional columns with arrows in the header              \"\"<\/a><\/li>\n<\/ol>\n\n\n\n

              That’s it. Here’s a quick screencast of me doing this in a minute<\/p>\n\n\n\n

              \"\"<\/a><\/figure>\n\n\n\n
              \n\n\n\n

              Power BI<\/h4>\n\n\n\n

              The steps in Power BI are almost identical to Excel.<\/p>\n\n\n\n

                \n
              1. Open Power BI Desktop<\/li>\n\n\n\n
              2. Select Get Data -> Web<\/li>\n\n\n\n
              3. Follow the connect and expand steps in the Excel instructions above<\/li>\n<\/ol>\n\n\n\n
                \n\n\n\n

                R<\/h4>\n\n\n\n

                # See https:\/\/github.com\/bergant\/airtabler for how to use this R package<\/p>\n\n\n\n

                library(pacman)
                p_load(devtools)
                devtools::install_github(“bergant\/airtabler”) # if not yet installed
                p_load(airtabler)
                # This won’t work unless you have a one-line file .Renviron in your home directory. To check where your home is, type path.expand(“~”)
                # You will get your API key and base id from your Airtable account
                base_id <- “pp0LWmcz9Bjd56KV”
                dat<\/p>\n\n\n\n

                \n\n\n\n

                curl<\/h4>\n\n\n\n

                $ curl \"https:\/\/api.airtable.com\/v0\/app0LWmcz9Bjd56KV\/Donations?maxRecords=5&view=Main%20View\" \\
                \n-H \"Authorization: Bearer YOUR_API_KEY\"<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

                This article shows you how to engage with the AirTable API to pull data into your preferred analysis tool such as Excel Get and Transform or Power BI.<\/p>\n","protected":false},"author":5,"featured_media":3095,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[162,143,114],"tags":[173,150],"class_list":["post-3087","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api","category-data","category-tech-tips","tag-airtable","tag-api"],"_links":{"self":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/posts\/3087","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/comments?post=3087"}],"version-history":[{"count":0,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/posts\/3087\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/media\/3095"}],"wp:attachment":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/media?parent=3087"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/categories?post=3087"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/tags?post=3087"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}},{"id":3076,"date":"2016-11-18T17:13:50","date_gmt":"2016-11-18T22:13:50","guid":{"rendered":"https:\/\/digitalborn.org\/?p=3076"},"modified":"2024-01-15T10:36:46","modified_gmt":"2024-01-15T15:36:46","slug":"get-survey-monkey-data-using-the-rest-api","status":"publish","type":"post","link":"https:\/\/digitalborn.org\/get-survey-monkey-data-using-the-rest-api\/","title":{"rendered":"Get Survey Monkey Data Using the REST API"},"content":{"rendered":"

                Update 12\/22\/17<\/strong> Survey Monkey requires you to alter the OAuth redirect url and update the scopes before you can deploy your app. I’ve updated the post to reflect these changes.
                \nUpdate 3\/31\/17:<\/strong> Survey Monkey now provides an access token for 90 days when you create an app. So unless you need to use the API for more than 90 days and you have a Platinum plan, then you can just read these long story short instructions.<\/p>\n

                Long Story Short<\/h2>\n

                When we talk about an “app” in our case, we’re just sort of faking it so that we can get an access token. <\/p>\n

                Instead of generating a “long lived” token (i.e., lasts more than 90 days), I would recommend just following these simple steps:<\/p>\n

                \"Click<\/a><\/p>\n

                1. Go to My Apps and click Add a New App<\/strong>
                \n                https:\/\/developer.surveymonkey.com\/apps\/<\/a><\/p>\n

                2. Give your app a nickname<\/strong><\/p>\n

                3. Set it to Public App<\/strong><\/p>\n

                4. Click Create App<\/strong><\/p>\n

                5. Click on Settings<\/strong><\/p>\n

                6. Change the OAuth Redirect URL<\/strong> to anything (e.g., your personal site, https:\/\/google.com, etc.)<\/p>\n

                7. Click Submit Changes<\/strong><\/p>\n

                8. Modify the scopes (e.g., set them to “Optional”><\/p>\n

                9. Click Update Scopes<\/strong><\/p>\n

                10. Click Deploy<\/strong><\/p>\n

                11. Copy your access token and use it for authorization in your request<\/p>\n

                That’s it. You don’t need to do anything else in terms of the app set up and configuration for our purposes. <\/p>\n

                Now that you have your access token you can request your survey data.<\/strong>
                \nIf you like Python you could use this:<\/p>\n

                import requests
                \nimport json<\/p>\n

                client = requests.session()<\/p>\n

                headers = {
                \n \"Authorization\": \"bearer %s\" % \"YOUR_ACCESS_TOKEN\",
                \n \"Content-Type\": \"application\/json\"
                \n}<\/p>\n

                data = {}<\/p>\n

                HOST = \"https:\/\/api.surveymonkey.net\"
                \nSURVEY_LIST_ENDPOINT = \"\/v3\/surveys\"<\/p>\n

                uri = \"%s%s\" % (HOST, SURVEY_LIST_ENDPOINT)<\/p>\n

                response = client.get(uri, headers=headers)<\/p>\n

                response_json = response.json()
                \n#survey_list = response_json[\"data\"][\"surveys\"]<\/p>\n

                print(response_json)<\/code><\/p>\n

                and replace “YOUR_ACCESS_TOKEN” with the access token in your app. <\/p>\n

                For cURL<\/strong><\/p>\n

                curl -i -X GET -H \"Authorization:bearer YOUR_ACCESS_TOKEN\" -H \"Content-Type\": \"application\/json\" https:\/\/api.surveymonkey.net\/v3\/surveys<\/code><\/p>\n

                Similarly, if you were using this in Power BI or Excel’s Get & Transform<\/strong>, you could<\/p>\n

                1. Get Data -> Web
                \n2. Paste https:\/\/api.surveymonkey.net\/v3\/surveys into the web source field
                \n3. Click Advanced
                \n4. Select Authorization in the dropdown and then in the field enter Bearer YOUR_ACCESS_TOKEN<\/p>\n

                \n

                Recently Survey Monkey updated their REST API to version 3, which increases the difficulty of requesting data for use in your analysis tools (e.g., R, Power BI, etc.). The language and instructions used in this post should be easy enough for a non-developer (with patience) to understand.\u00a0The goal is to allow Survey Monkey account administrators to create Survey Monkey apps that then allow authenticating and getting data from their Survey Monkey account.<\/p>\n

                Why would you want to do this? Well there could be several reasons but, for most people, the main benefit is to get real-time, updated data while eliminating the\u00a0need to login to Survey Monkey to perform several manual steps to get your data.<\/p>\n

                There are two parts of this process: 1. Authentication, and 2. Getting the data<\/p>\n

                You only need to authenticate occasionally since you can use a long lived token. For most people, the authentication part is the most difficult. Once we generate a token, getting data is pretty straightforward.<\/p>\n

                High-Level Workflow<\/h2>\n
                  \n
                1. Survey Monkey account administrator creates a Survey Monkey app (or uses an existing app)<\/li>\n
                2. Using the information provided with that app, get an access token to use for authentication with a data analysis tool<\/li>\n
                3. Once access token has been acquired, use data analysis tool to authenticate into Survey Monkey REST API<\/li>\n
                4. Retrieve Survey list to get survey ID<\/li>\n
                5. Retrieve survey responses<\/li>\n<\/ol>\n

                  Authenticate<\/h2>\n

                  Note: for the purposes of this guide, we will use Postman. You could choose a number of other routes but I think this is one of the more simple approaches for data analysts.<\/p>\n

                    \n
                  1. Log in to Survey Monkey<\/strong>
                    \n                    https:\/\/www.surveymonkey.com<\/a><\/li>\n
                  2. Go to My Apps<\/strong> in the Developer area
                    \nhttps:\/\/developer.surveymonkey.com\/apps\/
                    \n\"my_apps_survey_monkey_dev\"<\/a><\/li>\n
                  3. Either create a new app<\/strong> or use an existing app
                    \n<\/strong>                    \"my_apps\"<\/a><\/li>\n
                  4. Click on Settings<\/strong> for your app and use this OAuth Redirect URL
                    \nhttps:\/\/www.getpostman.com\/oauth2\/callback
                    \n                    \"oauth_redirect\"<\/a><\/li>\n
                  5. Install Postman<\/strong> on your computer
                    \nhttps:\/\/www.getpostman.com\/
                    \nOkay, this is where it gets complicated. I won’t go into details about why you have to use Postman. You could actually create a real app to interface with Survey Monkey but, since we just need to get data, this would be a lot of unnecessary work. So we fake it in order to get our access token using Postman.<\/li>\n
                  6. Open Postman <\/strong>and enter basic info
                    \n                    \"survey_monkey_postman_connect\"
                    \n<\/a>a. Add this URL<\/strong> to GET
                    \nhttps:\/\/api.surveymonkey.net\/oauth\/authorize<\/p>\n

                    b. Select OAuth 2.0<\/strong><\/p>\n

                    c. click Get New Access Token<\/strong><\/li>\n

                  7. Now use the information from your Survey Monkey app in My Apps to complete the info and click Request Token\"access_token_postman_sm\"<\/a><\/li>\n
                  8. It will prompt you to login using your Survey Monkey admin credentials and ask you to complete a captcha in order to <\/strong>Authorize<\/strong><\/li>\n
                  9. At long last, we will get our token. Use access_token to authenticate in your data analysis tool\"access_token_long_lived\"<\/a>
                    \nNote: you can create tokens that expire at a given date but that’s for another dayWhew! So with that out of the way, we can reuse this forever. You only have to do this once. Onward!<\/li>\n<\/ol>\n

                    Examples<\/h4>\n

                    Power BI Authentication Example<\/h4>\n
                      \n
                    1. In Power BI Desktop, click on Get Data -> Blank Query<\/li>\n
                    2. Click on Advanced Editor and use this to get a list of surveys from your Survey Monkey account. The two parts of this that are important are the Survey Monkey API URL and you will replace “<<API-TOKEN>>” with the access token you generated using Postman.\n
                      let\r\n\u00a0\u00a0\u00a0 Source = Web.Contents(\"https:\/\/api.surveymonkey.net\/v3\/surveys\", [Headers=[#\"Authorization\"=\"bearer <<API-TOKEN>>\"]]),\r\n\u00a0\u00a0\u00a0 convertToJson = Json.Document(Source),\r\n\u00a0\u00a0\u00a0 toTable = Table.FromRecords({convertToJson})\r\nin\r\n\u00a0\u00a0\u00a0 toTable<\/pre>\n<\/li>\n<\/ol>\n
                      R Authentication Example<\/h4>\n
                      You will enter your access token you painstakingly generated in Postman in the space where it says “Enter your access token for SurveyMonkey” below:<\/p>\n
                      sm_get_surveys <- function(auth_token, page = 1, per_page = 250) {\r\n if (missing(auth_token)) {\r\n auth_token <- readline('Enter your access token for SurveyMonkey: ')\r\n }\r\n auth <- paste(\"bearer\", auth_token, sep=\" \");\r\n url <- paste('https:\/\/api.surveymonkey.net\/v3\/surveys?page=', page, '&per_page=', per_page, sep='')\r\n survey_list_response <- httr::GET(url=url, add_headers(\"Content-Type\" = \"application\/json\", \"Authorization\" = auth ))\r\n \r\n if (survey_list_response$status_code != 200) {\r\n stop(paste('Bad response from server: ', httr::http_status(survey_list_response)))\r\n }\r\n json <- httr::content(survey_list_response, as = 'text')\r\n survey_list <- jsonlite::fromJSON(json)\r\n survey_list\r\n}<\/pre>\n
                       <\/p>\n
                      Get the Data<\/h2>\n
                      So after you authenticate, you have to request the data. I’ll show you how to get all of the responses for a survey but there are a lot of options available:
                      \nhttps:\/\/developer.surveymonkey.com\/api\/v3\/<\/a><\/p>\n
                        \n
                      1. Get survey list (list of survey ids)
                        \n                        https:\/\/api.surveymonkey.net\/v3\/surveys<\/a><\/li>\n
                      2. Use a specific survey id from the list retrieved in #1 to get responses in bulk
                        \n                        https:\/\/api.surveymonkey.net\/v3\/surveys\/{survey_id}\/responses\/bulk<\/a><\/li>\n<\/ol>\n
                         <\/p>\n
                         <\/p>\n","protected":false},"excerpt":{"rendered":"
                        This article demonstrates how to engage with the Survey Monkey API to get form data.<\/p>\n","protected":false},"author":5,"featured_media":3084,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[162,143,114],"tags":[150,172,171,170],"class_list":["post-3076","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api","category-data","category-tech-tips","tag-api","tag-oauth","tag-postman","tag-survey-monkey"],"_links":{"self":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/posts\/3076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/comments?post=3076"}],"version-history":[{"count":0,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/posts\/3076\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/media\/3084"}],"wp:attachment":[{"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/media?parent=3076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/categories?post=3076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/digitalborn.org\/wp-json\/wp\/v2\/tags?post=3076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}},{"id":3044,"date":"2016-07-05T16:00:18","date_gmt":"2016-07-05T20:00:18","guid":{"rendered":"http:\/\/digitalborn.org\/?p=3044"},"modified":"2016-07-05T18:25:12","modified_gmt":"2016-07-05T22:25:12","slug":"unlock-form-data-formidable-zapier-webhooks","status":"publish","type":"post","link":"https:\/\/digitalborn.org\/unlock-form-data-formidable-zapier-webhooks\/","title":{"rendered":"Unlock Form Data: Formidable and Webhooks by Zapier"},"content":{"rendered":"
                        Want to send Formidable form data using Zapier? If you’re using WordPress and Formidable forms, then you will likely find this guide helpful. This quick how-to guide walks you through easily sending your form data to other apps and services.<\/p>\n
                        Examples:<\/p>\n