Free SaaS simple billing system on Google Sheets with Print-To-Pdf (Apps Script)

2nd December 2020 – 1006 words

For some of my side projects I started to build a small side business ( But having to run a business, one needs to run business stuff like billing, too. After digging my toe into some SaaS, I was not too satisfied with the offered service/money. On the other hand, I don’t want to build yet another Rails app to maintain myself. Turns out, Google Sheets is enough for my usecase.

I have a couple of tables:

  • Customers
  • Bills (one entry per bill, I usually have one 1-2 positions per bill to keep things easy)
  • Offers (some companies need an offer beforehand)
  • PDF “Preview” - I’ve build a bill facsimile in Google Sheets/Excel which looks like a regular bill and grabs the data for the last bill
  • Print To PDF Button

Luckily, Google sheets allows to run some “AppScript” which looks a little like JavaScript. You can bind that kind of script to a button in your sheet, to automatically generate a PDF from the PDF “Preview” which to hammered into Google Sheets and save the result to a specified Google Drive folder.

To accomplish this, I’ve used the following AppScript. You might use it like you want. If you need help to implement it, please contact me.


function CONFIG() {
  return {
    // Base Folder where you store all your business PDFs within, just open the
    // folder in Google Drive and copy the ID from the URL
    "BASEFOLDER": "1d4-mrbTDyI2hxxxxxxxxxxxxxxxxxxxx",

    // Name of the sheet to print as a PDF
    "PRINT_BILL_SHEET_NAME": "Print Bill",
    "PRINT_OFFER_SHEET_NAME": "Print Offer",
    // Which Cell has the proposed Filename of the bill in the Config Sheet
    "FILENAME_CELL": "B20",
    // Name of the Sheet to configure a new print
    "CONFIG_SHEET_NAME": "Config For Print",
    // On the Config sheet: where to find the bill id

/// Bind this function to a button in yout sheet
function printBill(folder) {
  var folderId = getSubfolderIdForCurrentBill()
  var folderFullName = pathToFolder(DriveApp.getFolderById(folderId))


function printOffer(folder) {
  var folderId = getSubfolderIdForCurrentBill()


/// Internal Functions

// print the sheet printSheetName as fileNameCell into (GDrive)folder
function printPdfAndSave(folder, printSheetName, fileNameCell) {
  notification("PDF creating....");
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(printSheetName);

  var gid = sheet.getSheetId();
  var pdfOpts = '&size=A4&fzr=false&portrait=true&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenumbers=false&attachment=false&gid='+gid;

  var url = "" + ss.getId() + '/export?exportFormat=pdf&format=pdf' + pdfOpts;

  var name = ss.getActiveSheet().getRange(fileNameCell).getValue()

  var requestData = {
    "oAuthServiceName": "spreadsheets",
    "oAuthUseToken": "always",
  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
  var pdf = response.getBlob();
  var folder = DriveApp.getFolderById(folder)

  notification("PDF created: " + pathToFolder(folder) + "/" + name);


// Create yearly folder
function getSubfolderIdForCurrentBill() {
  var ss = SpreadsheetApp.getActive();
  var konfig = ss.getSheetByName(CONFIG().CONFIG_SHEET_NAME)


  var rechnungsnummer = ss.getRange(CONFIG().CONFIG_SHEET_BILL_ID_CELL).getValue()
  var year = rechnungsnummer.toString().slice(0, 4)
  var folder = DriveApp.getFolderById(BASE_FOLDER)

  var subdir = folder.getFoldersByName(year).next()
  if (subdir) {
    return subdir.getId()
  return folder.createFolder(name).getId()

function pathToFolder(baseFolder) {
  var folders = baseFolder.getParents();
  var names = [baseFolder.getName()]

  if (folders.hasNext())
    var folder =;
    var name = folder.getName();
  return names.join("/")

function notification(string) {