Next-Level Data Automation: Gemini CLI, Google Sheets, and MCP

GasTips
Google Apps Script / Gemini / Model Context Protocol / MCP

Gists

Abstract

This article explores the integration of the Gemini Command-Line Interface (CLI) with Google Sheets using the Model Context Protocol (MCP). It demonstrates how to leverage the open-source projects MCPApp and ToolsForMCPServer to create a bridge between the Gemini CLI and Google Workspace. This enables users to perform powerful data automation tasks, such as creating, reading, and modifying tables in Google Sheets directly from the command line, using natural language prompts. The article provides practical examples and sample prompts to illustrate the seamless workflow and potential for building sophisticated, AI-powered applications within the Google Cloud ecosystem.

Introduction

In a recent article, “Gemini CLI Tutorial Series — Part 4: Built-in Tools,” Romin Irani explores the powerful capabilities of the Gemini Command-Line Interface (CLI), an open-source tool that brings Google’s Gemini models directly to your terminal. Ref These built-in tools allow the CLI to interact with the local file system and the internet, enabling it to perform tasks such as reading folders, creating files, and performing Google searches. One particularly impressive demonstration in the article is how Gemini CLI can process a collection of image-based invoices and extract key information into a structured table, showcasing its powerful data extraction capabilities.

The potential of Gemini CLI can be further extended by using the Model Context Protocol (MCP), a standard that allows AI applications to securely connect with external systems and data sources. This is where the MCP server, built using Google Apps Script Web Apps, comes into play. It acts as a middleware, creating a bridge between the Gemini CLI and Google Workspace services like Google Sheets, Google Docs, and Gmail.

To achieve this synchronization, two key open-source projects, MCPApp and ToolsForMCPServer, can be utilized. Ref and Ref MCPApp is a library that simplifies the creation of an MCP server using Google Apps Script, while ToolsForMCPServer provides a suite of pre-built tools for interacting with various Google Workspace services. By leveraging these tools, you can create a powerful, self-contained network that operates exclusively within the Google Cloud ecosystem, which simplifies credential management.

The synchronization of a table between the Gemini CLI and Google Sheets opens up a world of automated workflows. For example, you could extract data from local files or websites and have it automatically populate a Google Sheet, or you could use natural language commands in your terminal to manipulate and analyze data stored in a Sheet. This integration of the command line with the collaborative features of Google Sheets creates a seamless and powerful environment for developers and data analysts alike, enabling them to build sophisticated, AI-powered applications that are deeply integrated with their personal or organizational Google data.

Usage and Sample Prompts

You can see how to use this at my repository https://github.com/tanaikech/ToolsForMCPServer?tab=readme-ov-file#usage

This section introduces sample prompts and answers using the Gemini CLI and the MCP server built with Google Apps Script Web Apps.

Create a table in Gemini CLI and copy the table to Google Sheets

The actual flow can be seen in the following image.

Prompt 1

Get the weather every noon from today until 5 days after in Tokyo, and display it as a table.

By “Prompt 1”, the data is retrieved using GoogleSearch, get_current_date_time, and get_specific_date_weather. And, show the data as a table on Gemini CLI.

Prompt 2

Put the table into a Google Spreadsheet of "sample table".

By “Prompt 2”, the showing table is put into Google Sheets using search_file_in_google_drive and put_values_to_google_sheets. By this, the table was correctly put into the spreadsheet “sample table” as follows.

Read a table from Google Sheets to Gemini CLI

The actual flow can be seen in the following image.

Prompt 1

Retrieve values from "Sheet1" on the Google Spreadsheet of "sample table" and display them as a table.

By “Prompt 1”, the table values are retrieved from Google Sheets using search_file_in_google_drive and get_values_to_google_sheets. And, show the data as a table on Gemini CLI.

Modify a table on Gemini CLI and synchronize it to Google Sheets

The actual flow can be seen in the following image.

Here, I tried to modify the header title of column “B” as follows.

Prompt 1

Modify the header title of column "B" to "Weather information".

It is found that when the above prompt is run, the Gemini CLI automatically changes the table values and shows the table on the terminal, and also updates the Google Spreadsheet, simultaneously. And, the header title of the column “B” was changed successfully on Google Spreadsheet as follows.

Note

The management of Google Sheets, Google Docs, Google Slides, and so on can be advanced by updating the scripts of the tools at the MCP server. By this, more complicated operations between Gemini CLI and the MCP server will be able to be achieved.

Summary

  • Seamless Integration: The Gemini CLI can be integrated with Google Sheets and other Google Workspace services through the Model Context Protocol (MCP).
  • Open-Source Tools: The MCPApp and ToolsForMCPServer open-source projects provide the necessary tools and libraries to build an MCP server with Google Apps Script.
  • Natural Language Commands: Users can interact with Google Sheets using natural language prompts in the command line, simplifying data manipulation tasks.
  • Automated Workflows: This integration enables powerful automated workflows, such as extracting data from local files or websites and populating it into a Google Sheet.
  • Enhanced Data Management: The ability to create, read, and modify tables in Google Sheets from the Gemini CLI provides a flexible and powerful environment for developers and data analysts.

 Share!