AppsScriptPulse

Automating YouTube trend analysis at scale with Google Apps Script, Vertex AI and Terraform

Understanding and spotting trends in organic YouTube traffic based on specific queries can often be a manual and time-consuming process. To solve this, the Google Marketing Solutions team has published an open-source repository for a tool called YouTube Topic Insights. Developed by Google Customer Solutions Engineer Francesco Landolina, the project transforms raw video data into actionable summaries by combining the data-gathering capabilities of the YouTube Data API with the video and language understanding of Gemini models.

For Google Apps Script developers interested in building scalable tools, this repository is a fantastic example of using Vertex AI at scale. It orchestrates complex, long-running processes directly from a Google Sheet and pairs them with Vertex AI batch prediction. In this post we will highlight some interesting infrastructure setup and the pattern used to bypass common Apps Script execution limits.

Project architecture and setup

The solution is divided into two main components that make it easier to setup and process the data:

  1. GCP Infrastructure as Code: The necessary cloud environment is defined using Terraform infrastructure as code. This makes the deployment easier by enabling required APIs like Vertex AI and the YouTube Data API. While the Terraform script attempts to set up the OAuth Consent Screen using the google_iap_brand resource, you will need to configure the OAuth Consent Screen manually within the Google Cloud Console as the official Terraform documentation notes that the underlying IAP OAuth Admin APIs were permanently shut down on March 19, 2026.
  2. Google Apps Script: The brain of the operation lives within a Google Sheet. It calls the YouTube API to find videos, prepares and submits analysis jobs to Vertex AI batch prediction, fetches the completed results, and writes the human-readable insights back into the spreadsheet interface.

For developers setting this up, the repository includes a Quick Start template. The script automatically handles the initialisation of the spreadsheet structure when first opened, creating the necessary configuration and logging sheets in a safe, non-destructive way.

Overcoming the execution limit with Vertex AI batch prediction

As you can imagine, analysing hundreds of videos using a LLM is going to be a compute-heavy task. If you attempt to process these synchronously in a simple loop, you will quickly hit the execution limit for Google Apps Script.

To get around this, the solution uses Vertex AI batch prediction (see Batch inference with Gemini). Instead of waiting for Gemini to process each video, the script initiates an asynchronous batch job and uses a chained-trigger system to poll for completion. Once the batch job completes, the script retrieves this context, parses the JSON response from Gemini, and writes the summarised data back to the Google Sheet.

Summary

The YouTube Topic Insights solution is an excellent example of how to build complex applications using Vertex AI Batch Prediction. It provides practical solutions for managing asynchronous tasks, interacting with advanced Vertex AI features.

If you are interested in exploring the code further or setting up the tool for your own research, you can find the complete source code and deployment instructions in the YouTube Topic Insights GitHub repository.

Source: YouTube Topic Insights: Automated YouTube Trend Analysis with Gemini AI

Vertex AI Advanced Service in Apps Script: A Step Forward or a Missed Opportunity?

As Google Apps Script developers, we are used to waiting. We wait for new runtime features, we wait for quotas to reset, and recently, we have been waiting for a first-class way to integrate Gemini into our projects.

With the recent release of the Vertex AI Advanced Service, the wait is technically over. But as detailed in Justin Poehnelt’s recent post, Using Gemini in Apps Script, you might find yourself asking if this was the solution we were actually looking for.

While the new service undoubtedly reduces the boilerplate code required to call Google’s AI models, it brings its own set of frustrations that leave me, and others in the community, feeling somewhat underwhelmed.

The “Wrapper” Trap

On the surface, the new VertexAI service looks like a win. As Justin highlights, replacing complex UrlFetchApp headers with a single VertexAI.Endpoints.generateContent() call is a significant cleanup.

However, this convenience comes with an administrative price tag. The Vertex AI Advanced Service requires a standard Google Cloud Project, understandable for billing, but requires the creation of an oAuth consent screen. For the majority of internal enterprise applications, I would imagine either a service account or a https://www.googleapis.com/auth/cloud-platform scope and associated IAM will be the preferred approach. This removes the need for a consent screen and, in the case of Service Accounts, rules out the Vertex AI Advanced Service.

It begs the question: Why didn’t Google take the approach of the Google Gen AI SDK?

In the Node.js and JavaScript world, the new Google Gen AI SDK offers a unified interface. You can start with a simple API key (using Google AI Studio) for prototyping, and switch to Vertex AI (via OAuth) for production, all without changing your core code logic. The Apps Script service, by contrast, locks us strictly into the “Enterprise” Vertex path. We seem to have traded boilerplate code for boilerplate configuration.

A Third Way: The Community Approach

If you are looking for that Unified SDK experience I mentioned earlier, where you can use the standard Google AI Studio code patterns within Apps Script, there is a third way.

I have published a library, GeminiApp, which wraps UrlFetchApp but mimics the official Google Gen AI SDK for Node.js. This allows you to write code that looks and feels like the modern JavaScript SDK, handling the complex UrlFetchApp configuration under the hood.

As you can see in the comparison above, the Advanced Service (left) abstracts away the request complexity, the UrlFetchApp method (middle) gives you the transparency and control you often need in production, and the GeminiApp library (right) offers a balance of both.

Disclaimer: As the creator of this library, I admit some bias, but it was built specifically to address the gap.

It is important to note a distinction in scope. Both the Google Gen AI SDK and GeminiApp are focused strictly on generative AI features. The Vertex AI Advanced Service, much like the platform it wraps, offers a broader range of methods beyond just content generation.

If your needs extend into those wider Vertex AI capabilities, but you still require the authentication flexibility of UrlFetchApp (such as using Service Accounts), I have a solution for that as well. My Google API Client Library Generator for Apps Script includes a build for the full Vertex AI (AI Platform) API. This gives you the comprehensive coverage of the Advanced Service with the architectural flexibility of an open-source library.

Here is how you can use the generated client library to authenticate with a Service Account, something impossible with the official Advanced Service:

/**
 * Example using the generated Aiplatform library with a Service Account.
 * Library: https://github.com/mhawksey/Google-API-Client-Library-Generator-for-Apps-Script/tree/main/build/Aiplatform
 */
function callGemini(prompt) {
  const projectId = 'GOOGLE_CLOUD_PROJECT_ID';
  const region = 'us-central1';
  const modelName = 'gemini-2.5-flash';

  const modelResourceName = `projects/${projectId}/locations/${region}/publishers/google/models/${modelName}`;

  const serviceAccountToken = getServiceAccountToken_(); 

  const vertexai = new Aiplatform({
    token: serviceAccountToken
  });

  const payload = {
    contents: [{
      role: 'user',
      parts: [{
        text: prompt
      }]
    }],
    generationConfig: {
      temperature: 0.1,
      maxOutputTokens: 2048
    }
  };

  const result = vertexai.projects.locations.publishers.models.generateContent({
    model: modelResourceName,
    requestBody: payload
  });

  return result.data.candidates?.[0]?.content?.parts?.[0]?.text || 'No response generated.';
}

When “Advanced” Means “Behind”

There is another catch that Justin uncovered during his testing: the service struggles with the bleeding edge.

If you are trying to access the latest “Preview” models to prototype, such as the highly anticipated gemini-3-pro-preview, the advanced service may fail you. It appears the wrapper doesn’t yet support the auto-discovery needed for these newer endpoints.

In his companion post, UrlFetchApp: The Unofficial Documentation, Justin reminds us why UrlFetchApp is still the backbone of Apps Script development. When the “official” wrapper doesn’t support a specific header or a beta model, UrlFetchApp is the only way to bypass the limitations.

The Verdict

The Vertex AI service is a welcome addition for stable, enterprise-focused applications. But for developers, particularly those who want to test the latest Gemini 3 capabilities, it feels rigid compared to the flexibility seen in other Google developer ecosystems.

It serves as a good reminder that in Apps Script, convenience services are great, but understanding the underlying HTTP requests via UrlFetchApp  extends what you can achieve.

Bring Your AI Agents to Life Across Google Workspace with the New Travel Concierge Sample

This tutorial shows you how to publish AI agents to Google Workspace as Google Workspace add-ons, using Apps Script or HTTP endpoints. After your publish your add-on, your users can interact with the AI agents within their workflows.

Google has released a comprehensive new tutorial that demonstrates how to bridge the gap between complex AI agents and the daily workflows of Google Workspace users. The Travel Concierge sample uses the Agent Development Kit (ADK) to deploy a conversational, multi-agent AI directly into the Google Workspace platform.

While many AI demos focus on standalone chat interfaces, this solution stands out by implementing the agent as a Google Workspace Add-on. This means the AI isn’t just a browser tab; it is a helpful sidebar accessible directly within Gmail, Calendar, Drive, Docs, Sheets, and Slides, as well as a fully functioning Google Chat app.

Key Features for Developers

The sample provides a robust blueprint for developers looking to build “agentic” workflows. Key capabilities include:

  • Multi-Agent Architecture: The solution uses Vertex AI to manage a “Travel Concierge” that orchestrates sub-agents for specific tasks, utilising tools like the Google Maps Platform Places API and Google Search Grounding.
  • Context Awareness: In applications like Gmail, the agent can read the context of the currently selected email to help plan trips based on actual correspondence.
  • Rich User Interfaces: The add-on moves beyond simple text, utilising the Card framework to display rich widgets and interactive elements.
  • Cross-Platform Persistence: User sessions are managed by Vertex AI, ensuring that a conversation started in Google Chat can be seamlessly continued in a Google Doc sidebar.

Flexible Implementation

The Apps Script implementation demonstrates how to connect a standard Workspace Add-on to the powerful Vertex AI backend using UrlFetchApp. It serves as an excellent reference for handling synchronous execution limits and rendering complex agent responses within the constraints of the Add-on sidebar.

Whether you are looking to build a travel planner or a complex enterprise assistant, this sample provides the architectural patterns needed to bring your AI agents to where your users actually work.

Source: Plan travels with an AI agent accessible across Google Workspace  |  Google Workspace add-ons  |  Google for Developers

Building Agentic Workflows: A 3-Part Series from Aryan Irani on ADK, Vertex AI, and Google Apps Script Tags: ADK, Vertex AI, Apps Script, Gemini, AI, Agents

 

Thanks to Google’s Agent Development Kit (ADK) and Vertex AI’s Agent Engine, that’s no longer just an idea. These frameworks let you build intelligent agents, deploy them at scale, and integrate them seamlessly into your Google Workspace tools - enabling a new era of agentic productivity.

In this three-part tutorial series, we’ll explore exactly how to do that.

The ability to build and integrate custom AI agents directly into Google Workspace is rapidly moving from a far-off idea to a practical reality. For developers looking for a clear, end-to-end example, Google Developer Expert Aryan Irani has published an excellent three-part tutorial series.

This comprehensive guide, which includes video tutorials for each part, offers a complete end-to-end journey for building agentic workflows. It starts by building a local ‘AI Auditor’ agent with the new Agent Development Kit (ADK)—programming it to verify claims with the Google Search tool. The series then guides developers through the entire cloud deployment process to Vertex AI Agent Engine, covering project setup and permissions. Finally, it ties everything together with Google Apps Script, providing the complete code (using UrlFetchApp and the OAuth2 for Apps Script library) to integrate the agent as a powerful, fact-checking tool inside Google Docs.

Why This Series Matters

This series is a fantastic, practical guide for any Google Workspace developer looking to bridge the gap between classic Apps Script solutions and Google’s powerful new generative AI tools. It provides a complete, end-to-end blueprint for building truly ‘agentic’ workflows inside the tools your organisation already uses.

A big thank you to Aryan Irani for creating and sharing this detailed resource with the developer community!

Sources:

Go Beyond the Gemini App: Building Custom AI Tools with Gemini CLI and Apps Script

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:

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

More than meets the AI: How AppSheet and the Gemini API can transform businesses

Image credit: Google

I recently explored the transformative power of Generative AI (GenAI) and how it’s reshaping the business landscape in a thought leadership piece for my employer, Appsbroker | CTS. Drawing from my experience in Gemini for Workspace pilots and custom GenAI solutions using the Gemini API in Vertex AI, I highlighted how AppSheet, a no/low-code platform, can be a game-changer.

AppSheet simplifies the integration of GenAI capabilities, enabling rapid prototyping and tailored solutions that deliver real-world results, whilst still benefiting from integrations into Google Workspace. I believe it’s a cost-effective and impactful way to harness GenAI’s potential without blowing the budget. In the article, I share some examples and the benefits of using AppSheet for GenAI innovation.

Curious to learn more? Join me at the Google Workspace Developer Summit in Berlin on September 17th, where I’ll be discussing AppSheet integrations, Gemini Function Calling, and more.

Source: More than meets the AI: why planning ahead is vital to reap the rewards of GenAI

Using Google Forms and the Gemini API to automate creation of multiple choice questions (MCQs)

This report proposes a novel learning method using Gemini to automate Q&A generation, addressing the challenges of manual Q&A creation. By integrating with Google tools, this approach aims to enhance learning efficiency, accessibility, and personalization while reducing costs.

The rapid advancement of technology has offered both opportunities and challenges to the education sector. While technology can be a valuable tool for supporting teaching and learning, concerns about its appropriate use have existed for a long time.

The education sector is witnessing an increase in AI tools, each promising to enhance teaching and learning. However, the quality of these tools varies significantly. Some are better designed and can – if used appropriately – can be beneficial, while others are poorly designed. This can make it challenging for educators to find the right solutions that meet their specific needs.

This blog post by Kanshi Tanaike sheds light on the inner workings of some commercial AI tools developed for educators. It demonstrates how these tools can use generative AI to create multiple-choice questions (MCQs) on a given topic. In this particular solution, Google Forms are used in the process, with questions and answers automatically generated by the Gemini API for the user to answer in a Google Form.

For educators who have experience with Google Apps Script, this project looks like a useful starting point to refine and create your own solution or simply used to gain insights into the functioning of similar commercial tools.

Source: A Novel Approach to Learning: Combining Gemini with Google Apps Script for Automated Q&A

Smart replacing images in Google Slides with Gemini Pro API and Vertex AI

Image credit: Ivan Kutil

Surely, you have also experienced having a presentation in which you needed to replace old content with new. Replacing text is very simple because you just need to use the Replace function, and you can either do it in the Google Slides user interface.

The problem arises when you need to replace one image with another, for example, if your corporate logo is updated to a new graphic design or if one of your favorite cloud services updates its icons (Gmail, blink blink ;-) It’s still somewhat bearable with one presentation, but what do you do when, like me, you have thousands of Google Slides files on your Google Drive?

This post explores a clever application of the Gemini API’s multimodal capabilities, created by Ivan Kutil. His code utilizes GenAI to automatically detect outdated logos within your Google Slides presentations. The original blog post (March 2024) used the Gemini Pro Vision API. As a sign of how rapidly this area evolves, Google now recommends switching to Gemini 1.5 Flash or Gemini 1.5 Pro.

Switching to the newer APIs is very straightforward as all you need to do is search the source code for models/gemini-pro-vision and replace with models/gemini-1.5-flash or models/gemini-1.5-pro.

For Apps Script projects I lean towards Gemini 1.5 Flash as it is designed for speed. I’m also always looking to opportunities to test the GeminiApp library for Apps Script and very quickly I was able to fork Ivan’s code and use Gemini 1.5 Flash using a service account.

While GeminiApp requires some initial setup, it offers significant advantages:

  • Easy Model Testing: Experiment with various models quickly.
  • Built-in Features: Includes functionalities like exponential backoff.

If you would like to explore here is a sample slide deck you can copy which has the container bound forked code (if you don’t want to use a service account here is more information on other setup options).

Source: Smart replacing images in Google Slides with Gemini Pro API and Vertex AI

Answer questions based on Google Chat conversations with a Gemini API powered Chat app

This tutorial shows how to make a Google Chat app that answers questions based on conversations in Chat spaces with generative AI powered by Vertex AI with Gemini. The Chat app uses the Google Workspace Events API plus Pub/Sub to recognize and answer questions posted in Chat spaces in real time, even when it isn’t mentioned.

Here is another great tutorial from the Google Chat DevRel team, this time showcasing how the Google Workspace Events API and some new Google Chat UI elements can be used to turn a Chat space into a Gemini Pro powered knowledge base.

The sample solution will let you consume your Chat space message history into a Firestore database. The Chat app is an intelligent agent that can then monitor for new questions and make suggestions using Gemini to generate content based on the previous messages.

There is quite a bit of setup required as part of this tutorial, but it provides a solid foundation for quickly scaling this to your needs.  A demonstration and explanation of the sample app was given as part of the Google Chat apps and APIs: Build connected workflows for the hybrid workplace session at Google Cloud Next and a recording might be available soon!

Source: Answer questions based on Chat conversations with a Gemini AI Chat app  |  Google Chat  |  Google for Developers