Brought to you by:

Hi Reader,

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

Recently a reader asked about a problem importing data from one Sheet to another when the sheet names were changing. Below, we look at how to solve this and see yet another benefit of the new Google Tables feature.

My family and I spent the weekend at Fairmont University in West Virginia, where my eldest son was competing in a Lego robotics competition. With his teammates, they built an autonomous robot that navigated a table-top world to retrieve items, knock things over, lift things, etc. Teams were scored on how many missions they completed.

They use a system called Lego Spike and code it with the Scratch language (block programming).

Super proud of them for finishing 6th out of 51 teams from across the State.

Brought to you by: Set & Forget Imagine the seamless Google Workspace of your dreams... We'll build it for you! If you're paying for tools that don't meet your needs, or you're lost among all the Google Workspace options, it's time for a change. We specialize in custom Google Workspace development that's designed specifically for your business. Say goodbye to cookie-cutter tools and hello to solutions that streamline your operations and workflow. Why settle for inadequate tools when our developers can muscle your Google Sheets into peak performance? Custom solutions that work as hard as they do, no sweat required! Book a free call to discuss your needs

➜ News

I.
Policy visualization is now generally available in Google Docs, Sheets, Slides, and Drive. This update concerns users working with policy-protected content, such as data loss prevention or trust rules.

​Read more here >>​

II.
​Google announced the ability to add granular response options to Google Forms, so you can control who is able to respond.

​Read more here >>​

➜ Sheets Tip #327: Yet another benefit of Tables in Google Sheets

The IMPORTRANGE formula is a special formula that transfers data from one Google Sheet file to another Google Sheet file. Because our Google Sheets exist as files in the Cloud, we can't simply click to connect them in the way you would do with Excel files on the desktop.

Hence, we have the special IMPORTRANGE function.

A standard IMPORTRANGE formula imports data from one Google Sheets file into another:

​
The formula looks something like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXXXXXXX/edit#gid=0","Sheet1!A1:G11")

where the URL is the source Google Sheet and the range reference refers to the portion of data we want to import, e.g. A1:G11 in Sheet1 in this example.

However, this formula is brittle because:

1. If the Sheet1 is renamed in the source file it doesn't automatically update the IMPORTRANGE formula in the destination sheet.
2. Similarly, if we add rows or columns in the source data the IMPORTRANGE does not automatically import the new data.

In both cases, we have to update the range reference in our IMPORTRANGE formula.

So, how can we make it more robust?

Enter Tables!

Select the source data and convert it to a Table:

Format > Convert to table

Then use the table name (e.g. "Table1") in our IMPORTRANGE formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXX/edit#gid=0","Table1[#ALL]")

Use Table1[#ALL] to get the data AND header row.

Use Table1 to get the data only.

Now, if we rename the sheet in the source file, or add rows or columns, the data will still be imported, automatically.

It also has the added benefit of making our IMPORTRANGE formula more readable too.

Great stuff!

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

Have a great week!

Cheers,
Ben

P.S. Welcome to adulthood. Can confirm, accurate.​