Related questions

Google App Script. Retrieve emails list from the sheet

2022-05-07 02:34 Antonio Padua imported from Stackoverflow

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

See also questions close to this topic

  • How do I show Popup Message everytime I go to another Sheet using Google Sheet/Apps Script?

    I have 3 sheets in a workbook - Sheet1, Sheet2, and Sheet3, every time I change to a Sheet there is a Popup message that will show up.

    What I had in mind... 

      function onOpen() {
      var ui = SpreadsheetApp.getUi();
      ui.alert('This is my pop up message!');
  }
  • Move row to a different sheet when box is checked

    Hi I'm very new to coding with Google Sheets and Excel. My goal is to move a row when it is checked off.

    The original sheet is called "School Assignments" and the sheet I want to move it to is called "Finished". When a box in column H (column 8) is checked the data value of the row becomes "Done". When it is not marked it is "Unfinished".

    I want the row to move to the Finished sheet when I mark it as "Done". I also want this to work vice versa where when I uncheck and it becomes "Unfinished" it it goes back to the School Assignments sheet. I have some code but honestly it was done months ago (I'm coming back to this project after a break) and I'm not sure what I was thinking when I wrote it or if it is correct at all. It doesn't work obviously.

      if(sh.getName() == "School Assignments" && e.range.columnStart == 8 && e.value == "Done") {
    let tsh = e.source.getSheetByName("Finished");
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(tsh.getRange(tsh.getLastRow() + 1, 1));
    sh.deleteRow(e.range.rowStart);
  } else if(sh.getName() == "Finished" && e.range.columnStart == 8 && e.value == "Unfinished") {
    let tsh = e.source.getSheetByName("School Assignments");
    let trg = tsh.getRange(tsh.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(trg);
    sh.deleteRow(e.range.rowStart);
  }
}

    I also have an auto-sorting piece earlier in the code which defines some constants.

        const ss = SpreadsheetApp.getActiveSpreadsheet()
    const ws = ss.getSheetByName("School Assignments")
    const range = ws.getRange(2,1,ws.getLastRow()-1,8)
  
    


    range.sort({column: 7, ascending: true })
  }

    I am totally unsure how to fix this and accomplish what I'm looking to do.

  • PERCENTILE IF using ARRAYFORMULA for a set of conditions

    I need to calculate the percentile using an if condition to calculate it by group of conditions, but Google Sheets doesn't provide PERCENTILEIF function. A nonarray solution is possible:

    =ARRAYFORMULA(PERCENTILE(if(range=value,values),percentile))

    but in my case value should be an array of possible values.

    Here is the sample data with the expected result highlighted: sample

    I tried several options to use an array of possible values, but in all cases, I get the wrong result:

    Using JOIN in G2:

    =arrayformula(if(len(E2:E3),percentile(split(regexreplace(join(",",
   Arrayformula(A2:A12 & "_" & B2:B12)),E2:E3  & "_(\d+)|.",",$1"),","),D2),))

    Using MATCH in H2:

    =ARRAYFORMULA(if(len(E2:E3),
   PERCENTILE(IFNA(--(match(A2:A12,E2:E3,0) > 0) * B2:B12,),D2),))

    here is the Spreadsheet file: https://docs.google.com/spreadsheets/d/1VDJIYvmOC46DI_9u4zSEfmxSan5R5VKK772C_kP5rxA/edit?usp=sharing