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.
For developers who work in the command line, the introduction of Google’s Gemini Command-Line Interface (CLI) has opened up new ways to interact with AI. The open-source tool allows you to bring the power of Gemini models directly into your terminal, making it a useful companion for coding assistance, content generation, and task automation. While the CLI can interact with your local file system and the internet, its standard capabilities don’t provide a direct path to your personal data within Google Workspace.
But what if you could extend it? What if you could create a secure connection, allowing the Gemini CLI to interact with your own files in Drive, events in Calendar, or emails in Gmail?
Over the past month, Google Workspace Developer Expert Kanshi Tanaike has published an insightful series of blog posts that provides a practical blueprint for achieving just that. In this series, he demonstrates how to use the flexibility of Google Apps Script to create a powerful, personalised bridge between the Gemini CLI and the entire suite of Google Workspace services.
At the centre of Kanshi’s solution is a simple and effective architecture. He shows readers how to build a Model Context Protocol (MCP) server using a Google Apps Script project deployed as a Web App. This server acts as the secure intermediary between the Gemini CLI and your Google account.
The key advantage of this approach lies in its security and simplicity. By using Apps Script, the complex and critical OAuth2 authorisation is handled natively within the Google ecosystem. This means developers can create a robust tool without the overhead of managing credentials or setting up separate cloud infrastructure.
Throughout the series, Kanshi doesn’t just provide the theory; he delivers the tools to make it happen. He has developed and shared two key open-source Apps Script libraries, MCPApp and ToolsForMCPServer, which significantly simplify the process. These libraries provide a ready-to-use framework and a rich set of functions for interacting with Workspace services.
The result is a system capable of executing complex, multi-step automations from a single, natural-language prompt. The examples in his posts speak for themselves:
Automated Data Entry: Fetching a multi-day weather forecast and populating it directly into a Google Sheet.
Content Creation: Uploading a local PDF to Drive, generating a summary, creating a new Google Slides presentation from that summary, and emailing it to a colleague.
Interactive Tasks: Generating a custom survey in Google Forms and sending out the link via Gmail.
This extensibility is what sets the approach apart. While a standard tool like the Gemini App (gemini.google.com) provides a familiar conversational interface, it offers a fixed set of features. This CLI-based method allows developers to keep that same intuitive, conversational style of interaction but makes it completely extensible. With the CLI and a custom MCP server, developers are no longer limited—they can build bespoke tools to solve their specific challenges.
For example, a common request from Workspace users is the ability to generate an entire presentation from source material. Using this framework, a developer could easily build a custom tool to do just that, triggered by a single command.
This series is a great example of the innovation happening within the Google Workspace developer community. While this command-line approach is naturally suited for developers and power users comfortable with scripting, it offers a level of control and automation that is difficult to achieve through graphical interfaces. It provides a clear, powerful, and extensible pattern for anyone in this group looking to explore their own sophisticated AI-powered automations.
We highly recommend diving into the series to see what’s possible. You can start with the most recent post, which links back to the previous articles in the series:
This framework integrates Google Apps Script web apps into a standard website
Integrating a Google Apps Script web app into a public website offers powerful possibilities, but developers can often hit a wall of limitations. Serving from a custom domain, managing multiple Google account logins, preventing security vulnerabilities like clickjacking, and providing a smooth user experience are significant hurdles.
A new open-source framework from developer Zig Mandel provides a comprehensive solution, offering a flexible framework for embedding Apps Script web apps securely and professionally. At its core, the framework uses a parent website to host the Apps Script in an iframe, using a secure postMessage protocol for communication between the parent website and your web app. A key innovation is its ability to dynamically and securely load different script deployments using a cryptographic signature (org/sig) system, preventing unauthorised use while enabling advanced features like centralised logging and analytics integration.
What This Framework Solves
This solution is more than just an iframe wrapper; it’s a complete integration kit designed to enhance your Apps Script web app to a professional standard. It addresses several key pain points:
Custom Domain Serving: Serve your app from your own domain, not the standard script.google.com URL.
Secure Embedding: A communication protocol and domain validation prevent your app from being embedded on unauthorized sites.
Multi-Account & Workspace Compatibility: The framework is built to handle complexities that arise when users are logged into multiple Google accounts or are using a Google Workspace account.
Seamless User Experience: The architecture eliminates the jarring “flash” of a new page loading within the iframe, creating a smooth transition for single-page application (MPA) style web apps.
Analytics & Logging Integration: Send events from your Apps Script directly to the parent website’s Google Tag Manager instance and forward client-side logs to a centralized location like Google Cloud Logging.
Summary
In short, this framework hopefully unlocks a better way to implement public-facing Google Apps Script for web applications. For developers it provides the tools to overcome common embedding hurdles, moving beyond simple scripts to create secure, professional, and seamlessly integrated user experiences. To explore the code and get started on your own integration, check out the repository on GitHub.
Exploring Agent2Agent (A2A) protocol implementation in Google Apps Script seamlessly allows AI agents to access Google Workspace data and functions. This could enable complex workflows and automation, overcoming platform silos for integrated AI applications.
The buzz around AI and collaborative agent systems is growing, and with it, the need for standardized communication. The recently introduced Agent2Agent (A2A) protocol aims to enable exactly that – allowing different AI agents, regardless of their underlying framework, to discover each other’s capabilities and work together. But how can the Google Workspace developer community tap into this exciting new frontier?
Enter prolific Apps Script contributor Kanshi Tanaike, who has once again provided a valuable resource for the community. Kanshi has published a detailed guide and a Google Apps Script library demonstrating how to build an Agent2Agent (A2A) server using Google Apps Script.
Why is this significant?
Kanshi’s work explores the feasibility of implementing a core A2A server component directly within the Google Workspace ecosystem. As Kanshi notes, “Such an implementation could seamlessly allow AI agents to securely access and utilize data and functionalities across Google services like Docs, Sheets, and Gmail via a standardized protocol.”. This opens up possibilities for:
Integrating AI agents with your Google Workspace data: Imagine AI agents that can directly interact with your Sheets, Docs, Drive files, or Gmail.
Building sophisticated AI-powered workflows: Automate complex tasks that span across different services, orchestrated by communicating AI agents.
Kanshi’s guide walks through the setup and implementing functions that allow AI agents to interact with Google Workspace services like Drive or external APIs via UrlFetchApp. This work significantly lowers the barrier to entry for Google Workspace developers looking to experiment with and implement A2A-compatible agents and as such I highly encourage you to check out the linked source post for the complete guide, code, and library details.
As a Google Workspace Developer Advocate, I’m always exploring innovative ways to leverage technology. I’m thrilled to share my new Jupyter Notebook that showcases the power of Gemini and the Google Calendar API for productivity coaching. 🚀✨
This notebook dives into:
– Using Gemini’s multimodal capabilities to analyze calendar data. 📅
– Leveraging function calling to connect Gemini with Google Workspace APIs. 🔗
– Developing a personalized AI productivity coach. 🧑🏫
It’s amazing to see how generative AI can transform the way we work and optimize our time. ⏳
Explore the notebook on GitHub and discover how to build your own AI-powered productivity tools! 🛠️
Are you struggling to manage your time effectively? Mohammad Al-Ansari, Google Developer Advocate, has recently shared how the Gemini API can act as a personal productivity coach. The solution uses a Google Colab notebook, which is connected to the Google Calendar API. The Gemini API is used to provide personalized insights and recommendations to boost your productivity and improve your work/life balance. For those who don’t know, Google Colab is a free cloud-based platform for running Jupyter Notebooks, which are interactive coding environments that allow users to write and execute code and can be a useful tool to have in the toolbox when exploring data.
Some of the key features in Mohammad’s ‘Productivity Coach’ are:
Function Calling: This notebook uses Gemini’s ‘function calling’ capabilities, allowing it to dynamically interact with the Google Calendar API and retrieve real-time data. This ensures that the analysis and recommendations are always up-to-date and relevant to your current schedule.
Google Workspace Integration: By integrating with the Google Calendar API, this notebook shows how the Gemini API can be seamlessly integrated with other Google Workspace services. This opens up exciting possibilities for quickly experimenting with other Workspace data sources, such as Google Docs, Sheets or Drive.
Personalized Coaching: As a bonus you can see how Gemini can act as your personal productivity coach, so if nothing else you can get some tailored guidance and support about your own calendar
If you have domain-specific knowledge that you want an LLM to leverage, you probably have a use case for fine-tuning. Fine-tuning can significantly improve how well the model understands and responds to your queries, whether it’s legal documents, medical texts, financial reports, or niche industry data.
The most crucial step in this process is structuring your data correctly. If your dataset is well-organized and formatted properly, the rest of the workflow becomes much more manageable. From there, it’s just a matter of setting up a few configurations and automating parts of the process with Apps Script. That’s where things get interesting and surprisingly efficient.
The GeminiApp library, designed to bring Google’s Gemini AI models into your Google Apps Script projects, has just received a major update. This new release, version 2025.01, introduces significant enhancements that expand the library’s capabilities for building sophisticated AI-powered applications within Google Workspace.
Here’s a quick look at what’s new:
Seamless Transition: The updated library allows for a smooth transition from Google AI Studio to Vertex AI.
JSON-Controlled Generation: Generate content in JSON format, either by providing a schema or allowing the model to infer it from your prompt.
Code Execution: Generate and execute code directly within prompts using gemini-2.0 models.
System Instructions: Guide the model’s behavior by providing system instructions during initialization.
Caching: Improve efficiency and reduce token usage by caching file uploads.
Easier Copy/Paste: The library now supports initialization using both new GeminiApp() and new GoogleGenerativeAI(), making it easier to copy code from Google AI Studio.
The update includes over 1.5K new lines of code, primarily in src/GeminiApp.js, src/GoogleAICacheManager.js, and tests/tests.js. Existing projects can be updated by replacing the existing GeminiApp.gs code with the updated library. This update provides a broader set of tools to create more personalized and efficient workflows.
For those new to the GeminiApp library, multiple setup options are available. Check out the updated examples and test cases in the README.md file to get started.
This update marks a step forward for developers integrating Gemini into Google Workspace. With new features and the ability to move between Google AI Studio and Vertex AI, the possibilities are vast.
Feel free to share your use cases, code improvements, and feature requests!
Recently, I worked on a project with a client who needed to parse XML files and convert them to JSON. Simple enough, right? The catch? These XML files were massive — each containing hundreds or even thousands of trades. Naturally, the first thing I thought of was Google Apps Script’s built-in XMLService. It’s reliable, widely used, and integrates smoothly into Apps Script. But when I ran it on a file with 1,000 trades, the parsing took 45 seconds! That’s painfully slow for an automation process.
[Editor: In a recent blog post, Dmitry Kostyuk has shared a valuable tip for developers working with Google Apps Script. When faced with the task of parsing large XML files, he discovered that the built-in XMLService was causing significant slowdowns. For files containing thousands of records, the parsing process could take an agonizing 45 seconds.
Dmitry’s solution? Using his Apps Script Engine (ASE) and the xml-js NPM library. By making this switch, he was able to achieve a remarkable 10x increase in parsing speed, reducing the 45-second process to only 4 seconds.
If you’re working with Google Apps Script and large XML files, click the source post link to find out how you can give ASE and xml-js a try!]
We’re all used to dealing with iterables such as Arrays, Maps and Sets. Creating a collection of input items, transforming then outputting is what we do all the time. However, sometimes that’s not possible – for example, where we have a potentially large input data set of unknown size that would be too big to fit in memory, or perhaps an output method with rate limits. Dealing with items 1 by 1, despite being by far the simplest to code, is often the least efficient to execute – so we use a paging method to get an input chunk at a time, and a bulk method to flush out the transformed results.
All of that can get a bit tricky, especially if you add asynchronicity into the mix.The use of iterators and generators can make coding as simple as 1 in -> 1 out, while handling chunking and bulking automagically. In this article, we’ll look at how to implement these.
In this blog post, Bruce Mcpherson explores the use of iterators and generators in JavaScript and how these can be used in Google Apps Script. A generator is a special type of function that can be paused and resumed at any point, yielding values one at a time. This is unlike regular functions that return all their results at once. Generators can be useful for handling large datasets efficiently by processing items in chunks and outputting results in bulk, even when dealing with asynchronous operations.
While Apps Script doesn’t natively support generators, Bruce has found a handy converter published by Facebook to make them compatible. As part of the post Bruce includes some Apps Script samples you can test and modify, demonstrating how to apply these techniques in real-world scenarios, even within the limitations of the platform. Bruce provides clear explanations of these concepts, illustrated with practical examples like finding breweries near specific locations and well worth spending the time to digest.
Welcome to the culmination of my Google Apps Script development journey — the Apps Script Engine. This isn’t just another template; it results from countless hours of refinement, driven by the passion to create the ultimate tool for Apps Script developers. Every ounce of my experience, every lesson learned, has been poured into building this robust, opinionated, yet highly configurable template. It’s designed to empower you to confidently and easily tackle even the most complex projects.
Developing Google Apps Script projects can be a pain, especially when dealing with modern JavaScript features like ES6 modules, the need for fast local development, and integrating NPM modules. The Apps Script Engine Template tackles these challenges head-on, offering:
Seamless ES6 Modules Integration: Finally, you can use this missing JavaScript feature with Apps Script.
Blazing Fast Local Development: Mock functions and promisified google.script.run calls make local development a breeze.
Front-End Framework Support: Includes Alpine.js and Tailwind CSS out of the box, with easy TypeScript integration.
NPM Module Support: Integrate NPM modules into front-end and back-end code effortlessly.
Automated Testing: Set up with Jest, so you can ensure your code works as expected.
CI/CD Integration: Easy integration with tools like GitHub Actions and Cloud Build ensures smooth, automated deployments.
Environment Management: Easily manage different environments (DEV, UAT, PROD) with specific configurations.
[Editor] This post from Dmitry Kostyuk introduces the Apps Script Engine, a template designed to streamline Google Apps Script development. It addresses common challenges by providing seamless integration of ES6 modules, fast local development, support for front-end frameworks and NPM modules, automated testing, CI/CD integration, and environment management.
The template simplifies the setup process, allowing developers to quickly create new projects. It offers a well-structured file system, including folders for compiled files, environment management tools, and source code.
Key features include the ability to use NPM modules in both client-side and server-side code, a custom Vite plugin for bundling, and Git hooks for automated formatting and testing. The template also facilitates environment management, making it easy to deploy code to different Google Apps Script projects.
For web apps, the template supports local development with a development server, and it provides a promisified version of google.script.run for cleaner code even allowing the easy mocking of server-side functions for realistic testing. The template also allows you to build scripts for different environments and even supports deploying libraries to NPM.
This is an incredibly impressive piece of work and Dmitry is encouraging contributions: “Your feedback, fresh ideas, and contributions are not just welcome — they’re what will make this tool even better. Let’s push the boundaries of what we can achieve with Google Apps Script together!”
Project IDX allows you to use all your favourite IDE extensions and themes, but also offers AI assistance through tools like Gemini Code Assist. It also has one-click integration to many Google products such as Firebase Hosting or deploying your app to Cloud Run. A really handy feature of IDX is the ability to share a workspace with a colleague so you can collaborate and develop together in real time! …
This guide will demonstrate how to develop your Apps script code from within Project IDX, and we will even touch on how to enable Google’s AI Code Assistant to get extra help when writing your App scripts.
The current Apps Script integrated development environment (IDE) has several limitations that can hinder collaboration and productivity. One significant limitation is the lack of real-time collaboration. Multiple users cannot simultaneously edit the same script, making it challenging for teams to work together on projects. This can lead to version control issues and conflicts, especially when multiple developers are making changes concurrently.
Project IDX is an experimental cloud-based IDE developed by Google. IDX is designed to make it easier to create, edit and deploy applications eliminating the tasks of configuring development environments for yourself and your team. For Google Workspace developers already doing local code development using VS Code the interface will be familiar as IDX is also built on Code OSS.
As well as being cloud-based IDX has some useful features built-in including GitHub import, extension and a Gemini code assistant. In addition to real-time collaboration (still highly experimental), IDX features a Gemini-powered code assistant with both a suggestion tool and a sidebar chat agent for code where you can discuss your code and get additional help.
This guide by Josh McMurdo demonstrates how you can set up IDX to develop Google Apps Script projects, including the installation of the clasp command line tool to push/pull code to Apps Script projects. As IDX is able to import existing GitHub repositories if you’ve not done local Apps Script development before I recommend Amit Agarwal’s Google Apps Script Starter kit, which can be cloned in IDX from https://github.com/labnol/apps-script-starter.
Once cloned IDX will automatically run the npm install to add all the dependencies. To switch to your own GitHub repository, create a new repo then in the terminal run the following command replacing YOUR_ACCOUNT and YOUR_REPO:
One other note on Josh’s post you don’t need the Add Gemini API step. IDX comes with Gemini code assist enabled, the Add Gemini API step is only required if you want to use Gemini in your Apps Script solution.
A reminder that Google states “Project IDX is in beta. Project IDX is likely to change and is not subject to any service level agreement (SLA) or deprecation policy. The implementation is subject to change without notice and in future releases. Use caution before proceeding to use IDX in a production environment.” IDX also can’t run Apps Script code so you’ll need to use clasp to push back into your Apps Script project and debug there.
