Skip to content

Instantly share code, notes, and snippets.

@bainternet
Created Oct 14, 2018
Embed
What would you like to do?
Google app-script to utilise Elementor Pro Froms webhook
/**
* 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'
} );
}
@mcnaveen

This comment has been minimized.

Copy link

@mcnaveen mcnaveen commented May 5, 2020

Still working?

@ElchananLevavi

This comment has been minimized.

Copy link

@ElchananLevavi ElchananLevavi commented Aug 11, 2020

Still working?

Yes. Excellent solution.

@bainternet

This comment has been minimized.

Copy link
Owner Author

@bainternet bainternet commented Aug 11, 2020

Still live and kicking

@NajmiHamdi

This comment has been minimized.

Copy link

@NajmiHamdi NajmiHamdi commented Sep 4, 2020

not working

@estherwec

This comment has been minimized.

Copy link

@estherwec estherwec commented Sep 30, 2020

Hi, the code works great.
I want to use it to compose a form of the Piotnet plugin but understand that the code does not work because the elementor has a_name_form and in this case has an ID_form
I want to know if you have any suggestions on how to enable this.
The big advantage of your way, is that the headings of the fields are created automatically and there is no need to link manually, as in Zafir.
And it's really critical when there are a lot of fields and a lot of forms.
Thank you!

@joaquin-alvarez

This comment has been minimized.

Copy link

@joaquin-alvarez joaquin-alvarez commented Nov 5, 2020

November 2020, Still working great!!

@deepakness

This comment has been minimized.

Copy link

@deepakness deepakness commented Nov 24, 2020

Thanks a lot. Still working.

One thing, what is the limit? I mean how many submissions can the script fetch every day or hour?

@joaquin-alvarez

This comment has been minimized.

Copy link

@joaquin-alvarez joaquin-alvarez commented Nov 24, 2020

Thanks a lot. Still working.

One thing, what is the limit? I mean how many submissions can the script fetch every day or hour?

Over a thousand in a day without problems. I guess there are no limits to the quantity.

@deepakness

This comment has been minimized.

Copy link

@deepakness deepakness commented Nov 24, 2020

Over a thousand in a day without problems. I guess there are no limits to the quantity.

Google has some script limits that you can check on this page. If this is URL Fetch Calls, then the free account gets 20,000/day and GSuite gets 100,000/day which is huge.

@ganatrask

This comment has been minimized.

Copy link

@ganatrask ganatrask commented Dec 17, 2020

I tried but unfortunately was not able to set it up the right way, but then I tried Zapier. It's much easier to set up and the free plan has a lot of things to do. Any cons of using Zapier instead of this?

@deepakness

This comment has been minimized.

Copy link

@deepakness deepakness commented Dec 17, 2020

I tried but unfortunately was not able to set it up the right way, but then I tried Zapier. It's much easier to set up and the free plan has a lot of things to do. Any cons of using Zapier instead of this?

The free plan of Zapier can only handle 100 responses per month. If It satisfies your requirements then it's good.

If you are having difficulties setting this automation up here's a link that explains this in details.

@ganatrask

This comment has been minimized.

Copy link

@ganatrask ganatrask commented Dec 17, 2020

Thanks, @deepakness, Actually this script and automation were working perfectly, it was saving data in new a sheet which I didn't observe.

By the free Zapier plan can now take 1000 responses per month, it's enough for me.

@shirokoweb

This comment has been minimized.

Copy link

@shirokoweb shirokoweb commented Dec 17, 2020

Works nice but doesn't send emails :[

@azmirzed

This comment has been minimized.

Copy link

@azmirzed azmirzed commented Dec 19, 2020

Hi,

Thanks for the code is working well.

But when I use a dropdown/radio type of form, the data is not sent to the google sheet.

just wondering if you have version that cater for this type of form!

@Trushar10

This comment has been minimized.

Copy link

@Trushar10 Trushar10 commented Jan 27, 2021

Did not work for me. Did exactly as instructed here.

  1. Created new google sheet file.
  2. Tools>Script editor
  3. paste the code and save.
  4. deploy>new deployment
  5. deploy> allow authorization
  6. copy web app link and paste in elementor form.

After submitting the form there is no update in google sheets.

Can anyone help?

@shirokoweb

This comment has been minimized.

Copy link

@shirokoweb shirokoweb commented Jan 27, 2021

@Trushar10,
It creates a new tab.

@Trushar10

This comment has been minimized.

Copy link

@Trushar10 Trushar10 commented Jan 27, 2021

@Trushar10,
It creates a new tab.

It doesn't create any tab

@syafeeq

This comment has been minimized.

Copy link

@syafeeq syafeeq commented Feb 4, 2021

Unfortunately it doesn't work for me.

Script function not found: doGet

@samuelllundberg

This comment has been minimized.

Copy link

@samuelllundberg samuelllundberg commented Feb 15, 2021

Doesn't work for me either, followed every step and when I paste the URL in the browser I get the response "Yepp this is the webhook URL, request received", but nothing happens in the sheet.

@ebatycki

This comment has been minimized.

Copy link

@ebatycki ebatycki commented Feb 23, 2021

I was getting Script function not found: doGet but it's funny that if I make a second one (select New Deployment again), then it works for some reason! And yes it creates a new sheet tab in the spreadsheet file, so I just delete the first blank one.
This is really great, but I wonder if there's a way to make the new row at the top, so newest submissions are first, as the list starts to get really long and there's a lot of scrolling. I know I could just re-order the sheet by date, but the client would have to remember to do that everyday.

@mannagod2021

This comment has been minimized.

Copy link

@mannagod2021 mannagod2021 commented Feb 27, 2021

I am struggling to get this to work. The script URL gets the correct results from the address bar - but the form data is not being passed to my google sheet. Any ideas?

@ariako

This comment has been minimized.

Copy link

@ariako ariako commented Apr 7, 2021

Works perfect for me! Thank you very much for sharing!!!

Questions: @bainternet

  1. Can I sort the columns in any way?
  2. Is it possible to exclude certain form fields? (in my case: field_18b53fb)
  3. Is it possible to rename certain form fields? (for example: "very very very long form field title" > "short title" )
@Super-Freak-BigHell

This comment has been minimized.

Copy link

@Super-Freak-BigHell Super-Freak-BigHell 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?

@ariako

This comment has been minimized.

Copy link

@ariako 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…

@Super-Freak-BigHell

This comment has been minimized.

Copy link

@Super-Freak-BigHell Super-Freak-BigHell commented Apr 9, 2021

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

@kerenelle

This comment has been minimized.

Copy link

@kerenelle kerenelle commented May 5, 2021

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

@ariako

This comment has been minimized.

Copy link

@ariako 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

This comment has been minimized.

Copy link

@kerenelle kerenelle commented May 5, 2021

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

@Trushar10

This comment has been minimized.

Copy link

@Trushar10 Trushar10 commented May 6, 2021

@kerenelle Can you share the link?

@maksimus12

This comment has been minimized.

Copy link

@maksimus12 maksimus12 commented Aug 27, 2021

Unfortunately it doesn't work for me.

Script function not found: doGet

Same here :(
Script function not found: doGet

@tizum

This comment has been minimized.

Copy link

@tizum 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

This comment has been minimized.

Copy link

@menidjel menidjel commented Sep 18, 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?

@mcnaveen

This comment has been minimized.

Copy link

@mcnaveen 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

This comment has been minimized.

Copy link

@Taliabiasevi Taliabiasevi commented Nov 25, 2021

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!!

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