Slide Deck Automation

Essential Tools

This powerful workflow is built upon a foundation of key Google tools:

• Gemini: Your intelligent co-pilot for code generation. With precise prompting, Gemini creates efficient Apps Script code, requiring no prior technical experience.
• Google Sheets: The central hub for your data. Design reusable data templates for recurring reports.
• Google Slides: The canvas for your final, automated presentations. Watch your data transform into polished, professional slides.
• Apps Script: The magic behind the scenes. Access it via "Extensions" > "Apps Script" in your Google document to bridge your apps together.

Authorizing Apps Script Projects

Before your script can work, it needs permission to interact with your Google files. When you first run the script, you will be prompted to "Review permissions." Click through the authorization screens to grant the necessary access to your Google Account.

Video Walkthroughs 🎥

Visual learner? These short videos will walk you through the key processes, from the overall workflow to specific setup steps.

Full Automation Walkthrough

An end-to-end overview of the entire process.

Google Sheets Setup

How to structure your data sheet for automation.

Using IMPORTRANGE

Linking data from other spreadsheets dynamically.

How It All Connects

The automation begins once you identify recurring slides in your presentations. Your next step is to prompt Gemini to generate the specific Apps Script code that will power your automation, based on the templates you define.

CRITICAL TIP: Always instruct Gemini to include an onOpen() function in its code. This vital function creates a custom menu in your document, allowing you to run the automation with a single click every time it's opened.

Crafting Effective Prompts

To get the most accurate code from Gemini, follow this structured approach to prompting:

1. Select Commentary: Pinpoint the exact text block in your slides to automate.
2. Identify Dynamics: Break down the commentary into its changing parts (metrics, verbs, names).
3. Name Metrics Clearly: Assign descriptive names to each dynamic component (e.g., `yoyPercentChange`).
4. Create Placeholders: Develop clear, bracketed placeholder phrases for each component (e.g., `[yoyPercentChange]`).

Full Prompt Structure Example

Here's a comprehensive example of how to structure your prompt for Gemini. This ensures all necessary information is conveyed for successful code generation. Always verify your Google Sheet ID, Slide ID, and the exact sheet name are correct in the final code.

Hello Gemini. Please generate the complete Google Apps Script code for my automated reporting pipeline.

## Global Information
- Spreadsheet ID: 1aBcDeF… (ID from the sheet URL)
- Sheet Name for Data: “QBR Data”
- Presentation ID: 2xYxZvW... (ID from the slides URL)

## Commentary Block 1
- Commentary Name: NAM OPEX and HC
- Slides Placeholder: {{NAM_OPEX_HC}}
- Sheet Output Cell: C41
- Data Mapping:
  - opexAmount: G41, "Total OPEX for the quarter"
  - headcount: G42, "Current headcount"
- Output Text Template: "North America OPEX for the quarter was {{opexAmount}} with a headcount of {{headcount}}."
- AI Analysis Rule: Flag for review if opexAmount > $3.5M

## Commentary Block 2
- Commentary Name: Trending Discount
- Slides Placeholder: {{TRENDING_DISCOUNT}}
- Sheet Output Cell: C31
- Data Mapping: 
  - avgDiscount: G31, "The average discount rate"
- Output Text Template: "The average discount rate for the quarter was {{avgDiscount}}."
- AI Analysis Rule: Flag for review if avgDiscount > 22%.

Please include an onOpen() function to create a menu to run this script. Thank you.

Seamless Data Linking

For integrating data from BigQuery or other sources, the =IMPORTRANGE("url", "range_string") function in Google Sheets is essential. It creates a live, referential link that updates automatically.

Best Practices for IMPORTRANGE

• Precise Range: Always include the exact sheet name followed by an exclamation mark (e.g., "Revenue 2025!A10:Q53").
• Import Only Essentials: To ensure your script runs quickly, only import the data you absolutely need. A lean data import strategy is key to performance.

Practical Placeholder Examples

Explore these examples of how placeholders are designed for common business commentaries. Use these as a template for your own solutions. Note the use of clear, descriptive placeholder names.

Top 10 EA Deals Slide

Among the top 10 EAs closed in {{quarter}} M1+M2:
{{sector_percent}}% (${{sector_value_M}}M) of the top 10 EA Deals were from {{top_sector}} ({{py_sector_percent}}% in PY), 

followed by {{second_sector_percent}}% (${{second_sector_value_M}}M) vs. {{py_second_sector_percent}}% in PY.

Trending Discount Slide

Total {{quarter}} NAM Discount at {{total_discount_percent}}%, {{yoy_direction}} {{yoy_points}} pts Y/Y, driven by both 

Renewal {{renewal_direction}} {{renewal_points}} pts (led by {{renewal_leader_sector}}) and TNB {{tnb_direction}}

{{tnb_points}} pts (led by {{tnb_leader_sector}}).