← 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
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
- Awareness: Cumulative installs, cumulative signups, current installed audience, total installs, uninstalls, and reinstalls.
- Engagement: First-time installs, signups, conversion, new engaged users, total engaged users, job applications, and job applicants.
- Retention: Retained users, new retained users, retention percentages, and old retained users.
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
- Query data in BigQuery using SQL.
- Join employer and sign-up data by month.
- Schedule the query to support regular KPI reporting.
- Sync the resulting data to Google Sheets.
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
- Total average responses
- Total bad responses
- Total employers who hired through Taraki
- Total jobs of employers who hired through Taraki
- Total hired
- Total jobs closed
- Experience with Taraki
- Good feedback
- Average feedback
- Bad feedback
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
- Create a main folder in Google Drive.
- Generate predefined subfolders within it.
- Create spreadsheet files from templates in the relevant folders.
- Duplicate Google Docs templates and place them in their respective folders.
- Update the master spreadsheet with file and folder names, IDs, and links.
04. Workflow
05. Technology
- Google Apps Script: Automates the batch creation process.
- Google Drive: Stores the folders, spreadsheets, and duplicated documents.
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
- Read authorized email addresses from a spreadsheet cell.
- Update sharing permissions for designated Google Drive files and folders.
- Grant access to listed email addresses.
- Remove access from addresses not currently listed.
- Support different access requirements for team members and clients.
04. Workflow
05. Technology
- Google Apps Script: Automates access and permission management.
- Google Drive: Stores the files and folders whose access is managed.
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
- Run the script on a daily schedule.
- Extract spreadsheet links from a master file.
- Open the relevant spreadsheet and read its date-specific data.
- Compare stored dates with the current date.
- Send customized email reminders when a matching date is found.
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
- Generate chart images using Google Apps Script.
- Automate the chart image generation process.
- Save the generated images to Google Drive.
04. Workflow
05. Technology
- Google Apps Script: Automates chart image generation.
- Google Drive: Stores the generated chart images.
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
- Format invoice data: Google Apps Script formats the data into an invoice.
- Generate PDF: The formatted invoice is produced as a PDF.
- Send via email: The PDF invoice is sent through email.
04. Workflow
05. Technology
- Google Apps Script: Automates invoice formatting, PDF generation, and email delivery.
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
- Read spreadsheet data: The script accesses the main spreadsheet and relevant inventory sheets.
- Compare records: It reads client information and account status values to identify the records that need processing.
- Update inventory columns: The script updates inventory-related values based on the information in the linked spreadsheet.
- Run custom actions: Users can select options from the Google Sheets custom menu to update inventories or add leading zeros.
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:
- Update Inventories — runs the inventory update function.
- Add Leading Zeros — runs the function for adding leading zeros.
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
- Google Apps Script: Executes the inventory update and leading-zero functions.
- Google Sheets: Stores inventory records and provides the interface for running custom menu actions.
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:
- Update Inventories
- Add Leading Zeros
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
- Google Apps Script: Automates CSV-based inventory data entry and provides a data-cleaning function.
- Google Sheets: Holds the inventory data and displays inventory records and flagged issues.
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:
- Task Allocation: Organize and allocate project tasks.
- Milestone Tracking: Track project milestones and schedules.
- Team Performance Monitoring: Monitor team performance through the tracker.
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
- Task allocation and organization
- Project milestone tracking
- Team performance monitoring
- Real-time updates
- Visual dashboards for interpreting project information
- Gantt-style schedule visualization in Google Sheets
05. Tools and Technologies
- Google Sheets: Provides the project tracking spreadsheet, schedule, and visual dashboard.
- Google Apps Script: Listed as a technology used in the project.
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
- Shopify: Source of the data being transferred.
- Sync With extension: Supports the transfer of Shopify data to Google Sheets.
- Google Sheets: Stores the transferred data and supports task allocation.
- Google Apps Script: Processes the data and distributes tasks across the agent roster.
- Aircall: Supports customer outreach using the integrated data.
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