Hi Reader,

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

You'll also find the new Sheets + AI Tip section below (tip #2 this week).

A big thank you to this week's sponsors who help keep this newsletter free for you to read:

SHEET GURUS Send Texts Directly from Google Sheets with Sheet Gurus Sheet Gurus is the simplest and easiest-to-use bulk texting solution built specifically for Google Sheets. Send thousands of personalized messages to your customers in seconds. Enable Sheet Gurus today!

SHEETGO Expanding spreadsheet automation technology: Sheetgo acquired Layer What happens when two spreadsheet automation products merge? A powerful platform emerges for users like you. Start creating automated spreadsheet connections with Sheetgo to save time and focus on what really matters. Start now for free

➜ News

I.
Google Cloud Next '23 Conference is happening next week in San Francisco. Although in-person tickets are sold out, you can still watch the sessions online with a Digital Access Pass. I'm not attending live, so I'm planning to watch the Workspace sessions online. I'll report any significant announcements in this newsletter.

​Register here​

II.
Speaking of Google Cloud Next, my friend and fellow GDE Martin Hawksey has put together a handy playlist of the Workspace-themed sessions happening at the conference.

​Find it here​

III.
Version History is coming to Apps Script! 🤩

Soon you'll be able to view previously deployed script versions and compare them to the current version. This will be available to all Workspace and personal account users and it's rolling out over the coming weeks. Woohoo!

​Read more here​

_______

➜ Google Sheets Tip #269: A Secret Use Case For Named Functions

Today's email took me longer to write than any previous newsletter.

The idea evolved substantially as I wrote the email, proving that the best way to understand something is to teach it to someone else.

Today we look at a special use case for named functions.

Bear with me, this tip is a little more involved but showcases a unique use case for Named Functions and IMPORTRANGE.

I'm curious to hear if you think it's useful or not.

The Scenario

Suppose you want to include a standardized piece of text on the top of your Sheets, e.g. a legal disclaimer or perhaps your company contact details. Sort of like an email signature, but for your Sheet.

There's a clever trick you can do with Named Functions and IMPORTRANGE to automate this across all your Sheets.

Best of all, if the information in the parent sheet is updated, these changes will flow through to any child sheet.

In this GIF, when we update the address in the parent sheet, the change automatically flows through to the child sheet:

Of course, you could skip this named function and just add the plain IMPORTRANGE function, but this requires you to write out that function and know the range reference.

I think this named function method simplifies that, especially for non-technical users.

How To Setup This Up

Start in the Parent Sheet, i.e. the Sheet that will be the source of truth for your information.

Create a new named function: Data > Named functions

Then select: Add new function at the bottom of the sidebar.

Call the new Named Function COMPANY_DETAILS and define it as follows:

The URL inside the IMPORTRANGE should be the URL of the Parent Sheet you're in, i.e. the Sheet where you're defining the function.

The range reference (Sheet1!B3:B5 in my case) should be the range containing your company information (e.g. contact details).

Click Next

On the following page, click Create

Back in your Sheet, you can now insert this snippet of text anywhere you like using this function:

This is cool but not particularly useful yet.

Using This Named Function In Other Sheets

It becomes super useful when you import this Named Function into other Sheets.

Then, if you change the text in the parent Sheet, it will propagate through to the child Sheet, because of the IMPORTRANGE function inside the Named Function.

This is a great boon for ensuring consistency between your Sheets. You only ever need to change the data in the parent Sheet.

Steps To Setup

In the child sheet where you want to add the information (e.g. a client report):

1. Go to Data > Named Functions
2. Click Import Function
3. Find the Parent Sheet file from the file picker, select it, and click Insert
4. Select the COMPANY_DETAILS function and click Import
5. Use this named function like any other function in your Sheet
6. Note: You will need to click Allow Access the first time you use it

As always, I welcome your comments. 😀

(I read every email and do my best to reply to them all, although it sometimes takes me a while depending on the week and childcare situation!)

_______

➜ Sheets + AI Tip #2

In this new section of the newsletter, I'll be sharing a quick, practical tip to help you leverage the power of AI tools with Sheets.

Last week (tip 268), we talked about using AI tools to explain formulas.

Well, these AI tools can also create formulas for you too.

It's important to be as specific as possible with your prompt.

For example, instead of “Create a formula that finds the average value of my range” say this “Create a Google Sheets formula to calculate the average value of data in the range D5:D20”.

They work very well for simple formulas.

For more complex formulas, think of the AI tool as an assistant that can generate ideas. The tools rarely get a complex formula correct the first time. But they can be super helpful in giving you ideas.

📌 In my experience so far, I've had better results from ChatGPT than Google Bard.

📌 Also, be aware that the tools can confidently suggest answers that are wrong. Make sure you test any suggestions carefully!

_______

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

Have a great week!

Cheers,
Ben

P.S. Race to a location​