AI Workflow Builder for Google Sheets Apps Script Issue #23

Describe a workflow in plain English, then use Google Apps Script + Gemini to generate automation logic and a structured workflow plan.

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

---

⭐ What You Will Build

In this issue, you’ll build an AI Workflow Builder that can:

✅ Turn plain-English workflow requests into structured automation plans
✅ Identify triggers, actions, conditions, and outputs
✅ Generate Apps Script starter code
✅ Write the workflow plan into a Google Doc
✅ Help users design automations before coding them manually

---

🧠 Why This Project Matters

Many users know what they want automated, but they don’t know how to design the workflow.

Example:

“Notify me when a row status changes to Completed.”

The AI can turn that into:

• Trigger: edit event
• Condition: status column equals Completed
• Action: send email notification
• Output: log result

This teaches users how to think like automation designers.

---

🧩 Architecture

User workflow request
↓
Apps Script sends request to Gemini
↓
Gemini returns structured JSON
↓
Apps Script creates a Google Doc with the workflow plan + starter code

---

🧱 Step 1 — Menu

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("AI Workflow Builder", "showWorkflowSidebar")
    .addToUi();
}

function showWorkflowSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Workflow Builder")
  );
}

---

🧱 Step 2 — Sidebar

Sidebar.html

<div style="font-family:Arial;padding:14px;">

<h2>AI Workflow Builder</h2>

<label><b>Describe the workflow</b></label>

<textarea id="prompt" style="width:100%;height:120px;">
Example: Notify me when a row status changes to Completed
</textarea>

<button onclick="generate()">Generate Workflow Plan</button>

<pre id="output" style="white-space:pre-wrap;margin-top:12px;"></pre>

<script>
function generate(){
  document.getElementById("output").textContent = "Generating workflow plan...";

  google.script.run
    .withSuccessHandler(res => {
      document.getElementById("output").textContent = res;
    })
    .generateWorkflowPlan(
      document.getElementById("prompt").value
    );
}
</script>

</div>

---

🧱 Step 3 — Generate Workflow Plan

WorkflowGenerator.gs

function generateWorkflowPlan(userPrompt) {
  if (!userPrompt) return "Enter a workflow request.";

  const prompt = `
You are an expert Google Apps Script automation architect.

Create a workflow automation plan.

Return JSON in this format:

{
  "title": "workflow title",
  "goal": "workflow goal",
  "trigger": "trigger description",
  "conditions": ["condition 1", "condition 2"],
  "actions": ["action 1", "action 2"],
  "outputs": ["output 1", "output 2"],
  "starterCode": "Google Apps Script starter code"
}

Workflow request:
${userPrompt}
`;

  let result;

  try {
    result = callGemini(prompt, "");
  } catch (e) {
    return "Gemini error: " + e;
  }

  result = result.replace(/```json/g, "").replace(/```/g, "").trim();

  let workflow;

  try {
    workflow = JSON.parse(result);
  } catch (e) {
    Logger.log(result);
    return "Could not parse Gemini output.";
  }

  return createWorkflowDoc_(workflow);
}

---

🧱 Step 4 — Create Workflow Doc

WorkflowDocWriter.gs

function createWorkflowDoc_(workflow) {
  const doc = DocumentApp.create(workflow.title || "AI Workflow Plan");
  const body = doc.getBody();

  body.appendParagraph(workflow.title || "AI Workflow Plan")
    .setHeading(DocumentApp.ParagraphHeading.HEADING1);

  body.appendParagraph("Goal")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendParagraph(workflow.goal || "");

  body.appendParagraph("Trigger")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendParagraph(workflow.trigger || "");

  body.appendParagraph("Conditions")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  (workflow.conditions || []).forEach(item => body.appendListItem(item));

  body.appendParagraph("Actions")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  (workflow.actions || []).forEach(item => body.appendListItem(item));

  body.appendParagraph("Outputs")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  (workflow.outputs || []).forEach(item => body.appendListItem(item));

  body.appendParagraph("Starter Apps Script Code")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendParagraph(workflow.starterCode || "");

  return "Workflow plan created: " + doc.getUrl();
}

---

🧱 Step 5 — Gemini Helper

Reuse your standard helper:

const GEMINI_API_KEY = "YOUR_API_KEY_HERE";
const GEMINI_MODEL = "gemini-2.5-flash";

---

🧪 Example Workflow Requests

Notify me when a row status changes to Completed

Create a workflow that sends a weekly summary email from this sheet

When a form response is submitted, create a task row and email the manager

Generate a workflow to archive old rows after 30 days

---

🔥 Advanced Exercises

✅ Generate installable trigger setup instructions
✅ Create actual Apps Script files automatically
✅ Add risk warnings before automation runs
✅ Add permissions/scopes explanation
✅ Generate test cases for the workflow

---

🔜 Next Issue (#24)

AI Apps Script Code Reviewer

Paste Apps Script code → Gemini reviews it for bugs, security issues, and improvements.