Automate Google Slides from Sheets with Gemini and Apps Script

The weekly ritual of creating status reports is a notorious productivity killer. Discover how to build an elegant, automated solution to eliminate this tedious task for good.

Introduction: The Problem with Manual Status Reporting

If you’ve ever managed a project, led a team, or been accountable for business metrics, you know the ritual all too well. It’s that recurring calendar event that signals the start of a multi-hour slog: “Prepare Weekly Status Deck.” It’s a cornerstone of corporate communication, yet it often represents one of the most significant drains on productivity—a perfect candidate for intelligent [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606). Before we dive into the code, let’s dissect the problem we’re setting out to solve and outline the elegant solution we’re going to build.

The Repetitive Task of Weekly Presentations

The process is painfully familiar. You open up a sprawling Google Sheet filled with raw data: weekly KPIs, project status updates from team members, budget trackers, and lists of blockers. Your mission, should you choose to accept it, is to transform this sea of cells into a coherent, digestible, and visually appealing Google Slides presentation.

This manual pipeline typically involves:

• Data Wrangling: Hunting down the correct rows and columns and filtering for the relevant time period.

• Copy-Paste Marathon: Shuttling key metrics, figures, and text snippets from the spreadsheet to individual slide placeholders.

• Manual Summarization: Reading through detailed updates and synthesizing them into concise bullet points or a high-level executive summary. This step is not just tedious; it’s prone to misinterpretation and inconsistency.

• Formatting Hell: Constantly adjusting text boxes, aligning elements, and ensuring everything adheres to the company’s branding template.

This isn’t strategic work; it’s high-effort, low-value administrative toil.

The Vision: An AI-Powered Generation Pipeline

Now, imagine a different reality. Imagine a system where your role shifts from a content creator to a content reviewer. You still own the data in your Google Sheet, but the bridge to a finished presentation is built for you. This is the promise of a generative AI pipeline.

Our vision is to create a system that doesn’t just move data from point A to point B. We want a system that understands it. We will leverage a Large Language Model (LLM) to act as an intelligent analyst. It will read the raw project updates, identify key trends in the metrics, and generate human-like narrative content—summaries, key takeaways, and action items—that gives the data context and meaning. The goal is to automate the most time-consuming and cognitively demanding part of the process: the synthesis of information.

High-Level Architecture: Sheets to Gemini to Slides

To bring this vision to life, we’ll construct a simple yet powerful workflow orchestrated entirely within the [Automatically create new folders in Google Drive, generate templates in new folders, fill out text automatically in new files, and save info in [Automated Web Scraping with [Multilingual Text-to-Speech Tool with SocialSheet Streamline Your Social Media Posting 123](https://votuduc.com/Multilingual-Text-to-Speech-Tool-with-Google-Workspace-p809282)](https://votuduc.com/Automated-Web-Scraping-with-Google-Sheets-p292968)](https://workspace.google.com/marketplace/app/auto_create_folder_and_files/430076014869) ecosystem. The architecture is a clean, three-step flow:

1. Source of Truth (Google Sheets): Our process begins with a structured spreadsheet. This sheet will act as our database, containing all the raw inputs: project names, status updates, key performance indicators (KPIs), and any other relevant data points for the reporting period.

2. The Intelligence Engine (Gemini & Apps Script): This is the heart of our Automated Quote Generation and Delivery System for Jobber. We’ll use [AI Powered Cover Letter Automated Work Order Processing for UPS Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automation-Engine-p111092), the JavaScript-based scripting platform for AC2F Streamline Your Google Drive Workflow, to act as the central controller. The script will read the designated data from our Google Sheet, format it into a carefully crafted prompt, and make an API call to Gemini. Gemini will process the data and the instructions in the prompt, generating the synthesized text—like a project summary or a list of risks—which it sends back to our script.

3. The Final Output (Google Slides): Armed with the AI-generated content from Gemini, our Apps Script will then interact with the Google Slides API. It will create a new presentation from a predefined template, find the correct placeholders (like {{project_summary}} or {{kpi_bullets}}), and populate them with the generated text and the original data.

In essence, we are building a data pipeline that flows from Data (Sheets) → Intelligence (Gemini) → Presentation (Slides), all glued together by the versatile power of Apps Script. Let’s get started.

Prerequisites and Environment Setup

Before we unleash the power of Gemini on our status reports, we need to lay the proper groundwork. A solid foundation is key to a smooth automation process. This section will walk you through setting up your Google Cloud project, structuring your data source in Sheets, designing a smart Slides template, and initializing your Apps Script project. Let’s get our digital ducks in a row.

Configuring Your Google Cloud Project and Enabling APIs

Genesis Engine AI Powered Content to Video Production Pipeline can perform many tasks out of the box, but for advanced services like the Gemini API, it needs to be linked to a standard Google Cloud Platform (GCP) project. This connection grants our script the necessary permissions and access.

A quick heads-up on billing: The Building Self Correcting Agentic Workflows with Vertex AI API (which houses Gemini) is a premium service. You will need to have a billing account enabled on your GCP project to use it. While the costs for this specific project are likely to be minimal, it’s crucial to be aware of the pricing and set up budget alerts in your GCP console.

Here’s how to get it set up:

1. Navigate to the Google Cloud Console: Head over to console.cloud.google.com.

2. Create a New GCP Project: It’s best practice to create a new project for this automation to keep things organized.

• Click the project dropdown at the top of the page and select “New Project”.

• Give it a descriptive name, like Gemini Slides Automation, and click “Create”.

3. Enable the Vertex AI API: This is the crucial step that gives your project access to Gemini.

• Once your project is created and selected, use the navigation menu (☰) to go to APIs & Services > Library.

• In the search bar, type Vertex AI API and select it from the results.

Click the* Enable** button. This may take a minute or two.

4. Link a Billing Account: If you haven’t already, you’ll be prompted to associate a billing account with your project. Follow the on-screen instructions to do so.

That’s it for the cloud console for now. We’ll link this newly configured GCP project to our script in a later step.

Structuring Your ‘Project Status’ Google Sheet for Success

The Google Sheet is the heart of our operation—it’s the single source of truth. The script will read data from this sheet row by row, so a consistent and logical structure is non-negotiable. “Garbage in, garbage out” very much applies here.

Create a new Google Sheet named “Project Status” and set up the following columns in the first row:

| Project Name | Project Manager | Status | Key Accomplishments | Upcoming Milestones | Blockers | Slide Generated? | Presentation ID | Slide URL |

| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |

| Project Alpha | Alice | On Track | Launched v2.1… | Finalize Q3 roadmap… | Dependency on Team X… | | | |

| Project Beta | Bob | At Risk | Completed user testing… | Address feedback… | Awaiting legal review… | | | |

Let’s break down these columns:

• Project Name, Project Manager, Status: These are structured data points we’ll place directly onto the slide.

• Key Accomplishments, Upcoming Milestones, Blockers: This is the raw, unstructured text that we will feed to Gemini. The model will synthesize this information into a concise, well-written summary.

• Slide Generated?: This will be our status flag. After the script successfully creates a slide for a row, it will place a “YES” or a timestamp here. This prevents us from accidentally generating duplicate slides every time we run the script.

• Presentation ID, Slide URL: These are optional but highly recommended. After generating a slide, our script can write back the unique ID of the presentation and a direct link to the newly created slide, making navigation a breeze.

Designing a Flexible Google Slides Template with Placeholders

Instead of creating a slide from scratch with code, which can be tedious, we’ll use a template. Our script will simply find this template, make a copy, and then fill in the blanks. The “blanks” are defined by unique placeholders.

1. Create a new Google Slides presentation. Let’s call it “Project Status Template”.

2. Design a single slide that will serve as your blueprint. Keep it clean and professional.

3. Insert text boxes where you want your dynamic data to appear. In these text boxes, use a unique placeholder format. The double curly brace {{placeholder}} convention is a popular and effective choice.

Here is an example layout:

• Slide Title: {{ProjectName}} - Status Update

• Subtitle Text Box: Project Manager: {{ProjectManager}} | Status: {{Status}}

• Main Content Body: A large text box containing a single placeholder: {{GeneratedContent}}. This is where Gemini’s AI-crafted summary will go.

Your finished template slide might look something like this:

Make sure your placeholders are unique and easy to remember. Once your template slide is ready, take note of its Presentation ID from the URL in your browser’s address bar (it’s the long string of characters between /d/ and /edit). You’ll need it for the script.

Initializing the Apps Script Project

With our data source and presentation template prepared, it’s time to write the code that ties them all together. We’ll create an Apps Script project that is “bound” to our Google Sheet, which makes it much easier to read and write data from that specific sheet.

1. Open your “Project Status” Google Sheet.

2. In the menu, navigate to Extensions > Apps Script. This will open the script editor in a new browser tab.

3. Give your script a meaningful name by clicking on “Untitled project” at the top. Something like “Gemini Status Report Generator” is a good choice.

Before we write a single line of code, we must connect this script to the Google Cloud Project we configured earlier. This is the step that authorizes our script to use the Gemini API.

1. In the Apps Script editor, click on the Project Settings icon (⚙️) in the left-hand navigation pane.

2. Scroll down to the Google Cloud Platform (GCP) Project section.

3. Click the Change project button.

4. You’ll be prompted for a GCP Project Number. You can find this on your Google Cloud Console dashboard in the “Project info” card.

5. Paste the number into the dialog box and click Set project.

With the connection established, our script now has the necessary permissions. We’re finally ready to start coding the automation logic.

Step 1: Reading and Preparing Data with Apps Script

Before we can ask Gemini to work its magic or create a single slide, we need to programmatically access the data living in our Google Sheet. This is the foundational step where we build the bridge between our spreadsheet and our script. Our goal is to transform the raw grid of cells into a structured format that’s easy to work with in code.

Connecting to Your Spreadsheet with SheetsApp

[Architecting Multi Tenant AI Workflows in Building Modular Agentic Apps Script with Gemini Function Calling](https://votuduc.com/architecting-multi-tenant-ai-workflows-in-google-apps-script-p-20260321290501) makes interacting with Workspace applications incredibly straightforward through its built-in services. To talk to Google Sheets, we use the SpreadsheetApp service. It’s our gateway to reading, writing, and manipulating spreadsheet data.

First, we need to get a reference to our specific worksheet. The most common way to do this is by getting the currently active spreadsheet (the one the script is bound to) and then selecting a sheet by its name.

Let’s open the Apps Script editor (Extensions > Apps Script) and write our first few lines:

function readMyData() {

// 1. Get the currently active spreadsheet file.

const ss = SpreadsheetApp.getActiveSpreadsheet();

// 2. Get the specific sheet within that file by its name.

//    Make sure this name exactly matches the tab name in your sheet!

const sheet = ss.getSheetByName('SourceData');

// A quick check to make sure we found the sheet

if (!sheet) {

Logger.log('Failed to find sheet named "SourceData". Check for typos!');

return;

}

Logger.log(`Successfully connected to sheet: ${sheet.getName()}`);

}

Here’s the breakdown:

• SpreadsheetApp.getActiveSpreadsheet(): This command targets the Google Sheet file that contains this script.

• ss.getSheetByName('SourceData'): From that spreadsheet, we grab the specific sheet (or tab) named “SourceData”. This is why consistent naming is crucial. If the sheet isn’t found, this method returns null, which is why we have the if (!sheet) check for basic error handling.

Fetching Row Data and Converting it to a Usable Object

Now that we have our sheet object, we can pull the data from it. The most efficient way to grab all the content is to get the entire data range and pull all its values at once. This returns the data as a two-dimensional array (an array of arrays), where each inner array represents a row.

// (Continuing inside the readMyData function from above)

// 3. Get the entire range of data in the sheet.

const range = sheet.getDataRange();

// 4. Get the values from that range as a 2D array.

const values = range.getValues();

// Let's see what we got!

Logger.log(values);

If you run this, the Logger.log(values) will output something like this in the execution log:

[[Topic, Source Material, Status], [The Power of APIs, APIs (Application Programming Interfaces) are the backbone..., ], [Introduction to AI, Artificial Intelligence (AI) is a transformative field..., ]]

This is great—we have the data! But working with it like row[0] for the topic and row[1] for the source material is clumsy and error-prone. What if we add a column later? Our entire script would break.

A much more robust and readable approach is to convert this array of arrays into an array of JavaScript objects. We want each row to look like this:

{ topic: "The Power of APIs", sourceMaterial: "APIs (Application..." }

To do this, we’ll use the first row of our data (the headers) as the keys for the properties in our objects.

// (Continuing inside the readMyData function)

// The first row is our headers.

const headers = values[0];

// The rest of the rows are our actual data.

// .slice(1) creates a new array starting from the second element.

const dataRows = values.slice(1);

// Use .map() to transform each data row into an object.

const slideDataObjects = dataRows.map(row => {

const rowObject = {};

headers.forEach((header, index) => {

// For each header, use it as a key and the corresponding

// cell in the current row as its value.

rowObject[header] = row[index];

});

return rowObject;

});

// Log the first object to see our beautiful, structured data!

Logger.log(slideDataObjects[0]);

This code is a classic data transformation pattern:

1. We separate the headers from the dataRows.

2. We use the .map() array method to iterate over every row in dataRows and transform it.

3. Inside the map, we create an empty rowObject.

4. We loop through our headers array. For each header at a given index, we create a property on our rowObject with that header as the key and the value from the row at the same index.

5. The result, slideDataObjects, is a clean array of objects—the perfect format for the rest of our script.

Building the Core Data Extraction Function

The logic we just wrote is fundamental to our project. Let’s encapsulate it into a reusable function. This is a core principle of good software design: create small, focused functions that do one thing well.

Our function will take a sheet name as an argument and return our clean array of objects. We’ll also add some improvements, like better error handling and a little trick to convert our header names (like “Source Material”) into a more code-friendly, camelCase format (like sourceMaterial).

/**

* Reads all data from a specified sheet and converts it into an array of objects.

* The first row of the sheet is assumed to be the header row.

* Keys for the objects are created by camel-casing the header names.

*

* @param {string} sheetName The name of the sheet to read data from.

* @returns {Object[]} An array of objects, where each object represents a row.

*/

function getSheetDataAsObjects(sheetName = 'SourceData') {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

if (!sheet) {

throw new Error(`Sheet with name "${sheetName}" not found.`);

}

const values = sheet.getDataRange().getValues();

// Ensure there's at least a header and one data row.

if (values.length < 2) {

return [];

}

const headers = values[0];

const dataRows = values.slice(1);

const objects = dataRows.map(row => {

// Only process rows that are not completely empty.

if (row.join('').length === 0) {

return null;

}

const obj = {};

headers.forEach((header, index) => {

// A simple trick to create a camelCase key from a header name.

// e.g., "Source Material" becomes "sourceMaterial"

const key = header.toString().charAt(0).toLowerCase() + header.toString().slice(1).replace(/\s+/g, '');

obj[key] = row[index];

});

return obj;

}).filter(Boolean); // Filter out any null entries from empty rows.

return objects;

}

// You can test your function like this:

function main() {

const slideData = getSheetDataAsObjects('SourceData');

console.log(JSON.stringify(slideData, null, 2));

}

Let’s review the enhancements in this final function:

• JSDoc Comments: The block at the top (/** ... */) explains what the function does. This is excellent practice for maintaining your code.

• Error Handling: Instead of just logging a message, throw new Error(...) will stop the script execution if the sheet doesn’t exist, preventing further errors down the line.

• Robustness: We check for empty rows and filter them out, ensuring we don’t process blank lines in our spreadsheet.

• CamelCase Keys: The line const key = ... is a small but powerful regular expression that transforms column headers like “Source Material” into sourceMaterial. This makes it much cleaner to access properties later (e.g., item.sourceMaterial instead of item['Source Material']).

• Testable main function: We’ve created a main function to call our utility function. This is the standard entry point for most of our automation logic. Using JSON.stringify with formatting makes the logged output much easier to read.

With this powerful getSheetDataAsObjects function in our toolkit, we are now perfectly positioned to take this structured data and move on to the next, more exciting step: sending it to the Gemini API.

Step 2: Engineering the Gemini Prompt for Structured Output

With our data pipeline from Google Sheets established, we now face the core creative and technical challenge: instructing Gemini to transform raw, tabular data into well-structured presentation content. Simply asking the model to “make a slide” will yield unpredictable, conversational text that is impossible for a script to reliably parse. The key to automation is forcing the AI to speak the language of our script. That language is JSON.

Why JSON Mode is Critical for Automation

A standard Large Language Model (LLM) response is designed for human readability. It might include pleasantries like “Sure, here is the content for your slide:” or use Markdown formatting for emphasis. While helpful for a human, this is a nightmare for an automated workflow. Your script would need complex and fragile logic—like regular expressions or string splitting—to find and extract the specific pieces of content it needs. This approach is brittle; a minor change in the model’s response format could break your entire automation.

This is where Gemini’s JSON Mode becomes indispensable.

When you enable JSON Mode in your API call, you instruct the model to constrain its entire output to a syntactically correct JSON object. There are no conversational intros, no concluding remarks, and no extraneous formatting—just pure, machine-readable data.

By enforcing a JSON output, we achieve several critical goals:

• **Predictability: Our Apps Script code knows exactly what data structure to expect. It’s no longer guessing where the title is; it’s looking for the title key in a parsed object.

• Simplicity: We can parse the model’s entire response with a single, standard command: JSON.parse(). This eliminates the need for custom, error-prone parsing logic.

• Robustness: The automation becomes resilient to changes in the model’s conversational style. As long as the model respects the JSON Mode constraint, our script will continue to function correctly.

In short, JSON Mode transforms the LLM from a creative but unpredictable conversationalist into a reliable data-processing engine, which is precisely what we need for this project.

Crafting the Prompt to Map Sheet Columns to Slide Elements

A great prompt is a clear contract between you and the model. It defines the task, the context, the input format, and, most importantly, the exact structure of the desired output. Our goal is to create a template that maps the columns from our Google Sheet row to the specific elements of a Google Slide.

Let’s assume our Google Sheet has the following columns: Topic, Speaker, Key Point 1, Key Point 2, Image Search Term.

Our prompt needs to instruct Gemini to take data from these columns and populate a specific JSON structure that our Apps Script will later use to build the slide. The JSON keys (title, subtitle, body, etc.) are of our own design, chosen to logically correspond with slide components.

Here is a robust prompt template that accomplishes this:

You are an expert AI assistant designed to generate content for a professional presentation. Your task is to take a comma-separated string of data representing a row from a spreadsheet and convert it into a structured JSON object for a single presentation slide.

The input data corresponds to the following columns in order:

1.  Topic

2.  Speaker

3.  Key Point 1

4.  Key Point 2

5.  Image Search Term

You MUST generate a response containing ONLY a valid JSON object with the following keys:

-   "title": A concise and engaging title for the slide, derived from the 'Topic'.

-   "subtitle": The speaker's name, derived from the 'Speaker' column. If the speaker is 'N/A', this key's value should be an empty string.

-   "body": A bulleted list for the main content of the slide. Combine 'Key Point 1' and 'Key Point 2' into two distinct bullet points. Each bullet point should start with a hyphen and a space (e.g., "- First point."). Separate the bullet points with a newline character (\n).

-   "speakerNotes": Detailed talking points for the presenter. Elaborate on the key points, providing context or additional details. This should be 2-3 sentences long.

-   "imageQuery": A clean, simple search query (2-3 words) suitable for finding a relevant background image, derived from the 'Image Search Term' column.

Do not include any explanatory text, markdown formatting like ```json, or anything outside of the final JSON object.

Here is the data:

{{SHEET_DATA}}

This prompt is effective because it is highly specific. It defines the persona, the task, the input schema, and the exact output schema, including formatting details like how to handle bullet points. The {{SHEET_DATA}} placeholder is where our Apps Script will inject the actual data from each row.

The Apps Script Function for Calling the Gemini 1.5 Pro API

Now, let’s translate this prompt into a reusable Apps Script function. This function will be responsible for making the HTTP request to the Gemini API. We’ll use Google’s built-in UrlFetchApp service to handle the communication.

Before you run this code, ensure you have your Gemini API key. It’s best practice to store it securely using Apps Script’s PropertiesService rather than hardcoding it directly.

/**

* Calls the Gemini 1.5 Pro API to generate structured slide content from a sheet row.

*

* @param {string} sheetData A comma-separated string of data from a single spreadsheet row.

* @param {string} apiKey The Gemini API key.

* @returns {Object|null} A parsed JSON object with slide content, or null on failure.

*/

function generateSlideContentFromGemini(sheetData, apiKey) {

const API_ENDPOINT = "https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro-latest:generateContent?key=" + apiKey;

// This is the prompt template we engineered in the previous section.

const prompt = `

You are an expert AI assistant designed to generate content for a professional presentation. Your task is to take a comma-separated string of data representing a row from a spreadsheet and convert it into a structured JSON object for a single presentation slide.

The input data corresponds to the following columns in order:

1.  Topic

2.  Speaker

3.  Key Point 1

4.  Key Point 2

5.  Image Search Term

You MUST generate a response containing ONLY a valid JSON object with the following keys:

-   "title": A concise and engaging title for the slide, derived from the 'Topic'.

-   "subtitle": The speaker's name, derived from the 'Speaker' column. If the speaker is 'N/A', this key's value should be an empty string.

-   "body": A bulleted list for the main content of the slide. Combine 'Key Point 1' and 'Key Point 2' into two distinct bullet points. Each bullet point should start with a hyphen and a space (e.g., "- First point."). Separate the bullet points with a newline character (\\n).

-   "speakerNotes": Detailed talking points for the presenter. Elaborate on the key points, providing context or additional details. This should be 2-3 sentences long.

-   "imageQuery": A clean, simple search query (2-3 words) suitable for finding a relevant background image, derived from the 'Image Search Term' column.

Do not include any explanatory text, markdown formatting like \`\`\`json, or anything outside of the final JSON object.

Here is the data:

${sheetData}

`;

// The payload is structured according to the Gemini API requirements.

// The key part is 'response_mime_type: "application/json"' which enables JSON Mode.

const payload = \{

"contents": [\{

"parts": [\{

"text": prompt

\}]

\}],

"generationConfig": \{

"response_mime_type": "application/json",

"temperature": 0.5, // Controls randomness. Lower is more predictable.

\}

\};

const options = \{

'method': 'post',

'contentType': 'application/json',

'payload': JSON.stringify(payload),

'muteHttpExceptions': true // Prevents script from stopping on HTTP errors (e.g., 4xx, 5xx)

\};

// We will add response handling in the next section.

const response = UrlFetchApp.fetch(API_ENDPOINT, options);

// For now, let's just log the raw response text for inspection.

Logger.log(response.getContentText());

// The full function will be completed in the next section.

return null; // Placeholder

\}

Handling API Responses and Parsing the JSON Output

The final piece of this step is to robustly process the response from the API call. This involves checking for errors, extracting the JSON content, and parsing it into a usable Apps Script object.

We’ll complete our generateSlideContentFromGemini function by adding a try...catch block for error handling and the logic to parse the JSON. The muteHttpExceptions: true option is important because it allows our script to inspect the response code and handle errors gracefully instead of crashing.

Here is the complete and final function for this step:

/**

* Calls the Gemini 1.5 Pro API to generate structured slide content from a sheet row.

*

* @param \{string\} sheetData A comma-separated string of data from a single spreadsheet row.

* @param \{string\} apiKey The Gemini API key.

* @returns \{Object|null\} A parsed JSON object with slide content, or null on failure.

*/

function generateSlideContentFromGemini(sheetData, apiKey) \{

const API_ENDPOINT = "https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro-latest:generateContent?key=" + apiKey;

const prompt = `

You are an expert AI assistant... [Prompt from above] ... Here is the data:\n${sheetData}

`; // The full prompt from the previous section goes here.

const payload = \{

"contents": [\{"parts": [\{"text": prompt\}]\}],

"generationConfig": \{

"response_mime_type": "application/json",

"temperature": 0.5,

\}

\};

const options = \{

'method': 'post',

'contentType': 'application/json',

'payload': JSON.stringify(payload),

'muteHttpExceptions': true

\};

try \{

const response = UrlFetchApp.fetch(API_ENDPOINT, options);

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode === 200) \{

// The API call was successful.

// The response from Gemini is a JSON object that itself contains the generated text.

const parsedApiResponse = JSON.parse(responseBody);

// The actual JSON content we want is in the 'text' field of the first candidate's content part.

const generatedJsonText = parsedApiResponse.candidates[0].content.parts[0].text;

// Now, parse the generated JSON text to get our final slide content object.

const slideContent = JSON.parse(generatedJsonText);

Logger.log("Successfully parsed slide content:");

Logger.log(slideContent);

return slideContent;

\} else \{

// The API returned an error.

Logger.log(`API Error: Received HTTP code ${responseCode}. Response: ${responseBody}`);

return null;

\}

\} catch (e) \{

// An exception occurred, e.g., network error or failed JSON parsing.

Logger.log(`Exception during API call: ${e.message}`);

Logger.log(`Stack: ${e.stack}`);

return null;

\}

\}

With this function, we have a reliable bridge between our spreadsheet and our desired slide structure. It takes a simple string of data, leverages a carefully engineered prompt and Gemini’s JSON Mode, and returns a perfectly structured JavaScript object. We are now ready to take this object and use it to programmatically create and format our Google Slides.

Step 3: Populating the Google Slides Template

With our structured JSON data hot off the Gemini press, we’ve reached the most satisfying part of our automation journey: bringing our Google Slides template to life. This is where we programmatically merge our content and our design, transforming a static template into a dynamic, data-driven presentation. We’ll lean heavily on Google’s SlidesApp and DriveApp services to perform this digital alchemy.

Using SlidesApp to Access and Manipulate Your Template

First things first, we need to interact with our template file. The golden rule of template automation is: never edit the original template directly. Always, always make a copy. This preserves your master template for future use and prevents accidental corruption.

We’ll use DriveApp to find our template file and create a copy, then use SlidesApp to open and manipulate that new copy.

Let’s start by defining our template’s ID and the ID of the folder where we want to save our generated presentations. You can get these from the URL of your file and folder in Google Drive.

// Add these constants to the top of your Code.gs file

const SLIDES_TEMPLATE_ID = 'YOUR_PRESENTATION_ID_HERE';

const DESTINATION_FOLDER_ID = 'YOUR_DESTINATION_FOLDER_ID_HERE';

/**

* Creates a copy of the template presentation for a new session.

* @param \{string\} newPresentationName - The desired name for the new presentation file.

* @return \{SlidesApp.Presentation\} The new presentation object.

*/

function createNewPresentation(newPresentationName) \{

const templateFile = DriveApp.getFileById(SLIDES_TEMPLATE_ID);

const destinationFolder = DriveApp.getFolderById(DESTINATION_FOLDER_ID);

// Create a copy of the template in the specified folder

const newFile = templateFile.makeCopy(newPresentationName, destinationFolder);

// Open the new copy using SlidesApp to begin manipulation

const presentation = SlidesApp.openById(newFile.getId());

Logger.log(`Created new presentation: ${presentation.getName()} with ID: ${presentation.getId()}`);

return presentation;

\}

This function neatly encapsulates the “copy-and-open” process. It takes a desired name for the new presentation, finds the template, copies it to our destination folder, and then returns an active Presentation object that we can start working with.

The Logic for Mapping JSON Data to Slide Placeholders

Now that we have a fresh copy of our presentation, how do we map the data from our JSON object to the {{placeholders}} we so carefully prepared?

The logic is straightforward:

1. Iterate: We’ll go through each slide in the newly created presentation.

2. Scan: On each slide, we’ll scan every element (like text boxes and shapes).

3. Identify & Replace: We’ll check if an element’s content matches one of our known placeholders (e.g., {{title}}, {{speaker_name}}). If we find a match, we’ll replace it with the corresponding value from our Gemini-generated JSON.

For text, this is a simple find-and-replace operation. For images, the strategy is slightly different. We’ll identify a placeholder shape not by its text, but by its “alt text” (the title and description you can set for accessibility). We’ll designate a shape with a specific alt text, like {{speaker_image}}, as our target. Then, we can replace that entire shape with our desired image.

This systematic approach ensures that every piece of data in our JSON object finds its correct home within the slide deck.

Code Walkthrough: Replacing Text and Images Dynamically

Let’s translate that logic into a robust Apps Script function. This function will take a presentation object and our JSON data as input and perform all the replacements.

/**

* Populates a presentation with data from a JSON object.

* @param \{SlidesApp.Presentation\} presentation - The presentation to populate.

* @param \{Object\} data - The JSON object containing the slide content.

*/

function populatePresentation(presentation, data) \{

// A simple way to handle bullet points for our key_points array

const keyPointsText = data.key_points.map(point => `• ${point}`).join('\n');

// Get all slides in the presentation

const slides = presentation.getSlides();

slides.forEach(slide => \{

// Replace text placeholders

slide.replaceAllText('\{\{title\}\}', data.title);

slide.replaceAllText('\{\{speaker_name\}\}', data.speaker.name);

slide.replaceAllText('\{\{speaker_title\}\}', data.speaker.title);

slide.replaceAllText('\{\{key_points\}\}', keyPointsText);

// Replace image placeholder

// We'll find the shape by its alt text, which we set to "\{\{speaker_image\}\}" in the template

const shapes = slide.getShapes();

shapes.forEach(shape => \{

// The "alt text" title is used to identify our placeholder shape

if (shape.getTitle() === '\{\{speaker_image\}\}') \{

// NOTE: For a real-world scenario, you would generate an image using a service

// like the DALL-E API or another image generation tool based on `data.image_prompt`.

// For this example, we'll use a placeholder URL.

const imageUrl = 'https://picsum.photos/400/400'; // Replace with your generated image URL

// Get position and size from the placeholder shape to maintain layout

const width = shape.getWidth();

const height = shape.getHeight();

const left = shape.getLeft();

const top = shape.getTop();

// Remove the placeholder shape

shape.remove();

// Insert the new image in its place

slide.insertImage(imageUrl, left, top, width, height);

Logger.log(`Replaced image placeholder on slide ${slide.getObjectId()}`);

\}

\});

\});

// Save and close the presentation to ensure all changes are written

presentation.saveAndClose();

Logger.log('Presentation populated and saved.');

\}

Key takeaways from this code:

• slide.replaceAllText(): This is an incredibly efficient method. Instead of looping through every shape to find and replace text, this single command operates across an entire slide.

• Handling Arrays: We transform the key_points array from our JSON into a single, newline-separated string, perfectly formatted for a bulleted list text box.

• Image Replacement by Alt Text: Using shape.getTitle() to check the alt text is a reliable way to tag shapes for replacement.

• **Preserving Layout: We grab the dimensions and position of our placeholder shape before removing it. This allows us to insert the new image in the exact same spot, maintaining our carefully crafted template design.

Assembling the Master Function to Run the Workflow

We now have all the building blocks: a function to get data from Sheets (from Step 1), a function to get JSON from Gemini (from Step 2), and our new functions to create and populate a presentation. Let’s assemble them into a single master function that orchestrates the entire process from start to finish.

This master function will be the one we ultimately trigger, for example, from a custom menu item in our Google Sheet.

/**

* Main orchestrator function.

* Generates a complete presentation based on a single row from Google Sheets.

*

* NOTE: This function assumes you have the functions `getSheetDataForPrompt(rowNumber)`

* and `getPresentationJsonFromGemini(prompt)` from previous steps.

*/

function generatePresentationForRow(rowNumber) \{

try \{

// Step 1: Get the topic and context from the specified row in our Sheet

const sheetData = getSheetDataForPrompt(rowNumber);

if (!sheetData || !sheetData.topic) \{

throw new Error(`No topic found in row ${rowNumber}.`);

\}

Logger.log(`Generating presentation for topic: "${sheetData.topic}"`);

// Step 2: Send the prompt to Gemini and get back structured JSON

const presentationJson = getPresentationJsonFromGemini(sheetData.prompt);

if (!presentationJson || !presentationJson.title) \{

throw new Error('Failed to get valid JSON from Gemini.');

\}

Logger.log(`Received JSON from Gemini for title: "${presentationJson.title}"`);

// Step 3.1: Create a new presentation from the template

const newPresentationName = `${presentationJson.title} - ${presentationJson.speaker.name}`;

const presentation = createNewPresentation(newPresentationName);

// Step 3.2: Populate the new presentation with the JSON data

populatePresentation(presentation, presentationJson);

// Final Step: Update the Sheet with the link to the new presentation

const presentationUrl = presentation.getUrl();

updateSheetWithLink(rowNumber, presentationUrl); // Assumes a function to write the URL back

Logger.log(`SUCCESS: Presentation created at ${presentationUrl}`);

return presentationUrl;

\} catch (error) \{

Logger.log(`ERROR: ${error.message}`);

// Optionally, update the sheet with an error status

updateSheetWithStatus(rowNumber, `Error: ${error.message}`);

\}

\}

// Dummy functions for context - you would have implemented these already

function getSheetDataForPrompt(rowNumber) \{

// Your code to read a row from Sheets and build a prompt

const topic = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Topics").getRange(rowNumber, 1).getValue();

const audience = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Topics").getRange(rowNumber, 2).getValue();

return \{ topic: topic, prompt: `Create a presentation about ${topic} for an ${audience} audience.` \};

\}

function updateSheetWithLink(rowNumber, url) \{

// Your code to write the presentation URL back to a specific cell in the row

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Topics").getRange(rowNumber, 3).setValue(url);

\}

function updateSheetWithStatus(rowNumber, status) \{

// Your code to write an error/status message back to the sheet

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Topics").getRange(rowNumber, 4).setValue(status);

\}

This generatePresentationForRow function is our complete, end-to-end workflow. It fetches the source data, communicates with the AI, creates the new file, populates it, and even updates our Sheet with the final result. With this function in place, the core logic of our automation is complete. The final step will be to create a simple way for a user to trigger it.

Conclusion and Automating Further

You’ve successfully built a powerful bridge between your raw data in Google Sheets and a polished, insightful Google Slides presentation, all powered by the intelligence of Gemini and the flexibility of Apps Script. This isn’t just a neat trick; it’s a fundamental shift in how you can create and distribute data-driven narratives. Now, let’s recap what you’ve accomplished and explore how to take this automation to the next level.

Recap: From Raw Data to Polished Presentation in Seconds

Let’s take a moment to appreciate the pipeline you’ve just constructed. You started with a simple Google Sheet—a collection of rows and columns representing project updates, sales figures, or any other quantifiable data. Through a single execution of an Apps Script function, you orchestrated a sophisticated workflow:

1. Data Ingestion: The script read the raw data directly from your Sheet.

2. AI-Powered Synthesis: It sent that data to the Gemini API, which acted as an expert analyst, interpreting the numbers, identifying trends, and generating concise, human-readable summaries and bullet points.

3. Automated Slide Creation: Armed with this AI-generated content, the script programmatically created a new Google Slides presentation from a template, inserting titles, subtitles, and speaker notes onto the appropriate slides.

The result? A complete, ready-to-present slide deck generated in a matter of seconds, not hours. You’ve effectively eliminated the tedious, error-prone manual labor of copy-pasting data and crafting summaries, freeing yourself up to focus on strategy and delivery.

Setting Up Time-Driven Triggers for Full Automation

Running the script manually from the editor is great for testing, but the true power of automation lies in removing the human from the loop entirely. This is where Apps Script’s built-in triggers come in. By setting up a time-driven trigger, you can schedule your script to run automatically on a recurring basis.

Imagine a fresh status report presentation waiting in your inbox every Monday at 8 AM, without you ever having to lift a finger. Here’s how to set it up:

1. Open the Apps Script Editor: Navigate to your script project.

2. Go to the Triggers Page: In the left-hand sidebar, click on the clock icon labeled “Triggers”.

3. Add a New Trigger: In the bottom-right corner, click the ”+ Add Trigger” button.

4. Configure the Trigger: A configuration modal will appear. Set it up as follows:

• Choose which function to run: Select your main function (e.g., createStatusReportPresentation).

• Choose which deployment should run: Leave this as Head.

• Select event source: Change this from “From spreadsheet” to “Time-driven”.

• Select type of time-based trigger: You have many options. For a weekly report, choose “Week timer”.

• Select day of week: Choose “Every Monday”.

• Select time of day: Choose a time that works for you, like “8am - 9am”. This means the script will run sometime within that one-hour window.

5. Save: Click the “Save” button. You may be asked to re-authorize the script’s permissions.

That’s it! Your script is now fully autonomous. It will execute every Monday morning, read the latest data from your Google Sheet, and generate a brand new presentation, effectively putting your reporting on autopilot.

Beyond Status Reports: Other Use Cases for This Architecture

While we focused on a weekly status report, the underlying architecture—Sheets as a Database, Gemini as a Brain, Slides as the Output—is incredibly versatile. Don’t limit your thinking to just one use case. This pattern can be adapted to solve countless problems across various domains:

• Marketing Analytics: Automatically pull performance data (clicks, conversions, spend) from ad platforms into a Sheet. Have Gemini generate weekly campaign summary slides highlighting top performers, key insights, and recommendations.

• Personalized Sales Pitches: Maintain a Sheet of prospective clients with their company info and needs. A script could generate a custom introductory slide deck for each client, using Gemini to tailor the value proposition based on the client’s industry and pain points.

• Educational Content: Teachers or students can list key concepts and definitions in a Sheet. The script can then auto-generate a deck of flashcards or a lesson review presentation.

• Financial Summaries: Connect a Sheet to financial data sources. Schedule a monthly trigger to create a presentation for stakeholders that summarizes revenue, expenses, and profitability, with Gemini providing the narrative analysis.

• HR & People Operations: Generate slides for monthly all-hands meetings by pulling new hires, work anniversaries, and department highlights from an HR master sheet.

The core components are modular. You can swap out the data source, refine the prompts sent to Gemini, and redesign the slide template to fit virtually any reporting or content creation need.

Streamline Your Workflow with the ContentDrive app Ecosystem

For those who want to leverage this power without diving deeper into code, or for teams looking to scale these automations, the ContentDrive app ecosystem offers the perfect next step. Think of it as a suite of no-code and low-code tools built on the very principles you’ve just learned.

Within the ContentDrive ecosystem, you can:

• Utilize Pre-built Connectors: Instead of manually exporting data, directly connect your Google Sheet to sources like Salesforce, Jira, Google Analytics, and more. Keep your data source perpetually up-to-date without any extra work.

• Access an Advanced Templating Engine: Move beyond basic layouts with a visual, drag-and-drop interface for designing beautiful, on-brand Google Slides templates. Link data fields directly to shapes and text boxes in your templates.

• Leverage a Prompt Library: Get started faster with a collection of professionally engineered prompts for Gemini, tailored for specific use cases like marketing analysis, financial reporting, and project management summaries.

• Manage Workflows Visually: Set up and manage complex, multi-step automations through a simple UI, without ever opening the Apps Script editor. Chain together data retrieval, AI analysis, presentation generation, and even email distribution in one seamless flow.

The script you built today is a powerful, custom solution. When you’re ready to scale that power across your entire organization with enhanced security, collaboration, and ease of use, exploring a platform like ContentDrive is the logical evolution of your automation journey.