How to Send Elementor Form Data to Google Sheets

Updated: November 11, 2022

Web forms are a very powerful tool for interacting with users. For instance, they can subscribe to your company’s newsletter just by entering their email address and any other data you wish to collect. Or perhaps you are an e-commerce company that wants to include a sales order form on your website as a convenience to your customers. Another type of form that may be useful to your business is including a customer feedback form so that you can gain better knowledge of what is working and what is not in your business.

Elementor Pro comes with many native widgets, one of the widgets to create a form easily on your website is the Form widget. With the Form widget, you can create a wide range type of forms on your WordPress website. For more information about the widget, you can check our previous article about Elementor Form Builder.

While Elementor has a built-in feature to manage form submissions (data), you might want to send the data to Google Sheets to organize and categorize data into a logical format.

In this article, we will show you how to get data from Elementor Form into Google Sheet which is a cloud spreadsheet editor that makes it easy to access and use for the users.

Start a New Spreadsheet in Google Sheets

You can access google sheets from sheets.google.com using a free Google account (for personal use) or a Google Workspace account (for business use). Create a new blank spreadsheet to get started.

Open the Apps Script Editor

Previously placed under the Tools menu by the name script editor, nowadays it is called Apps Script under the Extension menu.

Get the Code to Enable the Data Transfer

To be able to transfer the Elementor form data to Google Sheets, you need to connect them first using the following code.

// Change to true to enable email notifications
let emailNotification = false;
let emailAddress = "Change_to_your_Email";

// DO NOT EDIT THESE NEXT PARAMS
let isNewSheet = false;
let postedData = [];
const EXCLUDE_PROPERTY = 'e_gs_exclude';
const ORDER_PROPERTY = 'e_gs_order';
const SHEET_NAME_PROPERTY = 'e_gs_SheetName';
/**
 * 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 published App URL.
function doPost( e ) {
	let params = JSON.stringify( e.parameter );
	params = JSON.parse( params );
  postedData = params;
	insertToSheet( params );
	// HTTP Response
	return HtmlService.createHtmlOutput( "post request received" );
}
/**
 * flattenObject
 * Flattens a nested object for easier use with a spreadsheet
 * @param ob
 * @returns {{}}
 */
const flattenObject = ( ob ) => {
	let toReturn = {};
	for ( let i in ob ) {
		if ( ! ob.hasOwnProperty( i ) ) {
			continue;
		}
		if ( ( typeof ob[ i ] ) !== 'object' ) {
			toReturn[ i ] = ob[ i ];
			continue;
		}
		let flatObject = flattenObject( ob[ i ] );
		for ( let x in flatObject ) {
			if ( ! flatObject.hasOwnProperty( x ) ) {
				continue;
			}
			toReturn[ i + '.' + x ] = flatObject[ x ];
		}
	}
	return toReturn;
}
/**
 * getHeaders
 * normalize headers
 * @param formSheet
 * @param keys
 * @returns {*[]}
 */
const getHeaders = ( formSheet, keys ) => {
	let headers = [];
	// retrieve existing headers
	if ( ! isNewSheet ) {
		headers = formSheet.getRange( 1, 1, 1, formSheet.getLastColumn() ).getValues()[0];
	}
  const newHeaders = keys.filter( h => ! headers.includes( h ) );
  headers = [ ...headers, ...newHeaders ];
  // maybe set order
	headers = getColumnsOrder( headers );
  // maybe exclude headers
	headers = excludeColumns( headers );
  // filter out control columns
  headers = headers.filter( header => ! [ EXCLUDE_PROPERTY, ORDER_PROPERTY, SHEET_NAME_PROPERTY ].includes( header ) );
  return headers;
};
/**
 * getValues
 * normalize values
 * @param headers
 * @param flat
 * @returns {*[]}
 */
const getValues = ( headers, flat ) => {
	const values = [];
	// push values based on headers
	headers.forEach( ( h ) => values.push( flat[ h ] ) );
	return values;
}
/**
 * insertRowData
 * inserts values to a given sheet at a given row
 * @param sheet
 * @param row
 * @param values
 * @param bold
 */
const insertRowData = ( sheet, row, values, bold = false ) => {
	const currentRow = sheet.getRange( row, 1, 1, values.length );
	currentRow.setValues( [ values ] )
		.setFontWeight( bold ? "bold" : "normal" )
		.setHorizontalAlignment( "center" );
}
/**
 * setHeaders
 * Insert headers
 * @param sheet
 * @param values
 */
const setHeaders = ( sheet, values ) => insertRowData( sheet, 1, values, true );
/**
 * setValues
 * Insert Data into Sheet
 * @param sheet
 * @param values
 */
const setValues = ( sheet, values ) => {
	const lastRow = Math.max( sheet.getLastRow(), 1 );
	sheet.insertRowAfter( lastRow );
	insertRowData( sheet, lastRow + 1, values );
}
/**
 * getFormSheet
 * Find or create sheet for form
 * @param sheetName
 * @returns Sheet
 */
const getFormSheet = ( sheetName ) => {
	const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
	// create sheet if needed
	if ( activeSheet.getSheetByName( sheetName ) == null ) {
		const formSheet = activeSheet.insertSheet();
		formSheet.setName( sheetName );
		isNewSheet = true;
	}
	return activeSheet.getSheetByName( sheetName );
}
/**
 * insertToSheet
 * magic function where it all happens
 * @param data
 */
const insertToSheet = ( data ) => {
	const flat = flattenObject( data ),
		keys = Object.keys( flat ),
		formSheet = getFormSheet( getSheetName( data ) ),
		headers = getHeaders( formSheet, keys ),
		values = getValues( headers, flat );
	setHeaders( formSheet, headers );
	setValues( formSheet, values );
	if ( emailNotification ) {
		sendNotification( data, getSheetURL() );
	}
}
/**
 * getSheetName
 * get sheet name based on form field named "e_gs_SheetName" if exists or used form name
 * @param data
 * @returns string
 */
const getSheetName = ( data ) => data[SHEET_NAME_PROPERTY] || data["form_name"];
/**
 * getSheetURL
 * get sheet url as string
 * @returns string
 */
const getSheetURL = () => SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getUrl();
/**
 * stringToArray
 * split and trim comma seperated string to array
 * @param str
 * @returns {*}
 */
const stringToArray = ( str ) => str.split( "," ).map( el => el.trim() );
/**
 * getColumnsOrder
 * used to set the columns order, set this by adding a form field (hidden) named "e_gs_order"
 * and set its value to the names of the columns you want to seperated by comma in the order you want,
 * any other colum not in that field will be added after
 * @param data
 * @param headers
 * @returns {*}
 */
const getColumnsOrder = ( headers ) => {
	if ( ! postedData[ORDER_PROPERTY] ) {
		return headers;
	}
	let sortingArr = stringToArray( postedData[ORDER_PROPERTY] );
  // filter out non existing headers
  sortingArr = sortingArr.filter( h => headers.includes( h ) );
  // filterout sorted headers
  headers = headers.filter( h => ! sortingArr.includes( h ) );
  return [ ...sortingArr, ...headers ];
}
/**
 * excludeColumns
 * used to exclude columns, set this by adding a form field (hidden) named "e_gs_exclude"
 * and set its value to the names of the columns you want to exclude seperated by comma
 * @param data
 * @param headers
 * @returns {*}
 */
const excludeColumns = ( headers ) => {
	if ( ! postedData[EXCLUDE_PROPERTY] ) {
		return headers;
	}
	const columnsToExclude = stringToArray( postedData[EXCLUDE_PROPERTY] );
	return headers.filter( header => ! columnsToExclude.includes( header ) );
}
/**
 * sendNotification
 * send email notification if enabled
 * @param data
 * @param url
 */
const sendNotification = ( data, url ) => {
	MailApp.sendEmail(
		emailAddress,
		"A new Elementor Pro Forms submission has been inserted to your sheet", // mail subject
		`A new submission has been received via ${data['form_name']} form and inserted into your Google sheet at: ${url}`, //mail body
		{
			name: 'Automatic Emailer Script'
		}
	);
};

Credit for the code to bainternet.

Clear All Code and Paste the Code

After you get the code, paste it into the Apps Script editor, and clear all existing code first to make it works.

Rename the Project and Save the Changes

Once the code is ready, you can rename the script title, then click the save button .

Deploy the Script and Copy the Web App URL

After the change is saved, let’s deploy the script by clicking the Deploy button and selecting New deployment from the options.

On the appearing window click the gear icon , and choose the Web app option.

On the newly appearing form change the access to Anyone and click the Deploy button.

If somehow the deployment of the script is asking for permission for accessing your spreadsheet, please Authorize its access to make the script able to write the data on your spreadsheet.

After the deployment of the script is successful, copy the Web app URL.

Paste the Web App URL into Elementor Form Webhook Setting

On Elementor editor, click the form you want to connect with Google sheet, it will open the form setting on the left panel of the editor, then scroll down and click on the Action After Submit block — Add Webhook action — it then opens a new Webhook block below, clicking on it will give you a URL field for placing the copied web app link.

Please make sure you do not activate the Advanced Data on the Webhook settings since the script won’t work properly if you do because the data will be sent as an array.

Alternatively, if you need the submission date time from the Advanced Data, you can create a new Hidden field, By clicking on the + ADD ITEM and changing the type of newly created field to Hidden then adding a Label for it. Continue by going to the ADVANCED tab of the hidden field, then, in Default Value, click on the database(Dynamic Tags) icon to select the Current Date Time option.

Okay, that’s it, you can test the form by filling in the text field and clicking the send button.

We tested the form we created by filling in the form and clicking the send button 3 times.

On Google Sheets, the code will make a new sheet under the name of the form on Elementor. We named the form to google sheet, and the new sheet name is the same.

On the newly created sheet, the data we send from the Elementor form is all there, without missing any data at all.

The Bottom Line

Web forms are a useful tool for tracking certain information from your website visitors. And they are probably one of the most important elements on your website when it comes to achieving your goals. By connecting the form to our Google sheet, we can organize and categorize data into a logical format. Once this data is entered into the spreadsheet, we can use it to help organize and grow our business.

This page may contain affiliate links, which help support WP Pagebuilders. Learn more

19 thoughts on “How to Send Elementor Form Data to Google Sheets”

  1. Hey there,

    I’ve tried your method, now, I did receive a notification on my test email.
    However, I’m not seeing a new form being added onto my Google Sheets.
    Not sure what I’m doing wrong…

    Reply
    • Hey Simon, have you cleared the editor in App Script before you paste the code. this default code “function myFunction() { }” must be deleted first.

      Reply
  2. Hi Hendry!

    I want to activate the “Advanced Data” field on the elementor form but when I do so, the script stops working, it just creates a new sheet but no data is displayed.

    The script has been working just fine for about 3 months now, it’s just that I need to add the advanced data to it.

    Thanks in advance!

    Reply
      • Hi,

        May I ask what kind of “advanced data” do you mean?
        we tested with advanced form fields like File upload, password field, acceptance checkbox, and date field and it’s transferred successfully to Google Sheets.

        Reply
      • Turning off the advanced data toggle under the webhook section in Elementor seemed to fix the problem that I was having.

        Reply
  3. Hi, works great. Thank you very much! I have a questionnaire on my site and i want the script to automatically sum the items, but i cant manage to do so. Could you please help me with that? Thank you very much

    Reply
  4. Hi,

    This is great thanks and perfect for a customer of mine. One thing though, how can I remove fields that I dont need like Page URL, Remote IP, User Agent etc?

    Also my results are in a different order to the form, is it possible to have them in the right order?

    Sorry, I’m not very good at this!!

    Thanks Mark

    Reply
    • Hi Mark,
      Normally you’ll only get Form id as additional data on your spreadsheet.

      Can you kindly check if the Advanced Data on the Webhook Setting is active?

      If the setting is active, try to deactivate the setting.

      Reply
  5. Hi,

    I have the same problem as Mark. My results have many extra info like Remote IP, user agent, etc. Just needs the fields from the form.

    I checked the “Advanced data” toggle of the Webhook setting and is deactivated since the beggining.

    Could you help me with this? Maybe is something related with the code that enables the data transfer?

    Thanks

    Reply
    • Hi Raul,
      We have Updated the code.

      Now you can exclude the column you don’t need by adding a hidden field to the form with the name “e_gs_exclude”
      Then, add the columns you want to exclude in the Default Value separated by comma.

      Note: You need to re-deploy the script to apply the changes then copy the new webhook

      Reply
  6. Hi,

    Thank ou very much. THis script is perfect for one of my customers !

    I just have one problem. On Google Sheet, the columns don’t appear in the same order as my form fields.

    Did you solve it ?

    Thanks!

    Reply

Leave a Comment

Pin It on Pinterest

Thanks for commenting

Help us grow by sharing this post with your friends!

Join 1,000+ WordPress users receiving tips and insights on creating on WordPress with Elementor.

Creating with WordPress?​

Subscribe and join 1,000+ WordPress users receiving tips and insights on creating with WordPress in the no-code era. At WP Pagebuilders, we write about the following topics a lot.