Introduction: Coffee Machine Tracker With Raspberry Pi and Google Sheets

This instructable will show you how to build a Raspberry Pi-based tracker for the shared coffee machine in your office space. Using the tracker's OLED display and mechanical switches, the users can log their coffee consumption, see their balance and register their payments.

Your system will be able to

  • read/write data from/to a Google Sheet
  • display user names
  • record the coffee consumption of the users
  • record the payments of the users
  • show the balances of the users


  • (1x) Raspberry Pi Zero W (Wireless)
  • (1x) Micro USB Cable
  • (1x) 8GB Micro SD Memory Card
  • (1x) 128x64 Monochrome OLED Graphic Display
  • (1x) 2x20 Female Headers (2.54)
  • (3x) Mechanical Keyboard Switch
  • (3x) Keycap
  • (1x) Custom-built PCB (You can find schematic and PCB design here.)

Step 1: Assemble the Hardware

After getting all of the components listed in the previous section, you are ready to assemble your hardware. In this step, you are going to do some soldering.

  • Solder the 2x20 pin to the custom-built PCB.
  • Solder the OLED display to the custom-built PCB.
  • Solder the mechanical keyboard switches to the custom-built PCB.
  • If your Raspberry Pi does not have headers, you will also need to solder 2x20 Male headers to your Raspberry Pi.

And, you are done with the hardware!

Step 2: Configure Your Raspberry Pi

In this step, you will set up your Raspberry Pi. We will follow a headless setup, meaning you will not need a monitor/keyboard/mouse setup for the Rasberry Pi.

  • Download and write the Rasbian image to your Micro SD Card. You can find the additional instructions here.
  • Enable SSH on your Rasberry Pi. Here, you can find how to do in a headless setup.
  • Connect your headless Raspberry Pi to your WiFi using this link.

At this point, you should be able to SSH into your Raspberry Pi. However, you will need to figure out the IP address of your Raspberry Pi, which you can find using your router's web interface.

  • If you are not familiar with SSH, you can use this link to learn more.

If you are done with the SSH connection, you are ready to set up the software for the tracker!

Step 3: Set Up the Tracker Software

Before installing the software, you need to create your Google Spreadsheet and get your API key. You can use this spreadsheet as an example to form yours. If you follow this tutorial, you can generate your API key in less than 5 minutes. After the last tutorial, you should have downloaded a JSON file. You need to rename that JSON file to 'secret.json' in order for it to work with the provided software.

  • Download the file and unzip it.
  • Put the secret.json file in the unzipped folder (coffee_tracker).

You will need to transfer the folder to your Raspberry Pi. You can do that using Cyberduck with SSH File Transfer Protocol (SFTP). Transfer the folder to your Raspberry Pi's home directory.

The tracking software uses Python 3. You don't need to install it manually since the Raspberry Pi image comes with a pre-installed Python 3, but please remember to use python3 command instead of python.

Before running the software you need to install the dependencies using the commands below.

pip3 install gspread oauth2client

The tracking software also requires Adafruit OLED Libraries. You can install them by following this tutorial.

As the last step of your setup procedure, you need to edit the the in the code_tracker folder. Open the file and go to the line 13, as the comment on line 12 states, you need to change the spreadsheet name to the one you created for your application.

Now, you are all set to give it a try!

Navigate to the coffee_tracker folder and run the command below to start the tracker.

cd ~/cofee_tracker


If you want to start you each time your Raspberry Pi boots up, please follow these instructions.

Step 4: Use Your Tracker!

Congrats! You survived the previous step! Now, you can try and use your tracker.

The tracker starts with a screen displaying "Init..." followed by your IP address for debugging purposes. The tracker constantly checks its WiFi connection and if it loses connection, displays a "No Wi-Fi" message.

If your WiFi connection is stable, the tracker displays an animated screen as shown in the beginning of this instructable until the center button is pressed.

Pressing the center button gathers the user information from Google Sheets and makes the display show the user names. You can navigate between users using the left and right buttons. If you press the center button, you will be navigated for a user specific menu. If you don't take any action in 10 seconds the display will start to show the animation again.

In the user specific menu you can log your coffee, register your payment, see your balance. You can navigate through those options using left and right buttons. If you want to go back to the user list navigate to the go back icon and press the center button.

Step 5: Future Improvements

If you read this far, thanks for doing that! So far the functionality is limited but you can improve the tracker with a leaderboard! If you want to do a hardware improvement a laser cut case would be a good one.

Feel free to message me if you have any issues, questions or suggestions!

First Time Author Contest

Participated in the
First Time Author Contest