ODK Central to Google Sheets

Development
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