Google Apps Script - Part 1 - Send email result to selected address after submitting google form (basic)
So, you want to email someone (something like a feedback to that person) whenever you submitting the google form. Here you go, using this google apps script:
1. First, make sure you have already setup the responses spreadsheet and name it, for example: "sheet1".
Look for the spreadsheet ID in the URL. It's something like:
'11BvY139qDAc06KxJrSUowCRwPNKVgNgSsPtmjQGhOPQ'
2. Insert a blank column at the end of the responses spreadsheet, e.g column AH (34). Enter this formula in the first row:
=indirect("AG"&counta(A1:A))
"AG" is the column contains email addresses.
3. From the google form edit windows, choose Tools >> Script editor... to open script editor window and input this function:
4. In the Script Editor windows, select Resources >> Current Project's Triggers >> Add a new trigger
5. Go to the live form, and try to submit. Cool!!! \m/
1. First, make sure you have already setup the responses spreadsheet and name it, for example: "sheet1".
Look for the spreadsheet ID in the URL. It's something like:
'11BvY139qDAc06KxJrSUowCRwPNKVgNgSsPtmjQGhOPQ'
2. Insert a blank column at the end of the responses spreadsheet, e.g column AH (34). Enter this formula in the first row:
=indirect("AG"&counta(A1:A))
"AG" is the column contains email addresses.
3. From the google form edit windows, choose Tools >> Script editor... to open script editor window and input this function:
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"); | |
| } |
4. In the Script Editor windows, select Resources >> Current Project's Triggers >> Add a new trigger
5. Go to the live form, and try to submit. Cool!!! \m/