Apps Script AI Prompt Engineering Studio for Google Workspace

by

🚀 Apps Script + Gemini Mastery — Issue #30

AI Prompt Engineering Studio for Google Workspace

https://github.com/lsvekis/Google-Apps-Script-Gemini-Projects

We’ve spent the last several issues using Gemini to generate code, review projects, create documentation, build dashboards, and debug Apps Script.

But all of those projects depend on one thing:

The quality of the prompt.

For Issue #30, we’re going to build something different.

Instead of using AI to solve a specific Workspace task, we’ll build an AI Prompt Engineering Studio directly inside Google Sheets.

Enter a rough request such as:

“Summarize customer feedback.”

The studio transforms it into a structured, reusable prompt with a defined role, objective, context, constraints, output format, and examples.

You can then test the improved prompt against sample data—all without leaving Google Sheets.

⭐ What You Will Build

Our Prompt Engineering Studio will let you:

✍️ Enter a rough prompt

🧠 Ask Gemini to improve it

🎯 Define the AI’s role and objective

📋 Add context and constraints

📦 Specify a structured output format

🧪 Test prompts against sample data

💾 Save successful prompts to a prompt library

🔄 Reuse prompts in other Apps Script projects

This isn’t just a prompt generator.

We’re creating a reusable prompt-development environment.

🧠 Why This Project Matters

Consider these two prompts.

Prompt A

Summarize this data.

Gemini has to guess:

  • What matters?
  • Who is the summary for?
  • How long should it be?
  • What should it focus on?
  • What format should it use?

Now compare that with:

Prompt B

You are a business analyst. Analyze the customer feedback below and produce an executive summary for a product manager. Identify the three most common themes, recurring complaints, positive signals, and three recommended actions. Return the response using the specified JSON structure. Do not invent information that isn’t supported by the source data.

Same AI.

Very different instructions.

Prompt engineering isn’t about making prompts longer.

It’s about removing ambiguity.

🧩 The Architecture

Rough Prompt
     ↓
Prompt Analyzer
     ↓
Gemini
     ↓
Structured Prompt Design
     ↓
┌─────────────────────────┐
│ Role                    │
│ Objective               │
│ Context                 │
│ Constraints             │
│ Output Format           │
│ Improved Prompt         │
└─────────────────────────┘
     ↓
Test Against Sample Data
     ↓
Evaluate Result
     ↓
Save to Prompt Library

We’re introducing an important development workflow:

Design → Test → Evaluate → Save → Reuse

🧱 Step 1 — Create the Menu

Code.gs

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem(
      "Prompt Engineering Studio",
      "showPromptStudio"
    )
    .addToUi();

}

function showPromptStudio() {

  const html = HtmlService
    .createHtmlOutputFromFile("Sidebar")
    .setTitle("Prompt Engineering Studio");

  SpreadsheetApp
    .getUi()
    .showSidebar(html);

}

🧱 Step 2 — Build the Sidebar

Sidebar.html

<!DOCTYPE html>
<html>

<head>

<base target="_top">

<style>

body {
  font-family: Arial, sans-serif;
  padding: 14px;
}

textarea {
  width: 100%;
  box-sizing: border-box;
  margin-top: 6px;
}

button {
  margin-top: 10px;
  padding: 8px 12px;
}

.section {
  margin-bottom: 18px;
}

#output {
  white-space: pre-wrap;
  background: #f5f5f5;
  padding: 10px;
  margin-top: 10px;
}

</style>

</head>

<body>

<h2>Prompt Engineering Studio</h2>

<div class="section">

<label>
<b>What do you want AI to do?</b>
</label>

<textarea
  id="roughPrompt"
  rows="6"
  placeholder="Example: Summarize customer feedback">
</textarea>

<button onclick="improvePrompt()">
Improve Prompt
</button>

</div>

<div class="section">

<label>
<b>Sample Data</b>
</label>

<textarea
  id="sampleData"
  rows="6"
  placeholder="Paste optional test data here">
</textarea>

<button onclick="testPrompt()">
Test Improved Prompt
</button>

</div>

<pre id="output"></pre>

<script>

let improvedPrompt = "";

function improvePrompt() {

  const roughPrompt =
    document
      .getElementById("roughPrompt")
      .value;

  document
    .getElementById("output")
    .textContent =
      "Engineering prompt...";

  google.script.run

    .withSuccessHandler(function(result) {

      improvedPrompt =
        result.improvedPrompt;

      document
        .getElementById("output")
        .textContent =
          JSON.stringify(
            result,
            null,
            2
          );

    })

    .withFailureHandler(function(error) {

      document
        .getElementById("output")
        .textContent =
          error.message;

    })

    .improvePrompt(roughPrompt);

}

function testPrompt() {

  if (!improvedPrompt) {

    document
      .getElementById("output")
      .textContent =
        "Improve a prompt first.";

    return;
  }

  const data =
    document
      .getElementById("sampleData")
      .value;

  document
    .getElementById("output")
    .textContent =
      "Testing prompt...";

  google.script.run

    .withSuccessHandler(function(result) {

      document
        .getElementById("output")
        .textContent =
          result;

    })

    .testPrompt(
      improvedPrompt,
      data
    );

}

</script>

</body>

</html>

🧱 Step 3 — Turn a Rough Request into a Structured Prompt

Now Gemini becomes the prompt engineer.

PromptEngineer.gs

function improvePrompt(roughPrompt) {

  if (!roughPrompt.trim()) {

    throw new Error(
      "Enter a prompt first."
    );

  }

  const instruction = `
You are an expert AI prompt engineer.

Improve the user's rough prompt.

Do not perform the requested task.

Instead, design a better prompt that another AI model can execute.

Return valid JSON only using this structure:

{
  "role": "",
  "objective": "",
  "context": [],
  "constraints": [],
  "outputFormat": "",
  "improvedPrompt": ""
}

Guidelines:

1. Clearly define the AI's role.
2. Make the objective specific.
3. Identify useful context.
4. Add reasonable constraints.
5. Prevent unsupported assumptions.
6. Specify the desired output format.
7. Preserve the user's original intent.
8. Do not add requirements unrelated to the request.

Rough prompt:

${roughPrompt}
`;

  let response =
    callGemini(
      instruction,
      ""
    );

  response =
    cleanJsonResponse_(
      response
    );

  return JSON.parse(
    response
  );

}

🧱 Step 4 — Clean Gemini’s JSON

Structured responses are much easier to work with when we isolate cleanup logic.

Utilities.gs

function cleanJsonResponse_(text) {

  return String(text || "")
    .replace(/```json/gi, "")
    .replace(/```/g, "")
    .trim();

}

This helper looks simple, but separating utility functions keeps larger Apps Script projects much cleaner.

🧪 Example

Suppose we enter:

Write an email about late invoices.

Gemini might generate:

{
  "role": "Accounts receivable communication specialist",

  "objective": "Draft a professional payment reminder for customers with overdue invoices.",

  "context": [
    "The recipient has an unpaid invoice.",
    "The message should preserve the customer relationship."
  ],

  "constraints": [
    "Use a professional and respectful tone.",
    "Do not threaten legal action.",
    "Do not invent payment dates or invoice details."
  ],

  "outputFormat": "Return a concise email with a subject line and body.",

  "improvedPrompt": "You are an accounts receivable communication specialist..."
}

We’ve transformed a vague instruction into something reusable.

🧱 Step 5 — Test the Improved Prompt

Creating a better-looking prompt isn’t enough.

We need to see how it actually performs.

PromptTester.gs

function testPrompt(
  improvedPrompt,
  sampleData
) {

  if (!improvedPrompt) {

    throw new Error(
      "No prompt provided."
    );

  }

  const prompt = `
${improvedPrompt}

TEST DATA:

${sampleData || "No additional test data provided."}
`;

  return callGemini(
    prompt,
    ""
  );

}

Now we have a small prompt testing environment inside Google Sheets.

🧱 Step 6 — Add a Prompt Library

Good prompts shouldn’t disappear after you close the sidebar.

Let’s store them.

Create a sheet called:

Prompt Library

with these columns:

NameOriginal PromptImproved PromptCreated

Then add:

PromptLibrary.gs

function savePrompt(
  name,
  originalPrompt,
  improvedPrompt
) {

  const ss =
    SpreadsheetApp
      .getActiveSpreadsheet();

  let sheet =
    ss.getSheetByName(
      "Prompt Library"
    );

  if (!sheet) {

    sheet =
      ss.insertSheet(
        "Prompt Library"
      );

    sheet.appendRow([
      "Name",
      "Original Prompt",
      "Improved Prompt",
      "Created"
    ]);

  }

  sheet.appendRow([

    name,

    originalPrompt,

    improvedPrompt,

    new Date()

  ]);

  return "Prompt saved.";

}

Now your spreadsheet becomes a reusable prompt repository.

💡 Think Beyond Individual Prompts

This becomes especially powerful when prompts are organized by purpose.

For example:

Gmail

  • reply generator
  • email summarizer
  • sentiment analyzer
  • follow-up generator

Google Sheets

  • data analyzer
  • categorizer
  • formula generator
  • report writer

Google Docs

  • document summarizer
  • content improver
  • documentation generator

Development

  • code reviewer
  • debugger
  • test generator
  • documentation writer

You’ve effectively created a prompt component library for Workspace automation.

🧱 Step 7 — Add Variables to Prompts

Here’s where things get much more interesting.

Instead of saving:

Summarize this customer feedback.

save:

You are a business analyst.

Analyze the following {{DATA_TYPE}}.

Audience:

{{AUDIENCE}}

Focus on:

{{FOCUS}}

SOURCE DATA:

{{DATA}}

Your Apps Script can replace variables dynamically.

TemplateEngine.gs

function renderPrompt_(
  template,
  variables
) {

  let result = template;

  Object.keys(
    variables
  ).forEach(function(key) {

    const token =
      "{{" + key + "}}";

    result =
      result.split(token)
        .join(
          variables[key]
        );

  });

  return result;

}

Now prompts become templates rather than one-time instructions.

🔥 Example

const prompt =
  renderPrompt_(
    template,
    {

      DATA_TYPE:
        "customer feedback",

      AUDIENCE:
        "product manager",

      FOCUS:
        "recurring complaints",

      DATA:
        feedback

    }
  );

This is an important shift.

We’re no longer simply writing prompts.

We’re programming prompts.

🧠 Prompt Engineering vs Prompt Programming

Prompt engineering asks:

What’s the best instruction?

Prompt programming asks:

How can I build a reusable system that generates the right instruction for different situations?

Apps Script is particularly useful here because the variables can come directly from:

  • spreadsheet cells
  • Gmail messages
  • Forms
  • Docs
  • Calendar events
  • Drive files
  • API responses

🔥 Advanced Challenge — Prompt Comparison

Add two prompt versions:

Prompt A

Summarize this data.

Prompt B

Your engineered prompt.

Send the same source data to Gemini using both.

Then compare:

  • relevance
  • completeness
  • formatting
  • unsupported assumptions
  • actionability

You could even ask Gemini to score both responses.

🧱 Prompt Evaluation

Example evaluation structure:

{
  "clarity": 9,
  "accuracy": 8,
  "relevance": 10,
  "formatCompliance": 9,
  "unsupportedClaims": 0,
  "overall": 9
}

Now the application becomes more than a prompt generator.

It becomes a prompt experimentation platform.

🔥 Advanced Features

Take the project further by adding:

✅ Prompt version history

✅ A/B prompt testing

✅ Prompt scoring

✅ Reusable variables

✅ Prompt categories

✅ Searchable prompt library

✅ Temperature/model settings

✅ Structured JSON schemas

✅ Few-shot examples

✅ Automatic prompt refinement

✅ Response comparison

✅ Prompt performance tracking

🌟 The Bigger Lesson

One of the most useful AI development skills isn’t simply knowing how to ask Gemini a question.

It’s learning how to build repeatable systems around prompts.

Instead of this:

User → Prompt → AI

we’re moving toward:

User Intent
     ↓
Prompt Template
     ↓
Dynamic Context
     ↓
Constraints
     ↓
Structured Output
     ↓
Gemini
     ↓
Validation
     ↓
Application

That’s a much more powerful architecture.

And Apps Script gives us a fantastic environment for connecting that architecture directly to Google Workspace.

🧪 Challenge

Build three reusable prompt templates:

1. Gmail Assistant

Input:

Email message

Output:

Summary
Priority
Recommended response
Action items

2. Spreadsheet Analyst

Input:

Spreadsheet data

Output:

Key findings
Anomalies
Trends
Recommended actions

3. Apps Script Reviewer

Input:

Apps Script code

Output:

Issues
Performance
Security
Best practices
Suggested refactor

Store all three in your Prompt Library.

You now have the beginning of your own Google Workspace AI prompt framework.

🔜 Next Issue — #31

AI Apps Script Automation Agent

This is where the series takes another major step.

Instead of Gemini only returning text, we’ll give it a controlled set of tools.

The user could request:

“Create a weekly sales report from this sheet.”

Gemini determines which actions are required.

Apps Script executes only approved functions such as:

readSheetData
      ↓
analyzeData
      ↓
createGoogleDoc
      ↓
sendEmail

We’ll explore:

  • Gemini function calling
  • tool definitions
  • controlled Apps Script actions
  • multi-step workflows
  • action validation
  • execution logs
  • approval checkpoints

The goal is not simply an AI assistant.

We’ll begin building an AI agent that can safely take actions inside Google Workspace.