Using Google Apps script to sync data from Kobo server (or any other) with Google Sheets

Toon_Driesen · November 30, 2016, 7:19am

Hi all,

I'm looking for a practical way to sync the data on my KoboToolbox server
with Google Sheets to create real-time dashboards. I want to figure out if
it's possible to create such a sync using Google Apps script.

Any leads here?

Thanks!
Toon

Ronald_Munjoma · November 30, 2016, 7:33am

Hi Toon,

Ona.io is using Google Sheets, not sure how they are doing it. Hope this
might be of help to you: https://help.ona.io/guides/onaapps/#google-sheets

Best and Regards
Ronald Munjoma

···

On 30 November 2016 at 09:19, Toon Driesen wrote:

Hi all,

I'm looking for a practical way to sync the data on my KoboToolbox server
with Google Sheets to create real-time dashboards. I want to figure out if
it's possible to create such a sync using Google Apps script.

Any leads here?

Thanks!
Toon

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Best and Regards
Ronald Munjoma
+263 775 625 283

[image: --]

Ronald Munjoma
[image: https://]about.me/ronaldmunjoma
https://about.me/ronaldmunjoma?promo=email_sig

Tino_Kreutzer · November 30, 2016, 2:28pm

Hi Toon,
There's a way to do it with KoBoToolbox as well via the API, see

github.com

kobotoolbox/kobocat-googleapps-scripts/blob/master/Working_Examples/README.md

The Update_Spreadsheet working example uses token authentication to access the kobocat API v1 allowing users to update all data from within Google Spreadsheets. The script can be run through the added menu item. 

## Known Issues:
1. This script assumes all headers are in the first row. This script does not work on blank worksheets because it compares the headers from the sheet to the information received from kobocat
1. At the moment, the entire sheet is replaced. A Future improvement would be to run through the '_id' column of the sheet, compare it to the kobocat server and only download the instances that are missing. 
1. Blank fields from the kobocat server are tagged as 'Undefined' 


## Installation:
1. Export your (blank) dataset as an XLS file
1. Navigate to Google Drive, upload the XLS file, then click Open in Google Sheets to edit it
1. Click 'Tools>Script Editor...'. If you are presented with a welcome screen, click Blank Project.
1. In a separate tab open our import script and copy the entire code to your clipboard. Choose the right file depending on your server:  For kc.humanitarianresponse.info use [this file](Update_spreadsheet_hr_info) | For kc.kobotoolbox.org use [this file](Update_Spreadsheet) 
1. Back in your Google Sheet, delete any code in the script editor and paste in the code (see previous step) into the editor.
1. Click the menu item Run>OnOpen.
1. Select the menu item File > Save all. Name your new script "Update from KoBoToolbox" and click OK.
1. Complete the authorization and run the function again
1. A new menu item will appear called 'Update from kobocat'

This file has been truncated. show original

Tino

···

On Wed, Nov 30, 2016 at 2:34 AM ronald munjoma wrote:

Hi Toon,

Ona.io is using Google Sheets, not sure how they are doing it. Hope this
might be of help to you: https://help.ona.io/guides/onaapps/#google-sheets

Best and Regards
Ronald Munjoma

On 30 November 2016 at 09:19, Toon Driesen toondriesen@gmail.com wrote:

Hi all,

I'm looking for a practical way to sync the data on my KoboToolbox server
with Google Sheets to create real-time dashboards. I want to figure out if
it's possible to create such a sync using Google Apps script.

Any leads here?

Thanks!
Toon

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Best and Regards
Ronald Munjoma
+263 775 625 283

[image: --]

Ronald Munjoma
[image: https://]about.me/ronaldmunjoma
https://about.me/ronaldmunjoma?promo=email_sig

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Toon_Driesen · December 2, 2016, 5:16am

Thank Tino! I will try and let you know.

···

On Wednesday, 30 November 2016 17:28:59 UTC+3, Tino Kreutzer wrote: > > Hi Toon, > There's a way to do it with KoBoToolbox as well via the API, see > https://github.com/kobotoolbox/kobocat-googleapps-scripts/blob/master/Working_Examples/README.md > > Tino > > On Wed, Nov 30, 2016 at 2:34 AM ronald munjoma <sim...@gmail.com > wrote: > >> Hi Toon, >> >> Ona.io is using Google Sheets, not sure how they are doing it. Hope this >> might be of help to you: >> https://help.ona.io/guides/onaapps/#google-sheets >> >> Best and Regards >> Ronald Munjoma >> >> On 30 November 2016 at 09:19, Toon Driesen <toond...@gmail.com > wrote: >> >>> Hi all, >>> >>> I'm looking for a practical way to sync the data on my KoboToolbox >>> server with Google Sheets to create real-time dashboards. I want to figure >>> out if it's possible to create such a sync using Google Apps script. >>> >>> Any leads here? >>> >>> Thanks! >>> Toon >>> >>> -- >>> -- >>> Post: opend...@googlegroups.com >>> Unsubscribe: opendatakit...@googlegroups.com >>> Options: http://groups.google.com/group/opendatakit?hl=en >>> >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "ODK Community" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to opendatakit...@googlegroups.com . >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> >> >> -- >> Best and Regards >> Ronald Munjoma >> +263 775 625 283 >> >> >> [image: --] >> >> Ronald Munjoma >> [image: https://]about.me/ronaldmunjoma >> >> >> -- >> -- >> Post: opend...@googlegroups.com >> Unsubscribe: opendatakit...@googlegroups.com >> Options: http://groups.google.com/group/opendatakit?hl=en >> >> --- >> You received this message because you are subscribed to the Google Groups >> "ODK Community" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to opendatakit...@googlegroups.com . >> For more options, visit https://groups.google.com/d/optout. >> >

Ahmed_Hassan · August 27, 2017, 6:36am

Hello Tino,

I tested it and it was worked, but Manually,
I mean((You must click on the Kubotoolbox button and then click on the entry then "Select the survey(s) to import" then choose "Import data into which sheet?" finally "Import survey data" for update the data from Kobo to excel sheet)).
But, I need automaticly, do you have any ways

and this is image for the file that I used

and the file attached
KoboToolbox-GoogleApps-Scripts-master (1).zip (14.9 KB)

Note: I used

> =='basic'

thank you
Ahmed

Ahmed_Hassan · August 27, 2017, 6:39am

Hello Ronald,

thank you for your note,
but ona.io is not free for use google sheet.

best regards

Toon_Driesen · August 27, 2017, 9:00am

Thanks Admed, this is already very helfpful. I hope we'll find an automated
solution soon.
Toon

pierre · December 22, 2017, 7:11pm

Hi Ahmed,

I have seen your post and your were looking to automate the sync between Kobotoolbox and Google Sheet.
In case you did not find the answer, I think I have the answer to your request.
You will have to follow the following steps:
1- in your Google Sheet were the Kobo GSheet SYnc in running, go to Tools/Script editor
2- Once in the script editor, click on the clock icon
3- Add a new trigger
4- Set-up your trigger

And it should work. Good luck!

azam_iqbal · December 23, 2017, 3:46am

Hi Everyone,
Sorry for bad English
Please make a complete video related to this topic because everyone want to real time dashboard through Google Apps Scripts and it also free,Another way to define step by step code.
I know you can do help for everyone.

pierre · March 20, 2018, 9:35pm

Hi!
I do not have any video to offer, but there is a good resource here:

I hope this will help.

simond · August 22, 2018, 7:52am

Hello Pierre and all,

thank you for the information. With the TdH document I managed to link the Kobo account to a google sheet, it works very well!

I am a bit stuck for automating the refreshing of the data. After adding a trigger, I believe we need to code something like clear the tab and pull the data from this survey.

Unfortunately it exceeds my coding competencies, would you know how to do it?

Thanks a lot!

yanokwa · August 22, 2018, 5:38pm

Since this question has to do with Kobo, your best bet is to ask the Kobo team at http://help.kobotoolbox.org!