How to integrate Gravity Forms with Google Spreadsheets:

Pradeep Maurya

9 years ago

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 .

There are step by step process to integrate Gravity Forms with Google Spreadsheets:

Step1-> Open the google spreadsheet where you wish to save the form data

Step2-> create a header row with the column names for all the fields that you wish to save from Gravity Forms.

Step3-> go to Tools->Script Editor

step4-> paste the following code


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 
 
  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();
}

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

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();

}

——–
Step5-> Save the Google Script

Step6-> Goto RUN Menu and choose doPost

Step7-> Choose Publish, Deploy as web app

Step8-> Click Save New Version, Set access as Anyone, even anonymous

Step9-> Click Deploy

Step10-> Copy the Google Script URL as we will need it in the WordPress

Step11-> Open the wordpress function.php file and paste following code


<?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));
}
?>

<?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));

}

Step12-> Save the PHP file and submit a test entry. It should show up data in your Google Spreadsheet.

Pradeep Maurya

Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co