Hi everyone, I recently helped a local NGO to setup their field data collection in KOBO. To create Google Data Studio dashboards I used the ‘API connector’ extension in Google Sheets to pull the form responses into the Google Sheet.
But… in the free version of ‘API connector’ it is not automated or scheduled so they have to log-in twice a day to press the ‘update’ button. And the ‘pro’ version of ‘API connector’ is $12 a month which is not possible.
I just want to check - am I missing something? Is there another way to achieve the end-result without having to use this method?
It would be wonderful in the future to have an option in KoBo to automatically export to a google sheet and KoBo automatically takes care of updating the file. I used to use DoForms which had this functionality and made everything so much simpler are more accessible for small organisations (but it is $10/month per license).
Any help would be greatly appreciated!
Thanks!
Welcome back to the community, @Chris123! If your project does not have the repeat groups, you could do it as outlined in the support article shared earlier:
Or you could use this approach to load your data to google data studio:
Hello dear Kobo users,
if anyone is interested, I had created a GSheet template a while ago to automatically import Kobo data, thanks to the excellent ImportJSON library published by Brad Jasper and Trevor Lohrbeer (GitHub - bradjasper/ImportJSON: Import JSON into Google Sheets, this library adds various ImportJSON functions to your spreadsheet).
You can make a copy of the workbook and add a trigger on the “triggerAutoRefresh” script to set-up an automatic refresh of the data.
done quickly … It can be improved … but it is very easy to use (just fill in the yellow cells) and it also works if the questionnaire has repeated groups (but you might have to adapt a formula).
KInd regards,
Serge
Thank you Kal_Lam and TGHBoko this is super helpful. I like the data going to Sheets before DataStudio so that people can see and navigate the whole data set - I think that makes it easier to manage. That GSheet template is amazing, thank you!
But… this is too complex for most the people we work with. Do you know any ‘KoBo consultants’ that would charge a small fee to help local organizations setup these systems?
Thanks a lot!
Christian
Hi.
I have 3-4 KoboToolBox projects collecting survey data.
The data is exported to Google Sheets using a script
Then Google Data Studio links to the google sheet and present the report.
All steps are running automatically ( by a timer)
You need to paste this script (which I found by searching the internet) into your google sheet under Extensions / Appscript
"
// your spreadsheet must have a sheet named “Raw”
// however you can replace “Raw” with another name
// just dont forget to replace the “Raw” getSheetByName(“Raw”)
// with the other name
// You have to replace xxx with you API token
// to get your API token go to https://kf.kobotoolbox.org/#/account-settings
// then copy paste your token here
// also dont show your token to other people, its BAD idea
// the credentials are saved as script properties
// you can delete the saveKeys function the first
// time this code works
function saveKeys() {
var scriptProperties = PropertiesService.getScriptProperties();
scriptProperties.setProperties({
“method” : “Get”,
“Authorization”: “Token xxx”
});
}
function getKeys() {
var scriptProperties = PropertiesService.getScriptProperties();
var credentials = scriptProperties.getProperties();
Logger.log(scriptProperties.getProperties()[“method”]);
Logger.log(scriptProperties.getProperties()[“Authorization”]);
return(credentials);
}
//replace the xxx with your csv download
//to find your csv go here https://kc.kobotoolbox.org/api/v1/data
//scroll down then get the url to your data
//the url is something like this
//“https://kc.kobotoolbox.org/api/v1/data/form_id”
//append “?format=csv” to the end of the url
//so the url to download csv is “https://kc.kobotoolbox.org/api/v1/data/form_id?format=csv”
function getData() {
getKeys();
var scriptProperties = PropertiesService.getScriptProperties();
var url = “https://kc.humanitarianresponse.info/api/v1/data/xxx?format=csv”;
var params = {
“method” : scriptProperties.getProperties()[“method”],
“headers”: {“Authorization” : scriptProperties.getProperties()[“Authorization”]}
};
const response = UrlFetchApp.fetch(url, headers = params);
const csv = Utilities.parseCsv(response);
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“raw”).getRange(1,1,csv.length,csv[0].length).setValues(csv);
}
"
It looks like this:
To run the script with a timer:
However, you will find that columns may change order in the Google Sheet. (I have no idea why this happens) So you may need a second sheet in the workbook where you copy the data from the “raw” sheet using this function:
=FILTER(‘Private Report raw’!$A$2:$AOJ,‘Private Report raw’!$A$1:$AOJ$1=U$1)
So in the “report” sheet I have added:
row 1: full name of the kobotoolbox variable
row2: my label for google datastudio
row3: the above filter command (=FILTER(‘Private Report raw’!$A$2:$AOJ,‘Private Report raw’!$A$1:$AOJ$1=U$1))
It looks like this:
Regarding support - It is not easy to teach local partners to maintain this, but once it is set up, it should run “by itself”. I suggest you find a local google sheet “expert” who can set up the scripts and timers and the Google Data Studio data source.
Hi Chris123, thanks for your answer.
I’ve updated the template to make the management repeated groups easier.
If you try it, you’ll see that the use of this file is really very simple (I did it for colleagues who are absolutely not GSheets specialists).
The only thing that is a bit " trickier " is the definition of the trigger to refresh the data regularly, but you can easily find videos or tutorials showing how to do it (see also steen_andersen’s message above). If I have a moment I will make a short guideline later.
A remark about the fact that the password of the Kobo account must be written in clear text in the template : it is obviously modifiable but I consider that this template is for the owner of the Kobo account so it should not be a problem. The Kobo Questionnaire remains private, the GSheet too. Once the data are imported in the template, you can share what you want or link other GSheets to it.
Kind regards,
Serge
Hello dear Kobo users,
in case some users are interested, I have updated the template for automatic import of KOBO data into a google sheet. The changes concern :
1- the addition of a menu to automate the setting of triggers to automatically refresh data
2 - the possibility to redefine the headers of the data columns (so that they can be used more easily in Google Data Studio for example).
Hopefully this will be useful for some …
Kind regards,
Serge
Hola Serge, he logrado con exito usar la plantilla… te agradezco mucho. Ahora, para usar los disparadores para actualización me ha pedido autorización… ¿Es seguro dar esta autorización?
Gracias por tu ayuda.
Hi Serge, I have successfully managed to use the template… thank you very much. Now, to use the triggers for update it has asked me for authorization… Is it safe to give this authorization?
Thanks for your help.
Or maybe @Kal_Lam can help trying this options?
Hola @raquelchaicoj : yes, giving the authorization is safe : you can actually see the script itself in the google sheet code.
If you want, here is an updated Google Sheet template , which allows :
-
to enter and remove your token via a menu
-
to dowload data using synchronous exports (previously created using this procdedure : https://support.kobotoolbox.org/synchronous_exports.html )
-
set-up and remove triggers via a menu to automatically refresh the downloaded data
-
upload “simple” submissions to a survey … This feature uses a quick and dirty implementation of “csv_import” V1 Api, which has some constraints (it doesn’t work with repeated groups nor with media type data, and it can easily lead to duplicates if the process is not well controlled) … It can however be useful, especially when you want to transfer the ownership of a survey to another user (by uploading selected data in an empty clone of the initial survey) … use at your own risk …only if you know what you are doing ! (preferably on a blank survey)
Hola raquelchaico,
I cannot see your screen capture but here are some guidelines to use the GSheet : Guidelines
Hope this helps !
Best,
