Google Sheets Invoice Automation

You can use a simple Google Sheets script to automatically create and save multiple invoices. With just a few clicks, you can import data from a table into an invoice template — perfect for handling large batches of invoices. 

There is some issues with the formatting once again. I’ll try to fix that as soon as possible. This does not affect functionality. You can download the file here and copy the code below.

Watch the video tutorial:

Copy this code for 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);
    }
  }
}