Skip to content

Instantly share code, notes, and snippets.

@bitwiser
Last active June 2, 2021 22:04
Show Gist options
  • Star 7 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save bitwiser/9310634 to your computer and use it in GitHub Desktop.
Save bitwiser/9310634 to your computer and use it in GitHub Desktop.
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]});
}
}
@jonidepp08
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment