What now

Now that we are able to add stuff to our invoice, what exactly are we going to need to add? The required or ever expected info varies across counties and industries. What I'm going to focus on in this article are some basics that are most likely used everywhere.

• contact details
• recipient details
• send and due date
• line items

Contact Details

Contact details represent all the bits of info that a client receiving would need to get in touch with you. The simplest way to do this would be to hardcode all of this info directly into the script, but we're not going to do that because it would ruin the portability of the script. Ideally we should be able to plug in info from any other user and it should run. This can be accomplished in many different way, in this example we're going to be using a simple json.

First we need to make sure we have json installed. Which we do because json is a built in module in Python.

Here's how to import a json file from you local directory.

import json

f = open('contact-info.json')

data = json.load(f)

f.close()

The json should look something like this.

{
    "company-name": "@ Step One",
    "your-name": "John Doe",
    "address": "2468 Address St",
    "city": "City, State/Province/Country",
    "website": "stepone.com",
    "email": "john-doe@stepone.com"
}

Now that we have our info loaded into the script, we can start filling in the template.

First we need to get the address of the cell we want to update.

For example: if we were to use the template from the previous step, the address of the company name cell would be B3:D3.

With the following code the script can fill in all of the contact info.

data = json.load(f)

# Adding company name
worksheet.update('B3:D3', data["company-name"])

# Adding your name
worksheet.update('D12:E12', data["your-name"])

# Adding company address
worksheet.update('B4:D4', data["address"])

# Adding company street
worksheet.update('B5:D5', data["city"])

# Adding company phone number
worksheet.update('B6:D6', data["phone-number"])

# Adding company email address
worksheet.update('B7:D7', data["email"])

f.close()

If you run this the invoice template should update with the new values.

Recipient Details

This can be updated using the same method as the contact info, with a separate json for the clients info.

Send and Due Dates

Send date represents the date the invoice was sent, while due date is the final date by which the bill is expected to be payed. Different companies handle the due date differently, for the purpose of this example I'm going to set the due date at a week after the send date.

Working with date is notoriously complicated and that is why we're going to have the datetime library handle that.

import datetime as dt

Getting and adding the dates is pretty simple if you understand how datetime works.

# datetime.today() get todays date as a datetime object
now = dt.datetime.today()

# strftime() takes the datetime and spits out a formatted string
send_date = now.strftime("%d/%m/%Y")

worksheet.update('B9:C9',f'Submitted on {send_date}')

# timedelta lets you add/subtract days, weeks, months etc to a date
due_date =  (now + dt.timedelta(days=7)).strftime("%d/%m/%Y")

worksheet.update('F15:G15', due_date)

Line Items

In my line of work line items represent the individual services that have been rendered, often denoted in hours worked. This usually follows the format of description of work done, hours worked and hourly rate. Because we need to specify multiple values for each line item, we need our script to be able to take inputs from the user. Also, we don't know how many line items there are, so we have to enable the code to take multiple.

This code will accomplish that:

# Adding line items

print("Please add line items, enter 0 to finish.")

# this will help us know where to put the line item in the sheet
line = 19

while True:
    print("Enter unit description: ")
    description = input()
    if description == '0' :
        break

    print("Enter unit quantity: ")
    quantity = int(input())
    if quantity == 0 :
        break

    print("Enter unit price: ")
    unitPrice = float(input())
    if unitPrice == 0 :
        break

    worksheet.update(f'B{line}:D{line}', description)
    worksheet.update(f'E{line}', quantity)
    worksheet.update(f'F{line}', unitPrice)

    line+=1

Unfortunately the gspread library doesn't include the functionality to insert rows, so for the sake of simplicity I'm going to limit the number of line items to 5, so they can all fit inside the template.

This is accomplished with the following addition.

    if line == 5:
        print("Sorry, this sheet only supports 5 line items :(")
        break

The whole Script

Here's the whole script up until now.

import gspread
import json
import datetime as dt

service_account = gspread.service_account(filename="auto-invoice-service-account-file.json")

sheet = service_account.open("Invoice-template")

worksheet = sheet.worksheet("Invoice")

# Adding contact info
f = open('contact-info.json')

data = json.load(f)

# Adding company name
worksheet.update('B3:D3', data["company-name"])

# Adding your name
worksheet.update('D12:E12', data["your-name"])

# Adding company address
worksheet.update('B4:D4', data["address"])

# Adding company street
worksheet.update('B5:D5', data["city"])

# Adding company phone number
worksheet.update('B6:D6', data["phone-number"])

# Adding company email addres
worksheet.update('B7:D7', data["email"])

f.close()

now = dt.datetime.today()

send_date = now.strftime("%d/%m/%Y")

worksheet.update('B9:C9',f'Submitted on {send_date}')

due_date =  (now + dt.timedelta(days=7)).strftime("%d/%m/%Y")

worksheet.update('F15:G15', due_date)

# Adding line items

print("Please add line items, enter 0 to finish.")

# this will help us know where to put the line item in the sheet
line = 19

while True:
    print("Enter unit description: ")
    description = input()
    if description == '0' :
        break

    print("Enter unit quantity: ")
    quantity = int(input())
    if quantity == 0 :
        break

    print("Enter unit price: ")
    unitPrice = float(input())
    if unitPrice == 0 :
        break

    worksheet.update(f'B{line}:D{line}', description)
    worksheet.update(f'E{line}', quantity)
    worksheet.update(f'F{line}', unitPrice)

    line+=1

    if line == 5:
        print("Sorry, this sheet only supports 5 line items :(")
        break

In the next part we're going to use the google drive api to save invoices into a separate folder.

If you have any questions feel free to ask in the comment section below.