Google Sheets and Obsidian integration

I have just described in some previous articles how I have improved my Obsidian setup. Now, I want more. Actually, I can log almost everything in my life into Obsidian, except for workouts and expenses. Both are logged into a spreadsheet with Google Sheets. There doesn’t seem to be an automatic way to import the data so I want to build it myself. I’m not so good at coding, not good as a software engineer at least, but I think this is an interesting challenge.

Disclaimer, I know the code at the end may not be the best, so if anyone has any advice please email me. I started this project several months ago and it worked. Now, after joining CS50 I want to improve the structure and make it easily understandable and replicable. In this article, I will talk about the template for logging my workout but also explain how I made it so that anyone can edit it for their spreadsheet. I want to use Python because it is easy to use and full of useful packages.

The problem

I want to convert my spreadsheet into a series of markdown files that can be queried by dataview/dataviewjs inside Obsidian. Furthermore, I want the possibility to query numerical data to create a chart inside Obsidian. To make that I need to insert data inside the file header, with the YAML syntax, and recall it with dataview. It seems pretty simple, for each row that represents a single exercise, I can create a file with several information like date, exercise type, series, repetitions, and weight. I can store each file (of each exercise) in a separate folder.

|-- Workout/
|    |-- day 1/
|    |    | -- exercise1
|    |    | -- ....
|    |    | -- exercise n
|
|    |-- day 2/
|    |    | -- exercise1
|    |    | -- ....
|    |    | -- exercise n
|
|    |-- .... 
|
|    |-- day k/

It seems pretty easy, I need the create a markdown template and then insert data. Furthermore, I can also manage data to extract extra info, such as mean or standard deviation, by using Python. The spreadsheet is constructed of several rows, each one containing:

• Date
• Day type (as A, B, C, …)
• Exercise name
• Series and reps
• Weight
• Automatic function to split repetitions into five separate columns (each column represent a series). The i-th column is filled only if the exercise requires the i-th series. I made it to easily plot data into Google Sheets but it will help also in this conversion

But how can I read data from Google Sheets in Python? I have no idea but I found something on the internet, it is called gspread and it is a Python API for Google Sheets, so it allows us to read the spreadsheet inside the Python environment.

gspread

Before start using gspread, it requires a little configuration. You can find some extra information at the following link but long story short:

1. Create Google developers account
2. Enable Google Drive API
3. Enable Google Sheet API
4. Create credentials and set the email as: obsidian-sync@sheet-obsidian-sync.iam.gserviceaccount.com
5. Share the sheet to this email
6. Create JSON key for the credentials and download the file

Now we can install the package:

pip install gspread

And move the JSON key into a folder (we will need this folder later). For instance,

mkdir %APPDATA%\gspread
mv C:\Users\bigba\Downloads\key.json %APPDATA%\gspread\service_account.json

And we can read the sheet with the following commands:

import gspread
sa=gspread.service_account(filename="%APPDATA%\gspread\service_account.json")
sh=sa.open("Gym")
wks=sh.worksheet("Foglio1")
data=wks.get_all_values()
for row in data:
    print(row)

It works! I can read each row printed in the console.

Now I need to decompose each row and extract the data.

Check the date

I can cycle over the rows and the first step is to check if the first column (of each row) contains a date. It means that the row contains a valid exercise.

def validate(date_text):
    try:
        datetime.strptime(date_text, '%d/%m/%Y')
        return True
    except ValueError:
        return False
for row in data:
    #dateString.=row[0]
    if validate(row[0]):
        dateString=datetime.strptime(row[0], '%d/%m/%Y')
        print(dateString)
        print(row)

After that, I can extract data for each row respecting the spreadsheet layout. Each column contains different data that I can use to fill the markdown notes. gspread gives me a structure that could be called as: cell[row][column].

for i in range(len(data)):
    #dateString.=row[0]
    if validate(data[i][0]):
        dateValue=datetime.strptime(data[i][0], '%d/%m/%Y').date()
        print(dateValue)
        print(data[i])
        dayType=data[i][1]
        exerciseType=data[i][2]
        SxR=data[i][3]
        weigth=[data[i][4], data[i][5], data[i][6], data[i][7], data[i][8]]
        reps=[data[i][11], data[i][12], data[i][13], data[i][14], data[i][15]]

Templates

After extracting several pieces of information I can create the markdown files but I need a template that can be used for each line, i.e. for each exercise. I could define the template inside a function and then recall it by passing the different information that I want to insert inside the template.

Exercises

The most important template is the one to describe the single exercise. It contains all the information about the exercise in the header. This is the header for Obsidian, with the YAML syntax that will not be displayed in the reading mode. The single exercise file, in Obsidian, remains essentially empty and all the information is inside the header.

def fileHeader(date,exerciseType,SxR,reps,weigth):
    header=("--- \n"
    "type::gymExercise \n")
    datestring="date::[["+str(date)+"]] \n"
    exerciseName="exerciseName::[["+str(exerciseType)+"]] \n"
    SxR_string="SxR::"+str(SxR)+" \n"
    S1="S1::"+str(reps[0])+" \n"
    W1="W1::"+str(weigth[0])+" \n"
    S2="S2::"+str(reps[1])+" \n"
    W2="W2::"+str(weigth[1])+" \n"
    S3="S3::"+str(reps[2])+" \n"
    W3="W3::"+str(weigth[2])+" \n"
    S4="S4::"+str(reps[3])+" \n"
    W4="W4::"+str(weigth[3])+" \n"
    S5="S5::"+str(reps[4])+" \n"
    W5="W5::"+str(weigth[4])+"\n"
    weigthNum=[float(x.replace(',', '.')) for x in weigth]
    std= std="std::"+str(np.std(weigthNum))+"\n"
    maxWeigth="max::"+str(np.nanmax(weigthNum))+"\n"
    minWeigth="min::"+str(np.nanmin(weigthNum))+"\n"
    meanWeigth="mean::"+str(np.nanmin(weigthNum))+"\n"
    header=header+datestring+exerciseName+SxR_string+S1+W1+S2+W2+S3+W3+S4+W4+S5+W5+maxWeigth+minWeigth+meanWeigth+std
    header=header+'---'
    return header

I defined a function that takes as input the several information and returns a string with several lines. It returns this long string with several lines, each line contains one tag with the corresponding data, with the syntax tag::data. Some fields contains also the double brackets as [[ ]] that allow us to use the backlinks in Obsidian. For example:

---
type::gymExercise
date::[[2022-10-24]]
exerciseName::[[Biceps curl]]
SxR::3x12
S1::12
W1::10
S2::12
W2::10
S3::12
W3::10
S4::nan
W4::nan
S5::nan
W5::nan
max::15.0
min::15.0
mean::15.0
std::0.0
---

This allows dataview to recall the single information inside the daily workout page.

Daily workout

Another important template is the daily workout one. This template allows us to include a table with all the exercises done on the referenced day. It also links the daily workout with the Obsidian syntax as in datestring. It is defined as the concatenation of the double brackets and the date, cast as a string.

def createWorkout(date,dayType,exerciseType,SxR,reps,weigth):
    header=("---\n"
    "type::workout \n")
    datestring="date::[["+str(date)+"]] \ndateNoLink::"+str(date)+" \n"
    dayTypeString="dayType::"+str(dayType)+" \n"
    header=header+datestring+dayTypeString+"cal:: \n"
    header=header+'---\n'
    text=(
       "# Workout -" + str(date) +"\n"
       "[["+str(date)+"]] \n"
       "```dataview \n"
       "TABLE WITHOUT ID \n" 
       'exerciseName as "Name",\n'
        'SxR as "SxR",\n'
        'max as "Max",\n'
        'mean as "Mean",\n'
        'std as "Std"\n'
        'FROM "Workout" \n'
        'where contains(type,"gymExercise") and contains(string(date),"'+str(date)+'")\n'
        "```\n"
    )
    exType=["Stacco", "Bench press", "Pull up","Squat S","Military"]
    color=["ba342b","ba342b","58ba2b","ba6e34","bf2e91"]
    mychart=""
    for i in range(len(exType)):
        mychart=mychart+chartText(exType[i],color[i],str(date))
    return header+text+"\n"+mychart+"\n"

This function returns a long string that contains both the header and the body of the markdown file. The body contains a title (with h1 header) formatted with the current date and a table created with dataview. The table recalls all the exercise file which contains in the YAML header the tag #gymExercise and the corresponding date.

Furthermore, it includes several dynamic charts generated with chartjs.

Charts

It generates charts for the fundamental lifts such as the deadlift, squat, bench press, pull-up, and military press. To generate the chart it uses the chartjs syntax recalling a function (chartText) for each lift. This function generates the long string which is read inside Obsidian and converted into a chart.

def chartText(exType,color,date):
    textForChart=(" \n \n "
    "```dataviewjs  \n"
    f"let EX=\"{exType}\"; \n"
    "dv.header(3,EX);"
    f"const maxvalue=dv.array(dv.pages('\"Workout\"').where(p=>p.exercisename && p.exerciseName.path==EX && p.dateNoLink<dv.date(\"{date}\")+1 ).max );  \n"
    f"const meanvalue=dv.array(dv.pages('\"Workout\"').where(p=>p.exercisename && p.exerciseName.path==EX && p.dateNoLink<dv.date(\"{date}\")+1).mean );  \n"
    f"const mylabel=dv.array(dv.pages('\"Workout\"').where(p=>p.exercisename && p.exerciseName.path==EX && p.dateNoLink<dv.date(\"{date}\")+1).dateNoLink);  \n"
    "const chartData = {    \n"
    "type: 'line',    \n"
    "data: {    \n"
    "labels: mylabel.map(t => t.toLocaleString([], { month: '2-digit', day: '2-digit', year: '4-digit', hour: '2-digit', minute: '2-digit' })),   \n" 
    "datasets: [{    \n"
    "label: \"Max\",    \n"
    "data: maxvalue.values,  \n"
    f"backgroundColor: ['#{color}'],  \n"
    "borderWidth:  1  \n"
    "},{    \n"
    "label: \"Mean\",    \n"
    "data: meanvalue.values,      \n"
    "backgroundColor: ['#a0ada3'],  \n"
    "borderWidth:  1  \n"
    "}]    \n"
    "}    \n"
    "}  \n"
    "window.renderChart(chartData, this.container);  \n"
    "```   \n" 
    "\n \n")
    return textForChart

This long template essentially returns this long string that contains descriptions for both dataviewjs and chartjs. It allows Obsidian to render a chart with days vs weight, including both the average and the maximum values. Once converted into a string, with also the variables inserted as date, exType and color. It put inside the daily exercise note (in markdown) the following text:

```dataviejs
let EX="Stacco";
const maxvalue=dv.array(dv.pages('"Workout"').where(p=>p.exercisename && p.exerciseName.path==EX ).max );
const meanvalue=dv.array(dv.pages('"Workout"').where(p=>p.exercisename && p.exerciseName.path==EX ).mean );
const mylabel=dv.array(dv.pages('"Workout"').where(p=>p.exercisename && p.exerciseName.path==EX ).dateNoLink);
p.exerciseName.path==EX ).dateNoLink);  
const chartData = {    
type: 'line',    
data: {    
labels: mylabel.map(t => t.toLocaleString([], { month: '2-digit', day: '2-digit', year: '4-digit', hour: '2-digit', minute: '2-digit' })),   
datasets: [{    
label: "Max",    
data: maxvalue.values,  
backgroundColor: ['#ba342b'],  
borderWidth:  1  
},{    
label: "Mean",    
data: meanvalue.values,      
backgroundColor: ['#a0ada3'],  
borderWidth:  1  
}]    
}    
} 
window.renderChart(chartData, this.container);
```

This big block of text is a dataviewjs block that allows Obsidian to run JavaScript code inside the dataview enviromnent.

Dataviewjs

It is a high-powered JavaScript API that gives full access to the Dataview index and some convenient rendering utilities. In the main snippet, we define the name of the exercise (that is inserted by the Python function) and recall the maximum and mean value by searching in all the notes inside the /Workout/ folder which contain the same name as the exercise EX. The same for the mylabel variable that contains the date to pleach each day.

let EX="Stacco";
const maxvalue=dv.array(dv.pages('"Workout"').where(p=>p.exercisename && p.exerciseName.path==EX ).max );
const meanvalue=dv.array(dv.pages('"Workout"').where(p=>p.exercisename && p.exerciseName.path==EX ).mean );
const mylabel=dv.array(dv.pages('"Workout"').where(p=>p.exercisename && p.exerciseName.path==EX ).dateNoLink);
p.exerciseName.path==EX ).dateNoLink);  
const chartData = { 
  // ...
}
window.renderChart(chartData, this.container);

Then we define the chartData variables that contain the JavaScript code to render a chart with chartjs.

Chartjs

Chart.js is an open-source JavaScript library for data visualization, which supports several chart types. In this case, we use the line chart. We follow the syntax to convert the date from a text to an effective date with the map() function, then we pass also the maximal and average values.

const chartData = {    
type: 'line',    
data: {    
labels: mylabel.map(t => t.toLocaleString([], { month: '2-digit', day: '2-digit', year: '4-digit', hour: '2-digit', minute: '2-digit' })),   
datasets: [{    
label: "Max",    
data: maxvalue.values,  
backgroundColor: ['#ba342b'],  
borderWidth:  1  
},{    
label: "Mean",    
data: meanvalue.values,      
backgroundColor: ['#a0ada3'],  
borderWidth:  1  
}]    
}    
} 

Package

I want to make this code available for everyone but before publishing, I strongly need to clean the main code. To do that I want to move all the auxiliary functions to a Python package. I don’t need to create a package but it seems more interesting and also easier to understand for everyone who wants to use the code by converting it to their needs.

To create a package I need to create a subdirectory and the __init__.py file. Then I can import the package. I created two files, one with the templates function and another one to make the code available both for macOS and Windows.

|-- main/
|   |-- README.md
|   |-- main.py
|   |-- src/
|   |   |-- helpers/
|   |   |   |-- __init__.py
|   |   |   |-- check.py
|   |   |   |-- template.py

import src.helpers.check as ch
import src.helpers.template as tmp

Multi os

The check.py module allows me to define a different path for both the Obsidian main directory and the account.json file in Windows or macOS. It contains the validate function to check the date format and checkOS that use the os module to return information about the operative system. With this information I can select different paths to reach the Obsidian folder or the key.json file to access Google Sheets.

User input

The last step that I think could be useful is to define a starting date to write down all the files. It allows me to avoid rewriting all previous workouts. Initially, I used input() to ask for a date but the best procedure could be to use the runtime date. However, this could be a problem if some days it misses the run. So I want to use the Command Line Arguments to specify a date.

I defined the inputdate in two different way:

• if there is a command line argument the code checks for the input date
• if the command line argument is empty it fix the input date as the previous day

def main(argv):
    if len(argv) < 1:
        inputdate = datetime.today() - timedelta(days=1)
        inputdate=inputdate.strftime('%d/%m/%Y')
    elif len(argv)==1:
        inputdate=argv[0]
    else:
        exit(1)        
    if ch.validateDate(inputdate): #check the input
        wr.writeFiles(inputdate,data)
    else:
        print(f"Wrong input. You insert: \"{inputdate}\"")
if __name__ == "__main__":
   main(sys.argv[1:])

Then it starts writing all the notes corresponding to a date greater than the input date. I moved the main writing code to the writer.py module and the code writes the workout notes by using writer.writeFiles().

Improvements

Now I can run this script automatically with a daily schedule to synchronize my Google Sheets file with Obsidian.

Further improvements could be to define all the paths and templates in external files. it could also use some graphical interfaces to make the code available for everyone. But I have to study how to do it.

Repository

All the code is available at the GitHub repository: