API Connector Documentation

Print

Import KoBoToolbox Data to Google Sheets

Last Updated OnJanuary 8, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull Kobo API data directly into Google Sheets, using the API Connector add-on for Sheets.

We'll first get an API key from Kobo, and then set up a request to pull in data to your spreadsheet.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get your Kobo API Key

  1. While logged in to your Kobo account, check the domain in the address bar. For most people, this will be either kf.kobotoolbox.org or kobo.humanitarianresponse.info (this article describes the difference).
    kobo-img1
  2. Now navigate to https://YOUR_DOMAIN/token, i.e. https://kf.kobotoolbox.org/token/ or http://kobo.humanitarianresponse.info/token. Copy the token that you see there. That's it, you can now access Kobo's API.
    kobo-img2

Part 2: Pull Data from Kobo to Sheets

The easiest way to get started with the Kobo API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select KoboToolBox from the drop-down list of applications
    kobotoolbox-application
  3. Under Authorization, enter your API key with the word Token in front of it.
    kobotoolbox-authorization
  4. Choose an endpoint. We’ll start with /assets.json which is the endpoint to get a list of your assets.
    kobotoolbox-endpoints
  5. Under kpi-url, select your hostname (e.g. kf.kobotoolbox.org or kobo.humanitarianresponse.info, etc.)
  6. Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
    kobotoolbox-response
  7. Note the ID in the results.uid field, as you can plug that into the asset_uid input box for subsequent requests.
    kobotoolbox-response2
  8. When you run these requests, select grid mode with the Unwind option selected to get response data in table.
    kobotoolbox-grid

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration. This enables some more advanced features like filtering by date, e.g. https://kobo.humanitarianresponse.info/api/v2/assets/aQ111111111/data.json?query={"_submission_time":{"$gt":"2022-09-23"}}. Here's an example request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://kf.kobotoolbox.org/api/v2/assets.json
  • Headers:
    • Authorization: Token your_api_token
    • Accept: application/json

kobo-img4

Part 4: Handle Pagination

Kobo limits response data to 30,000 records as described here.

To get more records, you'll need to run your request in batches, where you first fetch 30K records, and then the next set of 30K, and so on. In API Connector, you can cycle through automatically with a multi-query request, like this:

https://kobo.humanitarianresponse.info/api/v2/assets/1111111111/data.json?sort={"_id":1}&start=0

https://kobo.humanitarianresponse.info/api/v2/assets/1111111111/data.json?sort={"_id":1}&start=30000

Part 5: API Documentation & Information

Kobo hasn't provided much documentation, but here are a few links for more information:

33 thoughts on “Import KoBoToolbox Data to Google Sheets”

  4. Hello,
    I have two questions:
    1. Is there any free way to have the document updated at regular intervals?
    2. How do I import the data labels, and not the names?
    Thanks, this is a really helpful page!

    Reply

    • Sorry, scheduling is a paid feature (if you're working for a nonprofit, we can offer a discount). I'm not sure what you mean by data labels though. What is the data label?

      Reply

      • Thanks so much for your reply Ana. Ok, that's fair - thanks. It is for a non-profit but manual is ok for now and I checked the pricing, it is fair so will upgrade if necessary.

        By labels, I mean the label within Kobo. So let's say I have a question: Where do you live, and it has multiple answer options. The label is what the enumerator sees in the Kobo form "Lives in a house" but what is imported is the name "lives_house", which is how the data is stored. I might not be using the right terms sorry, but hopefully I'm explaining that clearly. The connector is excellent but it's pulling over "lives_house" which doesn't read well when used in Data Studio - I would like it to pull over "Lives in a house". Does that make sense? Is there a way to do that?

      • That does make sense, but we already return everything that gets sent back by their API. So unfortunately if you don't see the label in the response, that means the API doesn't send that data back, and we have no way to include it. If you're familiar with Sheets functions, you could do something a little clever, like manually create a mapping table where column A contains the name and column B contains the label, and then use VLOOKUP to pull in the label to your sheet before importing it into Data Studio.

  5. Dear Ana,

    When I followed the steps mentioned in this article, I am getting the following as an output:

    count next previous results.1
    0

    Can you please help me to resolve this matter? I am not able to get the results.uid and other columns.

    Thank you for your support.

    Regards,
    Tarek

    Reply

    • I believe that just means there aren't any results. Please double check that you've entered an asset ID or date range that contains data.

      Reply

    • Sorry, I'm not really sure what a sub-form is (I don't use Kobo myself), and don't see anything about this in their documentation. Does the sub-form have its own ID that you could plug in to your request URL? If not, maybe you could try asking in their API support forum.

      Reply

  9. Hello Ana,
    I followed your instruction (both for "Custom" and for "Kobotoolbox") and as far as I can see it the request is successfully completed (after clicking "Run" it shows "Request completed successfully").
    But it doesn't show the expected columns and entries (there are data entries in the form I tested) but the following columns:
    "user" / "forms" / "notes" / "metadata" / "media" / "formlist" / "submissons" / "briefcase"
    and the entries are links like "https://kc.humanitarianresponse.info/api/v1/user" for "user" column or "https://kc.humanitarianresponse.info/api/v1/forms" for "forms" column
    This happens with all the endpoints (I included the right asset UID)
    Do you have any idea what I do wrong?

    Reply

    • Can you please check if data has been filtered out? You can open the field editor and reset any field filters by clicking Edit fields > Refresh fields. Also, please check that you haven't included any JMESPath filter (located at Output options > More > JMESPath).

      Reply

      • Thank you very much for your quick reply.

        I clicked "Refresh fields" in edit fields and checked if something is filtered out. Nothing is filtered there.
        The "raw response" says following:
        {
        "user": "https://kc.humanitarianresponse.info/api/v1/user",
        "forms": "https://kc.humanitarianresponse.info/api/v1/forms",
        "notes": "https://kc.humanitarianresponse.info/api/v1/notes",
        "metadata": "https://kc.humanitarianresponse.info/api/v1/metadata",
        "media": "https://kc.humanitarianresponse.info/api/v1/media",
        "formlist": "https://kc.humanitarianresponse.info/api/v1/formlist",
        "submissions": "https://kc.humanitarianresponse.info/api/v1/submissions",
        "briefcase": "https://kc.humanitarianresponse.info/api/v1/briefcase"
        }

        I also checked that JMESPath is empty.

        Do you have any other idea?

      • I see, in that case there's no error per se, that's just the response from KoboToolbox. I'm not sure which endpoint that was in response to, but it looks like a list of available URLs. What happens if you copy/paste one of those URLs into the Request URL field?

  10. I tried with four of the mentioned URLs in the URL field and tried all endpoint. Always the same response with the same columns and links. That is, no matter what URL path and which endpoint I take, I receive the same response.
    Any other idea?

    Reply

    • Since API Connector is "working" (i.e. it's connecting and returning Kobo's response; I realize it doesn't include the data you expected), I'm afraid I don't have any further suggestions. You may have better luck asking on or checking Kobo's forum, e.g. this thread gives a few reasons why data responses may be empty (in general, they're saying that there may not be any data available for those assets).

      Reply

Leave a Comment

Jump To