Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Send Mail after Form Submit to Google Sheet

I found this wonderful app script on Github for writing to my Google spreadsheet. It works perfectly. Now I’m looking for a way to send myself an email after the script has run. But this is proving difficult. I want to call my own EmailNote() function. It works very well on its own, i.e. when started from the editor, but when I try to integrate it in the doPost(e) script, no email is sent. Kindly ask for help!

My doPost(e) Script:

// Manual: https://github.com/levinunnink/html-form-to-google-sheet

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

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

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

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    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])
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }
 
  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

My EmailNote() Script:

function EmailNote() {
  var emailSender = "[email protected]";
  var emailRecipients = ["[email protected]", "[email protected]"];
  var emailSubject = "App Script - Email";
  var emailBody ='App Script - Email';

  // send mail
  GmailApp.sendEmail(emailRecipients.join(","), emailSubject, emailBody, {
    from: emailSender
  });
}
Tags:

2

Answers


  1. 0

    You are not at all calling intialSetup and EmailNote functions inside your doPost method.

    const sheetName = 'Entries' 

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

  try {
    var doc = SpreadsheetApp.getActiveSpreadsheet()
    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])

    // Call EmailNote here

    EmailNote()

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

  finally {
    lock.releaseLock()
  }
}
    Login or Signup to reply.
  2. 0

    Adding another answer as this is something that is a very common pitfall new and often even experienced engineers fall prey to.

    Make sure you redeploy your app after making any changes. And after deployment use the updated endpoint.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search