Google Sheets - Create PDF and Send Email (Different PDFs to Different Emails)

Question

I have a Google Sheet that updates information based on an Athlete Name that is in a specific cell (A24 in the "Dashboard" worksheet). Once the athlete name changes (e.g., A24 changes to a different athlete name), the information on the "Dashboard" worksheet changes to reflect the data for that athlete.

I have a list of athlete names (athleteList) and athlete emails (emails). Essentially, I want the script to do the following in a loop:

1. Update athlete name to athlete[i] in athlete list
2. Create PDF of the "Dashboard" worksheet (which is now updated to be athlete[i])
3. Send email of that PDF to athlete[i] (e.g., using their email, which is email[i]).

I'm new to Google Apps Script so I hope this makes sense... I'm very close. The emailing works fine with the code below, but the PDFs being created are not the right ones for the people receiving the emails. Any assistance would be appreciated. Thank you!

Link to editable sheet (different URL and GID than code): https://docs.google.com/spreadsheets/d/1Amkc1tDgLgaNxKCos95vsZEaxdyd67Xcdtfc4NEQT60/edit?usp=sharing

APPS SCRIPT CODE CODE:

     //Google Sheets URL Edit (Athletes Sheet Name)
     //Google Sheets URL Edit (Dashboard Sheet Name)
     //Google Sheets URL Export (Dashboard Sheet Name)
    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/edit/");
    var ssAthletes = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/edit/").getSheetByName("Athletes");

    var ssDashboard = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/edit/").getSheetByName("Dashboard");

    var ssDashboardExport = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/export?/").getSheetByName("Dashboard");

        var lastRow = ssAthletes.getLastRow()-1; //Define last Row
        var athleteList = ssAthletes.getRange(2,1,lastRow).getValues(); //get list of Athletes (Starting at row 2, column 1)
        var athleteEmails = ssAthletes.getRange(2,2,lastRow).getValues();



//THIS FUNCTION EMAILS as PDF

function emailSpreadsheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  // const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/edit");

  // We are going to get the email address from the cell "E2" from the "Lists" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  const value = ss.getSheetByName("Lists").getRange("E2").getValue();
  const email = value.toString();

    for (var i = 0; i < athleteList.length; ++i) {
    athleteName = athleteList[i];
  }

  // Subject of the email message
  const subject = 'Your Report';

  // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Hey " + athleteName + "! Great work today. Here is your report for " + Date();

  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = 'https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=680445839'; // the sheet's Id. Change it to your sheet ID.
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  // Generate the PDF file
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: "Dashboard" + Date() + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    })}



//THIS FUNCTION EMAILS MULTIPLE PEOPLE as PDF -- WORK IN PROGRESS!!!

function emailSpreadsheetAsPDFMulti() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  // const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/edit");

  // We are going to get the email address from the cell "E2" from the "Lists" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  const emails = ss.getSheetByName("Athletes").getRange(2,2,lastRow).getValues();
  const emailsString = emails.toString();

 // for (var i = 0; i < athleteList.length; ++i) {
 //  athleteName = athleteList[i];
 // }

  for (var i = 0; i < emails.length; ++i) {
    athleteEmail = emails[i];
    athleteName = athleteList[i];
  }

  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = 'https://docs.google.com/spreadsheets/d/1z15EwpuITPnmqPAIwHdxyV6DFDGMLBWKCxpDCQrGtBw/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=680445839'; // the sheet's Id. Change it to your sheet ID.
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid number of the sheet is on the end of your link.
  


// Cycle through athletes

  for (var i = 0; i < athleteList.length; i++) {
    var output = [];
    ssDashboard.getRange('A24').setValue(athleteList[i][0]);
    output.push([athleteList[i][0]]);
    Utilities.sleep(10000);
    
  

 // Generate the PDF file
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();

// Subject of the email message
  const subject = 'Report'

  var athletename = athleteList[i][0];

  // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Hey " + athletename + "! Great work today. Here is your report for " + Date();

console.log(output);
console.log(response);
console.log(body);
console.log(athletename);
console.log(emails[i]);

// Send the PDF file as an attachement 
    GmailApp.sendEmail(emails[i], subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: "Dashboard" + Date() + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
    }]
    })}}'''

Answer 1

I think you almost got it. This is how I would do it. Please don't forget to update the Id of the spreadsheet

function updateInfo(){

 // I get the emails from the Athletes sheet, hope is ok, otherwise you can point in to another range
  var athleteSheet = SpreadsheetApp.getActive().getSheetByName("Athletes");  
  var athleteList = athleteSheet.getRange("A2:B"+athleteSheet.getLastRow()).getValues();
 
  for (i=0;i<athleteList.length;i++){
    var name = athleteList[i][0];
    var email = athleteList[i][1];
    SpreadsheetApp.getActive().getSheetByName("Dashboard").getRange("A24").setValue(name);
    SpreadsheetApp.flush(); // this is important for the changes in the spreadsheet to take place
    sendInfo(name, email); // then I send the emails
  }
}

Now I used a very similar function to yours and send the files

function sendInfo(name, email){
   // change the id (this is the id of my file, a copy of yours)
    var ss = SpreadsheetApp.openById('1uftGO8ACoQTE8LtjURjIG2g5G-x3B-taIobVxzvJA2E'); 
    var sheet = ss.getSheetByName("Dashboard");
    var ssId = ss.getId();

    // export url
    var url = 'https://docs.google.com/spreadsheets/d/'+ ssId +'/export?'
      + 'exportFormat=pdf&format=pdf'
      + '&size=letter'                           // paper size legal / letter / A4
      + '&portrait=true'                     // orientation, false for landscape
      + '&fitw=true'                        // fit to page width, false for actual size
      + '&sheetnames=false&printtitle=false' // hide optional headers and footers
      + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
      + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
      + '&gid='+sheet.getSheetId();    // the sheet's Id
  
    var headers = { 
       'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
    }

  // request export url
    var response = UrlFetchApp.fetch(url, { headers: headers });
    var blob = response.getBlob();
    const subject = 'Your Report';
    const body = "Sent via Generate Dashboard Report from Google Form and print/email it";
 
    GmailApp.sendEmail(email, subject, body, {
        htmlBody: body,
        attachments: [{
              fileName: "Dashboard.pdf",
              content: blob.getBytes(),
              mimeType: "application/pdf"
          }]
      });

    DriveApp.createFile(blob.setName(name+".pdf"))

}

By the way, nice work you have done with those spreadsheets.