Hi Reader,

Welcome to the Google Sheets Tips newsletter #199!

➜ News

I.
​Love them or hate them, you'll soon be able to express your feelings with emojis in the comments of Google Docs. No word on whether this is coming to Sheets though. 🤓 🧐 👍

​New Emoji reactions in Google Docs on web​

II.​
Mainstream publications don't write about spreadsheets very often — they're dreadfully boring compared to web3 and NFT's! — but when they do I make sure to read it. Wired recently covered innovation in the space:

​Spreadsheets Are Hot—and Cranking Out Complex Code​

_______

➜ Google Sheets Tip #199: IMPORTRANGE and backlinks to source Sheet

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

Google Sheets are files in the cloud, not on our desktop, so we can't simply point at a cell in a different Sheets file and expect them to connect.

Instead, we use the IMPORTRANGE function to copy data from one Google Sheet file to another.

Part 1: IMPORTRANGE

Suppose we have yearly sales data in separate Google Sheet files and we want to consolidate it into a single Sheet.

Use IMPORTRANGE to copy data from each yearly Sheet into a single Sheet.

The formula looks like this:

where [URL] is the URL of the source data Sheet and Sheet1!A1:D100 is the range of data, e.g.:

When you setup the IMPORTRANGE function for the first time, you're prompted to allow access between the two Sheet files.

Part 2: IMPORTRANGE Backlink Formula

Now, what about going the other way? From the destination Sheet back to the source Sheet?

Of course, we could click into the formula bar, copy the URL, and then open that in our web browser, but that's not an elegant way to do it. Plus, it's easy for users to break the formula accidentally or copy the URL incorrectly.

Instead, we can add a link, like this:

The formula lets a user click directly to the source Sheet.

This formula in A1 will create a link to the source Sheet of an IMPORTRANGE formula in cell A2:

Peeling it apart with the onion framework, it works as follows:

FORMULATEXT converts the IMPORTRANGE to a formula string.

Next, REGEXEXTRACT extracts the URL part of the formula string. The REGEX rule is """(.+?)""" which has 3 double quotes each side of the bracket expression.

Then, HYPERLINK turns this URL into a clickable link.

Finally "Source "&CHAR(8699) shows pretty link text, instead of the full URL.

This might be a nice touch to add links back to source files for your important documents!

_______

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

Have a great week!

Cheers,
Ben

P.S. The only engineering cheat sheet you need...​