Kobotoolbox & Google sheet Sync

Maybe you could follow what @m.jaqmaqji has outlined in the post earlier.

It should definitely help you solve your issue.

I tried running the solution given by @m.jaqmaqji
but when i ran the final step for pulling the data, i received the following error:
image

Hi @mohan_n,
Have tried this demo https://github.com/pcurrier/KoboToolbox-GoogleApps-Scripts/blob/master/demo/Code.gs
Based on your authentication method you can choose what code in demo file to activate.

This is a working example of my test case:
image

In addition please provide more details about the error. you can share the Details of the error with us.

1 Like

Hello, sorry to bother you but i really need some help. I’m trying to sync the kobo data with Google sheets with the fork that was mentioned in the thread.

The problem is, it’s not going ahead after the CHOOSE HOST pop up window. I’ve raised the issue on GitHub as well.

I think there’s some problem in the HTML files. The call to functions in the button are not working.

Please help.

I am using a Google Sheet add on called ‘Coupler.io

and it works great. You can even use schedule using their free version.

However when selecting all data from each submitted form in Kobotoolbox, some records are repeated 2/3/4 or more times.
My question is - is there a way to transfer only one record for one form response.

Have you changed the sharing settings in Kobotoolbox.
Open “settings”
select “sharing”
and tick the sharing boxes!Screenshot 2021-05-01 at 08.36.23

Hi Again

I have been looking at the data exported to Google Sheet.
It turns out that most submissions in Kobo are exported as 4 records!
The only data change in the record is GEO location, where each coordinate is repeated twice - see the attached:!

Screenshot 2021-05-01 at 09.07.54|416x146

so any suggestion how to get the geolocation as two columns and only one record?
(I am collecting GIS location in the form and it returns in only one cell)

Hola

Quizá te sirva lo que hemos hecho.

Primero, vimos la solución que nos compartio @ti_saigon, posteriormente ingresamos a la dirección que nos compartió **https://kc.humanitarianresponse.info/api/v1/data?format=json ** y nos apareció lo que se visualiza en la imagen APIS. Esta información la copiamos a un documento word imagen APIS WORD y la seccionamos de acuerdo a los nombres de los proyectos iniciando por su ID. Al terminar copiamos únicamente la dirección https://kc.humanitarianresponse.info/api/v1/data/776414?format=json (en nuestro caso) y la ejecutamos en google sheets con el complemento api conector y ya pudimos visualizar la información como se muestra en la imagen PRUEBAS UNO



Try my code.
This is a google script.
Login your gmail/drive account in an incognito/browser with no other Gmail account logged in
Create a new spreadsheet, add a sheet named “Raw” and add a google script to it.
After the code runs schedule a trigger.

I have this script in addition to other functions used to send confirmation emails. In general, working with the APIs JSON is PITA and the csv is jumbled, so your column names will be jumbled to if this works.

// 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 = "xxx";
  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);
}

//run each function from top to bottom
//if you get an error send me a message
2 Likes

Hi,
Can we add script for two or more sheet in same XLS, I am try but error is generating

TypeError: Cannot read property ‘length’ of undefined
getData

Can you insert these lines in the getData function?

Logger.log(csv.length);
Logger.log(csv[0].length);

Then let me see the output?
I think theres something different with your dataset?
Is the dataset not empty?

My script is not a general solution.

1 Like

Thank you. Its working

Request
Please help more time, right we are calling the data in csv from but if we want call in JSON or XML format, their is no as such script available for that if you provide help on that side it will be amazing :pray: :pray:

What does your questionnaire look like?
I had troubles parsing JSON because my api call didn’t return same number of elements for each response. Hidden questions are completely missing from the JSON instead of the expected “question:”.

I have a script that was able to parse JSON but I don’t have access to it right now, not even sure if it will work.

Hi Pho,

I copied your code and follow all the instructions but I am getting this error when try to run the scripts “TypeError: Cannot read property ‘setProperties’ of null (line 18, file “Main”)”, any idea on how to get this off?

Thanks,
Hamisi

I get the same error, is the solution for these found already?

Helo,

I have copied and paste the codes but I am getting this error “Attribute provided with invalid value: Header:null (line 35, file “Code”)”

I did the same steps as you did but a windows pop-up shows the error.

I did the same steps as you did but a windows pop-up shows the error…

Hello @mohan_n ,

Thank you for this valuable tip. It works well for me.
I have a question, though.
Is it possible to exclude data from a specific question while syncing with Google Sheet? My KoBo form has a repeated group and I would like to exclude it because data from that repeated group creates additional columns. So, I cannot connect the Google Sheet as data source for Google Data Studio since the number of columns is not fixed.
I hope my question is clear.
Thanks

Chan