API to Google Sheet without 'API Connector Pro'

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