Google App Script. Retrieve emails list from the sheet

Question

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);
}

Answer 1

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:

• forEach()