Skip to content

Instantly share code, notes, and snippets.

@bainternet
Last active September 16, 2024 13:49
Show Gist options
  • Save bainternet/4b539b00a4bd7490ac3809d7ff86bd14 to your computer and use it in GitHub Desktop.
Save bainternet/4b539b00a4bd7490ac3809d7ff86bd14 to your computer and use it in GitHub Desktop.
Google app-script to utilise Elementor Pro Froms webhook
/**
* Google app-script to utilise Elementor Pro From webhook.
*
* In order to enable this script, follow these steps:
*
* From your Google Sheet, from the "Exstensions" menu select "App Script"…
* Paste the script from this gist into the script code editor and hit Save.
* From the "Deploy" 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".
* Last all you have to do is set up and Elementor Pro Form with a form name and Webhook action pointing to the URL from above.
*
* Update: 09/06/2022
* - Name the sheet: you can now add a field (could be hidden) to your form labeled "e_gs_SheetName" and set the defualt value to the name of the sheet you want to use.
* - Set the Order: you can now add a form field (hidden) labeled "e_gs_order" and set its defualt value to the names of the columns you want to seperated by comma in the order you want, any other colum not in that list will be added after.
* - Exclude Columns: you can now add a field (could be hidden) labeled "e_gs_exclude" and set its value to the names of the columns you want to exclude seperated by comma.
*/
// 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'
}
);
};
@ariako
Copy link

ariako commented Apr 9, 2021

Hi, @bainternet
I have a problem. The data sent to the spreadsheet is not in the same order as that of Form. Has anyone ever experienced this?

Yes, same here.
I wonder how to keep the order of the form…

@psybrbr
Copy link

psybrbr commented Apr 9, 2021

1 time Simple solution: Just reorder the columns in the spreadsheet manually.

@kerenelle
Copy link

Too bad this doesn't work for me. did anyone get it to work and can share the new code?

@ariako
Copy link

ariako commented May 5, 2021

@Karenelle The code works!
Not sure how to help. Github doesn’t provide a private message service or something, right?

@kerenelle
Copy link

Thanks :)) @ariako
I managed to find a other code that worked for me. so I'm fine.. thank you for the comment :)

@Trushar10
Copy link

@kerenelle Can you share the link?

@maksimus12
Copy link

Unfortunately it doesn't work for me.

Script function not found: doGet

Same here :(
Script function not found: doGet

@tizum
Copy link

tizum commented Sep 6, 2021

working perfectly here thanks!
I would like to know if it is possible to delete the form_id, form_name and some other form fields if necessary

@menidjel
Copy link

Hello guys, I'm getting a doGet error, the form is being submitted successfully, but the script is not working.

what can I do to avoid this error?

@mcnaveen
Copy link

mcnaveen commented Sep 23, 2021

Hello guys, I'm getting a doGet error, the form is being submitted successfully, but the script is not working.

what can I do to avoid this error?

@menidjel

Please make sure this option is unchecked and try again

image

@Taliabiasevi
Copy link

I love this script, I have used it a bunch of times and it works like a charm!!

Now that I added Piotnet forms to my web - in order to create more complex forms - the webhook is no longer pulling data - it creates new empty sheets and that's basically it - is there a way or an additional code to make the webhook work? piotnet forms + elementor pro + google sheets.

Thanksss!!

@cre8tiveph
Copy link

doesn't work on me :( the google sheet is blank.

@Sarthak2099
Copy link

how can we get a notification in every new submission?

@DanielQura
Copy link

Also me not working please any idea?

@joaquin-alvarez
Copy link

joaquin-alvarez commented Jun 21, 2022

Also me not working please any idea?

I just used it following the exact directions from the script and it works like a charm

@orcafalcon
Copy link

It worked very well for me. Awsome job. thank you

@orcafalcon
Copy link

how can i sort the headers in the order they appear in the form? headers are not loaded in their original order.

Thanks for the solution

@mariliacamara
Copy link

how can i sort the headers in the order they appear in the form? headers are not loaded in their original order.

Thanks for the solution

Have you find any solution for sorting the order?

@bainternet
Copy link
Author

Hi all, :)

The script is live and kicking, I just refactored the code with a more modern version and added a few enhancements:

  • Name the sheet: You can now add a field (could be hidden) to your form labeled e_gs_SheetName and set the default value to the name of the sheet you want to use.
  • Set the Order: you can now add a form field (hidden) labeled e_gs_order and set its default value to the names of the columns you want to separated by comma in the order you want, any other column not in that list will be added after.
  • Exclude Columns: you can now add a field (could be hidden) labeled e_gs_exclude and set its default value to the names of the columns you want to exclude separated by comma.

Other than that you can exclude columns by simply hiding them in the sheet and you can set the order once manually via the sheets UI.

Enjoy.

@fellixtara
Copy link

How to ordering column in spreadsheet? I have try & not work.

@hitdarlan
Copy link

hitdarlan commented Jul 28, 2023

I was facing a problem where only the first lead worked, because it created the spreadsheet, while the second tried to create a spreadsheet that already existed and then failed. fixed it with a small change

`const insertToSheet = ( data ) => {
const flat = flattenObject( data ),
keys = Object.keys( flat ),
formSheet = getFormSheet( getSheetName( data ) ),
headers = getHeaders( formSheet, keys ),
values = getValues( headers, flat );

if(isNewSheet) {
setHeaders( formSheet, headers );
}

setValues( formSheet, values );

if ( emailNotification ) {
sendNotification( data, getSheetURL() );
}
}
`

@useramitkr
Copy link

Works Perfectly.

Just check and update the data with yours.

@Welisagita
Copy link

Hi all, :)

The script is live and kicking, I just refactored the code with a more modern version and added a few enhancements:

  • Name the sheet: You can now add a field (could be hidden) to your form labeled e_gs_SheetName and set the default value to the name of the sheet you want to use.
  • Set the Order: you can now add a form field (hidden) labeled e_gs_order and set its default value to the names of the columns you want to separated by comma in the order you want, any other column not in that list will be added after.
  • Exclude Columns: you can now add a field (could be hidden) labeled e_gs_exclude and set its default value to the names of the columns you want to exclude separated by comma.

Other than that you can exclude columns by simply hiding them in the sheet and you can set the order once manually via the sheets UI.

Enjoy.

Can you add time and date for the submitted Form?thanks

@khiatsalaheddin
Copy link

Thanks :)) @ariako I managed to find a other code that worked for me. so I'm fine.. thank you for the comment :)

Can you share the code please

@ramon70dz
Copy link

Thank you so much
It works perfect

@GreeneSoloCup
Copy link

Can you add time and date for the submitted Form?thanks

@Welisagita any luck with getting a timestamp code to add to this?

@paulohmaciel
Copy link

Hi Guys, I added the timestamp in the code, can you guys try please?
Here is working:
image

Remember to change the format of the column of data

`/**

  • Google app-script to utilise Elementor Pro From webhook.
  • In order to enable this script, follow these steps:
  • From your Google Sheet, from the "Exstensions" menu select "App Script"…
  • Paste the script from this gist into the script code editor and hit Save.
  • From the "Deploy" 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".
  • Last all you have to do is set up and Elementor Pro Form with a form name and Webhook action pointing to the URL from above.
  • Update: 09/06/2022
    • Name the sheet: you can now add a field (could be hidden) to your form labeled "e_gs_SheetName" and set the defualt value to the name of the sheet you want to use.
    • Set the Order: you can now add a form field (hidden) labeled "e_gs_order" and set its defualt value to the names of the columns you want to seperated by comma in the order you want, any other colum not in that list will be added after.
    • Exclude Columns: you can now add a field (could be hidden) labeled "e_gs_exclude" and set its value to the names of the columns you want to exclude seperated by comma.
      */

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

// Add a timestamp field
flat['timestamp'] = new Date();
keys.push('timestamp');

const 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'
    }
    );
    };`

@GreeneSoloCup
Copy link

@paulohmaciel I had no luck implementing the new code into my spreadsheet and getting a timestamp column to be created.

@JoseMariani
Copy link

How can I implement it, I do everything in the steps and the spreadsheet is not created.
Does anyone have any updated code that works?

Como puedo implementarlo, hago todo lo que esta en los pasos y no se crea la hoja de calculo.

@MarianMurillo
Copy link

Hello, I tried to do it, but in the messages of the element form or it tells me that the form is invalid, but if it adds the data, I don't know why that is.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment