How to Send Elementor Form Data to Google Sheets

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.