Sigma

Context

Sigma was a monthly billing process that needed a short term solution that could be inexpensive to implement. It was prone to human errors within each step of its overall process which could be categorized into 3 main steps. These steps were: invoice creation, sending emails and billing adjustments. The script in mind would handle 2 out of the 3 steps and cut the overall turnaround time of this monthly process by 50%. Specifically, this script would automate creating the invoices and sending them out by email.

Invoice Creation (Manual Method)

  • An individual would copy and paste information from each row into an invoice template. Each invoice template references a different currency.
  • For each invoice template, it would then be exported to pdf with a click of a button.

Sending Emails (Manual Method)

  • An individual would use an email template to create a draft of the email and label the subject line correctly with whomever is being billed.
  • The correct invoice would be attached and then sent according to the email listed in the google sheet.

Tool: Google Sheets API

Language: GoogleScript (similar to JavaScript)

Scope

Data Format: Excel/Google Sheets

Invoice Template: Google Sheets

Requirement/s: Less room for human error, save time spent each month for this manual process and must be relatively easy to maintain as accounts grow, logic for populating each row of data accurately

Tools: Google Sheet API or Excel Macro

Project Description: Create a custom script that will take the data in the form of a google sheet and write each row into a predefined template that will then be exported into a pdf document. The script will be adjusted accordingly for each currency template until there is a need to consolidate.

Version 1:

  • Script will read each row of data and populate the template as it reads each row
  • Some pieces of the script is “hard-coded” and not dynamic by a user defined input (ie. month of the billing)
  • Multiple templates for multiple currencies (ie. usd, eur, gbp, etc)
  • Invoice creation is handled by script but individual still has to email invoices one by one

Version 2:

  • Script was modified to read all of data first, save it in an abstract data object and then another script wrote the data into a template by reading through the abstract data object (this method saved run time)
  • Pieces of the script that were “hard-coded” were modified to be dynamic by prompting the user for an input
  • Emailing of invoices added into script

Version 3:

  • Consolidate all currency templates into one template by coding the “templates” into the code itself (for ease of updating code)

Script Version 1:

Below are screenshots of the full code as it is too long to include it here.

   

Script Version 2:

Below are screenshots of the full code as it is too long to include it here.

Leave a Comment