Reference Cell for Email Address

Question

First code is what I am currently using, I am trying to get my email address to reference in a cell. I've found another script that does this but when I try to combine them, it doesn't work.

function getGoogleSpreadsheetAsExcel(){

try {

var ss = SpreadsheetApp.getActive();

var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" 
+ ss.getId() + "&exportFormat=xlsx";

var params = {
  method      : "get",
  headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
  muteHttpExceptions: true
};

var blob = UrlFetchApp.fetch(url, params).getBlob();

blob.setName(ss.getName() + ".xlsx");

MailApp.sendEmail("Email Address Here",  "Subject Here", "Body of Email 
Here", {attachments: [blob]});

} catch (f) {
Logger.log(f.toString());
}
}

And trying to combine this to it to reference cells for me instead of edited the script.

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Email Test'))
var startRow = 2;  // First row of data to process
var numRows = 2;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0];  // First column
var message = row[1];       // Second column
var subject = "Sending emails from a Spreadsheet";
MailApp.sendEmail(emailAddress, subject, message);
 }
}

Again both work on there own, but wont work when combined.

RobDawg2323 RobDawg2323 · Accepted Answer · 2018-01-11T19:00:09

This is what I have compiled and it does send the email and attachement, but just to myself, no other recipients. Needing at least it to go to 6 other people at once.

function getGoogleSpreadsheetAsExcel(){ 

try {

var ss = SpreadsheetApp.getActive();

var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" 
+ ss.getId() + "&exportFormat=xlsx";

var params = {
  method      : "get",
  headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
  muteHttpExceptions: true
};

var blob = UrlFetchApp.fetch(url, params).getBlob();

blob.setName(ss.getName() + ".xlsx");
var sheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Email Test'))
var startRow = 2;  // First row of data to process
var numRows = 2;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0];  // First column
var message = row[1];       // Second column
var subject = "Sending emails from a Spreadsheet";
MailApp.sendEmail(emailAddress, subject, message, {attachments: [blob]});
}
}
catch (f) {
Logger.log(f.toString());

} }

Reference Cell for Email Address

1 Answers