Google Sheets via Zapier


Commcare now has basic Zapier integration, allowing you to trigger any Zapier action off of incoming CommCare form submissions. This lets do all kinds of things based on your CommCare data - including syncing to Google Sheets, triggering emails, and connecting to hundreds of other applications supported by Zapier.

The following document will walk you through how to setup a trigger that inputs data into Google Sheets. 

Prerequisite

Before getting started, you will need to follow the instructions at to set up your CommCare action.

Once you have set up your CommCare trigger, you should follow these instructions to integrate with Google Sheets. 

Setup Google Sheet

Before we begin to create a zap, we should set up a Google Sheet where we will collect the data.

  1. Proceed to Google Sheets

  2. Create a new sheet

  3. Open the sheet

  4. Select one of the tabs in the sheet and enter the following text in the first row of the sheet:

caseid

casename

timestart

timeend

username

These are the basic fields we will be collecting data for in this integration. You can create headers for any piece of data that your form collects. During the setup of the Zap, you will be given the option to map data collected by your form to the headers you set up in your google sheet. Be sure to name the sheet something you can easily remember or find, as you will need it later in the setup.

Now that our sheet is set up to collect data, we can set up our zap!

Setup Action

  1. After setting up the trigger, you should be prompted to add an Action. Select to add one.

  2. Choose Google Sheets.

  3. For an action, select Create Spreadsheet Row. Select ‘Save + Continue.

  4. You should now be on Select Google Sheets Account. Click Connect a New Account and allow Zapier access. When you click continue, you will be prompted to link the Spreadsheet and Worksheet you've created.

  5. On the Set Up Template page, select the Spreadsheet dropdown. Select the spreadsheet you just set up.

  6. Select the worksheet that you added the headers to. The headers should now appear as drop down fields beneath the worksheet field.

  7.  

image-20240702-185602.png

  1. Proceed to match up the column headers as follows: 

    image-20240702-190620.png

    1. username = Form Meta Username

    2. timestart = Form Time Start

    3. timeend = Form Time End

    4. caseid = Form Case case ID

    5. case_name = Form Case Create Case Name

  2. After matching up the fields, select continue.

  3. You should be brought to a page where you can test the integration. Select Create and Continue.

  4. You should receive a notification that the action was successful or not.

    1. If successful, proceed to the spreadsheet and confirm it now has some data in it!

    2. If unsuccessful, please review the steps to ensure everything is setup correctly.

  5. After receiving the notification that the action was successful, select Finish.

  6. Turn on your zap!

Congratulations! You've now set up your CommCare zap! You should now be able to submit forms and have them populate in the google sheet that was setup.

You can also set up CommCare to trigger a number of actions, so feel free to try others besides Google Sheets!