Google Spreadsheet script using SpreadsheetApp.getActiveSheet(), changing to work in other sheet

1
votes

I am using a script with Google Spreadsheet to send an email. My file has 3 sheets, this one that sends email, is named "EMAIL". The code to send is this:

function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var emailAddress = '[email protected]'; 
    var subject = 'Subject BlaBlaBla';  
    var dados = sheet.getRange ("B2:M10").getValues(); //range with mail content
    var message2 = getTable(dados); //format the range in html table

    //Envia o email em formato HTML
    MailApp.sendEmail({
      to: emailAddress,
      subject: subject,
      htmlBody: message2
    });
}

I have a button in "EMAIL" sheet that start sendEmails(). It works fine.

My problem is:

I wanna put a button in other sheet, called "2019" that should start the email sending too, without having the sheet "EMAIL" active. What should i change on this code to work this function sendEmails() without having the "EMAIL" sheet active?

var sheet = SpreadsheetApp.getActiveSheet();

I have to change to something that keeps this function working

1
google-apps-scriptgoogle-sheetsspreadsheet

1 Answers

2
votes
  • You want to run the function of sendEmails() by giving the sheet name.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Pattern 1:

In this pattern, the script is run for the sheet name of 2019.

Modified script:

var sheet = SpreadsheetApp.getActiveSheet();

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2019");

Pattern 2:

In this pattern, when the active sheet is EMAIL and 2019, the script is run.

Modified script:

function sendEmails() {
  var sheetNames = ["EMAIL", "2019"];

  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheetNames.indexOf(sheet.getSheetName()) > 0) {
    var emailAddress = '[email protected]'; 
    var subject = 'Subject BlaBlaBla';  
    var dados = sheet.getRange ("B2:M10").getValues(); //range with mail content
    var message2 = getTable(dados); //format the range in html table

    //Envia o email em formato HTML
    MailApp.sendEmail({
      to: emailAddress,
      subject: subject,
      htmlBody: message2
    });
  }
}

References:

If I misunderstood your question and this was not the result you want, I apologize.