0

I'm trying to write a script that will allow me to email a list of 150 employees with their individual sales data for the week.

At the moment I have a main front sheet with a Column for Email, Subject, and Store number. Each store number correlates to a Sheet (tab) with the same name, for example joe@gmail.com at store number 5070 has a tab named '5070' with changing data.

The problem I'm having is referencing the changing variable sheet name.

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet1');
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,1).getValue();
var subject = sheet1.getRange(i,2).getValue();
var message = sheet1.getRange(i,3).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}

}

I am very new to the whole thing and have been searching around but have not had much luck. Thank you in advance!

Ray Montgomery
  • 1
  • 1
    Could you elaborate more on 'changing variable sheet name'? Besides, there is a quota limit of 100 recipients per 24 hours for normal Gmail user. – idfurw Aug 09 '21 at 05:18
  • I think your problem is not how to reference the sheet name. Your code **does not** actually **send** any sheet via email. You need to define what is your problem first. – Marios Aug 09 '21 at 06:32
  • Having said that. The question: I want to send a sheet is very abstract and it is very difficult for a person here to help you out if he does not know what exact data you want to send from a sheet. – Marios Aug 09 '21 at 06:36
  • And just in case: https://stackoverflow.com/questions/2744520/what-is-the-daily-email-limit-in-google-apps-script – Yuri Khristich Aug 09 '21 at 08:42
  • I have a g-suite I believe the limit is 1500? – Ray Montgomery Aug 10 '21 at 14:02
  • By send a sheet I mean: each person has a page on the sheet document. I need to send a range as a table or html or however. But each email address on my list gets a different range depending on who it is. So depending on the email, I need it to reference a different page of the sheet. Ray@gmail.com would get sent 'Ray!A1:H20' for example. – Ray Montgomery Aug 10 '21 at 14:09

1 Answers1

0

You can't send a sheet. You can send only a link to the sheet.

If you replace this:

var message = sheet1.getRange(i,3).getValue();

with this:

var sheet_name = sheet1.getRange(i,3).getValue();
var sheet      = ss.getSheetByName(sheet_name);
var message    = sheet.getUrl();

Your recipients will get the link to the spreadsheet (a whole sheet, not even to the particular sheet).

To send a link to a particular sheet of the spreadsheet you need a bit more complicated solution:

var sheet_name = sheet1.getRange(i,3).getValue();
var sheet      = ss.getSheetByName(sheet_name);
var message    = getSheetUrl(sheet);

function getSheetUrl(sht) {
  // credits: https://webapps.stackexchange.com/questions/93305/

  var ss = SpreadsheetApp.getActive();
  var url = '';
  sht = sht ? sht : ss.getActiveSheet();
  url = (ss.getUrl() + '#gid=' + ss.getSheetId());
  return url;
}

But all your recipients will see all the spreadsheet anyway with all its sheets. In case this is not the thing you want you have three options:

Option 1 -- Make a new spreadsheet, copy the data into it and send the link to this new spreadsheet.

Option 2 -- Make PDF from the sheet and send it. Actually you will need to perform Option 1 first, convert the new spreadsheet to PDF, and delete the new spreadsheet after you send it (as PDF).

Option 3 -- make a HTML table (or text table, why not?) from the data of the sheet and send the table.

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • Option 3 is completely viable, I have experience formatting HTML. Is there a way to make the email display in the email body? It just sends raw text. – Ray Montgomery Aug 10 '21 at 14:12
  • Honestly, I haven't tried it yet. Here is the tutorial https://spreadsheet.dev/send-html-email-from-google-sheets As far as I can tell you need to add some html-tags into the plain text that you get from `range().getValues()` (, ,
    , etc) and paste it into a html template. An implementation and template heavily depends on the data you have.
     – Yuri Khristich Aug 10 '21 at 15:58