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

For some of my side projects I started to build a small side business (Work-in-de.de). 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.

/// FUNCTIONS TO CALL FROM SHEET

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
    "CONFIG_SHEET_BILL_ID_CELL": "A2:A2"
  }
}

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

  printPdfAndSave(folderId, CONFIG().PRINT_BILL_SHEET_NAME, CONFIG().FILENAME_CELL)
}

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

  printPdfAndSave(folderId, CONFIG().PRINT_OFFER_SHEET_NAME, CONFIG().FILENAME_CELL)
}

/// Internal Functions

// print the sheet printSheetName as fileNameCell into (GDrive)folder
function printPdfAndSave(folder, printSheetName, fileNameCell) {
  SpreadsheetApp.flush();
  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 = "https://docs.google.com/spreadsheets/d/" + 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();
  pdf.setName(name);
  var folder = DriveApp.getFolderById(folder)
  folder.createFile(pdf)

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


/// HELPER FUNCTIONS

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

  const BASE_FOLDER = CONFIG().BASEFOLDER

  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 = folders.next();
    var name = folder.getName();
    names.unshift(name)
  }
  return names.join("/")
}

function notification(string) {
   SpreadsheetApp.getActiveSpreadsheet()
   .toast(string);
}