Create a PDF Invoice from Google Sheets Using PDF.co and Apps Script

Step 1: Arrange the Spreadsheet Data

This example reads invoice information from these cells:

Cell - Value

• B1 - Company name
• D1 - Company address
• F1 - Paid status
• B2 - Order or barcode value
• D2 - Order date
• F2 - Customer ID
• B3 - Billing name
• D3 - Billing address
• F3 - Shipped date
• B4 - Shipping name
• D4 - Shipping address
• F4 - Shipping method
• B5 - Freight
• D5 - Notes
• H1 - Generated PDF URL

Enter invoice items beginning on row 9:

• Column B - Item name
• Column C - Price
• Column D - Quantity

Keep the item rows together without blank rows.

Step 2: Store the PDF.co Settings

From the spreadsheet, select Extensions → Apps Script.

Open Project Settings and add these script properties:

• PDFCO_API_KEY — your PDF.co API key
• PDFCO_TEMPLATE_ID — the numeric ID of your HTML template
• PDFCO_LOGO_URL — the direct URL of your company logo

Storing the API key in script properties keeps it out of the source code.

Step 3: Add the Apps Script Code

Replace the editor’s contents with this script:

const PDFCO_ENDPOINT = 'https://api.pdf.co/v1/pdf/convert/from/html';

/**
 * Adds the PDF.co menu when the spreadsheet opens.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('PDF.co')
    .addItem('Get PDF Invoice', 'getPDFInvoice')
    .addToUi();
}

/**
 * Generates the invoice and writes its temporary URL to H1.
 */
function getPDFInvoice() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const properties = PropertiesService.getScriptProperties();

  const apiKey = properties.getProperty('PDFCO_API_KEY');
  const templateId = Number(properties.getProperty('PDFCO_TEMPLATE_ID'));
  const logoUrl = properties.getProperty('PDFCO_LOGO_URL') || '';

  if (!apiKey || !templateId) {
    throw new Error(
      'Add PDFCO_API_KEY and PDFCO_TEMPLATE_ID to the script properties.'
    );
  }

  const invoiceData = generateInvoiceJson_(sheet, logoUrl);
  const safeOrderId = String(invoiceData.order_id || 'invoice')
    .replace(/[^\w.-]+/g, '-');

  const payload = {
    templateId: templateId,
    templateData: JSON.stringify(invoiceData),
    name: `invoice-${safeOrderId}.pdf`
  };

  const response = UrlFetchApp.fetch(PDFCO_ENDPOINT, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'x-api-key': apiKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const responseCode = response.getResponseCode();
  const result = JSON.parse(response.getContentText());

  if (responseCode >= 200 && responseCode < 300 && !result.error && result.url) {
    sheet.getRange('H1').setValue(result.url);
    return;
  }

  throw new Error(result.message || `PDF.co returned HTTP ${responseCode}.`);
}

/**
 * Creates the template data from the spreadsheet.
 */
function generateInvoiceJson_(sheet, logoUrl) {
  const displayValue = cell => sheet.getRange(cell).getDisplayValue();

  return {
    paid: sheet.getRange('F1').getValue(),
    company_name: displayValue('B1'),
    company_address: displayValue('D1'),
    company_logo: logoUrl,
    barcode_value: displayValue('B2'),
    ocr_scanline: displayValue('B2'),
    order_id: displayValue('B2'),
    order_date: displayValue('D2'),
    customer_id: displayValue('F2'),
    shipped_date: displayValue('F3'),
    shipped_via: displayValue('F4'),
    bill_to_name: displayValue('B3'),
    bill_to_address: displayValue('D3'),
    ship_to_name: displayValue('B4'),
    ship_to_address: displayValue('D4'),
    freight: displayValue('B5'),
    notes: displayValue('D5'),
    items: getInvoiceItemsJson_(sheet)
  };
}

/**
 * Reads contiguous invoice items from columns B–D, starting at row 9.
 */
function getInvoiceItemsJson_(sheet) {
  const firstItemRow = 9;
  const lastRow = sheet.getLastRow();

  if (lastRow < firstItemRow) {
    return [];
  }

  const rows = sheet
    .getRange(firstItemRow, 2, lastRow - firstItemRow + 1, 3)
    .getDisplayValues();

  const items = [];

  for (const [name, price, quantity] of rows) {
    if (!name) {
      break;
    }

    items.push({
      name: name,
      price: price,
      quantity: quantity
    });
  }

  return items;
}

The range begins in column B, so the first value assigned during array destructuring is the item name. This avoids reading a multi-cell range with getValue(), which returns only its upper-left cell.

Step 4: How the Script Works

When the spreadsheet opens, onOpen() adds a PDF.co menu containing Get PDF Invoice.

Selecting that command:

1. Reads the invoice details and item rows.
2. Creates a JavaScript object matching the HTML template placeholders.
3. Serializes that object into the templateData string.
4. Sends it with the template ID to: POST /v1/pdf/convert/from/html
5. Writes the generated PDF URL into cell H1.

See the PDF from HTML Template API documentation for the request parameters.

Step 5: Generate the Invoice

Save the script and reload the spreadsheet.

Select PDF.co → Get PDF Invoice. Google will request authorization the first time the script runs.

After PDF.co finishes processing the template, cell H1 will contain a temporary link to the generated invoice. Open the link to review or download the PDF.

PDF.co output links are temporary. Transfer completed invoices to permanent storage if they need to be retained.