Apps Script to Merge a Google Sheet into a GDoc and Create a PDF

By /

This is a Google Apps Script to create a PDF file using a Google Doc template and the values in a Google Spreadsheet.

It takes the fields from the active row in your Google Spreadsheet (the one with a highlighted cell or row) and using a Google Doc template (identified by GDOC_TEMPLATE_ID) creates a PDF doc with these fields replacing the placeholders in the template. The place-holders are identified by having curly brackets around them, e.g. {{Name}}. It is run using the custom “Create PDF” menu that is created in the sheet.

There are many variations on the PDF creation theme.

And here is a function for formatting various number and date types properly into your final document.

Get in touch now if you need any help with this or any other GAS scripts or have a look through some other free scripts and snippets.
So to use:

  1. Create a Google Doc as a template using the placeholders with curly brackets around the words, e.g. {{Name}}. Make a note of the ID number (https://docs.google.com/document/d/ID NUMBER HERE/edit).
  2. Create a new Google Spreadsheet, or using an existing one, and use placeholders which are the names of the columns with double curly brackets, e.g. Name or Age (it is case-sensitive).
  3. Copy the script into Code.gs in the script editor (Tools > Script editor).
  4. Paste the template ID number into GDOC_TEMPLATE_ID value (between the ”s)
  5. Set the various configuration settings:
    1. Whether you want to produce a GDoc or PDF
    2. What you want the name of the file to be
    3. Whether you want it sent in an email
    4. Where you would like the new file to be stored
  6. Put the actual values you want to use in the spreadsheet under the appropriate columns, one row for each PDF file.
  7. Click a cell on a row of values you would like to use to select that row and use the “Create PDF > Create PDF”.

The new PDF will be created after a few moments.

If you are looking for a “Gmail merge” rather than a “GDoc merge”, take a look at this great article from Martin Hawksey.

There is a Gist where you can also see the code and A demo spreadsheet.

If you found this script useful why not buy me a coffee to support me create more free content list this.

Share

103 thoughts on “Apps Script to Merge a Google Sheet into a GDoc and Create a PDF”

  1. JP Carlin

    First let me thank you for creating this post. This is a neat little snippet and easy to follow. I have a question though. I’m not familiar with the method you’ve used here- copyBody = copyDoc.getActiveSection() but in testing it looks like it works perfectly. Is it the same as .getBody()? Where did you find out about it? Thanks for your help if you have time to answer.

  2. Mike Kelly

    Hey Andrew,

    This script is awesome. I got it working pretty quickly thanks to your instructions. I was hoping you might be able to answer a couple questions for me.

    Is it possible to modify this to change the name of the pdf document? This first to columns of my spreadsheet are building and room, would there be a way to include those in the title?

    Is there a way to modify this to create multiple pdf from multiple rows simultaneously?

    Thanks,
    Mike

    1. Andrew Roberts

      Hi Mike,

      Glad you found the script useful!

      “Is it possible to modify this to change the name of the pdf document?”

      I’ve not tested this but you could probably achieve this by can replace the line

      DriveApp.createFile(copyFile.getAs("application/pdf"));

      with

      DriveApp.createFile('New Text File', copyFile.getAs("application/pdf").getDataAsString());

      “This first to [sic] columns of my spreadsheet are building and room, would there be a way to include those in the title?”

      You can use the headerRow array to concat a title, e.g.

      var fileName = 'Building: ' + headerRow[0][0] + ' Room: ' + headerRow[0][1];

      “Is there a way to modify this to create multiple pdf from multiple rows simultaneously?”

      There is: you’d need to put a loop around createPdf() and pass it a row index rather than using the active row.

    1. Andrew Roberts

      Hi Tim,

      You’d need to alter the logic to step down the rows rather than across the columns. Drop me a line if you work it out and I’ll post it here. I’ll have a go if I get time.

      Andrew

  7. Arne

    I have tried to make a similar script to produce an invoice but when I call openById (var invDoc=DocumentApp.openById(invFile.getId());) I get an error message saying that I do not have permission to call openById. Why is this?

    1. Andrew Roberts

      Hi, Have you checked you actually do have permission to access the new file? I’d be happy to take a look at your script if you share a link with me (andrewr1969 ‘at’ gmail ‘dot’ com).

  9. Colin Greaves

    Amazingly (with my level of knowledge), I actually got this script to work! Great work Andrew, thank you.

    Probably barking up the wrong tree entirely here, but is there any way to adapt this script at all to incorporate other data in the PDF, say for instance a chart of some kind? Or is it strictly text only?

  10. Sarah Cornwell

    Andrew,

    Could you elaborate on your answer to Mike? To create a pdf with a specific name, replace
    DriveApp.createFile(copyFile.getAs(“application/pdf”));

    with

    DriveApp.createFile(‘New Text File’, copyFile.getAs(“application/pdf”).getDataAsString());
    var fileName = ‘Building: ‘ + headerRow[0][0] + ‘ Room: ‘ + headerRow[0][1];

    That gives me a jumbled text file. Can you let me know what I’m missing?

    1. Andrew Roberts

      You’re right that just produces garbage so I’ve added an extra variable at the top of the script (PDF_FILE_NAME) that lets you specify the file name. You could set this dynamically in the script with a field from the spreadsheet if that was more useful.

    1. Andrew Roberts

      Hi, I’m afraid it’s not possible to edit the existing PDF with this script (you’d have to look at Adobe’s PDF API for that, which would be beyond the scope of this script) just create a new one each time.

  13. varun

    Thanks for this article. I need help with two tweaks
    1) how do i save the pdf in a particular folder instead of root folder
    2) how do i also add an option which not only saves the pdf in my driver folder but also pops up a save as window to download on my hard disk?

    1. Andrew Roberts

      Hi,

      1) By default the file is saved in the root, so you’d need to add some code to move it. Take a look at the DriveApp.Folder docs, particularly Folder.addFile(). So get the folder id you want to move it to, use this to get it’s Folder object and then use addFile().

      2) That’s an interesting one! Take a look at File.getDownloadUrl() which will give you a link that should initiate a download when you click it. Take a look at my Dialog library for an easy way to show a dialog that includes some HTML (your link).

      These are both useful features, so drop me a line if you get them working and I can include them in the script!

      Cheers,

      Andrew

  15. Marcus Flores

    Hello. I appreciate your instructions and believe this is exactly what I am looking for, but I am having problems with the last step. I am unable to find the CreatePDF function. I have saved it to the script editor, but I do not know how to call it from the sheet. Can you please assist?

  16. Pingback: Google Apps Script to create and email a PDF - Andrew Roberts

  18. Pingback: PDF Creator - Email form submission in a PDF - Andrew Roberts

  19. youssef

    this is a very easy solution

    function SheetToPDF() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var gid = sheet.getSheetId();
    var id = ss.getId();
    var name = “pdfname”;
    var blob = DriveApp.getFileById(id).getAs(“application/pdf”);
    blob.setName(name + “.pdf”);
    DriveApp.createFile(blob);
    Browser.msgBox(“PDF created in Drive”);
    }

  20. Pingback: Apps Script to create a PDF from a multi-sheet Google Sheet - Andrew Roberts

  21. Ranu

    Thanks for the wonderful example. I am beginner for GAS. I tried to execute this but I am getting error “No item with the given ID could be found, or you do not have permission to access it”

    Can you please let me know what can be the issue?

    Thanks!

    1. Andrew Roberts

      I’m guessing this refers to the GDoc used as a template. Check that you have access to it from your account and that you’ve copied the ID over OK. If you still have a problem you can share the script with me (andrew@roberts.net) if you would like me to take a look. Andrew

  23. Pingback: Create a PDF from each of the rows in a Google Sheet - Andrew Roberts

  29. Kashif Iqbal

    Hi, Thank you for sharing such a useful stuff. I am stuck at one point. I didn’t understand your instruction before script you written to create documents as a templates etc… Can you explain again. As this script give me issues. I am unable to run it. Thank you

  30. Jono

    Hi Andrew,
    I’d like to try this script out but I will need to tweak i believe. I’d like to create in invoice pdf, each row would be a different line item in the final pdf.
    Any pointers as to which part I need to tweak would be appreciated.
    THanks Jono

  31. David Ridley

    Hi – thanks for sharing this great script! Couple of questions from a n00b:

    1) How could I modify the script to run for all rows of data in my spreadsheet, instead of just the active row?
    2) I’m not sure how to use the (PDF_FILE_NAME) to create a name for my PDF. If I have a column in the dataset called “Filename”, how could I incorporate that to be the name of my PDF?

  32. Marek

    Hello Andrew,
    firstly many thanks for an awesome script!

    It works smooth from the begining, however I have an issue when the numbers from sheets in decimals eg. 10.80, 9.00 runs to Docs/PDF and change into 10.8 and 9.

    Is there an “easy” way to fix it, that there will be everytime 2 decimals?

    1. Andrew Roberts

      You’ll need to look out for numbers and apply your own formatting rather than letting the script use it’s default:

      for (;columnIndex < headerRow[0].length; columnIndex++) {
      var value = activeRow[0][columnIndex];
      if (typeof value === ‘number’) {
      value = Utilities.formatString(‘%.2f’, Math.round(value * 100) / 100);
      }
      copyBody.replaceText(‘%’ + headerRow[0][columnIndex] + ‘%’, value);
      }

      1. Josh Hester

        How do I make sure that “$” come through as well with my numerals? In my spreadsheet, I have financial numbers listed with their respective dollar signs yet when I run this script it only grabs the numerals without the dollar sign.

  34. Krof

    Sorry . English is not my language. I asked the wrong question. One replacement element is in the doc header and the script can not replace it.
    I tested
    var copyHeader = copyDoc.getHeader ()
    copyHeader.replaceText (‘% element%’, ‘3333333’)
    did not work

  35. Dave Jackson

    I was using Autocrat to pick up fields from a Google Sheet – Autocrat developers (volunteers) were having problems – I found your code which is excellent and gave me full control again – BIG THANKS!

    The merge of %fields% into the Google Doc ‘template’ went well and displayed as desired for many days of testing.

    This morning, the font size is not being carried through to the pdf file. As a hint, when I first display the *template* there is a lag until Google finishes the formatting. What I am getting with the pdf is the pre-final-Google-formatting which now overflows the page. Suspect this is a Google problem? Any thoughts?

  38. Dave

    Hi Andrew, thanks for this great code. It’s very useful and I’m using it to create student records. But I have a problem as exporting images to pdf. I have student photos in google drive and I want to place images with row data in pdf file. Everything is fine except images don’t show up. What can I do for image printing? Thanks again.

  39. Madhu

    Hi Andrew, Thanks for the great article. I have an image inserted into one of the cells of the spreadsheet using =image(). When the pdf is generated using the script the image is not coming in the pdf. How to have the image in the pdf.

  40. Matthew S.

    Hi Andrew,
    I have the data in my spreadsheet set up in columns instead of rows.
    Any idea of how to modify this script to work based on the selected column instead of row?
    Thanks!

  41. Adam

    Hi there,

    Thanks for the script!

    When I have included in the spreadsheet which I want to export to a pdf and mail it I get the following error message:

    “The document is missing. It has possible been deleted”

    ?

    I use the ID after /d/ as template ID…

    Cheers,
    Adam

  44. Joe F

    This works amazingly! Thank you!

    I only have one question. Two of my columns contain dates… Is there anyway to easily format those dates when they are copied over? For instance, instead of writing Thu Jul 19 2018 00:00:00 GMT-0500 (CDT) is it possible to write 7/19/2018?

  47. Alberto M. Popelka

    Hi, first thanks for sharing the scripts and answering doubts.
    Maybe you can guide me with this:
    1. I’d like to add several rows to one PDF, (only the ones that accept a given condition).

    2. Can the date of creation of pdf be added to the filename?

    thanks !

  49. Jeff

    Thanks Andrew. This is super helpful and really cool. I’ve noticed when I try to edit and change place-holders or add more, etc. I go back and “run” the PDF creation again it will usually just return the place-holder names as opposed to the values. I’ve been able to change the file name or create a new Sheet and try that row again and get the first PDF created to work and then the same issue comes up when trying to run a 2nd or 3rd time. Is there a better way to reset or “try again” with the same row from the spreadsheet? Thanks!

    1. Andrew Roberts

      1) How can i create only the doc instead the pdf

      See the previous answer.

      2) How to save it on pc

      This is trickier – you’ll need to set up a web app that accesses the download link for the PDF (you can’t download a GDoc unless you convert it to something else)

      3) how to format a column values in currency like € xxxxxxx,xx

      Look at Utilities.formatString()

  52. Pingback: Formatting a value as a string - Andrew Roberts

  53. Javier Palacios

    Hello, your script works very well, but I have to share the spreadsheet with another person inside gsuite to use it. DriveApp usage permissions are activated for all users.
    When a user tries to run the script from the shared spreadsheet, an access denied error appears. Thank you very much for your help.

  54. kristi R

    Hi Andrew. Very cool script! 2 questions:
    1. Is there any way to direct that the PDF file get created in the folder location of the google sheet itself?
    2. How do use one of the column names as the PDF file name? I am puzzling over the syntax to insert a variable here…I tried double quotes and that didn’t work… My column name is Name of Referred CARE RECIPIENT.
    var PDF_FILE_NAME = ‘”Name of Referred CARE RECIPIENT”‘

    Thank you!

  55. Kristi Rinehart

    Also – it doesn’t appear that the copyFile.setTrashed(true) command is working to delete the GDoc template that is getting copied. Each time the CreatePDF is run, this extra GDoc is created and left in the folder where it was created (which is not My Drive’s root). Any thoughts?

  56. John

    Hi Andrew, Great script!

    I am wanting to modify it to take multiple rows of data and adding them to the pdf template. Is this something you have looked at before?

    Im trying to generate a monthly invoice of telephone consultations. each one has a row in the spreadsheet. I want to populate a table in the pdf with the spreadsheet data.

    Any help would be greatly appreciated!

    Best

    John

Leave a Comment

Scroll to Top