9.2.4 Google Sheets


TABLE OF CONTENTS
Note: This tutorial focuses exclusively on linking KoBoToolbox to Google Sheets. The procedure may vary depending on the mobile data collection platform (server) you use.
Linking your KoBoToolbox account to a Google Sheets is a very convenient way to share the data collected during a survey. Giving access to a shared Google Sheets will allow more people to read and work with your KoBoToolbox dataset without giving direct access to your KoBoToolbox account.
It also offers the possibility to perform advanced analyses unavailable in the KoboToolbox with the latest data collected, such as producing pivot tables, compiling data from different surveys in one single Google Sheets…
The import of the KoBoToolbox dataset into a Google spreadsheet described in this tutorial is based on a third-party procedure that can be found here.
Even though it works with most of the datatypes, there may be cases where additional support is required.
Warning: The operation worked when using Google Chrome and Mozilla Firefox. It is recommended to use one of these browsers for the entire process (the import did not work using Internet Explorer). Some of the steps described involves the manipulation of scripts/coding therefore it installation may be suitable only for users at ease with IT.
If you don’t use any server, it is possible to link ODK Collect directly to a Google Sheets. Humanitarian Data Solutions made a video on how to do it : Link ODK Collect Directly to Google Sheets in Google Drive
Installation
To share/export data from your KoBoToolbox database to a Google spreadsheet, you will need to create a Google account (if you don’t have one yet), then:
- Run Google drive
-
Click on “My Drive” then “Google Sheets” and “Blank spreadsheet”
-
The blank spreadsheet will open automatically. Rename it (e.g. Kobo_Google). Space or underscore can be used. Avoid any other special character.
-
Select the “Tools” menu and click on “Script editor…”
-
Several scripts are required for the operation to work. They are available for download here
The scripts can be found in the folders “demo” and “src” but it is recommended to download all (Download zip) by clicking on “Clone or download”:
In the event you can’t access the afore mentioned link, here (ADD LINK) is the zipped folder (as of February 14th 2018, files may evolve over time):
Unzip the folder to have access to the files
-
In the Script editor page of your blank Google spreadsheet, you will need to add all the scripts of the package. To do so, you first have to create New blank scripts and paste the content of the scripts downloaded into the newly created scripts. Here are the steps more in details: File > New > Script file
-
Name each script file with the names used in the package (e.g. KoBoToolbox.gs, Main.gs, etc.). Keep the same spelling and letter case (including the extension).
- If you don’t have one, download and install a software to read .gs files (e.g. Notepad++)
- Open each file in the package and copy-paste their entire code into the script created in the Google Sheets (e.g. the “Code.gs” code from the package should be copied and pasted into the new “Code.gs” script that you created in the script editor, etc.).
- The same operation should be performed with the two html files: File > New > Html file, name them the same way including the extension (e.g. “UploadForm.html”), then copy-paste their respective code from the two html files in the package.
Authentication
There are two working methods to have the Google Sheets being authenticated when accessing the KoBoToolbox account:
- The basic authentication: the script will require the KoBoToolbox account username and password
- The token authentication: the script will require the token value corresponding to your account
A third method is the OAuth2 authentication, but it is still under development.
To avoid displaying your username and password inside the script, it is recommended to use the token authentication:
- Open the script “Code.gs” of your Google project
- By default, the Token-based authentication is activated in the script (the lines should not be greyed out).
-
Edit the base URL that is used for Humanitarian organizations: baseUrl: ‘https://kobo.humanitarianresponse.info’
-
Find the value of your authToken by entering the following URL in your browser: https://kobo.humanitarianresponse.info/your_kobo_username/api-token Warning: Your KoBoToolbox account needs to be open before requesting the Token.
-
Copy the API token, then paste it into the value of the authToken configuration parameter in the Code.gs script:
-
Save all the changes (File > Save all) and close the window.
Importing the data
Once all the scripts have been prepared according to the procedure, you can import the data from your KoBoToolbox database:
-
Reload the Google Sheets to ensure that the scripts and changes are loaded.
Make sure that the sheet is empty. You can also add a new blank sheet with the “+” symbol at the bottom of the spreadsheet.
A new menu should have appeared: “KoBoToolbox” (there is a small delay until it appears):
-
Select this new menu and click on “Import KoBoToolbox Data into Sheet”. When running this script for the first time, Google will ask for a permission to access your spreadsheet data. Once validated, the script will open the following window:
-
Select the KoBoToolbox survey that you want to import (tick the box) as well as the Google Sheets where you want the data to be imported and click on “Import Survey Data”.
Note: It is possible to select more than one survey to import if their database structure (names and number of columns) is the same.
Warning: Each time you run the script, it will import ALL the data from KoBoToolbox (it is not an update function). If you re-run this process on a sheet that already contains KoBoToolbox survey data, it will append the rows at the end of the sheet each time. To avoid duplicates, it is recommended to use a blank sheet before importing it all.
Google Sheets Pivot table example
Once the Google Sheets link with KoBoToolbox is up and running, the data collected can be accessed by other users depending on the authorization given by the Google account owner (view, edit, comment). On top of that, other typical Google Sheets functions are available to handle the dataset.
The pivot table function helps visualize data in a certain way, by grouping and summarizing them according to your needs. Charts can also be produced based on the pivot table:
{:height="60%" width="70%"}
{: .text-center}
Here are the few steps to use this function in Google Sheets:
- Open your Google Sheets
- Import the data from KoBoToolbox (see previous chapter): KoBoToolbox > Import KoBoToolbox Data into Google Sheets
-
Select the menu “Data” and click “Pivot table…”
- If you haven’t selected your data before clicking “Pivot table…”, Google will ask you which data you want to include.
-
A new sheet will then automatically appear at the bottom of your spreadsheet
-
Select which data to put in Rows, Columns, Values and/or Filters with the pivot table editor:
Warning: To update your data, delete it all (main sheet) and re-run the import process. When you empty the data sheet, the pivot table linked to this dataset will automatically be empty. It will update itself automatically as new data are added (provided that the dataset has the same structure). If you want your pivot table to include newly imported data, remember to include the new rows in your pivot table editor.