Last active
January 6, 2021 15:24
-
-
Save dangtrinhnt/5f76f4bf995b7956cd20 to your computer and use it in GitHub Desktop.
Google Apps Script to send email when submitting form
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Trinh Nguyen, Nov 18, 2014 | |
function EmailFormConfirmation() { | |
// name of the response sheet | |
var sheetname = "sheet1" | |
// insert a blank column at the end of the response sheet | |
// store the sent mail result | |
// add this formula to the first row of this column: | |
// =indirect("AG"&counta(A1:A)) | |
var columnnumber = 34 | |
// get spreadsheet by ID of the reponse spreadsheet | |
var myspreadsheet = SpreadsheetApp.openById('11BvY139qDAc06KxJrSUowCRwPNKVgNgSsPtmjGhOPQ'); | |
//var myspreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = myspreadsheet.getSheetByName(sheetname); | |
var email = sheet.getRange(1,columnnumber).getValue(); | |
// Determines row number of most recent form submission and sets it as "lastrow" | |
var lastrow = -1; | |
if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") { | |
lastrow = sheet.getMaxRows() | |
} | |
else { | |
var count = 0 | |
for (var i = 0; i < sheet.getMaxRows(); i++) { | |
if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") { | |
lastrow = sheet.getMaxRows()-i | |
break; | |
} | |
} | |
} | |
Logger.log("Last row: %s", lastrow); | |
var subject = "Customer Feedback" | |
// get the column names | |
var columns = sheet.getRange(1,1,1,33).getValues()[0]; | |
var data = sheet.getDataRange().getValues()[lastrow-1]; | |
var message= '<html><body>'; | |
for (var keys in columns) { | |
var key = columns[keys]; | |
message += '<p>+ ' + key + ': ' + data[keys] + '</p>' | |
} | |
message += "</body></html>" | |
MailApp.sendEmail(email, subject, "", {htmlBody: message}); | |
sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent"); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment