Last active
June 2, 2021 22:04
-
-
Save bitwiser/9310634 to your computer and use it in GitHub Desktop.
Email Google Form Spreadsheet data as Attachment using Google Script
This file contains hidden or 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
/* | |
* author: bitwiser | |
* for complete tutorial, visit: http://bitwiser.in/2014/04/22/email-google-form-data-as-pdf.html | |
*/ | |
var START_ROW = 2; | |
/** | |
* Retrieves all the rows in the active spreadsheet that contain data and logs the | |
* values for each row. | |
* For more information on using the Spreadsheet API, see | |
* https://developers.google.com/apps-script/service_spreadsheet | |
*/ | |
function readRows() { | |
Logger.clear(); | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var rows = sheet.getDataRange(); | |
var numRows = rows.getNumRows(); | |
var values = rows.getValues(); | |
for (var i = 0; i <= numRows - 1; i++) { | |
var row = values[i]; | |
Logger.log(row); | |
} | |
}; | |
/** | |
* Adds a custom menu to the active spreadsheet, containing a single menu item | |
* for invoking the readRows() function specified above. | |
* The onOpen() function, when defined, is automatically invoked whenever the | |
* spreadsheet is opened. | |
* For more information on using the Spreadsheet API, see | |
* https://developers.google.com/apps-script/service_spreadsheet | |
*/ | |
function onOpen() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [{ | |
name : "Send Data to Email", | |
functionName : "sendToEmail" | |
}, | |
{ | |
name: "Send Email to All", | |
functionName: "sendToAll" | |
}, | |
{ | |
name: "Send Email to Admins", | |
functionName: "sendToAdmins" | |
} | |
]; | |
sheet.addMenu("Functions", entries); | |
}; | |
/* | |
This function is triggered when a new registration takes place on the desired google spreadsheet form | |
*/ | |
function sendReply(e){ | |
var email = e.values[5]; | |
var body = 'Hey '+e.values[1]+', you have been registered for CSPL 2k14 with nick "'+e.values[2]+'". Any further information will be sent to you on this email or your phone number. For any queries, ping SilverCrow @ [email protected].'; | |
Logger.log(body); | |
if(email!=''){ | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var lastRow = sheet.getLastRow(); | |
Logger.log(lastRow); | |
sheet.getRange(lastRow, getColIndexByName("Status")).setValue(1); | |
MailApp.sendEmail(email,'CSPL 2k14 registration',body); | |
} | |
} | |
function sendToAll(){ | |
Logger.clear(); | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var rows = sheet.getDataRange(); | |
var numRows = rows.getNumRows(); | |
var values = rows.getValues(); | |
for( var i=1;i<=numRows;i++){ | |
var row = values[i]; | |
var em = ''; | |
if(row){ | |
em = row[5]; | |
} | |
if(em!='' && row[6]!=1){ | |
Logger.log(em); | |
var body = 'Hey '+row[1]+', you have been registered for CSPL 2k14 with nick "'+row[2]+'". Any further information will be sent to you on this email or your phone number. For any queries, ping SilverCrow @ [email protected].'; | |
Logger.log(body); | |
MailApp.sendEmail(em, 'CSPL 2k14 Registration', body); | |
sheet.getRange(START_ROW+i,7).setValue(1); | |
} | |
} | |
} | |
function getColIndexByName(colName) { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var numColumns = sheet.getLastColumn(); | |
var row = sheet.getRange(1, 1, 1, numColumns).getValues(); | |
for (i in row[0]) { | |
var name = row[0][i]; | |
if (name == colName) { | |
return parseInt(i) + 1; | |
} | |
} | |
return -1; | |
} | |
function sendToAdmins(){ | |
var id = SpreadsheetApp.getActiveSpreadsheet().getId(); | |
var spreadsheetFile = DocsList.getFileById(id); | |
var blob = spreadsheetFile.getAs('application/pdf'); | |
var adminEmails = ['[email protected]','[email protected]','[email protected]']; | |
for(var i=0;i<adminEmails.length;i++){ | |
MailApp.sendEmail(adminEmails[i], 'Event Registration Data', '', {attachments:[blob]}); | |
} | |
} | |
function sendToEmail(){ | |
var name = Browser.inputBox('Enter email', Browser.Buttons.OK_CANCEL); | |
if(name!=="cancel" && name!==""){ | |
var id = SpreadsheetApp.getActiveSpreadsheet().getId(); | |
var spreadsheetFile = DocsList.getFileById(id); | |
var blob = spreadsheetFile.getAs('application/pdf'); | |
MailApp.sendEmail(name, 'Event Registration Data', '', {attachments:[blob]}); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Is it possible to use this code if the email address is located on a different tab and the sheet that I want to send as a pdf is also coming from a different one?
I tried using this code but it gives me this error: ReferenceError: DocsList is not defined
Here's the file that I have. I'm trying to send the Ticket form to one of the employees listed on Employee List tab.
https://docs.google.com/spreadsheets/d/15apdB5PqAuCNjKF7FldZqZTYu5lODUTH5e3XtiBBb4o/edit#gid=0