🚀 Apps Script + Gemini Mastery — Issue #21

AI Task Manager for Google Sheets

Turn raw notes, project updates, or meeting summaries into structured tasks automatically using Google Apps Script + Gemini.

---

⭐ What You Will Build

In this issue, you’ll build an AI Task Manager that can:

✅ Extract tasks from plain text
📅 suggest due dates
👤 identify owners when mentioned
🏷 assign priority levels
📋 write the tasks into a Google Sheet automatically

Instead of manually converting notes into action items, the tool does the first pass for you.

---

🧠 Why This Project Matters

A lot of work gets stuck between:

• meeting notes
• email threads
• project updates
• brainstorm documents

Everyone knows something needs to be done, but nobody has turned it into a clear task list yet.

This tool solves that problem.

You can simply paste:

“Sam will update the landing page by Friday. Jen needs to review the email copy. We should also schedule a client follow-up next week.”

And Apps Script + Gemini will transform that into a structured task sheet.

---

🧩 Architecture

User text input
↓
Apps Script sends content to Gemini
↓
Gemini extracts structured tasks
↓
Apps Script writes tasks into Google Sheets

---

🧱 Step 1 — Add the Menu

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("AI Task Manager", "showTaskSidebar")
    .addToUi();
}function showTaskSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Task Manager")
  );
}

---

🧱 Step 2 — Sidebar UI

Sidebar.html

<div style="font-family:Arial;padding:14px;"><h2>AI Task Manager</h2><label><b>Paste notes or task-related text</b></label><textarea id="prompt" style="width:100%;height:120px;">
Example: Sam will update the landing page by Friday. Jen reviews the email copy. Schedule client follow-up next week.
</textarea><button onclick="extractTasks()">Extract Tasks</button><pre id="output" style="white-space:pre-wrap;margin-top:12px;"></pre><script>
function extractTasks() {
  document.getElementById("output").textContent = "Extracting tasks...";  google.script.run
    .withSuccessHandler(res => {
      document.getElementById("output").textContent = res;
    })
    .extractTasksToSheet(
      document.getElementById("prompt").value
    );
}
</script></div>

---

🧱 Step 3 — Ask Gemini to Extract Tasks

TaskExtractor.gs

function extractTasksToSheet(userText) {  if (!userText) return "Enter some notes or task content.";  const prompt = `
You are a project task extraction assistant.Extract actionable tasks from the text below.Return JSON in this format:{
  "tasks": [
    {
      "task": "task description",
      "owner": "person or team or unknown",
      "due": "date or unknown",
      "priority": "High | Medium | Low"
    }
  ]
}Rules:
- Return only valid JSON
- Do not include non-actionable commentary
- If owner is missing use "unknown"
- If due date is missing use "unknown"
- Infer priority only if it is reasonably impliedText:
${userText}
`;  let result;  try {
    result = callGemini(prompt, "");
  } catch (e) {
    return "Gemini error: " + e;
  }  result = result.replace(/```json/g, "").replace(/```/g, "").trim();  let json;
  try {
    json = JSON.parse(result);
  } catch (e) {
    Logger.log(result);
    return "Could not parse Gemini output.";
  }  return writeTasksToSheet_(json.tasks || []);
}

---

🧱 Step 4 — Write Tasks Into a Sheet

TaskWriter.gs

function writeTasksToSheet_(tasks) {  if (!tasks.length) return "No tasks found.";  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName("AI Tasks");  if (!sheet) {
    sheet = ss.insertSheet("AI Tasks");
    sheet.appendRow(["Task", "Owner", "Due", "Priority"]);
  }  const rows = tasks.map(t => [
    t.task || "",
    t.owner || "unknown",
    t.due || "unknown",
    t.priority || "Medium"
  ]);  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 4).setValues(rows);  return rows.length + " tasks added to AI Tasks sheet.";
}

---

🧱 Step 5 — Gemini Helper

Reuse the same helper file from earlier issues.

GeminiHelpers.gs

const GEMINI_API_KEY = "YOUR_API_KEY_HERE";
const GEMINI_MODEL = "gemini-2.5-flash";function callGemini(prompt, text) {
  const url = `https://generativelanguage.googleapis.com/v1/models/${GEMINI_MODEL}:generateContent?key=${GEMINI_API_KEY}`;  const payload = {
    contents: [{
      parts: [{
        text: prompt + (text ? "\n\n" + text : "")
      }]
    }]
  };  const res = UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });  const json = JSON.parse(res.getContentText());
  return json.candidates[0].content.parts[0].text;
}

---

🧪 Example Inputs

Users can paste:

Sam will update the pricing page by Thursday.
Maria should review the onboarding sequence.
We need to send the proposal next week.

or

Finalize video script, book guest speaker, and confirm webinar date.

---

📋 Output Example

The tool writes rows like this into AI Tasks:

Task	Owner	Due	Priority
Update pricing page	Sam	Thursday	High
Review onboarding sequence	Maria	unknown	Medium
Send proposal	unknown	next week	Medium

---

🔥 Advanced Exercises

Readers can extend this by adding:

✅ task status column
✅ checkbox completion tracking
✅ deduplication of repeated tasks
✅ automatic due-date normalization
✅ export tasks into Google Tasks or Calendar

---

🔜 Next Issue (#22)

AI Meeting Agenda Generator

Describe a meeting goal → Apps Script generates a structured agenda automatically.

Example:

“Create an agenda for a client onboarding call”