Link Search Menu Expand Document
Mobile Data Collection toolbox

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:

  1. Run Google drive
  2. Click on “My Drive” then “Google Sheets” and “Blank spreadsheet”

    image info

  3. The blank spreadsheet will open automatically. Rename it (e.g. Kobo_Google). Space or underscore can be used. Avoid any other special character.

    image info

  4. Select the “Tools” menu and click on “Script editor…”

    image info

  5. 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”:

    image info

    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):

    image info

    Unzip the folder to have access to the files

  6. 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

    image info

  7. 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).

    image info

  8. If you don’t have one, download and install a software to read .gs files (e.g. Notepad++)
  9. 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.).
  10. 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:

  1. The basic authentication: the script will require the KoBoToolbox account username and password
  2. 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:

  1. Open the script “Code.gs” of your Google project
  2. By default, the Token-based authentication is activated in the script (the lines should not be greyed out).
  3. Edit the base URL that is used for Humanitarian organizations: baseUrl: ‘https://kobo.humanitarianresponse.info’

    image info

  4. 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.

    image info

  5. Copy the API token, then paste it into the value of the authToken configuration parameter in the Code.gs script:

    image info

  6. Save all the changes (File > Save all) and close the window.

    image info

Importing the data

Once all the scripts have been prepared according to the procedure, you can import the data from your KoBoToolbox database:

  1. 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): image info

  2. 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:

    image info

  3. 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:

![image info](../../../assets/images/toolbox7/9_analysing_mdc/9_2_tool_focus/9_2_4_google_sheet_pivot_table.jpg){:height="60%" width="70%"}
{: .text-center}

Here are the few steps to use this function in Google Sheets:

  1. Open your Google Sheets
  2. Import the data from KoBoToolbox (see previous chapter): KoBoToolbox > Import KoBoToolbox Data into Google Sheets
  3. Select the menu “Data” and click “Pivot table…”

    image info

  4. If you haven’t selected your data before clicking “Pivot table…”, Google will ask you which data you want to include.
  5. A new sheet will then automatically appear at the bottom of your spreadsheet

    image info

  6. Select which data to put in Rows, Columns, Values and/or Filters with the pivot table editor:

    image info

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.

image info