How to run a server side function on html form submit

0
votes

I created an html form to allow users to submit their emails to spreadsheet tab "Emails"

In the spreadsheet I created another tab "Sort Emails" to sort the results of "Emails" tab in a reverse order so while people submit their emails, I have the new submitted email in Sort Emails B2.

The problem is: Now I am trying to automatically send an email to new submitted email on-Submit, i.e once a new user submits the form, the script sends an email to the user's email that is supposed to be located in Sort Emails B2. Any help please. Thanks in advance!

Please note when I run the function SendEmail manually from the sheet script editor, it does the job but my question is about running the function automatically on form submit

I'm not much experienced but I think we need a trigger to run the SendEmail function automatically whenever a new row is added to the sheet and of course I tried the current project's triggers in the script editor (both onEdit and onChange) but no luck (I don't understand why??)

This is my html file:

<!DOCTYPE html>
<html lang="en">
<body>

<form name="Subscribe" id="Subscribe" action="https://script.google.com/macros/s/ScriptKey/exec" method="POST" onsubmit="myFunction()">
Form Inputs ..
</form>

<script>
function myFunction() {
  google.script.run.SendEmail();
}
</script>

</body>
</html>

And this is my gs file:

function doGet() {
    return HtmlService.createTemplateFromFile('Form.html')
        .evaluate() // evaluate MUST come before setting the Sandbox mode
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

var sheetName = 'Emails'
var scriptProp = PropertiesService.getScriptProperties()


function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)
  
  try {
    var doc = SpreadsheetApp.getActive();
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    var y = sheet.getLastRow();
    var x = sheet.getRange(y,2).getValues();

    return ContentService
      .createTextOutput(JSON.stringify({ 'Central Says': 'Successfully subscribed', 'Email': x }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

function SendEmail() {
var e = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sort Emails").getRange("B2").getValues();
var m = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange("A1").getValues();
var subject = 'Welcome ..';
MailApp.sendEmail(e, subject, m);
}
2
javascriptgoogle-apps-scriptgoogle-sheets
Can you share the full code? Do you have anything on doGet / doPost? Also have you checked how Client-Side API works? developers.google.com/apps-script/guides/html/reference/run – Kessy
I updated the question with the full gs code. thanks – Mahmoud Bayoumi
Since you have already the doPost(e) set-up, why is that you are not sending the email at the end of the doPost(e) function? It is already a trigger and ,actually, the one you need to use because OnEdit(e) runs when a user changes something in a spreadsheet. – Kessy

2 Answers

0
votes

First you have to change the getActiveSpreadsheet() for openById("SPREADSHEET ID") and then you have two options:

You keep the SendEmail() function:

function SendEmail() {
  var e = SpreadsheetApp.openById("SPREADSHEET ID").getSheetByName("Sort Emails").getRange("B2").getValue();
  var m = SpreadsheetApp.openById("SPREADSHEET ID").getSheetByName("Template").getRange("A1").getValue();
  var subject = 'Welcome ..';
  MailApp.sendEmail(e, subject, m);
}

Or do it everything inside the doPost and delete the call to the SendEmail() function in the HTML:

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {

    var e = SpreadsheetApp.openById("SPREADSHEET ID").getSheetByName("Sort Emails").getRange("B2").getValue();
    var m = SpreadsheetApp.openById("SPREADSHEET ID").getSheetByName("Template").getRange("A1").getValue();
    var subject = 'Welcome ..';
    MailApp.sendEmail(e, subject, m);

    var doc = SpreadsheetApp.getActive();
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    var y = sheet.getLastRow();
    var x = sheet.getRange(y,2).getValues();

    return ContentService
    .createTextOutput(JSON.stringify({ 'Central Says': 'Successfully subscribed', 'Email' : x}))
    .setMimeType(ContentService.MimeType.JSON)  
  }
  catch (e) {
    return ContentService
    .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
    .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}
-1
votes

The correct way to do that is making a request to a server side application that you you should create to handle this server side stuffs, an API. You can create this API very easily work frameworks like Express.js or Koa.js

Then in your server side you would handle like this:

... // Initializing stuffs
app.post('/sendEmail/:email', function(req, res, next) {
    const userEmail = req.params.email;
    try {
        google.script.run.SendEmail(userEmail);
        res.json({ message: 'success' });
    } catch (err) {
        res
          .status(400)
          .json({ message: 'failure' + err.message })
    }
});