Welcome to issue 28 of the Sheets Insiders membership.

This week, we’re looking at a super cool, cutting-edge demo using the new AI formula and Gemini.

We’re building a workflow that will a massive time saver if you’ve ever had to respond to feedback en masse.

Check it out below.

Automated Feedback Tool, with Education Data

In this scenario, we’re playing the role of a teacher who is sharing student feedback with parents.

We have a table of student data in our Sheet and we need to email the results to each parent:

Ordinarily, this is task that could take hours!

But today, I’m going to show you how we can leverage AI tools to dramatically speed up the process.

Video Tutorial

Template

Open the Automated Feedback Workflow Template

Click on “Use Template” in the top right corner to make your own copy.

Open Extensions > Apps Script to view the code.

Step 1: Use the AI Formula to draft emails

The AI Formula is the newest formula in Google Sheets. In fact, it’s still in early release mode for paying Workspace users (switch on the Alpha program to make it available).

(If you don’t have it, use the alternative approach detailed below instead.)

The AI formula consists of a prompt and an optional range to include any relevant data:

=AI(”prompt”, [optional range])

In our example, add a new column to the table with this AI formula:

=AI( "Write a professional email to the parent or guardian of this student, summarizing their academic performance and classroom behavior. The email should be written from me, Ben Collins. I am their teacher. Start each email with a subject line formatted exactly as: Subject: Name of Student’s Progress Report. For example: Subject: John Smith’s Progress Report. The tone should be warm, supportive, and informative—highlight both strengths and areas for improvement. Avoid overly technical language, and keep the email clear and easy to understand." , A2:F2)

The prompt includes specific instructions on how to write the draft email, including what the subject line should be and what the tone should be.

The range reference A2:F2 includes specific data about that student on that row.

The output is a draft email:

Alternative approach if you don’t have the AI Formula

If you don’t have access to the AI Formula yet, then we can create a regular formula to build a simple draft email.

All we’re doing is joining text strings together (concatenation) to build an email.

We use the CHAR formula to add line breaks.

For example, this formula generates the subject line and includes the first exam score:

="Subject: "&A2&"'s Progress Report"&CHAR(10)&CHAR(10)&Table2[[#HEADERS],[Midterm Score (%)]]&" "&B2

(Note the table name is Table2 in this formula.)

We can extend this formula to build a longer draft email. See the example in the template.

Step 2: Use Apps Script to move the draft emails into Gmail

So far, we’ve created a series of draft emails in Sheets. Undoubtedly, this has saved us time, but it would be much more effective if we automatically move these drafts into Gmail.

So let’s do that, with the help of Gemini and Apps Script.

Open Gemini in a new browser tab.

Select the new 2.5 Pro (preview) model.

Give Gemini a detailed prompt about the automation. Feel free to copy this one that I used:

I have a list of draft emails in column G of my Google Sheet, starting from row 2.

I want you to create an apps script that will take the content of column G and create a draft email in my Gmail, for rows where column H is blank. Within each cell of column G is a “subject” line which should be the subject line of the draft email. Remove the word “Subject:” from the subject line in the gmail draft. It’s only there as a label in the cell of the Sheet. The rest of the cell is the body text.

When the draft has been created in gmail, the script should put a timestamp in column H on that row to indicate that row has been processed. Don’t create draft emails for any rows where column H already has a timestamp.

Add a custom menu so it can be run from the Sheet.

Write the script to minimize the number of calls to services like SpreadsheetApp.

Make the code concise with only high level comments.

Back in our Sheet, open the Apps Script editor, under the Extensions menu.

Delete the boilerplate code in the window and paste in the code that Gemini generated:

Select the onOpen function and press run. We need to grant permissions the first time we run the script.

Back in our Sheet, we now have a new custom menu called something like “Email Drafts”:

Click on it to run the script.

Once the script finishes, we get a notification popup like this:

And in our Gmail drafts folder, we should see a bunch of new draft emails:

where each draft email is a great starting point:

We could quickly modify them if needed before we send them.

Talk about saving time! ⏱️

Two Important Notes

One

Don’t blindly trust the AI generated draft emails. They should be sense checked before sending to ensure they don’t contain errors.

Two

The script I generated also converted the AI formulas to plain text values.

This is probably a useful step, but it was not explicitly part of our program directive.

So, if we want to maintain live AI formulas, we would want to clarify that in our Gemini prompt.