Mail Merge with Google Sheet

Question

i built a google sheet for my team result. I need to send out "KPI report" in PDF for each staff. The name is stored on (B2), and I have set a Vlookup to linkup the staff email (D2) for mail merge. Somehow it doesnt work. It bounds back with below error msg.

Error
Exception: Failed to send email: no recipient (anonymous) @ Trial.gs:37 myFunction @ Trial.gs:12

Can anyone advise solution? https://docs.google.com/spreadsheets/d/15Egyl8q6pgxiCx4Tu0Rv6nRUADQGTEMxoIEvkPBE0D8/edit?usp=sharing

function myFunction() {
// 1. Retrieve values from "Sheet2".
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Individual KPI Report");
 var values = sheet.getRange("A1:O29" + sheet.getLastRow()).getValues();
 var template =  ss.getSheetByName("PDF");
 var ssId = ss.getId();
 var token = ScriptApp.getOAuthToken();
 var filenameOfPdf = template.getSheetName();
 
// 2. Create sheets for converting to PDF data.
 values.forEach(([v], i) => {
   // 1. Copy the sheet `PDF` as the template.
   var temp = template.copyTo(ss).setName("temp" + (i + 1));
   
   // 2. Set the name at the cell "B2".
   temp.getRange("B2").setValue(v);
   SpreadsheetApp.flush();
   var email = temp.getRange("D2").getValue();
   
   // 3. Export the template sheet as the PDF data.
   var subject = "Test Receipt ";
   var body = "\n Please see attached your current test receipt: ";
   var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ssId);
   var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
   + '&size=letter' // paper size legal / letter / A4
   + '&portrait=true' // orientation, false for landscape
   + '&fitw=true&source=labnol' // 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='; // the sheet's Id
   var response = UrlFetchApp.fetch(url + url_ext + temp.getSheetId(), {headers : {'Authorization' : 'Bearer ' + token}}).getBlob().setName(filenameOfPdf + ".pdf");
   
   // 4. Send an email using the email corresponding to the name of "B2".
   if (MailApp.getRemainingDailyQuota() > 0)
   GmailApp.sendEmail(email, subject, body, {htmlBody : body,attachments : [response]});
   
   // 5. Delete the template sheet.
   ss.deleteSheet(temp);
 });
}

Answer 1

I think its the sheet you were referencing to since the email is in Individual KPI Report

var email = sheet.getRange("D2").getValues();