← All case studies

Google Apps Script

Custom scripts that connect Gmail, Sheets, Drive, and Docs directly, automation built right into Google Workspace.

All 13 case studies in this section follow in full.

Contents

Google Apps Script

Case studies in full

Automated PDF Data Extraction from Gmail and Google Sheets Integration

Developed an automated solution for a client who received daily PDFs via email.

Section: Google Apps Script

Project Overview

This solution processes PDF attachments received by email, extracts relevant information, and transfers the data into a designated Google Sheet. It also supports manual PDF uploads and scheduled email fetching.

Technology: Gmail, Google Sheets, Python, FastAPI

01. The Challenge

Processing daily PDF attachments manually requires downloading files, extracting important information, validating data, and entering it into Google Sheets.

02. The Solution

An automated workflow was developed to fetch PDF attachments from Gmail, extract relevant fields, validate the data, and populate Google Sheets. A manual upload interface is also available.

03. Key Features

04. How It Works

Scheduled workflow

  1. Fetch PDF attachments from Gmail.
  2. Parse the PDF and extract relevant data.
  3. Validate the extracted information.
  4. Transfer the data to Google Sheets.

Manual workflow

  1. User logs in.
  2. User uploads a PDF.
  3. The backend processes the file and sends the extracted data to Google Sheets.

05. Workflow

Manual Upload:

06. Implementation

The FastAPI backend handles file uploads, invoice processing, and spreadsheet integration. The process.py module processes uploaded files, converts extracted invoice data into a dataframe, and passes it to the Google Sheets client.

Image: The FastAPI backend that handles the upload, invoice parsing, and Sheets integration.

07. Outcome

The solution reduces manual PDF processing and data-entry work by combining scheduled email fetching, manual uploads, data extraction, validation, and Google Sheets integration.

Page · Plain text

Growth Model Development with Defined KPIs

Designed a dynamic growth model in Google Sheets, complete with Key Performance Indicators (KPIs) to enhance visibility and guide decision-making.

Section: Google Apps Script

Project Overview

This project uses Google Sheets to organize growth metrics and track performance across awareness, engagement, and retention.

Technology: Google Sheets, Google Apps Script

01. The Solution

A growth model was designed in Google Sheets with defined KPIs to improve performance visibility and support decision-making.

02. Key Metrics

03. How It Works

The model organizes KPIs by growth funnel stage and displays results month over month, making it easier to review changes in performance over time.

Image: The growth funnel model: awareness, engagement, and retention KPIs tracked month over month.

04. Outcome

The growth model provides a consolidated view of awareness, engagement, and retention KPIs to enhance visibility and guide decision-making.

Page · Plain text

BigQuery to Google Sheets Data Pipeline

Implemented a robust data pipeline using SyncWith and SQL to extract and sync data from BigQuery to Google Sheets for real-time analytics and reporting.

Section: Google Apps Script

Project Overview

This project uses SyncWith and SQL to extract and synchronize data from BigQuery to Google Sheets, supporting analytics and reporting.

Technology: Google Apps Script, BigQuery, Google Sheets, SQL, SyncWith

01. The Solution

A data pipeline was implemented using SQL queries and SyncWith to retrieve data from BigQuery and sync it into Google Sheets for reporting.

02. How It Works

03. Workflow

04. Implementation

The SQL query shown in the screenshot groups records by year and month and joins employer and sign-up data for KPI reporting.

Image: A scheduled BigQuery SQL query joining employer and sign-up data by month for the KPI sheet.

05. Outcome

The pipeline supports synchronized data reporting from BigQuery to Google Sheets, helping make KPI data available for analytics.

Page · Plain text

Google Sheets Data Visualization Dashboard

Created an interactive dashboard within Google Sheets for effective data visualization and tracking, serving as a quick reference tool for stakeholders.

Section: Google Apps Script

Project Overview

This project uses Google Sheets to present CRM data in a dashboard format, giving stakeholders a quick reference for hiring totals, job closures, and feedback scores.

Technology: Google Sheets, Google Apps Script

01. The Solution

An interactive dashboard was created within Google Sheets to organize key metrics and make performance data easier to review.

02. Dashboard Metrics

03. Implementation

The dashboard uses Google Sheets formulas to calculate and display metrics from multiple data tabs, providing a consolidated view of hiring activity and feedback.

Image: A CRM dashboard inside Google Sheets tracking hiring totals, job closures, and feedback scores.

04. Outcome

The dashboard serves as a quick reference for stakeholders to track hiring activity, job closures, and feedback scores in one place.

Page · Plain text

Batch Creation of Folders and Files

The script initiates by creating a structured main folder in Google Drive.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script to create a structured folder hierarchy in Google Drive, generate spreadsheet files from templates, duplicate Google Docs templates, and record the created files and folders in a master spreadsheet.

Technology: Google Apps Script, Google Drive

01. The Challenge

Creating folders and preparing multiple files manually for each client requires repetitive work and makes file organization more time-consuming.

02. The Solution

A Google Apps Script automates folder creation, spreadsheet generation from templates, and duplication of Google Docs templates. A master spreadsheet records the names, IDs, and direct links of the created items.

03. How It Works

04. Workflow

05. Technology

06. Outcome

The automation streamlines the creation and organization of client folders and files while maintaining a master spreadsheet for easy navigation.

07. Implementation

Batch-Creation Script: Generates each client's folder structure, contract, and accounting files from templates.

Image: The batch-creation script that generates each client’s folder structure, contract, and accounting files from templates.

Page · Plain text

Batch Management of Access

The script reads from a spreadsheet cell that lists email addresses authorized for file access.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script and Google Drive to manage file and folder access based on email addresses listed in a spreadsheet.

Technology: Google Apps Script, Google Drive

01. The Challenge

Managing access manually can make it difficult to keep file and folder permissions aligned with the current list of authorized users.

02. The Solution

A Google Apps Script automates access management by granting permissions to email addresses listed in the spreadsheet and removing access for addresses that are no longer listed.

03. How It Works

04. Workflow

05. Technology

06. Outcome

The automation simplifies permission management and helps keep file access aligned with the authorized email list, reducing the risk of unauthorized viewing or editing.

Page · Plain text

Email Reminder System

The script is scheduled to run daily to ensure real-time relevancy of its functions.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script to check dates stored in linked spreadsheets and send customized email reminders when tasks or events are due. The scripts support daily reminders, due-diligence checkups, and individual screening workflows.

Technology: Google Apps Script

01. The Challenge

Manually checking multiple spreadsheets for due dates and sending reminders can be time-consuming and may lead to missed tasks or delayed communications.

02. The Solution

Google Apps Script automates the process of locating linked spreadsheets, reading date-specific data, comparing stored dates with the current date, and sending email reminders when the required conditions are met.

03. How It Works

04. Workflow

05. Implementation Examples

Example 1: Extracting Spreadsheet Links

The script checks spreadsheet cells for hyperlinks, extracts the target URL, and locates the appropriate file before processing its data.

Image: Extracting a hyperlink from a spreadsheet cell to locate the right file before checking its dates.

Example 2: Due-Diligence Email Reminders

The due-diligence script compares the current date against spreadsheet data and sends a compliance-related email when the required date conditions are met.

Image: A due-diligence checkup variant: comparing today’s date against the sheet before sending a scheduled compliance email.

Example 3: Individual Screening Automation

The screening script iterates through rows in the master sheet, checks confirmation values, extracts linked spreadsheet URLs, and processes the corresponding files.

Image: The companion screening script that walks each row and extracts the linked spreadsheet before processing it.

06. Technology

Google Apps Script: Automates scheduled execution, spreadsheet link extraction, date comparisons, and email notifications.

07. Outcome

The automation reduces repetitive spreadsheet checks and helps maintain consistent communication by sending reminders when the specified date conditions are satisfied.

Page · Plain text

Google Apps Script for Chart Automation

Utilized Google Apps Script to automate the generation and saving of chart images to Google Drive, streamlining the reporting process.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script to automate the generation of chart images and save them to Google Drive, streamlining the reporting process and reducing manual effort.

Technology: Google Apps Script, Google Drive

01. The Challenge

Generating chart images and saving them manually can add repetitive steps to the reporting process and make report preparation less efficient.

02. The Solution

A Google Apps Script automation was implemented to generate chart images and save them directly to Google Drive, simplifying chart management for reporting.

03. How It Works

04. Workflow

05. Technology

06. Outcome

The automation streamlines chart image generation and storage, reducing repetitive manual work in the reporting process.

Page · Plain text

Automated PDF Invoicing via Email

Created Google Apps Script to automatically format data into PDF invoices and send them via email, saving manual effort and enhancing operational efficiency.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script to format data into PDF invoices and send them via email, reducing manual effort and improving operational efficiency.

Technology: Google Apps Script.

01. The Challenge

Preparing invoices manually involves formatting data into invoice documents and sending them to recipients by email. This process requires repetitive manual effort.

02. The Solution

A Google Apps Script solution was developed to automate invoice formatting, PDF generation, and email delivery.

03. How It Works

04. Workflow

05. Technology

06. Outcome

The automation reduces manual invoicing effort by combining PDF invoice creation and email delivery into a scripted process.

Page · Plain text

Automated Column Management in Inventory Sheet

Developed an app script to automatically update columns in an inventory sheet based on values in another sheet, enabling seamless data synchronization within Google Sheets.

Section: Google Apps Script

Automated Column Management in Inventory Sheet

Automating inventory column updates and synchronizing information between Google Sheets using Google Apps Script.

Project Overview

This project uses Google Apps Script to update columns in an inventory sheet based on values in another spreadsheet. It also provides custom menu options for updating inventory values and adding leading zeros.

Tools: Google Apps Script, Google Sheets.

01. The Challenge

Maintaining inventory information across linked spreadsheets requires consistent updates. Manual changes can leave records out of sync, while inventory values such as SKU and location codes may require leading zeros to be preserved.

02. The Solution

A Google Apps Script solution was developed to automate inventory updates and synchronize relevant column values between spreadsheets.

The scripts provide custom menu actions to run inventory updates and normalize leading zeros when needed.

03. How It Works

04. Workflow Architecture

The workflow connects the main spreadsheet, Google Apps Script, and the linked inventory spreadsheet.

Custom Menu Actions

The provided Apps Script screenshots show these menu options:

05. Implementation Screenshots

Inventory synchronization

The first screenshot shows the Apps Script function for checking account status and synchronizing information between the main sheet and a linked inventory spreadsheet.

Image: Syncing active/inactive status between a main sheet and a linked inventory spreadsheet.

Custom inventory menu

The second screenshot shows the custom menu available in Google Sheets for running inventory updates and adding leading zeros.

Image: A custom menu for updating inventory values and normalizing leading zeros on demand.

06. Tools and Integrations

07. Outcome

The solution automates inventory column updates between linked spreadsheets and provides on-demand menu actions for inventory updates and leading-zero normalization.

Page · Plain text

CSV-Based Inventory Data Entry Automation

Created a script to automate data entry in an inventory sheet from CSV files.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script to automate the import of inventory data from CSV files into an inventory sheet. It also includes a data-cleaning function to help maintain the accuracy and integrity of imported information.

Technology: Google Apps Script.

01. The Challenge

Inventory data from multiple warehouses can contain inconsistencies that need to be identified during processing. The provided inventory sheet shows examples of data-quality issues, including mismatched warehouse locations and unidentified SKUs.

02. The Solution

A Google Apps Script was created to automate CSV-based inventory data entry. The script also includes a data-cleaning function to help ensure that imported data is accurate and consistent.

03. How It Works

Step 1: Import Inventory Data

The script automates data entry into the inventory sheet using CSV files as the data source.

Step 2: Clean the Imported Data

A data-cleaning function processes the imported data to help maintain accuracy and integrity.

Step 3: Identify Inventory Issues

The inventory sheet displays problems such as mismatched locations and unidentified SKUs, helping highlight records that require attention.

04. Google Sheets Custom Menu

The provided screenshot shows a custom menu in Google Sheets with two options:

These options provide menu-based access to the inventory-related script functions.

05. Example: Inventory Data Quality

The inventory sheet contains warehouse names, SKUs, locations, updated on-hand quantities, and a Problems column. The displayed examples include mismatched location information and unidentified inventory records.

Image: Multi-warehouse inventory data with flagged data-quality issues the cleaning step catches: mismatched locations and unidentified SKUs.

06. Technology

07. Outcome

The solution automates inventory data entry from CSV files and includes data cleaning to support accurate and consistent inventory records.

Page · Plain text

Project Tracker Management

Worked on developing a Project Tracker in Google Sheets to allocate tasks, track project milestones, and monitor team performance, complete with real-time updates and visual dashboards for easy interpretation.

Section: Google Apps Script

Project Overview

This project involved developing a Project Tracker in Google Sheets to help manage tasks, monitor project milestones, and track team performance. The tracker includes real-time updates and visual dashboards for easier interpretation of project schedules and progress.

Tools: Google Apps Script and Google Sheets.

01. Project Objectives

The tracker was developed to support three main activities:

02. The Solution

A project tracking system was developed in Google Sheets, with real-time updates and visual dashboards to make project information easier to understand.

03. Project Schedule Visualization

The tracker includes a Gantt-style project schedule generated directly in Google Sheets. The schedule displays task start and end dates across a timeline, making it easier to view project durations and overlapping activities.

Image: A Gantt-style project schedule generated directly in Google Sheets.

04. Key Features

05. Tools and Technologies

06. Outcome

The Project Tracker provides a centralized way to allocate tasks, track milestones, and monitor team performance through Google Sheets, supported by visual scheduling and dashboard features.

Page · Plain text

Shopify to Google Sheets Data Transfer with Task Allocation

Leveraged expertise in API integration and Google Sheets to create an automated system that transfers data from Shopify to Google Sheets using the ‘Sync With’ extension.

Section: Google Apps Script

Project Overview

This project uses Google Apps Script and Google Sheets to automate data transfer from Shopify and distribute customer-specific tasks across agents. The system uses the Sync With extension to transfer Shopify data to Google Sheets and integrates the resulting data with Aircall for customer outreach.

The system is configured to update daily.

Tools: Google Apps Script, Shopify, Google Sheets, and Aircall.

01. The Objective

The objective was to automate the transfer of Shopify data into Google Sheets and allocate customer-specific tasks to agents, reducing the need for manual task distribution.

02. The Solution

Google Apps Script functions handle the data processing and task allocation. The system uses the agent roster to distribute unattended calls evenly across available agents and integrates the data with Aircall for customer outreach.

03. How It Works

Step 1: Transfer Shopify Data

Shopify data is transferred to Google Sheets using the Sync With extension.

Step 2: Process the Agent Roster

Google Apps Script reads the agent names from the working sheet.

Step 3: Distribute Tasks

The task-distribution function calculates the allocation of data across the agent roster and assigns tasks evenly.

Step 4: Integrate with Aircall

The resulting data is integrated into Aircall to support customer outreach.

Step 5: Update Daily

The system is configured to update daily.

04. Task Distribution Logic

The Google Apps Script function distributes unattended calls across the agent roster. The provided code checks the agent list, reads the data count, calculates the number of tasks per agent, and assigns the calculated values in the working sheet.

Image: The task-distribution function that splits unattended calls evenly across the agent roster.

05. Workflow Overview

06. Tools and Integrations

07. Outcome

The solution automates Shopify data transfer, distributes customer-specific tasks across agents, and integrates the data with Aircall. The system is configured for daily updates.

Page · Plain text