Google API Work Hours Export

A Python Programm using the Google Workspace API for exporting work hours from calendar to a Google Sheet.

A Docker image is provided for easy deployment and executing the synchrinization every 12h.

Getting Started

Prepare your Google Workspace for the script
1. Create a Google Drive folder for the statistics
2. Create a Google Sheet in the folder
3. Create a Google Calendar for the work hours
Enable the Google Workspace API
1. Create a OAuth 2.0 Client ID
2. Add the Google Workspace API scopes
Run the script
1. Build the image
2. Run the container a first time interactive
3. Create the Authentification Token
4. Stop the container
Run the script again to export the work hours to the Google Sheet.

Create a Google Drive folder for the statistics
1. Add the ID of the folder to the work_hours.json file under folder_id
Create a Google Sheet in the folder
1. Name the first sheet "Sheet1"
2. (Optional) Add the needed columns in the first sheet: "Month", <Company_1>, <Company_1>, ..., <Company_n>
3. Add the ID of the sheet to the work_hours.json file under sheet_id
Create a Google Calendar for the work hours
1. Add the ID of the calendar to the work_hours.json file under calendar_id
2. Add events for the time worked to the calendar with the following format: <Company>. Example: Company A

Create a Google Cloud project
1. Go to the Google Cloud Console
2. Create a new project
Enable the Google Workspace API
1. Go to the Google Cloud Console
2. Enable the APIs: Drive, Sheers, Calendar, People
Create a OAuth 2.0 Client ID
1. Go to the Google Cloud Console
2. Go to the Credentials tab
3. Create a new OAuth 2.0 Client ID
4. Download the JSON file and save it as credentials.json in the config folder
Add the Google Workspace API scopes
1. Go to the Google Cloud Console

Build the image
1. docker build -t jearde/google-api-work-hours:latest .
Run the container
1. docker run -it -p8088:8088 -v $(pwd)/config:/app/config --rm --name google-api-work-hours -e SERVER=1 jearde/google-api-work-hours:latest
2. or docker-compose up -d
Stop the container
1. docker stop google-api-work-hours
2. or docker-compose down

You can run the script manually in native Python. This will

python3.9 -m venv env
source env/bin/activate
pip install --upgrade pip
pip install -r requirements.txt

source env/bin/activate
python work_hours/main.py

Run the script in native Python to create the token.json file

Copy your credentials.json files to the config folder
python work_hours/main.py to create the token.json file
The native authentication should open a browser window and ask you to login to your Google account automatically

Run the script in Docker interactive mode to create the token.json file

Copy your credentials.json files to the config folder
Build the image docker build -t jearde/google-api-work-hours:latest .
Run the image interactive docker run -it -p8088:8088 -v $(pwd)/config:/app/config --rm --name google-api-work-hours jearde/google-api-work-hours:latest
Open the URL in your browser
Copy the code from the browser URL between &code= and &scope= and paste it into the terminal and press enter
The token.json should now be created in the config folder and used in the next runs
You now don't need to run the authentication again until your token has expired

Gets all calendar entries from the calendar with specified ID
Saves the calendar entries in a Google Sheet with specified folder
Creates a new folder for each name found in the calendar entries (company names)
Creates a new sheet for each name found in the calendar entries (hours per month for each company)
Appends the hours per month to a specified main spreadsheet
Appends the hours by calendar week to a specified main spreadsheet