gslides-automator 0.3.0

Generate data and reports from Google Sheets and Drive

Google Slides Automator

This repository contains a python package / binary for generating multiple Google Slide reports in Google Drive by copying data from Google sheets. This package can be used to a automate pdf report generation by first modifying placeholders ({{placeholder}}) in a Google Slide with data and converting the slide to a report.

Following elements in a slide can be replaced,

1. Text placeholders in a paragraph
2. Charts
3. Tables
4. Pictures

The real value of this package is the ability to generate a report for different entities. By providing the raw data for each entity the package in a structure format the code can generate reports for each entitiy automatically.

Getting Started

Requirements to running the reports are:

1. Python 3.12 or above.
2. Google Sheets, Slides and Drive API enabled service account credentials.
3. Access to Google Drive Shared Drive containing slide and data templates and L0 data.

Service Account Setup

1. Create a service account in Google Cloud Console with the following scopes:
   • https://www.googleapis.com/auth/spreadsheets
   • https://www.googleapis.com/auth/drive.readonly
   • https://www.googleapis.com/auth/drive.file
   • https://www.googleapis.com/auth/drive
   • https://www.googleapis.com/auth/presentations
2. Download the JSON key file and save it as service-account-credentials.json in the project root
3. Important: Share all required Google Drive files and folders with the service account email (found in the JSON file as client_email) with Editor permissions
   • This includes all folders (L0-Data, L1-Data, output folders)
   • All template files (spreadsheets, presentations)
   • For Shared Drives, add the service account as a member with appropriate permissions

If you see "File not found" errors when trying to delete files, it means the service account doesn't have access to those files. The error messages will include the service account email that needs to be granted access.

How it works

The package works exclusively on Google Drive files only. One of the inputs to the package functions will be a Google Drive folder id. It expects the Google Drive to have the exact structure.

/
├── L0-Raw/
│   ├── entity-1/
│   │   ├── s1-table-performance.csv
│   │   ├── s2-chart-profit.csv
│   │   ├── s3-picture-distribution.png
│   │   └── ...
│   ├── entity-2/
│   │   ├── s1-table-performance.csv
│   │   ├── s2-chart-profit.csv
│   │   ├── s2-picture-distribution.png
│   │   └── ...
│   └── ...
├── L1-Merged/
│   ├── entity-1/
│   │   ├── entity-1.gsheet
│   │   ├── s3-picture-distribution.png
│   ├── entity-2/
│   │   ├── entity-2.gsheet
│   │   ├── s3-picture-distribution.png
│   └── ...
├── L2-Slide/
│   ├── entity-1.gslide
│   ├── entity-2.gslide
│   └── ...
├── L3-Pdf/
│   ├── entity-1.pdf
│   ├── entity-2.pdf
│   └── ...
├── templates/
│   ├── slide-template.gslide
│   ├── data-template.gsheet
│   └── ...
└── entities.csv

• entitites.csv - This file contains a csv file with single column and multiple rows. For each row the library will generate a report. The csv has a header row which contains the placeholder which will be replaced by the entity name in the report. For example if the header is car_name all occurences of {{car_name}} in text boxes in the report will be replaced by the car names.

• templates/ - This folder contains 2 files. A data template gsheet file and a slide template gslide file.

The data template is will have multiple sheets. Each sheet will have the data for a single element (chart/table/picture). One sheet will have data for a element and there can be multiple elements in a sheet.

The main purpose of template is to create charts to be embedded into the Google Slide report.

• L3-Pdf/: Pdf reports for each entity. These are generated by converting the Google Slide reports to pdf.

• L2-Slide/: Google Slide reports for each entity. These are generated by copying the slide template and replacing the placeholders with the data from the L1-Data sheet. This folder will have one slide per entity.

• L1-Merged/: Processed and structured data—one spreadsheet per entity, used to generate charts. This folder will have sub folders for each entity. Each entity folder will have a spreadsheet for each slide and also the pictures used in the slides.

• L0-Raw/: Raw input data for each entity. This folder will have sub folders for each entity. Each entity folder will have the raw data for each slide and also the pictures used in the slides.

To understand the data better refer to the drive below which contains sample data for a couple of bike dealers.

https://drive.google.com/drive/u/0/folders/1EaaTMa5H6EOuWMom_4iE6RZ51qWYf2af

Why is L0-Raw needed?

Technically if you are able to generate data in the L1-Merged structure you do not need L0-Raw. However, not all languages have good API's to interact with Google Sheets like R. So to be compatible in such scenarios the library provides L0-Raw as just csv files. But if have the ability to generate merged datat for L1, skip L0 data generation.

Steps to use the libary

WIP

Future work

• Distribute as binary for OSX (brew), Windows (nuget)