CommCare to Google Sheets Integration

This page outlines how to integrate CommCare data directly into Google Sheets using Google Apps Script. This method allows you to pull case data from one or multiple CommCare projects into a Google Sheet, automatically updating existing rows when data changes ("upserting") and adding new rows as cases are created.

1 CommCare to Google Sheets Integration
- 1.1 Overview
- 1.2 Prerequisites
- 1.3 Method 1: Single Project Sync
  - 1.3.1 Setup Instructions
  - 1.3.2 The Script
- 1.4 Method 2: Multi-Project Robust Sync
  - 1.4.1 Setup Instructions
  - 1.4.2 The Script
- 1.5 Mapping Custom Case Properties
  - 1.5.1 Understanding c. vs p.
  - 1.5.2 Step-by-Step Instructions
    - 1.5.2.1 1. Add the Column Header
    - 1.5.2.2 2. Map the Data
    - 1.5.2.3 3. Save and Refresh
  - 1.5.3 Troubleshooting
- 1.6 Automating the Sync

Overview

While CommCare does not have a native "one-click" integration with Google Sheets, you can achieve a robust, automated connection using CommCare's standard API and Google's built-in scripting tools.

This approach allows you to:

Pull Case Data Live: Fetch case properties directly from your project space.
Sync Automatically: Set up triggers to refresh data hourly or daily.
Upsert Data: The script intelligently checks if a case ID already exists in your sheet. If it does, it updates the row; if not, it adds a new one. This ensures you can "top up" your data without wiping the sheet every time.

Prerequisites

Before setting up the script, you will need:

A CommCare API Key: You can generate this in CommCare HQ under My Account Settings > API Keys.
Project Space Name: This is the project identifier found in your URL (e.g., www.commcarehq.org/a/[project_space_name]/...).
A Google Account: Access to Google Sheets.

Method 1: Single Project Sync

This script is best if you need to pull data from a single CommCare project into one sheet.

Setup Instructions

Open a new Google Sheet.
Navigate to Extensions > Apps Script.
Delete any code in the Code.gs file and paste the script below.
Update the Configuration section at the top with your specific CC_PROJECT_SPACE, CC_USERNAME, and CC_API_KEY.
Click Save (floppy disk icon).
Run the topUpCommCareData function to test the sync.

The Script

function topUpCommCareData() {
  // --- CONFIGURATION ---
  var CC_PROJECT_SPACE = 'YOUR_PROJECT_SPACE_NAME_HERE';
  var CC_USERNAME = 'YOUR_EMAIL_HERE';
  var CC_API_KEY = 'YOUR_API_KEY_HERE';

  // --- API SETUP ---
  var limit = 100;
  var offset = 0;
  var baseUrl = 'https://www.commcarehq.org/a/' + CC_PROJECT_SPACE + '/api/v0.5/case/';

  var headers = {
    "Authorization": "Basic " + Utilities.base64Encode(CC_USERNAME + ":" + CC_API_KEY)
  };

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 1. READ EXISTING DATA INTO A MAP FOR FAST LOOKUP
  var lastRow = sheet.getLastRow();
  var existingMap = {};

  if (lastRow > 1) {
    var ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
    for (var i = 0; i < ids.length; i++) {
      existingMap[ids[i][0]] = i + 2;
    }
  } else {
    // Added "Last Modified" and "All Properties" to match the robust script
    sheet.appendRow(["Case ID", "Case Name", "Owner ID", "Status", "Date Opened", "Last Modified", "All Properties (JSON)"]);
  }

  var hasMore = true;

  while (hasMore) {
    var url = baseUrl + "?limit=" + limit + "&offset=" + offset;

    var response = UrlFetchApp.fetch(url, {
      "method": "GET",
      "headers": headers,
      "muteHttpExceptions": true
    });

    if (response.getResponseCode() !== 200) {
      Logger.log("Error: " + response.getContentText());
      break;
    }

    var data = JSON.parse(response.getContentText());
    var cases = data.objects;

    if (!cases || cases.length === 0) {
      hasMore = false;
      break;
    }

    var newRows = [];

    for (var i = 0; i < cases.length; i++) {
      var c = cases[i];
      var p = c.properties || {};

      var rowData = [
        c.id || c.case_id,
        p.case_name || c.case_name,
        c.user_id || p.owner_id,
        c.closed ? "Closed" : "Open",
        c.date_opened || p.date_opened,
        c.server_last_modified,
        JSON.stringify(p) // Dumps ALL properties so you can see field names
      ];

      var caseId = rowData[0];

      if (existingMap[caseId]) {
        // UPDATE existing row
        var rowNum = existingMap[caseId];
        sheet.getRange(rowNum, 1, 1, rowData.length).setValues([rowData]);
      } else {
        // QUEUE for append
        newRows.push(rowData);
      }
    }

    if (newRows.length > 0) {
      sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
      
      var newStartRow = sheet.getLastRow() - newRows.length + 1;
      for (var j = 0; j < newRows.length; j++) {
        existingMap[newRows[j][0]] = newStartRow + j;
      }
    }

    if (data.meta && data.meta.next) {
      offset += limit;
    } else {
      hasMore = false;
    }
  }
}

Method 2: Multi-Project Robust Sync

This advanced script is designed for syncing multiple CommCare projects into separate tabs within a single Google Sheet. It includes additional columns for Last Modified dates and a All Properties (JSON) dump to ensure you catch every data change, even for new case properties not explicitly mapped.

Setup Instructions

Open a new Google Sheet.
Navigate to Extensions > Apps Script.
Paste the script below.
Update the CC_USERNAME and CC_API_KEY.
In the DOMAINS_TO_SYNC list, add as many projects as you need. For each, specify the projectSpace (from the URL) and the sheetName (the tab name you want created).
Run the pullMultipleDomainsRobust function.

The Script

function pullMultipleDomainsRobust() {
  // --- CONFIGURATION ---
  var CC_USERNAME = 'YOUR_EMAIL_HERE';
  var CC_API_KEY = 'YOUR_API_KEY_HERE';

  var DOMAINS_TO_SYNC = [
    { projectSpace: 'project-alpha', sheetName: 'Alpha Data' },
    { projectSpace: 'project-beta',  sheetName: 'Beta Data' }
  ];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var authHeader = "Basic " + Utilities.base64Encode(CC_USERNAME + ":" + CC_API_KEY);

  for (var d = 0; d < DOMAINS_TO_SYNC.length; d++) {
    var config = DOMAINS_TO_SYNC[d];
    syncSingleDomainRobust(ss, config.projectSpace, config.sheetName, authHeader);
  }
}

function syncSingleDomainRobust(ss, projectSpace, sheetName, authHeader) {
  Logger.log("Starting sync for: " + projectSpace);

  // 1. Get or Create Sheet
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    // Includes "Last Modified" and "All Properties" for robust auditing
    sheet.appendRow(["Case ID", "Case Name", "Owner ID", "Status", "Date Opened", "Last Modified", "All Properties (JSON)"]);
  }

  // 2. Map Existing IDs to Row Numbers
  var lastRow = sheet.getLastRow();
  var existingMap = {};

  if (lastRow > 1) {
    var ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
    for (var i = 0; i < ids.length; i++) {
      existingMap[ids[i][0]] = i + 2;
    }
  }

  // 3. API Fetch Loop
  var limit = 100;
  var offset = 0;
  var hasMore = true;
  var baseUrl = 'https://www.commcarehq.org/a/' + projectSpace + '/api/v0.5/case/';

  while (hasMore) {
    var url = baseUrl + "?limit=" + limit + "&offset=" + offset;

    var response = UrlFetchApp.fetch(url, {
      "method": "GET",
      "headers": { "Authorization": authHeader },
      "muteHttpExceptions": true
    });

    if (response.getResponseCode() !== 200) {
      Logger.log("Error syncing " + projectSpace + ": " + response.getContentText());
      return;
    }

    var data = JSON.parse(response.getContentText());
    var cases = data.objects;

    if (!cases || cases.length === 0) {
      hasMore = false;
      break;
    }

    var newRows = [];

    for (var i = 0; i < cases.length; i++) {
      var c = cases[i];
      var p = c.properties || {};

      var rowData = [
        c.id || c.case_id,
        p.case_name || c.case_name,
        c.user_id || p.owner_id,
        c.closed ? "Closed" : "Open",
        c.date_opened || p.date_opened,
        c.server_last_modified,
        JSON.stringify(p)
      ];

      var caseId = rowData[0];

      if (existingMap[caseId]) {
        // Force update existing row with fresh data
        var rowNum = existingMap[caseId];
        sheet.getRange(rowNum, 1, 1, rowData.length).setValues([rowData]);
      } else {
        // Queue new row
        newRows.push(rowData);
      }
    }

    if (newRows.length > 0) {
      sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, newRows[0].length).setValues(newRows);

      var newStartRow = sheet.getLastRow() - newRows.length + 1;
      for (var j = 0; j < newRows.length; j++) {
        existingMap[newRows[j][0]] = newStartRow + j;
      }
    }

    if (data.meta && data.meta.next) {
      offset += limit;
    } else {
      hasMore = false;
    }
  }
  Logger.log("Finished sync for: " + projectSpace);
}

Mapping Custom Case Properties

By default, the script includes a column called All Properties (JSON) which dumps all your data into one cell. However, you will likely want to pull specific custom fields (like village_name, patient_age, or risk_score) into their own dedicated columns for sorting and filtering.

To do this, you need to modify two specific parts of the script: the Headers and the Row Data.

Understanding `c.` vs `p.`

In the script, you will see variables named c and p. It is critical to use the right one when mapping fields:

c (Case Object): Represents the top-level system data. Use this for built-in CommCare fields like c.case_id, c.closed, c.date_opened, or c.server_last_modified.
p (Properties Object): Represents your custom case properties. Use this for all data you defined in your application, like p.village, p.child_name, or p.risk_score.

Step-by-Step Instructions

1. Add the Column Header

Find the line starting with sheet.appendRow. Add your new column names to this list.

// BEFORE
sheet.appendRow(["Case ID", "Case Name", "Owner ID", "Status", "Date Opened", "Last Modified", "All Properties (JSON)"]);

// AFTER (Adding "Village" and "Risk Score")
sheet.appendRow(["Case ID", "Case Name", "Owner ID", "Status", "Date Opened", "Last Modified", "Village", "Risk Score", "All Properties (JSON)"]);

2. Map the Data

Scroll down to the loop where var rowData is defined. You must add the data fields in the exact same order as your headers.

// Inside the loop...
var rowData = [
  c.id || c.case_id,                 
  p.case_name || c.case_name,        
  c.user_id || p.owner_id,           
  c.closed ? "Closed" : "Open",
  c.date_opened || p.date_opened,
  c.server_last_modified,
  
  // --- NEW CUSTOM FIELDS START HERE ---
  p.village_name || "",  // Use 'p.' for custom properties. || "" handles empty values.
  p.risk_score || 0,     // Use 'p.' here too. || 0 handles missing numbers.
  // ------------------------------------

  JSON.stringify(p)      // Keep the JSON dump at the end as a backup
];

3. Save and Refresh

Save your script.
Delete the existing Header Row (Row 1) in your Google Sheet.
- Note: If you don't delete the old header row, the columns might not align correctly on the next run.
Run the function again. The script will re-populate the sheet with your new columns included.

Troubleshooting

I don't know the exact property name!

If you aren't sure if your property is named village or village_name, look at the All Properties (JSON) column in your Google Sheet. This shows the raw data names exactly as CommCare sends them. Always use that exact spelling after the p..

Automating the Sync

To keep your data fresh without manual intervention, set up a trigger:

In the Apps Script editor, click the Triggers (Clock icon) on the left sidebar.
Click + Add Trigger.
Select your function (topUpCommCareData or pullMultipleDomainsRobust).
Set the event source to Time-driven.
Choose your frequency (e.g., Hour timer -> Every hour).
Click Save.

Your Google Sheet will now automatically pull fresh data from CommCare on that schedule.

Google Sheets Integration

CommCare to Google Sheets Integration

Overview

Prerequisites

Method 1: Single Project Sync

Setup Instructions

The Script

Method 2: Multi-Project Robust Sync

Setup Instructions

The Script

Mapping Custom Case Properties

Understanding c. vs p.

Step-by-Step Instructions

1. Add the Column Header

2. Map the Data

3. Save and Refresh

Troubleshooting

Automating the Sync

Understanding `c.` vs `p.`