Jadu Forms: Send Form Submission Data to a Google Sheet

This guide explains how to automatically send data from a Jadu Central submission to a Google Sheet using a Departmental or Functional Gmail account and a Google Apps Script.

In this article:

• Process Overview
• Step 1:  Create a new Google Account
• Step 2: Set Up Your Google Sheet
• Step 3: Create the Jadu Central Email Template
• Step 4: Add the Script to Your Spreadsheet
• Step 5: Authorize and Run
• Step 6: Set Up an Automatic Trigger
• Tips

Process Overview

A Google Apps Script is used to automate the transfer of data from Jadu email submissions into a Google Sheet. The Jadu submission first sends an email containing the necessary data in the body to a designated/shared email account. The script continuously monitors this inbox, extracting the data from each incoming email and adding it as a new row to the Google Sheet. After an email is successfully processed and its data recorded, the script applies a label to mark it as processed and then archives the email.
 

Note: While this does not export any file attachments, the Google Apps Script could be enhanced to do so.

Step 1: Creating a new Google Account

• This account should be dedicated to processing only the responses from your Jadu Central form. Consider using a functional account if available to you.
• Learn more about different kinds of university accounts.

Step 2: Setting Up Your Google Sheet

1. Log in to the new Google Account.
2. Create a new spreadsheet.
3. The sheet (tab) can either be left with its default name of Sheet1, or you can rename it.
   • Note: the sheet (tab) name is referenced later in the Google Apps Script.
4. Add column headers in row 1 of your sheet to identify the data you will be capturing in the spreadsheet.
   1. Column A will always contain a timestamp of when the row is added, so you must either leave this column header blank or label it as Timestamp.
   2. Add your own headers starting with column B to track the data you will be receiving from the Jadu submission.
      

Step 3: Creating the Jadu Central Email Template

In your Jadu Central Form, create an email template that contains the information you want written to the Google Sheet.

1. In the To* field, add the Gmail address created in Step 1 above.
2. In the Subject* field, enter a unique & descriptive subject. It is recommended to add the Standard variable Form - Reference No to the end of the subject line to keep each email as its own thread.
   • Example: Survey Responses #{{ Form - Reference No }}
3. In the Content* field, add one line per answer you want written to the sheet. Each line should be in the following format:
   1. Label: value where label = the column header in the sheet and value = a name to represent the data in the sheet
   2. For example: Name: Answer - Name (Requester Info page)

Example of the Email template setup 

If your Jadu email format differs, adjust the DATA_LABELS array and the field names in the script to match.

Step 4: Adding the Script to Your Spreadsheet

1. Open your Google Sheet
2. Navigate to Extensions, then Apps Script
   
3. Delete any code in the editor
4. Copy this Google Apps example script, paste it into the editor, and select Save project to Drive 
5. Edit the Configuration Constants values to match your spreadsheet and Email setup.
   1. SPREADSHEET_ID - update with the spreadsheet ID (found in the URL of your Google Sheet)
      
   2. SHEET_NAME - update to match the name of the sheet(tab) data should be written to
   3. EMAIL _SUBJECT - update to include the static portion of the Jadu Email template subject line
      
   4. LABEL_NAME - update to define the name of the Gmail label that will be created and used by the script
      
   5. DATA_LABELS - update to match the labels defined in the Jadu email body and the column headers in your spreadsheet. 
      
      
6. Optionally, you can rename the project to have a more meaningful name.
7. Select Save project to Drive to save your changes

Step 5: Authorizing and Running

1. Click the Run button.
   
2. When running for the first time, Google will ask for permission.
   1. Click through Review Permissions and Allow.
3. Upon running, the script will import any unread Jadu emails in your inbox, add them to the sheet, label them as Imported, and archive them.

Step 6: Setting Up an Automatic Trigger

A trigger tells Google Apps Script how often it should automatically run your import script. This allows the system to check for new Jadu submission emails on a schedule (such as every hour) without you needing to press Run manually. Once the trigger is set, the script will continuously monitor your inbox and import new submissions as they arrive.

For more information, refer to Google's AppsScript Timers documentation.

1. In the Apps Script editor, click the clock icon (Triggers) in the left toolbar.
   
2. Select the Add Trigger button in the lower right corner
3. Update the following settings:
   1. Choose function to run: captureJaduSubmissions
   2. Deployment: Head
   3. Select event source: Time-driven
   4. Select type of time-based trigger:  Hour timer (or other interval as needed)
   5. Select hour interval: Every hour (or other interval as needed)
   6. Failure Notification settings: Notify me daily
      
4. Click Save.

Tips

• Any changes to the column headers in the spreadsheet or email need to be updated in the DATA_LABELS constant within the AppsScript.
• You can adjust the filter that chooses which emails to process by editing EMAIL_SUBJECT variable in the Google Apps Script.
• To see processed emails, just open the label in Gmail that was created for this process.