From Google Sheet Data to Pdf (including url in data spreadsheet) to Email.

[English Connection Utrera]

Dear all,

I am working on a script that can:
1. generates pdfs for each row in a spreadsheet through a function "createbulkPDFs"
2. includes the url in my spreadsheet after creating PDFs
3. email autogenerated pdf through a Function"sendemail" 

I am stuck at part 2. I cannot figure out how to add the url automatically. I am completely new to programming and unfortunately do not know where to find information to complete my task. 

Any help would be greatly appreciated. 

Here is the link to the google sheet, i believe that you should be able open the app script tool form there.
https://docs.google.com/spreadsheets/d/1q9MovpS3ky4xXQro6E4iisSCplALPJ6SawuCcISZDIY/edit?usp=sharing

Thanks for your interest. 

[Scott Bennett]

Can you share what code you have so far?

Scott Bennett

Sent from my iPhone 

On Nov 26, 2021, at 8:33 AM, English Connection Utrera <utr...@englishconnection.es> wrote:

Dear all,

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0fb39672-a227-4c1e-9fdb-b9e75784c4dbn%40googlegroups.com.

[English Connection Utrera]

Yes of course!! I have added my code below.
-----------------------------------

function sendPDFs()

{

}

function createBulkPDFs()

{  

  const pdfFolder = DriveApp.getFolderById("1GVEM9iEoUypMZk2n5MsCv_UTCDD_FQVj");

  const tempFolder = DriveApp.getFolderById("1mudQ4f7TnNupo-SGizgIVA8WVRXUbY0o");

  const templateDoc = DriveApp.getFileById("1o5Y2fdvYOvWAsQCXXD1fGLVAzWyu4A5-kyMweW0hf2w");

                                                                                                        

  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");getRange(getRow(),14).setValue(data.getUrl());

  

  const data = currentSheet.getRange(2, 2, currentSheet.getLastRow()-1, 12).getValues();

  

  data.forEach(row => 

  {

        createPDF(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],"Boletin de notas - 1st term" + " - " + row[1] + " " + row[3],templateDoc,tempFolder,pdfFolder);       

  });

}

 function createPDF(Teacher,Group,Term,Student,UOE,WRIT,READ,LIST,SPEAK,SCORE,GRADE,COMMENT,pdfName,templateDoc,tempFolder,pdfFolder)

{

  const newTempFile = templateDoc.makeCopy(tempFolder); 

  

  const openDoc = DocumentApp.openById(newTempFile.getId());

  const body = openDoc.getBody();

  body.replaceText("{Teacher}", Teacher);

  body.replaceText("{Student}", Student);

  body.replaceText("{Group}", Group);

  body.replaceText("{Term}", Term);

  body.replaceText("{UOE}", UOE);

  body.replaceText("{WRIT}", WRIT);

  body.replaceText("{READ}", READ);

  body.replaceText("{LIST}", LIST);

  body.replaceText("{SPEAK}", SPEAK);

  body.replaceText("{SCORE}", SCORE);

  body.replaceText("{GRADE}", GRADE);

  body.replaceText("{COMMENT}", COMMENT);

  openDoc.saveAndClose();

  const blobPDF = newTempFile.getAs(MimeType.PDF);

  const pdfFile = pdfFolder.createFile(blobPDF).setName(pdfName);

  tempFolder.removeFile(newTempFile);

[Zack Reynolds]

So at the end of your code where you have const "pdfFile =" -- you can follow that with:

"let pdfUrl = pdfFile.getUrl();" and then use that variable to write to your spreadsheet, send in email, etc.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b8c7d041-4823-453e-996a-b353eea85217n%40googlegroups.com.

[Scott Bennett]

Looks like Zach beat me to it.

Scott Bennett

Sent from my iPhone 

On Nov 26, 2021, at 8:51 AM, Zack Reynolds <zackre...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAC%3D0rfhpah3r4kfdGV6SjRno7b1f3jMK4WTCH7bASrgtjdfn1A%40mail.gmail.com.

[English Connection Utrera]

thanks for your help but it is not working. I think that my line of code (the one in bold does not work) 

I am supposed to add " let pdfUrl = pdfFile.getUrl();" in the function "createBulkDFs()"

I have made the following changes:
_______

  const pdfFile = pdfFolder.createFile(blobPDF).setName(pdfName);
let pdfUrl = pdfFile.getUrl();

  tempFolder.removeFile(newTempFile);

[Zack Reynolds]

So you need to move your first bolded line to the end, and it should be using the pdfURl variable, not data.getUrl();

(along with another mistake of trying to use the data variable before you even assigned it)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f3cab1e1-2675-40c5-bca9-3f3f0cff06f9n%40googlegroups.com.

[English Connection Utrera]

It is still not working. 

When i run "createBulkPDFs", i get the pdf correctly created but the link still does not appear in my spreadsheet. 

[adroi...@gmail.com]

I'm pretty sure the code works fine, but I think zack might have over estimated your familiarity with Google apps script based on the caliber of the project you are undertaking. The line of code creates a variable called pdfUrl, but it doesn't add it to the sheet. I believe he thought you were already familiar with that. The code will go something like this.

var spreadsheet = SpreadsheetApp.openByUrl("YOUR SPREADSHEET URL");

var sheet = spreadsheet.getSheetByName("YOUR SHEET NAME");

sheet.appendRow([pdfUrl]);

[adroi...@gmail.com]

I have made an edit which removed a call to a function to a get row function that didn't exist in the project. I changed some small things to that line as well, let me know if it works.

[English Connection Utrera]

I have just run the function "create bulkPDFs" and it did not work. In fact now i get the pdfs generated but the data are no longer included in the pdf. 

I am a complete beginner at using app script. In other word, let's say that i don't speak Chinese and have moved to China a week ago. That's my level when it comes to coding. 

[adroi...@gmail.com]

So I believe you actually saved over the edit I made to the script.

[English Connection Utrera]

Hi,

I have saved the script but i did not change anything you may have changed as i copied the script and put in a new script so i can work on it. 

The original script i was working on was the one called CODE, the one i believe you might have modified. So I copied the original  one and added it to the one called ADD A PDF LINK so that it does not affect anything anyone can modify while i try to figure it ou.