Google App Script. Retrieve emails list from the sheet

In the following script I have to manually insert the email addresses. I rather get the list from the sheet to be able to update it without editing the script. How do I retrieve a list of email addresses from the sheet (Sheet!A2:A)?

    function sendReport() {
  
  EMAIL = 'email'
  URL = 'url'
      + '/export?'
      + 'format=pdf'
      + '&size=A4' 
      + '&gid=id' 
      + '&scale=4'
      + '&portrait=true';

  
  SpreadsheetApp.getActive();

  var response = UrlFetchApp.fetch(URL, {
    headers: {
      'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
    }
  });

  var message = {
    to: EMAIL,
    subject: "subject",
    body: "body",
    attachments: [response.getBlob().setName('file.pdf')]
  }

  MailApp.sendEmail(message);
}

1 answer

  • answered 2022-05-07 02:40 Tanaike

    Is the script you expect as follows?

    Modified script:

    function sendReport() {
      URL = 'url'
        + '/export?'
        + 'format=pdf'
        + '&size=A4'
        + '&gid=id'
        + '&scale=4'
        + '&portrait=true';
      var response = UrlFetchApp.fetch(URL, {
        headers: {
          'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
        }
      });
    
      // Retrieve values from Spreadsheet.
      var sheet = SpreadsheetApp.getActiveSheet();
      var values = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues();
      
      // Send the email using the email addresses.
      values.forEach(([email]) => {
        if (email) {
          var message = {
            to: email,
            subject: "subject",
            body: "body",
            attachments: [response.getBlob().setName('file.pdf')]
          }
          MailApp.sendEmail(message);
        }
      });
    }
    
    • If you want to set the specific sheet, please modify var sheet = SpreadsheetApp.getActiveSheet(); to var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");.

    Reference:

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum