1

Just need your help and advise. I am quite new in coding stuff. Recently, I have coded below stuff. I was expecting to fill in all the blanks on google form including users' email as long as the form has been submitted, it will be recorded to a google spreadsheet and generate a pdf then send to the email address that was typed in the google form. I have tested below scripts, now function afterFormSubmit and function createPDF both working the pdf file could be generated properly and all the information is populated into the google doc properly then convert to pdf formate however function sendEmail does not work. When I run function sendemail , it returns error message "Exception: Invalid argument: attachments (line 36, file "Code")" Could someone please help ?

function afterFormSubmit(e) {
  const info = e.namedValues;
  const pdfFile = createPDF(info); 
  const entryRow = e.range.getRow();
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  ws.getRange(entryRow, 7).setValue(pdfFile.getUrl());
  ws.getRange(entryRow, 8).setValue(pdfFile.getName());
  sendEmail(e.namedValues['Student Email'][0],pdfFile);
}


function sendEmail(currentEmail,pdfFile){
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").activate();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  var quotaLeft = MailApp.getRemainingDailyQuota();
  for (var i = 2;i<=lr;i++){
    
    var currentEmail = ss.getRange(i, 6).getValue();
    var currentStudentName = ss.getRange(i, 2).getValue();
    var currentName = ss.getRange(i, 2).getValue(); 
    var messageBody = templateText.replace("{recipient}",currentName);
    var subjectLine = "Congratulations on your merit!" + currentStudentName;
    GmailApp.sendEmail(currentEmail, subjectLine, messageBody, {
      attachments:[pdfFile],
      name: 'St Clare Merits'});
  }
}

function createPDF(info) {
  const pdfFolder = DriveApp.getFolderById("1WIgfR7YESk6fcRPbzKa_Gsi_yp3-gemg");
  const tempFolder = DriveApp.getFolderById("1QgnNLCbmm1z9RvPQCjA-vSnuw-P1Fw4X");
  const templateDoc = DriveApp.getFileById("1fh5uRKQ9WZOY-ngXK0sOxH85UX_HHR1HYDefQhjfZVI");
  const newTempFile = templateDoc.makeCopy(tempFolder);
  const openDoc = DocumentApp.openById(newTempFile.getId());
  const body = openDoc.getBody();
  body.replaceText("{name}", info['Student name'][0]);
  body.replaceText("{reason}", info['Reason for Merit'][0]);
  body.replaceText("{Sname}", info['Staff Name'][0]);
  body.replaceText("{Date}", info['Date'][0]);
  openDoc.saveAndClose();
  const blobPDF = newTempFile.getAs(MimeType.PDF);
  const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Student name'][0]+ " " + new Date());
  newTempFile.setTrashed(true);
  return pdfFile; 
}
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
Anfernee
  • 11
  • 2
  • Your attachments have to be an array of `[BlobSource]` objects, not `[File]`s. Try returning `blobPDF` from `createPDF`, not `pdfFile`. See [Grabbing multiple attachments with a specific file name in Google Appscript](https://stackoverflow.com/questions/64165255/grabbing-multiple-attachments-with-a-specific-file-name-in-google-appscript) – Rafa Guillermo Oct 22 '20 at 07:47
  • Hi Rafa What exactly mean by an array of Blobsource? I just want to generate 1 pdf to be sent off the the email that was typed in the google form. Could you please show me the correct code for attachments argument in my script ? thx – Anfernee Oct 22 '20 at 08:47
  • Check the answer I linked. You are attaching `[pdfFile]` which has a PDF type but you actually need to attach a file which is a `BlobSource` object. https://developers.google.com/apps-script/reference/gmail/gmail-app#sendemailrecipient,-subject,-body,-options – Rafa Guillermo Oct 22 '20 at 08:49
  • You attachments syntax is corect but you're attaching the files in the wrong structure. Try `return blobPDF` from `createPdf` not `return pdfFile`. – Rafa Guillermo Oct 22 '20 at 08:51
  • in the link example, it is showing attachments: [file.getAs(MimeType.PDF)], and it has to define var file = DriveApp.getFileById('1234567890abcdefghijklmnopqrstuvwxyz'); . In my example, I do not have such file to define in var because my script will need to generate a pdf file from a google doc template with populating the data that was collected from google forms. it does not have such id. I am quite new to these coding stuff. so please show me the change in my example if you can ? thank you. – Anfernee Oct 22 '20 at 08:57
  • Change `return pdfFile` to `return pdfFile.getAs(MimeType.PDF)`. – Rafa Guillermo Oct 22 '20 at 09:01
  • //return pdfFile; return pdfFile.getAs(MimeType.PDF); ReferenceError: PDFs is not defined at sendEmail – Anfernee Oct 22 '20 at 09:06
  • I don't understand where this is coming from. – Rafa Guillermo Oct 22 '20 at 09:10
  • email still could not be sent out. showing new error ReferenceError: PDFs is not defined at sendEmail after I change code to return pdfFile.getAs(MimeType.PDF) in my original code. – Anfernee Oct 22 '20 at 09:11
  • But your code doesn't have any reference to `PDFs`? – Rafa Guillermo Oct 22 '20 at 09:12
  • Exception: Invalid argument: attachments at sendEmail Now showing same error again – Anfernee Oct 22 '20 at 09:20
  • Okay, rather than `attachments: [pdfFile]`, instead declare a variable `var attach = [];` and push the file to is with `attach.push(pdfFile)` before GmailApp, and then send with the option `attachments: attach`. – Rafa Guillermo Oct 22 '20 at 09:22
  • `var attach = []; attach.push(pdfFile); GmailApp.sendEmail(currentEmail, subjectLine, messageBody, { attachments: attach, name: 'St Clare Merits'});` like so – Rafa Guillermo Oct 22 '20 at 09:23
  • var attach = []; attach.push(pdfFile); GmailApp.sendEmail(currentEmail, subjectLine, messageBody, { name: 'St Clare Merits', attachments: attach, No email with attachment sent. if click on run showing Exception: Invalid argument: attachments – Anfernee Oct 22 '20 at 09:29
  • of coz I saved first – Anfernee Oct 22 '20 at 09:30
  • Okay so I copied your code exactly, set up a test environment and ran the code. The things I assumed are: 1. `pdfFolder` and `tempFolder` both exist on your Drive. 2. `templateDoc` is a Google Docs file. I put in dummy text in the Sheet and hard coded values for `currentEmail`, `currentStudentName` etc, and the whole code worked, I received the email with no error. – Rafa Guillermo Oct 22 '20 at 09:51
  • do you mean my original code working fine ? – Anfernee Oct 22 '20 at 09:59
  • my code was working fine a while ago but just stopped working now and returns this error saying attachments invalid. – Anfernee Oct 22 '20 at 10:00
  • Yes, your code exactly as it is. The only things I edited were hard-coded values to use some dummy data. What have you changed since it was working? – Rafa Guillermo Oct 22 '20 at 10:05
  • Nothing was changed. I post as what it is. just suddenly stopped working with such an invalid argument error coming up one day. – Anfernee Oct 22 '20 at 10:18
  • If i remove attachments and name from my original code in sendemail function , i still can receive the email without any attached pdf. but i need the pdf as attached in my work. – Anfernee Oct 22 '20 at 10:26
  • Just curious - if you copy this code into a new project and run it, do you get the same issue? You will have to replace all instances of `SpreadsheetApp.getActiveSpreadsheet()` to `SpreadsheetApp.openById()` to do this, though. Just trying to troubleshoot, I am wondering if this is project-specific. – Rafa Guillermo Oct 22 '20 at 12:56
  • Hi Rafa , after I replace SpreadsheetApp.getActiveSpreadsheet() to SpreadsheetApp.openById(), I get the following error Exception: The parameters () don't match the method signature for SpreadsheetApp.openById. (line 13, file "Code") – Anfernee Oct 22 '20 at 21:18
  • You need to put your Spreadheet's ID in the function call... like `SpreadsheetApp.openById("spreadsheet-id"`... – Rafa Guillermo Oct 29 '20 at 08:11

0 Answers0