Categories
Technology

Elementor Form Data to Google Sheets without Zapier

Easiest way to send the Elementor forms’ data to the Google Sheets.

Yes, you can really send the Elementor Form data to Google Sheets.

For this to work, you will have to create a custom script in the Google Sheets’ Script Editor option and use the Webhook feature in the Elementor form.

Here, I will explain each and every step to send your Elementor form data to Google Sheets without using any third-party tools like Zapier

Elementor Form Data to Google Sheets

1. Create a new Google Sheet

Create a new Google Sheet by going to Google Drive and clicking on the new blank spreadsheet, or just by simply going to the sheets.new URL.

And, name your new Google spreadsheet anything you like.

2. Add the Custom Script

Now, here’s the real magic.

Move to the Script Editor by clicking on the Script editor under Tools drop-down and name the project anything you like.

Creating a New Google Sheets and Going to Script Editor

Delete any existing code and copy-paste the below code (by bainternet) in the area:

/**
* Google app-script to utilise Elementor Pro Froms webhook
* For Usage see: https://github.com/pojome/elementor/issues/5894
*/
/*
In order to enable this script, follow these steps:
* From your Google Sheet, from the Tools menu select Script Editor…
* Paste the script from this gist into the script code editor and hit Save.
* From the Publish menu, select Deploy as web app…
* Choose to execute the app as yourself, and allow Anyone, even anonymous to execute the script. (Note, depending on your Google Apps instance, this option may not be available. You will need to contact your Google Apps administrator, or else use a Gmail account.) Now click Deploy. You may be asked to review permissions now.
* The URL that you get will be the webhook that you can use in your elementor form, You can test this webhook in your browser first by pasting it. It will say "Yepp this is the webhook URL, request received".
*/
// Change to true to enable email notifications
var emailNotification = false;
var emailAddress = "Change_to_your_Email";
// DO NOT EDIT THESE NEXT PARAMS
var isNewSheet = false;
var recivedData = [];
/**
* this is a function that fires when the webapp receives a GET request
* Not used but required.
*/
function doGet( e ) {
return HtmlService.createHtmlOutput( "Yepp this is the webhook URL, request received" );
}
// Webhook Receiver – triggered with form webhook to pusblished App URL.
function doPost( e ) {
var params = JSON.stringify(e.parameter);
params = JSON.parse(params);
insertToSheet(params);
// HTTP Response
return HtmlService.createHtmlOutput( "post request received" );
}
// Flattens a nested object for easier use with a spreadsheet
function flattenObject( ob ) {
var toReturn = {};
for ( var i in ob ) {
if ( ! ob.hasOwnProperty( i ) ) continue;
if ( ( typeof ob[ i ] ) == 'object' ) {
var flatObject = flattenObject( ob[ i ] );
for ( var x in flatObject ) {
if ( ! flatObject.hasOwnProperty( x ) ) continue;
toReturn[ i + '.' + x ] = flatObject[ x ];
}
} else {
toReturn[ i ] = ob[ i ];
}
}
return toReturn;
}
// normalize headers
function getHeaders( formSheet, keys ) {
var headers = [];
// retrieve existing headers
if ( ! isNewSheet ) {
headers = formSheet.getRange( 1, 1, 1, formSheet.getLastColumn() ).getValues()[0];
}
// add any additional headers
var newHeaders = [];
newHeaders = keys.filter( function( k ) {
return headers.indexOf( k ) > 1 ? false : k;
} );
newHeaders.forEach( function( h ) {
headers.push( h );
} );
return headers;
}
// normalize values
function getValues( headers, flat ) {
var values = [];
// push values based on headers
headers.forEach( function( h ){
values.push( flat[ h ] );
});
return values;
}
// Insert headers
function setHeaders( sheet, values ) {
var headerRow = sheet.getRange( 1, 1, 1, values.length )
headerRow.setValues( [ values ] );
headerRow.setFontWeight( "bold" ).setHorizontalAlignment( "center" );
}
// Insert Data into Sheet
function setValues( sheet, values ) {
var lastRow = Math.max( sheet.getLastRow(),1 );
sheet.insertRowAfter( lastRow );
sheet.getRange( lastRow + 1, 1, 1, values.length ).setValues( [ values ] ).setFontWeight( "normal" ).setHorizontalAlignment( "center" );
}
// Find or create sheet for form
function getFormSheet( formName ) {
var formSheet;
var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
// create sheet if needed
if ( activeSheet.getSheetByName( formName ) == null ) {
formSheet = activeSheet.insertSheet();
formSheet.setName( formName );
isNewSheet = true;
}
return activeSheet.getSheetByName( formName );
}
// magic function where it all happens
function insertToSheet( data ){
var flat = flattenObject( data );
var keys = Object.keys( flat );
var formName = data["form_name"];
var formSheet = getFormSheet( formName );
var headers = getHeaders( formSheet, keys );
var values = getValues( headers, flat );
setHeaders( formSheet, headers );
setValues( formSheet, values );
if ( emailNotification ) {
sendNotification( data, getSeetURL() );
}
}
function getSeetURL() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
return spreadsheet.getUrl();
}
function sendNotification( data, url ) {
var subject = "A new Elementor Pro Froms subbmition has been inserted to your sheet";
var message = "A new subbmition has been recived via " + data['form_name'] + " form and inserted into your Google sheet at: " + url;
MailApp.sendEmail( emailAddress, subject, message, {
name: 'Automatic Emailer Script'
} );
}
view raw script.gs hosted with ❤ by GitHub

If you want to receive email updates, you can change the var emailNotification value from false to true and put your email address next to the var emailAddress.

3. Deploy as Web App

Click on the Publish drop-down select the option Deploy as web app. A popup will appear, just change the Who has access to the app: option from Only myself to Anyone, even anonymous.

Adding Custom Script to Google Sheets Script Editor

Hit Deploy and it will ask you to Review Permissions, click on it and select your Gmail account with which you want to run the script.

Google Unsafe App Notice

Sometimes, it gives you a warning saying This app isn’t verified, click on Advanced and then on the Go to {your-project-name} (unsafe).

Copy the Webhook URL

Grant all the permissions that it’s asking for by clicking on the Allow button; navigate back to the already opened Script Editor tab and copy the shown URL under the Current web app URL: option.

4. Add Webhook to the Elementor Form

Log in to your wp-admin and edit the page where you want to show your form with Elementor. Create a form (if not already); under form settings, move to the Actions After Submit section and add Webhook in the Add Actions field.

Add Webhook to Elementor Form

Go to the below Webhook section and paste the URL that you copied in the previous step. Update the page and you’re good to go.

Open the page where you’ve added the form, put some dummy data in the form and there will be a new sheet created in your Google Sheet. Navigate to the new sheet and your form data should be there.

That’s it.

If you are stuck somewhere then feel free to let me know by dropping a quick comment below.

Also, share the article with the people who you think might be interested in reading it.

Recommended: Send Contact Form 7 data to Google Sheets

By Deepak K

I develop business strategies and write about marketing & technology.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s