Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #379, your Monday morning espresso, in spreadsheet form!

Anyone else feeling a little overwhelmed with everything AI these days?

Every day, panicked people on Twitter are prophesying the end of white-collar jobs. And Block recently announced they are reducing their headcount by 40% (4,000 people) because of AI, so maybe they have a point.

But on a visit to a new dentist last month, I still had to fill out a paper form and then watch as someone typed that into a computer. A good reminder that adoption always lags way behind the technological innovation.

Zooming out further though, it's hard to know what the world will look like in 10 years' time, when super intelligence might be as free and abundant as Google Search is today. By then, anything that we do on the computer will be done by AI agents. What sort of careers will my children have then? Will I still be writing Google Sheet tips?!? 😂

Okay, that's enough of crystal ball gazing. Let's put our feet back on the ground.

➜ News

I.
​Two new functions coming soon to Google Sheets:

• SHEET() to get the index number of a specified sheet name or reference.
• SHEETS() to count total number of sheets in the Google Sheet file.

Interesting!

I got access to these functions a couple of days ago and used SHEET to build a dynamic, formula-driven table of contents.

I'll share more practical use cases in the coming weeks. I'm interested to see what everyone builds with these.

​Read more here >>

II.
​Gemini conversation history is coming to the side panel in Google Workspace. This is a welcome update.

​Read more here >>​

III.
​Google Chat now available as a data source in Gemini app. Gemini will be able to cross-reference your chats if you choose, to provide more context-aware responses.

​Read more here >>​

IV.​
Speak of Gemini, I'm working on a new course for Gemini tools.

It's designed to catch you up on Gemini AI and move you from simply chatting in the Gemini app to building useful AI systems, within Gemini and Google Workspace.

I recorded the first video at the end of last week, which is always an exciting milestone in course projects. I'm working hard to get this ready by early April.

Brought to you by: Time Reports Pro Connecting Your Google Calendar to Google Sheets for Seamless Reporting Time Reports Pro eliminates the "app-switching shuffle" by letting you add custom fields like Client, Rate, or Project ID directly to your calendar events. In just one click, you can transform a month of messy events into professional, auto-generated invoices or timesheets. It is the ultimate tool for anyone looking to automate their workflow and capture better data without ever leaving their schedule. Start Your 7-Day Free Trial and Reclaim Your Time!

➜ Sheets Tip #379: Iterate your way to a solution by sending follow-up prompts to Gemini

In last week's newsletter, we looked at how to use Gemini to generate a formula to extract a phone number from text.

It used the difficult to understand REGEX formula, and Gemini was super helpful because the REGEX formulas are notoriously difficult to write yourself because of the challenging syntax.

Several readers replied that often your data is not as neat as the example data I used. And they wondered how Gemini would cope with more complex data extraction and how to prompt it.

So in this newsletter, let's find out!

Last week we started with data that looked like this and Gemini generated a simple REGEX solution:

This week, let's assume we have data that's messy, with lots of variation in the telephone number formats:

Some numbers have periods, some with parentheses, some with nothing, some with commas, etc. It's a challenging problem to extract them.

Let's prompt Gemini with these variations and see how it gets on.

Working in the same chat window that I used for last week's formula, I just continue the conversation with a new prompt:

After a minute or so, Gemini offered the following REGEX solution, which did extract all of these different variations:

However, the extracted telephone numbers were still all formatted differently. Ideally, we want them in the same format.

Let's prompt Gemini to extend the formula:

After another minute or so, Gemini produced another formula, this time pretty complex and not something that is easily created manually:

Testing it out and it worked perfectly first time with all of the variations of data:

If I had thousands of rows of data like this, using Gemini to build a formula would have saved me a huge amount of time.

It would have taken forever to do it manually and perhaps an hour or two to create a universal REGEX formula myself.

Skill Takeaway

You'll notice that the prompt from last week and the two prompts from this week are very specific and include:

• mention of working in a Google Sheet
• what cell the input data is in
• what problem I'm solving
• some examples of input data
• what I want the output to look like

When you hit all of these points with your prompts, Gemini has a much higher chance of generating a formula that will solve the problem for you.

Have fun building your formulas!

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. An antidote to the AI-infused panic: the reversible cup!​