How to Create Google Sheets PDF Reports with Apps Script

Why this matters

Generating PDFs directly from spreadsheet data saves time, reduces human error, and provides a consistent look for every report. When the workflow is automated, updates to the source data instantly reflect in the next PDF without re‑creating templates. Teams that need to distribute regular performance or financial snapshots benefit from a repeatable process that lives inside the tools they already use.

What goes wrong

A naïve manual export often breaks when the sheet contains images, custom formatting, or when the file name needs to match a naming convention. Users also hit limits when they try to run the same steps repeatedly – they forget to clean up old files, leading to clutter and version confusion.

Without a scripted approach, the workflow quickly becomes brittle – a single formatting tweak can break every subsequent export, and keeping track of generated PDFs turns into a manual filing nightmare.

What the workflow looks like

The end‑to‑end workflow stitches together a small Excel export macro and a Google Apps Script helper. First, Excel prepares the data in a format the script can ingest; then Apps Script creates the PDF and stores it where the team can access it.

A visual example

Simple visual illustration.

AI-generated illustration for article.

A grounded VBA example

A short VBA macro can prepare the CSV file that Apps Script will later read. The macro also guarantees that any referenced images are placed alongside the CSV, making the Google side resilient.

Sub ExportDataAndImages()
    Dim ws As Worksheet
    Dim csvPath As String, imgFolder As String
    Dim fso As Object, pic As Shape
    Set ws = ThisWorkbook.Sheets("ReportData")
    ' Ensure output folders exist
    csvPath = ThisWorkbook.Path & "\Export"
    imgFolder = csvPath & "\Images"
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(csvPath) Then fso.CreateFolder csvPath
    If Not fso.FolderExists(imgFolder) Then fso.CreateFolder imgFolder
    ' Export sheet as CSV
    ws.Copy
    With ActiveWorkbook
        .SaveAs Filename:=csvPath & "\data.csv", FileFormat:=xlCSVUTF8
        .Close SaveChanges:=False
    End With
    ' Export each picture using Shape.Export (supported in recent Excel versions)
    For Each pic In ws.Shapes
        If pic.Type = msoPicture Then
            Dim imgPath As String
            imgPath = imgFolder & "\" & pic.Name & ".png"
            ' Export at 96 DPI; adjust as needed
            pic.Export Filename:=imgPath, Filter:=xlPNG, ScaleWidth:=0, ScaleHeight:=0
        End If
    Next pic
    MsgBox "Export complete. CSV and images ready in " & csvPath, vbInformation
End Sub

Run the macro before launching the Google helper; the generated files stay on your local drive, ready for the cloud script to pick up.

A small Google-side helper

On the Google side, a compact Apps Script reads the CSV, fills a template sheet, and exports a PDF. The script can be attached to a custom menu for on‑demand runs or scheduled via a trigger.

function generatePdfFromCsv() {
  var folderId = 'YOUR_DRIVE_FOLDER_ID'; // replace with your folder ID
  var csvFile = DriveApp.getFilesByName('data.csv').next();
  var csvData = Utilities.parseCsv(csvFile.getBlob().getDataAsString());
  var ss = SpreadsheetApp.create('TempReport');
  var sheet = ss.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  // Hide the sheet used for generation
  sheet.hideSheet();
  // Apply any formatting needed here (e.g., set column widths, insert images from folder)
  var pdfBlob = ss.getAs('application/pdf').setName('Report_' + new Date().toISOString() + '.pdf');
  DriveApp.getFolderById(folderId).createFile(pdfBlob);
  // Cleanup temporary spreadsheet
  DriveApp.getFileById(ss.getId()).setTrashed(true);
}

You can extend the script to email the PDF, move it to another folder, or log the operation status in a separate spreadsheet.

Where VBA starts to strain

While VBA excels at local data preparation, it starts to strain when you need to manage large images, complex conditional logic, or cross‑application automation beyond Excel and Word.

A calmer way to standardize the workflow

If you want a fully cloud‑native, repeatable pipeline, consider moving the entire preparation step into Apps Script or Google Cloud Functions. This removes the Windows‑only dependency and lets the whole workflow stay inside Google Workspace.

Frequently asked questions

Common questions about turning Sheets data into PDFs