/** | |
* 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' | |
} ); | |
} |
This comment has been minimized.
This comment has been minimized.
Yes. Excellent solution. |
This comment has been minimized.
This comment has been minimized.
Still live and kicking |
This comment has been minimized.
This comment has been minimized.
not working |
This comment has been minimized.
This comment has been minimized.
Hi, the code works great. |
This comment has been minimized.
This comment has been minimized.
November 2020, Still working great!! |
This comment has been minimized.
This comment has been minimized.
Thanks a lot. Still working. One thing, what is the limit? I mean how many submissions can the script fetch every day or hour? |
This comment has been minimized.
This comment has been minimized.
Over a thousand in a day without problems. I guess there are no limits to the quantity. |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
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? |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
Works nice but doesn't send emails :[ |
This comment has been minimized.
This comment has been minimized.
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! |
This comment has been minimized.
This comment has been minimized.
Did not work for me. Did exactly as instructed here.
After submitting the form there is no update in google sheets. Can anyone help? |
This comment has been minimized.
This comment has been minimized.
@Trushar10, |
This comment has been minimized.
This comment has been minimized.
It doesn't create any tab |
This comment has been minimized.
This comment has been minimized.
Unfortunately it doesn't work for me. Script function not found: doGet |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
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 comment has been minimized.
This comment has been minimized.
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? |
This comment has been minimized.
Still working?