Google Sheets Invoice Automation: Export Many Invoices as PDFs (2024)

Download the template

The formatting of the template still has some issues. I’ll try to fix that as soon as possible. It does not affect how the script works.

How it works

1. Open the template in Google Sheets. It needs two sheets named Database and Invoice. The Database sheet holds one invoice number per row in the first column.
2. Open the script editor (Extensions, then Apps Script) and paste in the code from the bottom of this page.
3. Reload the sheet. A new menu called Print Invoices PN appears.
4. Choose Print All Invoices PN to export every invoice, or Select Invoices to Print and enter invoice numbers separated by commas.
5. The script writes each invoice number into cell F12 of the Invoice sheet, exports the sheet as a PDF and saves it in a Drive folder called pdf_invoices2. It creates the folder if it does not exist.

Things to know

• “Print All” works in batches of 5 invoices per run (the BATCH_SIZE value). The script remembers where it stopped, so run it again to continue. This keeps it under Google’s execution limits.
• If Google answers with a rate limit error (429), the script waits and retries up to five times.
• The page size in the export is set to letter. Change the size value if you need A4.
• Some comments in the code are in German, because I wrote it for my own use.

Video tutorial

The script

var BATCH_SIZE = 5;  // Anzahl der Rechnungen, die pro Ausführung verarbeitet werden

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Print Invoices PN')
    .addItem('Print All Invoices PN', 'PrintToPDF')
    .addItem('Select Invoices to Print', 'promptUserToSelectInvoices')
    .addToUi();
}

function PrintToPDF() {
  var spreadsheet = SpreadsheetApp.getActive();
  var databaseSheet = spreadsheet.getSheetByName('Database');
  var invoiceSheet = spreadsheet.getSheetByName('Invoice');
  var lastProcessedIndex = getLastProcessedIndex();  // Fortschritt vom letzten Lauf
  
  var dataRange = databaseSheet.getRange(2, 1, databaseSheet.getLastRow() - 1, 1);
  var data = dataRange.getValues();
  
  for (var i = lastProcessedIndex; i < data.length && i < lastProcessedIndex + BATCH_SIZE; i++) {
    var valueToPrint = data[i][0];
    invoiceSheet.getRange('F12').setValue(valueToPrint);
    
    var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?';
    var params = {
      exportFormat: 'pdf',
      format: 'pdf',
      size: 'letter',
      portrait: 'true',
      fitw: 'true',
      sheetnames: 'false',
      printtitle: 'false',
      top_margin: 0,
      bottom_margin: 0,
      left_margin: 0,
      right_margin: 0,
      gid: invoiceSheet.getSheetId()
    };
    
    var range = invoiceSheet.getDataRange();
    var rangeParam = '&range=' + range.getA1Notation();
    url += getUrlParams(params) + rangeParam;

    var backoffTime = 1000;
    var maxBackoffTime = 10000;
    var attempts = 0;
    var response;

    while (true) {
      try {
        Utilities.sleep(backoffTime);
        response = UrlFetchApp.fetch(url, {
          headers: {
            Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
          },
          muteHttpExceptions: true
        });
        
        break;
      } catch (e) {
        if (e.message.indexOf('Exception: Request failed for') === -1 || e.message.indexOf('returned code 429') === -1) {
          throw e;
        }
        
        attempts++;
        if (attempts >= 5) {
          throw e;
        }
        
        backoffTime *= 2;
        backoffTime = Math.min(backoffTime, maxBackoffTime);
      }
    }

    var fileName = 'Invoice_' + valueToPrint + '.pdf';
    var downloadsFolder = getOrCreateFolder('pdf_invoices2');
    var file = downloadsFolder.createFile(response.getBlob().setName(fileName));
    var fileUrl = file.getUrl();
    Logger.log('PDF file URL: ' + fileUrl);

    Utilities.sleep(5000);
  }

  // Speichere den Fortschritt
  saveLastProcessedIndex(i);
  
  if (i >= data.length) {
    Logger.log('Alle Rechnungen verarbeitet.');
  } else {
    Logger.log('Fortsetzung erforderlich.');
  }
}

// Funktion, um den letzten verarbeiteten Index zu speichern
function saveLastProcessedIndex(index) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('lastProcessedIndex', index);
}

// Funktion, um den letzten verarbeiteten Index abzurufen
function getLastProcessedIndex() {
  var scriptProperties = PropertiesService.getScriptProperties();
  var lastProcessedIndex = scriptProperties.getProperty('lastProcessedIndex');
  
  return lastProcessedIndex ? parseInt(lastProcessedIndex) : 0;  // Startet bei 0, wenn nichts gespeichert wurde
}

// Ordnerfunktion
function getOrCreateFolder(folderName) {
  var folders = DriveApp.getRootFolder().getFoldersByName(folderName);
  if (folders.hasNext()) {
    return folders.next();
  } else {
    return DriveApp.getRootFolder().createFolder(folderName);
  }
}

// Hilfsfunktion zur Erstellung von URL-Parametern
function getUrlParams(params) {
  var keys = Object.keys(params);
  var query = '';
  
  keys.forEach(function(key, index) {
    query += key + '=' + params[key];
    
    if (index < (keys.length - 1)) {
      query += '&';
    }
  });
  
  return query;
}

// Funktion zur Auswahl der Rechnungen
function promptUserToSelectInvoices() {
  var selectedInvoices = [];
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Select Invoices to Print', 'Enter the invoice numbers separated by commas:', ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() === ui.Button.OK) {
    var input = response.getResponseText().trim();
    selectedInvoices = input.split(',').map(function(value) {
      return value.trim();
    });

    PrintSelectedToPDF(selectedInvoices);  // Drucke nur die ausgewählten Rechnungen
  }
}

// Funktion zum Drucken der ausgewählten Rechnungen
function PrintSelectedToPDF(invoicesToPrint) {
  var spreadsheet = SpreadsheetApp.getActive();
  var databaseSheet = spreadsheet.getSheetByName('Database');
  var invoiceSheet = spreadsheet.getSheetByName('Invoice');
  
  var dataRange = databaseSheet.getRange(2, 1, databaseSheet.getLastRow() - 1, 1);
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; i++) {
    var valueToPrint = data[i][0];
    
    if (invoicesToPrint.length === 0 || invoicesToPrint.includes(valueToPrint)) {
      invoiceSheet.getRange('F12').setValue(valueToPrint);
      
      var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?';
      var params = {
        exportFormat: 'pdf',
        format: 'pdf',
        size: 'letter',
        portrait: 'true',
        fitw: 'true',
        sheetnames: 'false',
        printtitle: 'false',
        top_margin: 0,
        bottom_margin: 0,
        left_margin: 0,
        right_margin: 0,
        gid: invoiceSheet.getSheetId()
      };
      
      var range = invoiceSheet.getDataRange();
      var rangeParam = '&range=' + range.getA1Notation();
      url += getUrlParams(params) + rangeParam;

      var backoffTime = 1000;
      var maxBackoffTime = 10000;
      var attempts = 0;
      var response;
      
      while (true) {
        try {
          Utilities.sleep(backoffTime);
          response = UrlFetchApp.fetch(url, {
            headers: {
              Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
            },
            muteHttpExceptions: true
          });
          
          break;
        } catch (e) {
          if (e.message.indexOf('Exception: Request failed for') === -1 || e.message.indexOf('returned code 429') === -1) {
            throw e;
          }
          
          attempts++;
          if (attempts >= 5) {
            throw e;
          }
          
          backoffTime *= 2;
          backoffTime = Math.min(backoffTime, maxBackoffTime);
        }
      }
      
      var fileName = 'Invoice_' + valueToPrint + '.pdf';
      var downloadsFolder = getOrCreateFolder('pdf_invoices2');
      var file = downloadsFolder.createFile(response.getBlob().setName(fileName));
      var fileUrl = file.getUrl();
      Logger.log('PDF file URL: ' + fileUrl);
    }
  }
}

This post may contain affiliate links. These links are marked with an * and I may earn a commission from qualifying purchases.