The AI formula in Google Sheets is a powerful and easy way to use Gemini AI directly inside of your Google Sheets. In this example, we use the AI formula to generate draft emails providing feedback on student exam scores. Then we use AI to write Apps Script that moves the draft emails from our Sheet into drafts in our Gmail, ready to send.
For many, the process of drafting personalised emails from a list of contacts in Google Sheets is a tedious, manual task. It involves switching between tabs, copying and pasting data, and carefully composing each message. But what if you could automate the entire workflow, generating perfectly tailored draft emails in Gmail with the click of a button?
In a recent video tutorial, Google Developer Expert Ben Collins demonstrates a powerful and efficient method to achieve exactly this. He showcases how to combine the intelligence of the new AI() formula in Google Sheets with the automation capabilities of Google Apps Script to turn structured data into ready-to-review email drafts.
What makes this solution so compelling is that it’s more than just a clever solution; it’s a glimpse into the future of work in Google Workspace. As Google continues to embed AI and end-to-end automations like Workspace Flows, the ability to effectively guide these systems becomes paramount. Ben’s solution provides a perfect, practical sandbox for honing a crucial new skill of prompt engineering and mastering the art of communicating with Large Language Models.
To see the full demonstration and get started on your own prompting journey, be sure to watch Ben’s complete video.
🤖 AI-powered Gmail assistant that automatically analyzes emails and drafts responses using Google’s Gemini AI
Google is rapidly integrating Gemini across the Workspace platform, bringing powerful AI features directly into the tools we use every day. While these native integrations offer incredible value, developer-led solutions provide a tantalising glimpse into the future of hyper-personalised automation, especially as we look toward the potential of the new Google Workspace Flows. This is where the real magic begins, allowing for custom agents that can handle complex, multi-step tasks.
Google Developer Advocate Zack Akil has created one such example solution that turns your Gmail inbox into a smart, automated assistant. His open-source project, the “AI-got-this-gmail-delegator,” is a Google Apps Script that uses the Gemini API to analyse incoming emails, categorise them, and even draft replies based on your own knowledge and writing style.
This project is a great example of how to combine the power of Google Workspace automation with the intelligence of modern AI. The script works by:
Analysing Incoming Emails: When a new email arrives, the script sends the content to the Gemini API for analysis.
Providing Context: The AI’s analysis is guided by a “knowledge base” that you provide in a Google Doc. This could include your role, common responses, or specific project details.
Intelligent Triage: Based on the email’s content and your knowledge base, the script decides whether the email needs a reply, requires manual intervention, or can be archived.
Drafting Replies: For emails that need a response, the script uses Gemini to generate a draft reply in your own writing style, ready for you to review and send.
The entire process is logged in a Google Sheet, giving you a complete overview of your AI assistant’s activity.
What makes this project so compelling is its practicality and customisability. By modifying the Google Doc knowledge base and the writing style prompts, you can create a personalised email assistant that understands the nuances of your work.
To explore the code and set up your own AI email delegator, check out the project on GitHub.
In this video we build a Chat app as a Google Workspace add-on with Apps Script and extend it to other Workspace applications (Calendar, Gmail, Drive, Docs, Sheets, and Slides).
To see this new framework in action, Pierrick Voulet from the Google Developer Relations team has released a fantastic video tutorial, ‘Build a Chat app as a Google Workspace add-on with Apps Script’.
The ‘OOO Assistant’: A Practical Demonstration
The video centres on building an ‘Out-of-Office Assistant’ that can block out the user’s Calendar for the day and set a Gmail out-of-office auto-reply. The real magic is how this Chat-based logic extends into a full Workspace Add-on, available in the sidebars of Gmail, Calendar, Drive, Docs, Sheets and Slides. This is achieved with minimal code changes, primarily by configuring the appsscript.json manifest to include:
A homepageTrigger to display a UI card in the add-on sidebar.
universalActions that reuse the exact same functions (blockDayOut(), setAutoReply()) originally built for the Chat quick commands.
The video provides a complete overview of the process, and you can get hands-on with the shared code yourself. For more technical details, check out the official Extend Google Chat documentation.
Nine years ago, I wrote an article describing how to scrape data from the internet in about five minutes. It featured a Google Apps Script library that allowed you to specify what to download from a webpage by identifying the text surrounding your target information. This became one of my most-read articles, and the library itself saved me a significant amount of time.
With the advent of large language models like Gemini, this entire paradigm is shifting. Just as “vibe coding” describes a more intuitive approach to programming, I’d say we’re now entering an era of “vibe-scraping.”
Using data from the web has long been a staple. But what if you could just describe the information you want and have an AI fetch it for you?
Google Developer Expert, Ivan Kutil, introduces a modern approach he calls “vibe scraping.” In his latest post, he demonstrates a powerful method that combines Google Apps Script with the Gemini API to pull structured data from any webpage directly into a Google Sheet.
A Practical Example: From Movie Links to a Full Database
To understand the power of this technique, Ivan set out to solve an old problem. He had a Google Sheet with a list of movies, each with a URL pointing to its page on ČSFD (a popular Czech-Slovak film database). His goal was to automatically populate columns with details like genre and rating by extracting the data directly from those linked pages.
At the heart of his solution is a new, experimental Gemini API feature called the URL Context tool. As the documentation notes, this tool is surprisingly flexible: it “can then retrieve content from the URLs and use that content to inform and shape its response.”
The tool can be used in two main ways:
URL Context “By Itself”: In this mode, the tool directly fetches and processes the content from the URL you provide. This is a straightforward way to analyze the live content of a single page.
URL Context with Google Search Grounding: In this mode, the tool can read content from a URL and then use Google Search for a more in-depth understanding.
What makes Ivan’s script so elegant is how it combines this tool with the simplicity of Google Sheets. For each row, the script constructs a prompt for the Gemini API, asking it to analyse the content of the provided URL and find the specific information defined in the spreadsheet headers.
The result is a system where modifying your data query is as easy as renaming a column. This turns a potentially complex scraping task into a simple spreadsheet edit, allowing anyone to create powerful, custom data importers.
To get the full details and the code to build this yourself, check out Ivan’s original post.
Last month, we introduced the ability to take basic actions to create and modify tables via the Google Sheets API. This week, we’re expanding the API functionality to include the ability to write and read file and people smart chips.
Following last month’s update for tables, it was recently announced that the Google Sheets API now allows developers interact with the file and people smart chips. This update provides developers with direct access to manage these types of smart chips, which were previously only available through the user interface. For detailed documentation and code snippets on implementation, refer to the official Google Developers support page.
Here is a quick overview of what you can do:
Reading Smart Chips: You can retrieve data for all smart chips within a cell. The API response will detail the properties for chipped sections of text and will also identify the plain text sections of the cell. As noted in the documentation to get the chip’s display text, you must request the formattedValue.
Adding Smart Chips: You can now insert smart chips for people and files into cells.
People Chips: These can be created to link to an individual using their email address. You can also specify the display format for the person’s name.
Rich Link Chips: These can be used to create a chip that links to a Google Drive file. Note that writing these chips requires your application to have at least one Drive OAuth scope.
In the feature image above you can hopefully see how a recruitment tracker could be created and read. In the example people chips are used for the ‘Applicant’ and ‘Interviewer’ columns, providing immediate access to contact information. A file chip is used in the ‘Resume’ column to link directly to the candidate’s document in Google Drive. A developer could automate this entire process. For instance, a script could be triggered when a new application is submitted, automatically adding a new row to the tracker complete with the relevant person and file chips, streamlining the entire recruitment workflow.
For those using Google Apps Script, these new functionalities are accessible via the Google Sheets Advanced Service. This update provides better methods for managing smart chips in Google Sheets.
In this guide, I’ll show you how to build a data-driven organizational chart based on Google Sheets data, using Apps Script and the OrgChart JavaScript library.
Recently, I used Gemini Deep Reacher to “find the most popular or useful Google Apps Script tutorials?”. There was no mention of AppsScriptPulse (rude, but if you are curious, here is the report :), but it did introduce me to the work of Joseph Petty on the GreenFlux Blog.
One tutorial, in particular, stood out for its practical application for many organisations: “Building A Data-Driven Organisational Chart In Apps Script”.
Creating diagrams, particularly organisational charts, often requires laborious design work and manual revisions. Petty’s tutorial directly addresses this common frustration by demonstrating how Google Sheets, Apps Script, and the OrgChart JavaScript library can produce dynamic, data-driven organisational charts that automatically stay current.
The Core Idea: From Spreadsheet Data to Visual Chart
The beauty of this approach lies in its simplicity and efficiency. Most organisations already maintain employee data, including supervisor information, in spreadsheets or databases. Petty’s method cleverly taps into this existing data source.
The tutorial guides you through several key steps:
Foundation Setup: Deploy a basic Apps Script web app, creating an index.html for the frontend and Code.gs for server-side logic (including a doGet() function).
OrgChart Library Integration: Introduce the open-source OrgChart JavaScript library, which uses a nested JSON structure to define the visual hierarchy.
Data Transformation: Write an Apps Script function (getEmployeeData) to pull data from Google Sheets (employee IDs, names, titles, supervisor IDs) and convert it into the required nested JSON format for the chart.
Dynamic Connection: Pass the generated JSON data from Code.gs to the OrgChart library in index.html using google.script.run.withSuccessHandler() to render the chart.
The Result? An Always Up-to-Date Org Chart
By following these steps, you can create an organisational chart that’s directly linked to your Google Sheet. When an employee changes roles, or a new team member joins, simply update the spreadsheet, and your web app will reflect these changes automatically. No more manual graphic updates!
For the complete code and detailed setup instructions, please follow the link to the original tutorial on GreenFlux Blog.
[Update 2025-05-16: A great tip from Pablo Felip Monferrer: “might be worth mentioning that the good old Sheets has a native organizational chart that can be easily published, too (hint: employee names in a third column can be displayed as tooltips)”.
Following the improvements made to tables in Google Sheets in March and April, we’re excited to introduce API support for tables. Now, users will be able to take basic actions to create and modify tables via the Sheets API.
Following enhancements to Google Sheets tables last year, Google recently highlighted an update for developers: the Sheets API now supports tables. This is great news for the Google Workspace developer community, as it allows for direct programmatic creation and modification of tables, moving beyond previous reliance on workarounds.
For a while, developers have found clever ways to interact with table-like structures, such as Kanshi Tanaike’s notable 2024 solution using a mix of Apps Script and Sheet formulas. While these methods were very clever, the new, direct API support offers a more robust and straightforward way for interacting with tables in Google Sheets. For Google Apps Script users, for now this will require using the Google Sheets Advanced Service to call these new table methods, as direct integration into the SpreadsheetApp service hasn’t been announced at this time.
Key API Capabilities for Tables:
The Sheets API now lets developers:
Add Tables: Create tables with defined names, ranges, and specific column properties (like ‘PERCENT‘ or ‘DROPDOWN‘ with validation).
Update Tables: Modify table size (add/remove rows/columns), and toggle table footers. The API also provides methods like InsertRangeRequest and DeleteRangeRequest for more granular control.
Append Values: Easily add new rows to the end of a table using AppendCellsRequest, which intelligently handles existing data and footers.
Delete Tables: Remove entire tables and their content (DeleteTableRequest) or just the formatting while retaining data (DeleteBandingRequest).
Utilise Column Types: Work with various column types including numeric, date, dropdown, smart chip, and checkbox.
I’ve created a small (pseudo) class that more easily clears and creates conditional formatting rules in a Google Sheet tab with Google Apps Script. Why? Well in Google Apps Script, conditional formatting rules are all or nothing. You can only ever set, get or clear ALL rules in a specified Google Sheet tab.
Conditional formatting in Google Sheets can be a powerful tool. However, Google Apps Script’s native setConditionalFormatRules() method replaces the entire set of rules, requiring the retrieval and reconstruction of the complete rule array to add, modify, or remove a single rule. This can be cumbersome and inefficient.
Fortunately Scott Donald has developed Range_ConditionalFormatting(), a valuable utility that simplifies conditional formatting management. This includes a SetRule method that allows you to add new rules without overwriting existing ones, and the position parameter provides precise control over rule order, which is crucial as conditional formatting rules are applied sequentially.
Scott’s post includes all the code you need to get this working in your own project along with a number of example snippets. This makes managing conditional formatting much more efficient and manageable.
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.
Archive Google Sheet Data with Google Sheets API Advanced Service in Google Apps Script
Keeping your Google Sheets organised can be a chore. A recent tutorial by Scott Donald shows you how to automatically archive old rows based on multi-column parameters using Google Apps Script and the Advanced Sheets API, making data management easier and more efficient. This post highlights Scott’s approach to scripting a solution, including how to set up the Sheets API, the main components of the script, and potential applications.
As always Scott packs in a lot of useful tips and guidance; in particular, the tutorial highlights how to archive data using just five API calls, potentially making it faster than the standard SpreadsheetApp approach. Scott also details how to modify the script to suit different processes and includes details on his SsReq class. It provides a structured way to perform common operations like retrieving data, finding rows based on criteria, copying rows, appending rows, and removing rows, making it a great addition to your personal Apps Script toolbox.
For detailed explanations, code examples, and helpful tips, consult the complete tutorial.
