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

CodePudding user response：

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()