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.
Today I had the task of implementing a newsletter for internal updates and decided to use Google Apps Script since I was already using it for managing the project data. Based upon past experiences with emails using HTML I need this area was full of landmines and I didn’t want to navigate through it. 💣
So I decided to use MJML with Google Apps Script.
Sending emails using Google Apps Script is a common task, however, creating HTML emails that render consistently across different email clients and that look visually appealing can be challenging.
Justin Poehnelt’s post on DEV Community, “Using MJML in Google Apps Script to Send Beautiful Emails,” offers a solution to this problem. MJML is a markup language that makes it easy to create responsive HTML emails. It removes some of the complexities of HTML email development, allowing users to focus on the content of their emails.
In the post, Justin explains how to use MJML with Google Apps Script to send beautiful, responsive emails. He also mentions that he has published a library, MJMLApp, that “hides the gnarly bits” of using MJML with Google Apps Script. This library is available on GitHub in the mjml-apps-script repo where you can find the library ID and basic usage information.
Are you overwhelmed by a cluttered inbox? If you’re a power Gmail user, you may already know about the plus addressing feature that allows you to create custom email variations by appending a + and a label to your email address. For example, username+shopping@gmail.
Are you tired of sifting through a mountain of emails every day? Gmail’s “plus addressing” feature can be a lifesaver, and with a little Google Apps Script magic, you can transform it into a powerful automation solution.
If you are not familiar ‘plus addressing’ is a feature of Gmail which allows you to create custom variations of your email address by adding a “+” symbol and a label after your username, but before the “@” symbol. For instance, if your email is [email protected], you can use [email protected] for newsletters. While these emails still land in your main inbox, this subtle tweak opens the door to some clever automations using Google Apps Script.
This blog post, written by Senior Product Designer Niyas V, provides a step-by-step guide on using Google Apps Script to automatically label and archive emails based on the “plus addressing” variations. The script scans incoming emails, extracts the label after the “+”, applies the corresponding Gmail label, and then neatly archives the thread. The post includes the complete script, instructions on setting up a time-driven trigger for automation, and clear steps to save, authorise, and test your creation.
This tutorial is a fantastic resource for developers looking to learn practical Gmail automation using Google Apps Script and a great starting point if you are thinking about building other solutions using your inbox.
As a Developer Advocate for Google Workspace, I live and breathe productivity tools. But even the most tech-savvy among us can have frustratingly simple oversights. My recurring pain point? Forgetting to turn on my Gmail vacation responder to automatically notify people of my absence before heading out of office.
Google Workspace Developer Advocate, Chanel Greco, has shared a clever solution to automate their Gmail out-of-office notification using Google Apps Script. Faced with the recurring problem of forgetting to set up the out-of-office notification, Chanel crafted a script that does the job based on Google Calendar events marked as ‘outOfOffice’.
Chanel’s project not only solves a personal problem but also demonstrates the benefits of Google Apps Script for task customization and productivity. By harnessing the power of Workspace APIs, you can create innovative solutions to automate routine tasks and streamline workflows.
Using Google Gemini API with Google Sheets to create personalized mail merges. An exploration in generative AI ‘function calling’
Google latest generative AI solution, Gemini, includes the capability to declare functions that the LLM can use in it’s response. The response includes the name of the function and the parameters the script needs to run the function.
The function isn’t executed by the LLM, but run with your code, which creates really interesting opportunities for Google Apps Script solutions. In particular, given user identity and authorisation is an integral part of Apps Script and how it integrates with other Google services it means solutions like personalised mail merges can be created in a couple of lines of code.
Follow the source link to find out more about function calling and exploring Gemini’s capabilities with data in Google Sheets.
Creating dynamic charts in the sidebar of your Google Workspace Add-on can be an effective approach to getting across a lot of meaning in a somewhat confined space. In this tutorial, we will use Google’s Chart API to generate a live chart, first from some static data and then live from an external data source like a Google Sheet. All with the help of a little Google Apps Script magic.
Here’s another great tutorial from Scott Donald which provides a detailed tutorial for including charts in a Google Workspace Add-on using the Card Service. The tutorial covers how you can embed dynamic chart data from a Google Sheet. Whilst targeted at Google Workspace Add-ons (Gmail and Drive) you should be able to easily modify this for cards used in Google Chat. The source tutorial includes a video giving an overview of the solution and if you want to take this project further you can signup for Scott’s Create and Publish Google Workspace Add-ons with Google Apps Script: Master Class.
Use Google Apps Script to automate email-based expense tracking. Store and track your receipts entirely through Gmail, Drive and Sheets
Here’s a nice tutorial on how to create an email-based expense tracking system using Google Apps Script. The solution allows users to submit expense reports via email, which are then automatically processed and stored in a Google Sheet with attachments stored in Google Drive.
The blog post by Joshua Mustill provides detailed instructions on how to set up the system, including how to create the Gmail labels and filters, the Google Sheet and the Apps Script code. There are some nice features in the code you might want to use in other projects including the creation of date based Google Drive folders for storing Gmail attachments.
Gmail will not render base64 images embedded inside HTML emails but with Google Apps Script, you can use blobs to send the base64 encoded images
Base64 is a way of encoding binary data (like images) as a string of text. This makes it possible to embed images in webpages and HTML emails without hosting images on a server. An issue however, particularly for email, is that not all email services like Gmail support base64 encoded images.
Amit Agarwal’s latest blog post discusses a workaround for Gmail which involves using Google Apps Script to convert the base64 images to a blob and then embedding the blob in the email. There are some nice techniques in this post for handling/searching text strings with regular expressions as well as converting them to a Gmail friendly format.
