Introduction

Recently I decided that my time is way too valuable to waste 15 minutes every month filling in invoices, so I decided to bust out my admittedly rusty python skills and automate it instead.

Relevant xkcd comic

The Setup

Install Python

You probably already have it installed, in case you don't here's some great instructions. Before proceeding any further make sure your terminal looks something like this.

zhare@StepOne ~ %python --version
Python 3.8.12

Install Gspread

Gspread is lovely little python library that helps us open and edit google sheets. We'll be using it extensively to update and add data to our invoice. You can install it using pip.

pip install gpsread

Give script access to google API

In order to edit google sheets via our script, we are going to need access to the google sheets API. We can do this using a service account. A service account is a special type of account that allows non-human users to make use of google services.

We can do that using this lovely guide.

What you're going to need is the service account which will look something like this:

invoice-tutorial@invoice-tutorial-419726.iam.gserviceaccount.com

And the service-account-secret.json file which will look something like this:

{
    "type": "service_account",
    "project_id": "invoice-tutorial-419726",
    "private_key_id": "2ac … dc4",
    "private_key": "-----BEGIN PRIVATE KEY-----\nHpDjWl … gJHKx/9\n-----END PRIVATE KEY-----\n",
    "client_email": "invoice-tutorial@invoice-tutorial-419726.iam.gserviceaccount.com",
    "client_id": "110190909640833398764",
    ...
}

Create google sheet template

For this example we can use the default invoice template offered by google sheets. You can use any other one it just needs to be a google sheets file otherwise it can't be edited via the api.

Make sure to share access to this sheet with your service account.

Check if it works

Create a directory for the script, in that folder place the service-account-secret.json and create a invoice-tutorial.py file. In that file add the following code.

import gspread

service_account = gspread.service_account(filename="service-account-secret.json")

This code will take the credentials from the service-account-secret.json file and use them to create a service account object which will give us access to the google sheets shared with our service account.

We can the use that object to open the google sheets we're using as a template, I have it titled "template" for simplicity.

sheet = service_account.open("template")

worksheet = sheet.worksheet("Sheet1")

If the sheet is not shared with the service account or not a google sheet (ex. it's an .xlsx file), the script will throw an error.

Now we can start editing the sheet.

worksheet.update('B3:D3', "@ Step One")

Now run the script and the sheet should be updated.

In the next part we're going to write the functionality that will add all the required information to the invoice.

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