Build an AI Project Manager with MCP, Node.js, and Google Sheets

Learn to build an intelligent task management system that understands natural language and stores data in Google Sheets using the Model Context Protocol.

💡 I included the link to the example repository at the conclusion of this tutorial.

This guide walks through building a simple project management MCP server using Node.js, TypeScript, Google Sheets as a backend, and OpenAI for natural language interactions. You'll learn how to:

1. Expose tools via the Model Context Protocol (MCP)
2. Write tasks into a Google Sheets spreadsheet
3. Query tasks naturally using OpenAI agents

We will build from scratch using:

1. Node.js + Express (REST API backend)
2. Google Sheets API via googleapis
3. OpenAI Agents + MCP (for AI reasoning & tool usage)
4. Zod (validation)
5. TypeScript (strong typing)

Here is how the spreadsheet will look like when the project is done:

Prerequisites

Before we start, make sure you have:

• Node.js (>= 18)
• npm or yarn
• Google Spreadsheet credentials (for managing the spreadsheet)
• An OpenAI API key (we'll use it to run the agent)

Project Setup

Let's start by creating our project and installing the necessary dependencies. We need several packages to build our MCP server with Google Sheets integration.

## Create folder
mkdir blog-ai-mcp-pm && cd blog-ai-mcp-pm

## Init Node.js project
npm init -y

## Install dependencies
npm install express zod dotenv
npm install googleapis openai @openai/agents @modelcontextprotocol/sdk

## Dev deps
npm install -D typescript tsx @types/node @types/express

Here's what each dependency does:

• express: Our web server framework
• zod: Runtime validation for API inputs
• googleapis: Official Google Sheets API client
• @modelcontextprotocol/sdk: Core MCP protocol implementation
• tsx: Fast TypeScript execution for development

Now let's set up TypeScript configuration for our project:

npx tsc --init

I'm using a specific TypeScript configuration optimized for modern Node.js development with ESM modules. Replace the generated tsconfig.json with this:

{
  "compilerOptions": {
    // Ignore module structure
    "module": "Preserve",

    // Allow JSON modules to be imported
    "resolveJsonModule": true,

    // Allow JS files to be imported from TS and vice versa
    "allowJs": true,

    // Use correct ESM import behavior
    "esModuleInterop": true,

    // File Layout
    // "rootDir": "./src",
    "outDir": "./dist",

    // Environment Settings
    // See also https://aka.ms/tsconfig/module
    "target": "ES2020",
    "types": [],

    // Other Outputs
    "sourceMap": true,
    "declaration": true,
    "declarationMap": true,

    // Stricter Typechecking Options
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,

    // Recommended Options
    "strict": true,
    "verbatimModuleSyntax": true,
    "isolatedModules": true,
    "noUncheckedSideEffectImports": true,
    "moduleDetection": "force",
    "skipLibCheck": true,
  }
}

The key settings here are:

• module: "Preserve": Keeps imports/exports unchanged so Node.js can resolve them as ESM.
• esModuleInterop: true: Makes import express from "express" work with CommonJS packages.
• outDir: "./dist": Compiled JS goes into dist/.
• target: "ES2020": Uses modern JS features supported by recent Node.js.
• strict: true: Enforces strong typing overall.

Project Structure

blog-ai-mcp-pm/
├── dist/
├── src/
│   ├── agent.ts
│   ├── index.ts
│   ├── mcpServer.ts
│   ├── config/
│   │   ├── config.ts
│   │   └── constants.ts
│   ├── routes/
│   │   └── tasks.ts
│   ├── services/
│   │   └── googleSheetsService.ts
│   ├── tools/
│   │   ├── addTask.ts
│   │   ├── getTasks.ts
│   │   └── index.ts
│   ├── types/
│   │   └── task.interface.ts
│   ├── utils/
│   │   ├── formatters.ts
│   │   └── helpers.ts
├── .env
├── package.json
├── tsconfig.json

We will organize the project using a clean modular structure to keep concerns separated and maintainable. The core entry points are index.ts, which initializes the app, agent.ts, which configures the OpenAI MCP Agent, and mcpServer.ts, which sets up the MCP server itself.

Features are broken down into their own layers: routes/ defines task-related endpoints, tools/ holds the MCP tools like addTask and getTasks, and services/ contains integrations such as googleSheetsService.ts for external APIs.

Shared logic is centralized under utils/ for formatters and helpers, while types/ defines TypeScript interfaces like task.interface.ts to ensure type safety. Configuration constants and environment variables live in config/ and .env, keeping settings flexible and secure.

Environment Config

Let's continue with the config setup. We need to create a centralized configuration system that reads our environment variables and provides type-safe access to them throughout our application.

// src/config/config.ts

import "dotenv/config";

export const config = {
  port: process.env.PORT ? parseInt(process.env.PORT, 10) : 3000,
  google: {
    projectId: process.env.GOOGLE_PROJECT_ID,
    privateKeyId: process.env.GOOGLE_PRIVATE_KEY_ID,
    privateKey: process.env.GOOGLE_PRIVATE_KEY,
    clientEmail: process.env.GOOGLE_CLIENT_EMAIL,
    clientId: process.env.GOOGLE_CLIENT_ID,
  },
  spreadsheet: {
    id: process.env.SPREADSHEET_ID
  },
  openAI: {
    apiKey: process.env.OPENAI_API_KEY,
  },
  isProd: process.env.NODE_ENV === "production",
};

This configuration file centralizes all our environment variables in one place. The import "dotenv/config" automatically loads our .env file, and we're creating a typed config object that the rest of our application can safely import. Notice how we're parsing the port as an integer and providing a default value - this prevents runtime errors if the environment variable isn't set properly.

💡 Note: You Need API Usage Balance

Keep in mind that using the OpenAI API is a paid service. To make requests to the API, your OpenAI account must have a sufficient balance or an active subscription plan. If you run out of balance, your API requests will fail until you top up your account or renew your subscription.

You can get the detail of the google credentials by downloading the json that looks like this:

{
  "type": "service_account",
  "project_id": "",
  "private_key_id": "",
  "private_key": "-----BEGIN PRIVATE KEY-----\n\n-----END PRIVATE KEY-----\n",
  "client_email": "[e-mail]",
  "client_id": "",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "",
  "universe_domain": "googleapis.com"
}

Now let's define some constants that our application will use:

// src/config/constants.ts

export const USER_AGENT = "project-management-app/1.0";

export const TASK_STATUSES = ["Todo", "In Progress", "Done"] as const;

These constants define our application's identity for API calls and the allowed task statuses. Using as const on the task statuses array gives us strict TypeScript typing - TypeScript will know exactly which strings are valid, not just that it's an array of strings. This helps prevent typos when working with task statuses later in our code.

Create Utility Codes

Now we'll create some helper utilities that our MCP server will use throughout the application. These utilities handle common formatting tasks and provide consistent interfaces for our tools.

Create Formatter Helper

// src/utils/formatters.ts
interface ToolResponseProps {
  content: {
    type: "text";
    text: string;
  }[];
}

export function toolResponse(message: string): ToolResponseProps {
  return {
    content: [
      {
        type: "text",
        text: message,
      },
    ],
  };
}

This formatter creates standardized responses for our MCP tools. The MCP protocol expects responses in a specific format with a content array containing text objects. Rather than manually creating this structure every time a tool returns data, we use this helper function to ensure consistent formatting across all our tools.

Read also:

• Building a CLI Tool in JavaScript: Streamlining Tasks With Simple Commands
• Laravel Service Pattern: Applying SOLID Principles for Scalable Code

Create General Helper

// src/utils/helpers.ts

export const formatDate = (date: Date): string => {
  let d = date.getDate().toString().padStart(2, "0");
  let m = (date.getMonth() + 1).toString().padStart(2, "0");
  let y = date.getFullYear();
  return `${d}/${m}/${y}`;
}

This utility formats JavaScript Date objects into a consistent DD/MM/YYYY string format. We'll use this when creating tasks with due dates or displaying task information. The padStart(2, "0") ensures single-digit days and months get a leading zero, so we get "01/03/2024" instead of "1/3/2024" for better consistency in our spreadsheet.

Google Sheet Service

Let's create the Google Sheets service that will handle authentication and provide access to the Sheets API. This service will be the foundation for all our spreadsheet operations.

// src/services/googleSheetsService.ts

import "dotenv/config";
import { google } from "googleapis";
import { config } from "../config/config.js";

const SCOPES = ["https://www.googleapis.com/auth/spreadsheets"];

const {
  projectId,
  privateKeyId,
  privateKey,
  clientEmail,
  clientId,
} = config.google;

if (
  !projectId ||
  !privateKeyId ||
  !privateKey ||
  !clientEmail ||
  !clientId
) {
  throw new Error("Missing required Google service account environment variables.");
}

const auth = new google.auth.GoogleAuth({
  credentials: {
    type: "service_account",
    project_id: projectId,
    private_key_id: privateKeyId,
    private_key: privateKey.replace(///n/g, "/n"),
    client_email: clientEmail,
    client_id: clientId,
  },
  scopes: SCOPES,
});

export const sheets = google.sheets({ version: "v4", auth });

This service sets up authentication with Google's service account system. We're using service account credentials (not OAuth) because our MCP server needs to access the spreadsheet programmatically without user interaction. The SCOPES array specifies we only need spreadsheet access, following the principle of least privilege. The exported sheets client will handle all our spreadsheet operations.

Create The Tools

Now we'll build the core MCP tools that our AI agent will use to manage tasks. Each tool follows the MCP protocol specification and includes proper validation.

Create Add Task Tool

// src/tools/addTask.ts

import { z } from "zod";
import { TASK_STATUSES } from "../config/constants.js";
import { sheets } from "../services/googleSheetsService.js";
import { toolResponse } from "../utils/formatters.js";
import { formatDate } from "../utils/helpers.js";
import { config } from "../config/config.js";

const addTaskToolSchema = {
  task: z.string().max(1000).describe("The task to assign"),
  assignee: z.string().max(100).describe("The name of the person that should be assigned to"),
  status: z.enum(TASK_STATUSES).describe("Status of the task"),
  dueDate: z.string().max(100).describe("Due date of the task with toLocaleDateString format (DD/MM/YYYY)"),
};

const zAddTaskToolSchema = z.object(addTaskToolSchema);

type AddTaskToolProps = z.infer<typeof zAddTaskToolSchema>;

export const addTaskTool = {
  name: "add_task",
  description: "Add task to the spreadsheet",
  schema: addTaskToolSchema,
  async execute({ task, assignee, status, dueDate }: AddTaskToolProps): Promise<any> {
    try {
      if (!config.spreadsheet.id) {
        return toolResponse("Spreadsheet ID is missng");
      }

      const createdDate = formatDate(new Date);
      const values = [[task, assignee, status, createdDate, dueDate]];
      await sheets.spreadsheets.values.append({
        spreadsheetId: config.spreadsheet.id,
        range: "Tasks!A:E",
        valueInputOption: "USER_ENTERED",
        requestBody: { values },
      });

      const message = `✅ Task added:\n- Task: ${task}\n- Assignee: ${assignee}\n- Status: ${status}\n- Created: ${createdDate}\n- Due: ${dueDate}`;

      return toolResponse(message);
    } catch (error) {
      console.error("Error adding task:", error);
      throw error;
    }
  },
};

This tool handles creating new tasks in our spreadsheet. The Zod schema validates all inputs before execution - ensuring task descriptions aren't too long, assignee names are reasonable, and status values match our predefined constants. The sheets.spreadsheets.values.append method adds a new row to our "Tasks" sheet, and we return a formatted confirmation message that the AI agent can show to users.

Create Get Tasks Tool

// src/tools/getTasks.ts

import { config } from "../config/config.js";
import { sheets } from "../services/googleSheetsService.js";
import type { Task } from "../types/task.interface.js";
import { toolResponse } from "../utils/formatters.js";

export const getTasksTool = {
  name: "get_tasks",
  description: "Get all tasks",
  schema: {},
  async execute(): Promise<any> {
    try {
      if (!config.spreadsheet.id) {
        return toolResponse("Spreadsheet ID is missng");
      }

      const response = await sheets.spreadsheets.values.get({
        spreadsheetId: config.spreadsheet.id,
        range: "Tasks!A:E",
      });

      const rows = response.data.values || [];
      if (rows.length <= 1) {
        return toolResponse("📭 No tasks found.");
      }

      // skip header row
      const tasks: Task[] = rows.slice(1).map((row) => ({
        task: row[0] || "",
        assignee: row[1] || "",
        status: (row[2] as Task["status"]) || "Todo",
        createdDate: row[3] || "",
        dueDate: row[4] || "",
      }));

      // Format tasks as text
      const formattedTasks = tasks
        .map(
          (t, i) =>
            `${i + 1}. ${t.task} — ${t.assignee} [${t.status}] (Created: ${t.createdDate}, Due: ${t.dueDate})`
        )
        .join("\n");

      return toolResponse(`📋 Task list:\n\n${formattedTasks}`);
    } catch (error) {
      console.error("Error fetching tasks:", error);
      throw new Error("Failed to fetch tasks");
    }
  },
};

This tool retrieves all tasks from our spreadsheet and formats them for display. We're reading from the "Tasks!A:E" range (columns A through E, remember the screenshot I shared above), skipping the header row with slice(1), and mapping each row to our Task interface. The formatted output includes emojis and clear structure so users can easily read the task list when the AI agent returns it. Notice how we handle empty cells with fallback values using the || "" operator.

Create the Types Used

// src/types/task.interface.ts

import type { TASK_STATUSES } from "../config/constants.js";

export type TaskStatus =  typeof TASK_STATUSES[number];

export interface Task {
  task: string;
  assignee?: string;
  status: TaskStatus;
  createdDate?: string;
  dueDate?: string;
}

Here we're defining the core data structure for our project management system. The TaskStatus type extracts the literal string types from our TASK_STATUSES constant array, giving us type safety - TypeScript will only allow "Todo", "In Progress", or "Done" as valid status values. The Task interface represents a single task with optional fields for assignee and dates, matching the column structure we'll use in our Google Sheets.

Register the Tools

// src/tools/index.ts

import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { addTaskTool } from "./addTask.js";
import { getTasksTool } from "./getTasks.js";

export function registerTools(server: McpServer) {
  server.tool(addTaskTool.name, addTaskTool.description, addTaskTool.schema, addTaskTool.execute);
  server.tool(getTasksTool.name, getTasksTool.description, getTasksTool.schema, getTasksTool.execute);
}

This registration function connects our tools to the MCP server. Each tool gets registered with its name, description, input schema, and execution function. The MCP server uses this information to expose the tools to AI agents, allowing them to understand what each tool does and how to call it properly. This centralized registration makes it easy to add new tools later - just import them and add another server.tool() call.

Create the MCP Server

// src/mcpServer.ts

import "dotenv/config";
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { registerTools } from "./tools/index.js";

async function main() {
  const server = new McpServer({
    name: "project-management",
    version: "1.0.0",
    capabilities: {
      resources: {},
      tools: {},
    },
  });

  registerTools(server);

  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error("Project Management MCP Server running on stdio");
}

main().catch((error) => {
  console.error("Fatal error in main():", error);
});

This creates our MCP server that will expose our project management tools to AI agents. The server uses stdio transport, which means it communicates through standard input/output streams - this is how OpenAI agents will connect to it. We register our tools using the function we created earlier, and the server announces its capabilities to any connecting agents.

Read also:

• Building an AI Assistant for Exploratory Data Analysis with Node.js and OpenAI
• Building a Telegram Bot in Node.js: Create Your Own Daily Word Bot

Create the Agent Using OpenAI Agent

// src/agent.ts

import { Agent, MCPServerStdio } from "@openai/agents";
import OpenAI from "openai";
import { config } from "./config/config.js";

const client = new OpenAI({ apiKey: config.openAI.apiKey });

const mcpServer = new MCPServerStdio({
  name: "pmMcp",
  fullCommand: config.isProd ? "node ./dist/mcpServer.js" : "tsx ./src/mcpServer.ts",
});

mcpServer.connect();

export const agent = new Agent({
  name: "PMAgent",
  instructions: "You are a project management agent. Help the user with adding and getting all tasks.",
  mcpServers: [mcpServer],
});

This sets up our OpenAI agent that will use our MCP server. The MCPServerStdio connects to our MCP server by running it as a subprocess - using the compiled version in production and the TypeScript version during development. The agent gets instructions about its role as a project management assistant and has access to all the tools we registered in our MCP server. When users interact with this agent, it can intelligently decide when to use our add_task and get_tasks tools based on natural language requests.

Create the Route

// src/routes/tasks.ts

import { run } from "@openai/agents";
import express from "express";
import z from "zod";
import { agent } from "../agent.js";

const tasksRouter = express.Router();

tasksRouter.post("/chat", async (req, res) => {
  try {
    const chatSchema = z.object({
      query: z.string().min(1),
    });

    const parsed = chatSchema.safeParse(req.body);
    if (!parsed.success) {
      return res.status(400).json({
        error: "Invalid request body",
      });
    }

    const { query } = parsed.data;

    const systemInstruction = `Always use the current date: ${new Date().toLocaleDateString("en-GB")}.`;
    const result = await run(agent, [
      {
        role: "system",
        content: systemInstruction,
      },
      {
        role: "user",
        content: query,
      },
    ]);

    return res.json({
      data: {
        answer: result.finalOutput,
      },
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Something went wrong" });
  }
});

export default tasksRouter;

This route creates the API endpoint where users can send natural language queries about project management. The /chat endpoint accepts POST requests with a query string, validates the input using Zod, then passes it to our OpenAI agent. The run function executes the agent with access to our MCP tools - the agent will automatically decide whether to add tasks, retrieve tasks, or just respond conversationally based on the user's request. We return the agent's final output as JSON.

Create the Index

// src/index.ts

import express from "express";
import tasksRouter from "./routes/tasks.js";
import { config } from "./config/config.js";

const app = express();
app.use(express.json());

app.use("/api/tasks", tasksRouter);

app.listen(config.port, () => console.log(`Server running on http://localhost:${config.port}`));

This is our main server file that ties everything together. We create an Express app, enable JSON parsing for incoming requests, and mount our tasks router at the /api/tasks path. This means our chat endpoint will be available at POST /api/tasks/chat. The server starts on the configured port and provides a simple way to interact with our project management system through HTTP requests.

With this setup, users can send requests like {"query": "Add a task to review the documentation, assign it to John, due tomorrow"} and the agent will automatically use our MCP tools to create the task in Google Sheets.

Package.json Configuration

Here's our package.json scripts:

{
  "name": "mcp-node",
  "type": "module",
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "start": "tsx src/index.ts",
    "build": "tsc",
    "prod": "npm run build && node dist/index.js"
  },
  "devDependencies": {
    // ...
  },
  "dependencies": {
    // ...
  }
}

Explanation:

• "type": "module": Enables ES modules (import/export syntax) instead of CommonJS (require/module.exports). This is essential for modern Node.js development and MCP compatibility.
• "dev": "tsx watch src/index.ts": Runs our TypeScript code directly with hot reload. When you save any file, the server automatically restarts. Perfect for development.
• "start": "tsx src/index.ts": Runs the TypeScript code once without watching for changes. Useful for testing the production-like behavior without compilation.
• "build": "tsc": Compiles our TypeScript code into JavaScript in the dist/ folder using our tsconfig.json settings.
• "prod": "npm run build && node dist/index.js": First compiles the code, then runs the compiled JavaScript. This is what you'd use in production deployment.

Testing

Now let's try our app by running npm run dev.

Conclusions

We've successfully built a complete project management system that combines modern AI capabilities with practical data storage. Here's what we accomplished:

• Built an MCP Server: Created a Model Context Protocol server that exposes project management tools to AI agents, allowing natural language interaction with our task management system.
• Integrated Google Sheets: Set up service account authentication and created tools that can read from and write to Google Sheets, turning a simple spreadsheet into a powerful backend for our application.
• Created AI-Powered Interface: Developed an OpenAI agent that can understand natural language requests like "add a task for John to review the docs by Friday" and automatically translate them into structured data operations.
• Implemented Type Safety: Used TypeScript and Zod throughout the project to catch errors early and ensure data consistency between our API, spreadsheet, and AI agent interactions.
• Modular Architecture: Organized the code into clean, separated concerns - configuration, services, tools, routes, and utilities - making the system easy to extend and maintain.

What You Can Do Next

With this foundation, you can easily extend the system:

• Add more tools: Create update task, delete task, or filter tasks tools
• Enhance the agent: Give it more sophisticated project management capabilities
• Build a frontend: Create a web interface that uses the /api/tasks/chat endpoint
• Add authentication: Implement user management and task ownership
• Scale the data: Move from Google Sheets to a proper database when needed

The beauty of this MCP approach is that your AI agent automatically gets access to any new tools you create, without needing to retrain or reconfigure the AI model. You simply register new tools, and the agent can start using them intelligently based on user requests.

You now have a working project management system that demonstrates the power of combining traditional APIs, modern AI agents, and the Model Context Protocol for building intelligent, extensible applications.

💻 The repository for this example can be found at fajarwz/blog-ai-mcp-pm.

💡 Thanks for reading all the way to the end. If you found this helpful, please like or star the Github repo so I know it's really helpful. Otherwise, let me know what I can improve in the comment or email.