I want to Auto-Run Script in Google Sheets every time someone registers from my custom form on my website and the Data Entry to the Sheet.
I tried with adding an OnSubmit event on Trigger but it did not work because I don't use Google Forms I use a Script to collect the data from my website.
Here is the Send Emails Script that I want to Auto-Run and send an email each time someone's data comes into the sheet:
function sendMail() {
const emailTemp = HtmlService.createTemplateFromFile("email");
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("form1");
const startRow = 2;
const data = sh.getRange(startRow, 2, sh.getLastRow() - 1, sh.getLastColumn() - 1).getValues();
data.forEach((row, i) => {
emailTemp.first = row[2];
emailTemp.last = row[3];
emailTemp.phone = row[9];
let htmlMessage = emailTemp.evaluate().getContent();
let emailSent = row[11];
if (emailSent != "EMAIL_SENT") {
GmailApp.sendEmail(
row[1],
"Thank you!",
"Your email doesn't support HTML.",
{ name: "Email App", htmlBody: htmlMessage });
sh.getRange(startRow + i, 13).setValue("EMAIL_SENT");
}
});
}
Here is the Data Collection Script on my website:
// Variable to hold request
var request;
var url = window.location.href;
$( document ).ready(function() {
$('#url').val(url);
});
console.log(url);
// Bind to the submit event of our form
$("#request-form").submit(function(event){
// Abort any pending request
if (request) {
request.abort();
}
// setup some local variables
var $form = $(this);
// Let's select and cache all the fields
var $inputs = $form.find("input, select, button, textarea");
// Serialize the data in the form
var serializedData = $form.serialize();
// Let's disable the inputs for the duration of the Ajax request.
// Note: we disable elements AFTER the form data has been serialized.
// Disabled form elements will not be serialized.
$inputs.prop("disabled", true);
// Fire off the request to /form.php
request = $.ajax({
url: "https://script.google.com/macros/s/AKfycbwa50RJFxZfnBXQX-LBHR4OD6Lzxb0a2tAclBzGV57nT0XY3WU/exec",
type: "post",
data: serializedData
});
// Callback handler that will be called on success
request.done(function (response, textStatus, jqXHR){
// Log a message to the console
console.log("Hooray, it worked!");
console.log(response);
console.log(textStatus);
console.log(jqXHR);
});
// Callback handler that will be called on failure
request.fail(function (jqXHR, textStatus, errorThrown){
// Log the error to the console
console.error(
"The following error occurred: "+
textStatus, errorThrown
);
});
// Callback handler that will be called regardless
// if the request failed or succeeded
request.always(function () {
// Reenable the inputs
$inputs.prop("disabled", false);
console.log("It's running");
window.location.href = 'success.html';
});
// Prevent default posting of form
event.preventDefault();
});
And here is the Script I have used on the Sheet and Deploy it As Web App, and made it accessable to Anyone, even anonymous.
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Leads";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date(), "UTC-7");
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
Thank you.
