9.2.4 Google Sheets

TABLE OF CONTENTS

• Installation
• Authentication
• Importing the data
• Google Sheets Pivot table example

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…

Even though it works with most of the datatypes, there may be cases where additional support is required.

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”

   

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

   

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

   

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

   

   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

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

   

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

   

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’

   

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.

   

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

   

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

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

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:

   

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

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…”

   

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

   

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