How to get data into Google Data Studio?

Can someone guide or write a tutorial on support.kobotoolbox.org to enable pulling data into Google Data Studio, the way it is done in Microsoft Excel Power Query?
It will be great help. Thanks

1 Like

Guys, I have figured pulling the data into Google Data Studio and it has worked. The submissions for primary question are visible. However, there is an issue. My form also has ‘repeat-group’ questions for which i am not able to see the data collected. Can someone guide how to solve this issue?

1 Like

Hi @mohan_n,

Thank you for sharing this with the community! Could you also help the forum by sharing your steps that you followed to get data into google data studio so that other users could benefit from the same. GOOD DAY!

I have used your ‘power query’ tutorial as a template to create this step-by-step tutorial for Google Data Studio(GDS)

For seamless data analysis, you can leverage the KoBo API to pull your data into other tools for data analysis. This article covers how to connect to the API to pull your data into Google Data Studio.
KoBo API

  1. Login to your account.

  2. Next, go to the API for your account by going to either https://kc.humanitarianresponse.info/api/v1 or https://kc.kobotoolbox.org/api/v1/ . Select the URL for the instance in which your account is located.

  3. Under the Data heading, click on /api/v1/data

  4. Click on “GET” and select “json”.

  5. This action will open a webpage having links to json files of all your projects.

  6. Next, locate your project’s data structure inside the data file. It will be in the following format: description,id,id_string,title,url
    For example:
    MIRA_DO,1314,MIRA_DO,MIRA_DO,https://kc.humanitarianresponse.info/api/v1/data/1314?format=json

  7. Before transitioning over the Google Data Studio, make sure that there is a) at least 1 record stored in your data table in KoboToolbox and b) under the Project’s settings, set it to “share data publicly”.
    Google Data Studio

  8. In Google Data Studio, click on Create > Data Source

  9. Type “custom json/csv/xml” in the search bar and select the first search result.

  10. Now, in the emerging webpage, select ‘json’ for the “Data Type” field and paste url of your json file that you have noted in step 6 into the ‘Source URL or Google Drive path” field.

  11. Click “Connect”

  12. Now, you can see all the fields of your form. Select “Explore” or ‘Create Report” and you are good to go.


1 Like

Hi @mohan_n,

Thank you for sharing it in details with screenshots for better understanding. This should be very helpful to those who intend to try it out at their end!

1 Like

I am trying to link my kobotoolbox with google data, but not all columns could be retrieved by data studio. Can you suggest how to solve the problem?

I am not a pro at this. The connector used in the above tutorial is from the company “supermetrics”. They can better answer your query.
Also I have figured out another way to link kobo data to google data studio, i.e. through google sheets. The googlesheets add-on I use for this is “API Connector”.

2 Likes

Hi sir @mohan_n how did you implemented it using the googlesheets add-on “API Connector”?

Hi @mohan_n, could you please show how to use “API Connector” in order to link kobo data to google sheet?

1 Like

I figured this out! Thanks so much for this write up but the instruction is incomplete. Using some info from here: API call sometimes forgets authentication?

I figured it out!

  1. you need to go to here:
    https://www.base64encode.org/

type in USERNAME:PASSWORD (use your own obviously), then hit “encode”, then take that BASE64 String and put it in this string, replace YOURSTRINGHERE

{“Authorization”:“Basic YOURSTRINGHERE”}

so you end up with something like

{“Authorization”:“Basic amZzdGF0czpuZWhlb2lhaA==”}

  1. take that and paste into “HTTP Headers” when you set up the connection in data studio.

Now you should be ok because the API needs authentication!

Buy me a coke :smiley:
Ray

3 Likes

Welcome tothe community @Ray and thank you for this wonderful post. :clap: It should help a lot of users having an issue with the same. Expecting the same in the upcoming days as well. :star_struck:

May I request a detailed steps of your solution from accessing your KOBO API to DATA STUDIO connection?

Thanks @Ray! for your solution.

1 Like

If you look at the original posting from mohan_n, insert my post between his steps 10 and 11, that should be what you need.

2 Likes

Hello all, thanks for a great write up.

@mohan_n did you figure out how to get repeat group questions in data studio?

1 Like

I couldn’t figure out.

Oh! I’m stuck in the step suggested by @Ray. I encoded my username:password in base64 and added it to HTTP headers field in data studio, but it gives me an error saying “Unexpected token in object literal”. So I tried authorization with token by :

{“Authorization”:“Bearer MY_TOKEN”}

Now it connects, but the fields are not present, instead there are 2 fields which basically say details not found

EDIT : OMG I just had to change from bearer to Token and it worked!!!

{“Authorization”:“Token MY_TOKEN”}

MY_TOKEN can be obtained from https://kc.humanitarianresponse.info/YOUR_USERNAME/api-token

EDIT 2 : @mohan_n This is working for repeat group questions as well

1 Like

Hi Community (@Kal_Lam , @mohan_n and everyone)

I have tried to connect my data from Kobotoolbox to Google data studio following the steps by @mohan_n, and I found them here, but I am getting some errors, and I am not able to see any data. Can anyone help me, please? This is super urgent for me, and if anyone knows the answer, I can set up a 20 minutes meeting on “Google meet”, my email is charama@visualst.org! Thanks very much in advance!

The ERROR I am getting is below:

Thanks very much in advance!

i have emailed you to connect for a possible troubleshoot. though i am not a pro, let us try.

1 Like

if you want to get the complete list then you can visit the website from google here is the link
https://datastudio.google.com/data

here you will get 800+ data connectors.

1 Like

Welcome to the community, @dennisfrancis050! Thank you for sharing this wonderful link. This should be helpful to a lot of users.