Logging RTT data directly to Google Sheets

Raise your hand if you have ever used this flow: output some data from an embedded system to a terminal or a CSV file to analyze it later in a spreadsheet. Whether logging traffic latency over a bus, measuring system performance in a climate chamber, logging memory usage over a weekend, to checking GNSS time to first fix across several days. I did this flow sooo many times. I wondered if there is a better way to handle data from these one-off experiments. Building a dedicated database would be overkill as "you only have to plot one variable across 24 hours" but what about... Google Docs?!

I regard myself as a mild cloud skeptic. I know it is just someone else's computer. However, it has some very valid use cases. What if I could log my data directly into a Google Sheet? There are entire companies that (ab)use sheets as their production databases so there must be some API & libraries, right? Of course!

After a few seconds of searching I found gspread. It is a project that has a simpler API than the official Google libraries. Putting new data into a Google Sheet requires... 4 lines of Python. It is as simple as:

1
2
3
4

gc = gspread.oauth()
sh = gc.open("my_spreadsheet") # adjust for your document name
ws = sh.get_worksheet(3) # adjust for your worksheet number
ws.update_cell(row_number, column_number, "some value")

On the first run you will have to log in to your Google account to give access (if you want the script to pretend being you).

Complete script

Having tested the basic example I wrote a simple script that read textual data coming from my device over SEGGER RTT. Active J-Link session exposes RTT data on a TCP socket so reading it is very easy. I read data line by line, then I do some basic parsing to split the row into the right columns that then go into the spreadsheet. This flow works perfectly for low speed data, like a printf() every couple of seconds.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

#!/usr/bin/env python3
import gspread
import socket
import time
import traceback

gc = gspread.oauth()
sh = gc.open("my_spreadsheet") # adjust for your document name
ws = sh.get_worksheet(3) # adjust for your worksheet number

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect(("localhost", 19021)) # default RTT port when J-Link is running
sio = socket.SocketIO(s, "r") # this takes care of receiving complete lines from the socket

row = 2 # start writing from this spreadsheet row

while True:
    chunk = sio.readline().decode("ascii").strip()
    lines = chunk.split("\n")
    ts = time.strftime("%Y%d%m %H:%M:%S") # local timestamp
    for x in lines:
        line = x.replace("\r","").strip()
        print("%s RTT >>" % ts, line)
        if "adc" in line: # example output from device: adc 123 321
            try: # the line may have wrong contents so keep the script running
                y = line.split(' ')
                ch1 = y[1]
                ch2 = y[2]
                ws.update_cell(row, 1, ts)  # column 1 - timestamp
                ws.update_cell(row, 2, ch1) # column 2 - channel 1
                ws.update_cell(row, 3, ch2) # column 3 - channel 2
                print("%s LOG >> row %d, ch1 %s ch2 %s" %(ts, row, ch1, ch2))
                row += 1
            except Exception:
                traceback.print_exc()

This script reads RTT data from a J-Link TCP socket but it can work equally well for other data sources like a serial port, or a direct network if the device supports it, or lab instruments.

I used this script many times with good results. Of course the Internet connection has to be working and Google has to be up. In the worst case you might end up having some rows missing so I would not use it for critical or difficult to repeat experiments.

I now see many benefits of using generic Google Sheets for "throwaway" experimental data. It is very easy to share the data with others. If you are logging data from a serial port to a file you have to process the file and then distribute data to other people. In an online sheet this happens automatically, for as many people as you want, and in real time. Other team members or customer contacts don't need any software nor special tooling. It is safe to assume that everybody is somewhat familiar with Google Sheets (compared to Gnuplot or matplotlib). Annotating the data and adding comments is trivial. Finally, I can supervise an experiment from far away on a phone and without arranging remote access or VPNs. 😀