Send Gravity Forms Data to Google Spreadsheet or Email

May 26, 2017

Gravity Forms is an extremely popular Forms plugin for the WordPress. When someone submits a form created with Gravity Forms, the form data is saved inside the MySQL database associated with your WordPress installation. There are paid add-ons through, Zapier for example, that let you do more when someone submits a Form.

For instance, you can setup a task in Zapier that will automatically save the Gravity Form data to a specific Google Spreadsheet. Or you can setup a rule where the data is emailed to you as soon as a form is submitted.

Zapier offers a visual tool to maps your Gravity Forms to Google Spreadsheets but you can do something similar with Google Apps Script and WordPress hooks without needing to subscribe to Zapier. Let me show you how:

From Gravity Forms to Google Spreadsheets

First we need to create a web-app with Google Scripts that will receive the Form data from Gravity Forms and either save it to Google Sheets or send it via Gmail. Also see: Get Google Forms Data in Email.

Open the Google Spreadsheet where you wish to save the Forms data and create a header row with the column names for all the fields that you wish to save from Gravity Forms. Next go to Tools, Script Editor and paste the following snippet.

function doPost(e) {
  if (!e) return;

  var sheetID = 'GOOGLE_SPREADSHEET_ID'; // Replace this with the Google Spreadsheet ID
  var sheetName = 'Form Responses'; // Replace this with the sheet name inside the Spreadsheet

  var status = {};

  // Code based on Martin Hawksey (@mhawksey)'s snippet

  var lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(sheetName);
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

    // Add the data and time when the Gravity Form was submitted
    var column,
      row = [],
      input = {
        timestamp: new Date(),
      };

    for (var keys in e.parameter) {
      input[normalize_(keys)] = e.parameter[keys];
    }

    for (i in headers) {
      column = normalize_(headers[i]);
      row.push(input[column] || '');
    }

    if (row.length) {
      sheet.appendRow(row);
      status = {
        result: 'success',
        message: 'Row added at position ' + sheet.getLastRow(),
      };
    } else {
      status = {
        result: 'error',
        message: 'No data was entered',
      };
    }
  } catch (e) {
    status = {
      result: 'error',
      message: e.toString(),
    };
  } finally {
    lock.releaseLock();
  }

  return ContentService.createTextOutput(JSON.stringify(status)).setMimeType(ContentService.MimeType.JSON);
}

function normalize_(str) {
  return str.replace(/[^\w]/g, '').toLowerCase();
}

Save the Google Script. Go to the Run menu and choose doPost to authorize the Google Scripts. Next choose Publish, Deploy as web app and save your work. Click Save New Version, set access as Anyone, even anonymous and click Deploy. Make a note of the Google Script URL as we will need it in the WordPress snippet.

From WordPress to Google Spreadsheets

Now we need to write an Action Hook on WordPress side that will send the data to Google Script which will then save the data to Google Spreadsheet. Go your WordPress theme folder and paste this snippet inside your functions.php file.

<?php

/* Replace XXX with your Gravity Form ID. e.g., gform_after_submission_2 for Form 2 */
add_action('gform_after_submission_XXX', 'add_to_google_spreadsheet', 10, 2);

function add_to_google_spreadsheet($entry, $form) {

    // This is the web app URL of your Google Script create in previous step
    $post_url = 'https://script.google.com/macros/s/XYZ/exec';

    // Put all the form fields (names and values) in this array
    $body = array('name' => rgar($entry, '1'), 'age' => rgar($entry, '2'), 'sex' => rgar($entry, '3'),);

    // Send the data to Google Spreadsheet via HTTP POST request
    $request = new WP_Http();
    $response = $request->request($post_url, array('method' => 'POST', 'sslverify' => false, 'body' => $body));
}
?>

Save the PHP file and submit a test entry. It should show up in your Google Spreadsheet instantly.

Google Apps Script Google Sheets WordPress

ShareWhatsAppTwitterFacebook

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory