ODK Central to Google Sheets

Development
1

What is the simplest way to pull data directly from ODK Central to Google Sheets?

2

Hi Tony,

Currently there is no out-of-the-box way to pull data into Google Sheets. Google has recently added Data Connectors for BigQuery and Looker, they might build a connector for general APIs, till then you have two options:

  1. Manually export CSV from Central and import it in Google Sheets
  2. Rely on third-party add-ons

:warning: I don't endorse any third-party add-ons, use it at your own risk. Make sure to read the terms and conditions + privacy agreement before installing any add-ons

I have just tried API Connector, it does the job

image
image859×1017 72.4 KB

3

HI. Thanks for your reply. Would it be possible to do a quick step by step guide to link API Connector to Central an pull the headers and data in?

Thanks

4

Something that I thought would take some serious time was solved by chatGPT in one prompt.

Step by Step Instructions

  1. Open a new Google Sheet
  2. Extensions -->App Script
  3. Copy and Paste the following code in code.gs
  4. Replace the 4 const values in lines 2-5 with your values
  5. Run Code and approve permissions
  6. Check your google sheet and celebrate! :raising_hands:

I haven't used this extensively but it works for my basic test!

// CONFIGURATION
const ODK_URL = 'https://your-odk-server.org/v1/projects/1/forms/myform/submissions.csv';
const ODK_USERNAME = 'yourusername';
const ODK_PASSWORD = 'yourpassword';
const SHEET_NAME = 'ODK Data'; // Sheet name to write to

function importODKData() {
  const encodedCreds = Utilities.base64Encode(ODK_USERNAME + ':' + ODK_PASSWORD);

  const options = {
    method: 'get',
    headers: {
      'Authorization': 'Basic ' + encodedCreds
    },
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(ODK_URL, options);
  const code = response.getResponseCode();

  if (code !== 200) {
    Logger.log("Error fetching data: " + code + " - " + response.getContentText());
    return;
  }

  const csvData = Utilities.parseCsv(response.getContentText());
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME) || 
                SpreadsheetApp.getActiveSpreadsheet().insertSheet(SHEET_NAME);

  sheet.clearContents();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
2 Likes