Email Google Form Spreadsheet data as Attachment using Google Script
/* | |
* 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 @ pranav.raj18@gmail.com.'; | |
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 @ pranav.raj18@gmail.com.'; | |
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 = ['admin1@gmail.com','admin2@gmail.com','admin3@gmail.com']; | |
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