data Archives -

Automatic Data Sharing Across Sheets With IMPORTRANGE and Named Functions

This automatic data sharing technique first appeared in my weekly Google Sheets Tip newsletter #269, August 2023.

(Don’t miss out! Join today to get future editions.)

This post showcases a unique use case for Named Functions and IMPORTRANGE.

Automatic Data Sharing 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.

Automatic Data Sharing in Sheets
Continue reading Automatic Data Sharing Across Sheets With IMPORTRANGE and Named Functions

How To Get Fathom Analytics Data Into Google Sheets, Using Apps Script

In this post, we’re going to create a tool that calls the Fathom Analytics API and pastes website traffic data into Google Sheets:

Get Fathom Site Data

But first, a quick backstory:

Earlier this year (2022), Google announced the sunsetting of the old implementation of Google Analytics, in favor of GA4.

At the time I was running the old Google Analytics software, implemented through Google Tag Manager (along with Facebook’s pixel tracker).

It was time for me to update my web analytics software.

But I didn’t want to just shove GA4 into my existing tag manager setup. From what I’d heard, GA4 was difficult to use and way overblown for my needs.

Also, I really wanted to remove the dependency on Tag Manager from my site, because it’s too complex for my use case and I’m not particularly familiar with it. Plus, it’s been years since I’ve used the Facebook analytics pixel so I wanted to get rid of that too. I wanted to improve my site speed, and removing all this javascript would help with that goal.

So I cast around for alternative analytics software and landed on Fathom.

Fathom Analytics is a lightweight, easy-to-use, privacy-focused analytics software that is perfect for my website.

It was ridiculously easy to set up and I’ve been delighted with how easy it is to use. I jump in and can quickly see everything I need to know for my website:

Continue reading How To Get Fathom Analytics Data Into Google Sheets, Using Apps Script

How To Use The IMPORTRANGE Function In Google Sheets To Transfer Data From One Sheet To Another

Since Google Sheets are files in the cloud, not on your desktop, you can’t click on a cell in a different Sheets file to connect them.

Instead, you use the IMPORTRANGE function in Google Sheets to connect Google Sheet files and import data from one Sheet file into another.

Once set up, the function will automatically sync with the source data so that changes are reflected in the destination Sheet.

IMPORTRANGE In Google Sheets

If you look closely, you’ll see a URL in the formula — the URL of the source Google Sheet file, where the data is being imported from.

Continue reading How To Use The IMPORTRANGE Function In Google Sheets To Transfer Data From One Sheet To Another

How To Copy Only Visible Cells In Google Sheets

In this post, you’ll learn how to copy only visible cells in Google Sheets so that you only paste the results you want and don’t include any hidden data.

For this tutorial, we’ll use this dataset, shown in full to begin:

Data in Google Sheets