How I Automated PDF Form Filling with Google Sheets for Free (Step-by-Step)

Two years ago, I almost quit a high-paying client project out of sheer boredom. My job was simple on paper: take 45 client onboarding entries from a Google Sheet every Friday and manually type them into individual PDF contracts. Name, address, package rate, dates... click, copy, paste, save as PDF. By month three, I was making silly typos and wasting three full hours every week on work a machine should be doing.

I looked into paid tools like Zapier and PDF.co. They work great, but the costs add up quickly when you are generating dozens of documents monthly. Being cheap (and slightly stubborn), I decided to dig into Google Apps Script.

It turns out, you can build your own automated PDF builder using native Google tools without spending a single dime. Let me show you exactly how I built it so you can steal my setup today.

Why Pay for Automation Tools When You Have Google Apps Script?

Before we jump into the code, let us address the elephant in the room. Why not just buy a tool?

Most SaaS products cap your monthly tasks on their free tiers. You run 100 workflows, hit a paywall, and suddenly you are paying $20 a month for a simple script. Google Apps Script runs directly inside your Google Drive, giving you up to 90 minutes of daily execution time for standard free accounts. That is enough to generate hundreds of PDFs every single day.

Besides saving money, building this yourself means complete privacy. Your sensitive client data stays inside your own Google ecosystem instead of passing through third-party servers.

My colleague Sarah was hesitant because she had zero coding experience. But after I handed her this exact workflow, she had her first invoice automated in less than fifteen minutes. Trust me, if she can do it, you can too.

What You Need Before Setting Up

To make this magic trick work seamlessly, we will use Google Docs as our design canvas and output the final file as a PDF. Make sure you have these three items ready in your Google Drive:

1. A Google Sheet with Your Data

Create a basic sheet with clear column headers in the first row. For example: First Name, Last Name, Email, Service Fee, and Date.

2. A Google Doc Template

Design your document or contract in Google Docs exactly how you want it to look. Wherever you want sheet data to appear, use double curly braces as placeholders. For instance: Dear {{First Name}}, your total fee is {{Service Fee}}.

3. A Destination Drive Folder

Create a fresh folder in Google Drive where your newly minted PDF files will be saved automatically.

Pro Tip: Keep your Google Doc template clean and minimal. Complex tables or heavily stacked floating images can occasionally misalign during the automated PDF export process.

Step-by-Step: Setting Up Your Free Script

Follow these steps carefully. You do not need to understand every line of code—just copy, paste, and adjust the spreadsheet column titles.

1. Open your Google Sheet containing your data.
2. Click on Extensions > Apps Script in the top menu bar. Delete any code currently in the editor.
3. Paste the following lightweight JavaScript snippet into the window:

function createPDFs() {
  const docTemplateId = "YOUR_GOOGLE_DOC_TEMPLATE_ID"; 
  const folderId = "YOUR_DESTINATION_FOLDER_ID"; 
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const targetFolder = DriveApp.getFolderById(folderId);
  
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    // Skip rows marked as processed to avoid duplicates
    if (row[headers.indexOf("Status")] === "Done") continue;
    
    const copy = DriveApp.getFileById(docTemplateId).makeCopy(row[0] + " Document", targetFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    
    headers.forEach((header, index) => {
      body.replaceText("{{" + header + "}}", row[index]);
    });
    
    doc.saveAndClose();
    
    // Convert to PDF and remove temporary Doc file
    const pdfFile = targetFolder.createFile(copy.getAs('application/pdf'));
    copy.setTrashed(true);
    
    // Mark row as processed
    sheet.getRange(i + 1, headers.indexOf("Status") + 1).setValue("Done");
  }
}

4. Replace YOUR_GOOGLE_DOC_TEMPLATE_ID and YOUR_DESTINATION_FOLDER_ID with the actual IDs from your browser URLs. (The ID is the long string of letters and numbers between /d/ and /edit in your browser address bar).
5. Add a column titled Status in your Google Sheet so the script knows which rows it has already created PDFs for.
6. Click the Save icon, then click Run.

Google will prompt you to authorize permissions the first time you run it. Click through the safety prompts to grant your script access to your own Drive account.

Fine-Tuning and Troubleshooting Common Mistakes

When I first deployed this snippet, I hit a few annoying speed bumps. Save yourself the headache by keeping these two edge cases in mind.

Handling Date and Currency Formatting

Google Sheets sometimes exports raw dates and numbers into Apps Script in bizarre formats (like turning $500.00 into 500 or displaying full timezone strings for dates). If your generated PDFs look messy, format your Google Sheet cells explicitly as Plain Text before running the script, or format the values using Google Doc's cell formatting settings.

Fixing Authorization Warnings

When granting permissions, Google might show a big red screen saying "Google hasn't verified this app." Don't panic! You built the app yourself, so it is completely safe. Click Advanced, scroll to the bottom, and click Go to Untitled Project (unsafe) to complete authorization.

Frequently Asked Questions (FAQ)

Can I auto-fill existing fillable PDF forms directly without using Google Docs?

While you can read original fillable PDFs directly with complex third-party JavaScript libraries inside Apps Script, it requires advanced programming. Converting your design to a Google Doc template first is by far the easiest free workaround for 99% of use cases.

Is there a daily limit on how many PDFs I can generate?

Yes, standard personal Google accounts can run scripts for up to 6 minutes per execution, with a total limit of 90 minutes of runtime per day. For most small business owners, this allows you to create several hundred PDFs daily without hitting limits.

Will my file storage fill up quickly?

PDF files do take up space in your Google Drive storage. I recommend setting a calendar reminder every few months to archive old generated PDFs to an external drive or cloud storage if space becomes tight.

Final Thoughts

Automating repetitive tasks is not about being lazy—it is about freeing up your mind for creative work that actually moves the needle. Setting up this script saved me over 100 hours of boring manual data entry last year alone.

Give this quick script a try today on a dummy test spreadsheet. Once you see those PDFs pop into your Google Drive folder automatically with a single click, you will never go back to copying and pasting manually again.

Got stuck on a line of code or custom formatting? Drop your question in the comments section below and I will gladly help you tweak your script!