Secure and Conversational Google Workspace Automation: Integrating Gemini CLI with a gas-fakes MCP Server

Gists

Abstract

This article introduces a method for securely executing AI-generated Google Apps Script. By implementing a “fake-sandbox” using the gas-fakes library as an MCP server, users can empower the Gemini CLI to safely automate Google Workspace tasks with granular, file-specific permissions, avoiding significant security risks.

Introduction

“Have you ever faced a task that isn’t part of your routine but is tedious to do manually, like, ‘I need to add a “[For Review]” prefix to the titles of all Google Docs in a specific folder this afternoon’? Or perhaps you’ve thought, ‘I want to use AI to work with my spreadsheets, but I’m concerned about the security implications of granting a tool full access to my Google Drive’?

Recently, I published an article, “A Fake-Sandbox for Google Apps Script: A Feasibility-Study on Securely Executing Code Generated by Gemini CLI”. This article introduced the possibility of a “fake-sandbox” for Google Apps Script using the gas-fakes library. This approach directly addresses the significant security risks associated with executing AI-generated Google Apps Script. The core issue is that Apps Script’s standard services often require sweeping permissions, such as the https://www.googleapis.com/auth/drive scope, which grants broad access to all of a user’s files. This presents a clear danger when running code from a source that isn’t fully trusted. The gas-fakes library mitigates this by emulating the Google Apps Script environment locally in Node.js and translating familiar Apps Script calls into their direct, underlying Google API requests. This abstraction allows for the use of more granular, file-specific permissions, providing a much higher level of security. After this article was published, the sandbox of gas-fakes was updated, further enhancing the secure execution of code generated by generative AI.

Now, this fake-sandbox becomes even more powerful when used as a Model Context Protocol (MCP) server. An MCP server acts as a bridge, allowing the Gemini CLI and other AI agents to interact with external tools and data sources. By exposing the gas-fakes sandbox as an MCP server, the Gemini CLI gains the ability to safely discover and execute Google Workspace automation tasks. This article introduces the steps for using the gas-fakes fake-sandbox with the Gemini CLI as an MCP server, unlocking significant potential for secure, AI-driven automation in your local environment. This integration allows developers to extend the Gemini CLI’s capabilities to perform actions like interacting with databases, APIs, and custom scripts without granting overly broad permissions.

A Bridge Between Fixed Tools and Ad-Hoc Tasks

While many excellent add-ons and fixed tools exist for Google Workspace, they are limited to their pre-defined functions. The approach described here is different: it’s like creating a ‘disposable, high-end tool’ in real-time through conversation.

It’s designed for user-specific, spontaneous requests that cannot be handled by the fixed tools of an MCP server. This method doesn’t replace those tools; it complements them, providing a secure and flexible way to handle the unpredictable nature of daily work.

You can see this workflow as follows.

Mermaid Chart Playground

Prerequisites

Before you begin, ensure you have the following installed on your system:

  • Node.js and npm: Required to install and run the gas-fakes library and the MCP server script.
  • Gemini CLI: The AI agent that will be used to generate and execute the Google Apps Script.

Setup and Configuration

Follow these steps to set up the environment.

Step 1: Install gas-fakes

Install gas-fakes in your working directory (Here, the “sample” directory is created.). You can find detailed instructions at the official gas-fakes GitHub repository. A crucial step is to correctly authorize the necessary scopes for gas-fakes to interact with Google services on your behalf.

npm install @mcpher/gas-fakes

Step 2: Install MCP Server for gas-fakes

Next, install the required modules for the MCP server in the same directory.

npm install @modelcontextprotocol/sdk zod

Then, create a file named gas-fakes-mcp.js in the root of your project directory and paste the following script into it. This script defines the run_gas_with_gas-fakes tool that the Gemini CLI will use.

import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { exec } from "child_process";
import fs from "fs/promises";
import { setTimeout } from "node:timers/promises";
import { promisify } from "util";
import { z } from "zod";

const server = new McpServer({
  name: "MCP server for gas-fakes",
  version: "0.0.1",
});

const execAsync = promisify(exec);

const tool = {
  name: "run_gas_with_gas-fakes",
  schema: {
    description:
      "Use this to safely execute a script of Google Apps Script in a sandbox using gas-fakes.",
    inputSchema: {
      gas_script: z
        .string()
        .describe(
          `Provide a Google Apps Script. The Google Apps Script is the generated script or the script provided by a prompt. When you put the script in a function like \`function sample() { script }\`, it is required to add \`sample();\` to run the function. When you directly put the script, the script can be run. In the current stage, gas-fakes cannot use \`Logger.log\`. So, please use \`console.log\` instead of it. If an error occurs, modify the script by referring to StackOverflow again.`
        ),
      whitelistItems: z
        .array(z.string().describe(`File ID of file on Google Drive`))
        .describe(
          `Use this to access the existing files on Google Drive. Provide the file IDs of the files on Google Drive as an array. When this is used, the property "sandbox" is required to be true. The default is no items in an array.`
        )
        .optional(),
      sandbox: z
        .boolean()
        .describe(
          `The default is true. When this is true, the script is run with the sandbox. When this is false, the script is run without the sandbox.`
        ),
    },
  },
  func: async (object = {}) => {
    const { sandbox = true, whitelistItems = [], gas_script } = object;
    const importFile = "./sample_gas.mjs";

    function getImportScript() {
      const importScriptAr = [
        `import "./node_modules/@mcpher/gas-fakes/main.js"`,
        "",
      ];
      if (whitelistItems.length === 0) {
        importScriptAr.push(
          sandbox ? `ScriptApp.__behavior.sandBoxMode = true;` : "",
          `\n\n${gas_script}\n\n`,
          sandbox ? `ScriptApp.__behavior.trash();` : ""
        );
      } else {
        const wl = whitelistItems
          .map((id) => `behavior.newIdWhitelistItem("${id}").setWrite(true)`)
          .join(",");
        importScriptAr.push(
          `const behavior = ScriptApp.__behavior;`,
          `behavior.sandboxMode = true;`,
          `behavior.strictSandbox = true;`,
          `behavior.setIdWhitelist([${wl}]);`,
          `\n\n${gas_script}\n\n`,
          `ScriptApp.__behavior.trash();`
        );
      }
      return importScriptAr.join("\n");
    }

    try {
      const importScript = getImportScript();
      await fs.writeFile(importFile, importScript);
      await setTimeout(500);

      const { stdout } = await execAsync(`node ./${importFile}`);
      return {
        content: [{ type: "text", text: stdout || "Done." }],
        isError: false,
      };
    } catch (err) {
      return {
        content: [{ type: "text", text: err.message }],
        isError: true,
      };
    } finally {
      try {
        await fs.unlink(importFile);
      } catch (err) {
        console.error(err.message);
      }
    }
  },
};

const { name, schema, func } = tool;
server.registerTool(name, schema, func);

const transport = new StdioServerTransport();
await server.connect(transport);

Step 3: Install Gemini CLI

If you haven’t already, install the Gemini CLI. Instructions can be found at the official Gemini CLI GitHub repository.

Step 4: Configure Gemini CLI

To connect the Gemini CLI to your newly created MCP server, you need to modify its settings file. Locate the settings.json file in your .gemini directory and add the mcpServers configuration.

{
  "security": {
    "auth": {
      "selectedType": "### your setting ###"
    }
  },
  "ui": {
    "theme": "Default"
  },
  "mcpServers": {
    "gas-fakes": {
      "command": "node",
      "args": ["./gas-fakes-mcp.js"]
    }
  }
}

Step 5: Verify Your Directory Structure

After completing the setup, your project directory (e.g., named sample) should have the following structure:

/sample/
├── .env
├── gas-fakes-mcp.js
├── gasfakes.json
├── package-lock.json
├── package.json
├── .gemini/
│   ├── settings.json
└── node_modules/

Demonstration

With everything configured, you can now launch the Gemini CLI.

gemini

Here are a few sample prompts to demonstrate the capabilities of this setup.

Sample 1: Creating and Running a Script in the Sandbox

This prompt asks Gemini to create a Google Apps Script to perform several actions on a new Google Sheet and then execute it.

Prompt:

Follow the following mission in order.

## 1. Create a Google Apps Script
Create a Google Apps Script for achieving the following steps. Here, the script is not required to be run. Just show the created script.

1. Create a new Google Spreadsheet as "temp spreadsheet".
2. Insert a new sheet as "temp" to the created Spreadsheet.
3. Put a value of `[["header1", "header2", "header3"], ["a2", "b2","c2"]]` into the "temp" sheet.
4. Get the values of row 2 of the "temp" sheet.
5. Show the values.
6. Show the created script.

## 2. Run script.
Run the created script.

Result:

As shown in the result, Gemini correctly generates the script and then uses the run_gas_with_gas-fakes tool to execute it within the secure fake-sandbox. The script runs without needing broad access to your Google Drive.

Sample 2: Interacting with Existing Files Securely

This example demonstrates how to securely interact with an existing file by adding its ID to a whitelist, ensuring the script can only access that specific file.

The “Sheet1” sheet of a spreadsheet on Google Drive is as follows.

Prompt:

Follow the following mission in order.

## 1. Create a Google Apps Script (A)
Create a Google Apps Script for achieving the following steps. Here, the script is not required to be run. Just show the created script.

1. Search a Google Spreadsheet in my Google Drive. The name of the Google Spreadsheet is "sample for gas-fakes".
2. Get Spreadsheet ID.

## 2. Run script.
Run the script (A) with a sandbox.

## 3. Create a Google Apps Script (B)
Create a Google Apps Script for achieving the following steps. Here, the script is not required to be run. Just show the created script.

1. Retrieve values from the "Sheet1" sheet in the Google Spreadsheet using the retrieved Spreadsheet ID.
2. Show the retrieved values.

## 4. Run script.
Run the script (B) with a sandbox including the retrieved Spreadsheet ID in the whitelist.

Result:

This two-step process first identifies the specific file ID and then runs a second script that is only granted permission to access that ID. This showcases the granular control offered by the sandbox, allowing you to use Drive data as a resource and save generated content back to your Drive safely.

Alternatively, you can disable the sandbox for specific tasks if you trust the operation.

Prompt:

Follow the following mission in order.

## 1. Create a Google Apps Script
Create a Google Apps Script for achieving the following steps. Here, the script is not required to be run. Just show the created script.

1. Search a Google Spreadsheet in my Google Drive. The name of the Google Spreadsheet is "sample for gas-fakes".
2. Get the Spreadsheet ID from the searched Spreadsheet.
3. Retrieve values from the "Sheet1" sheet in the Google Spreadsheet using the Spreadsheet ID.
4. Show the retrieved values.

## 2. Run script.
Run the created script without a sandbox.

Result:

The tool allows for toggling the sandbox, providing flexibility for different security needs.

Sample 3: Handling Limitations

The gas-fakes library is continuously evolving. This example shows how Gemini handles a feature that is not yet implemented.

Prompt:

Follow the following mission in order.

## 1. Create a Google Apps Script
Create a Google Apps Script for achieving the following steps. Here, the script is not required to be run. Just show the created script.

1. Create a new Google Spreadsheet as "temp spreadsheet".
2. Put a value of `[["header1", "header2"], [1, 2], [2, 3], [3, 4]]` into the 1st sheet.
3. Insert a chart using the values.

## 2. Run script.
Run the created script.

Result:

Currently, gas-fakes does not support chart manipulation. The test shows that when the initial script fails due to an unimplemented method, Gemini intelligently interprets the error message and modifies the script to avoid the problematic function call.

Summary

Integrating a gas-fakes MCP server with the Gemini CLI transforms Google Apps Script from a low-code platform into a conversational and secure automation powerhouse. This approach addresses critical security concerns while unlocking new possibilities for productivity.

  • Enhanced Security: By default, AI-generated scripts run in a secure sandbox, preventing them from accessing your Google Drive files without explicit, granular permission.
  • Conversational Automation: Users can generate and execute complex Google Workspace automations using simple, natural language prompts, making scripting accessible to a broader audience.
  • Granular Permissions: The sandbox allows for whitelisting specific file IDs, ensuring that scripts only have access to the files they absolutely need for a given task.
  • Flexible Execution: The ability to toggle the sandbox on or off provides the flexibility to run trusted scripts with broader permissions when necessary.
  • Intelligent Adaptation: The integration demonstrates that the AI agent can interpret errors from the sandbox environment and modify scripts to work around current limitations.

 Share!