Month: February 2022

Copy and Paste Range Values from one Google Sheet into another with Google Apps Script

You can easily use Google Apps Script to copy a range of data from one Google Sheet to another Google Sheet, just like you would with using the IMPORTRANGE function in Google Sheets. However, there are some clear advantages to importing ranges with Google Apps Script.

In this beginner-friendly tutorial, we’ll create an importRange() Google Apps Script function that you can quickly duplicate and even expand on in your own projects. We’ll also show you how to apply certain formatting and a time trigger to your code.

Note! This tutorial covers how to replace a range with existing data using Google Apps Script. If you wish to append data please head to the ‘Further reading’ section for more tutorials on this topic.

As usual, read what you need and skip the rest. 

Continue reading “Copy and Paste Range Values from one Google Sheet into another with Google Apps Script”

Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial.

When I first sat down to write this tutorial, I had a specific opinion that one approach to using VLOOKUP on IMPORTRANGE data was better than another. However, I wanted to be certain. I ran some basic tests comparing the two approaches to see how they both perform over large data sets, and you know what? I was surprised to find I couldn’t find a discernable difference in performance.

Next, I reached out to some of the other fellow Google Sheets nerds, who like to go way too far with software for all the wrong reasons, and they seemed to feel that those demi-gods of Google devs really understand the wayward predilections of their users and may have benevolently stored the IMPORTRANGE data locally in the sheet you have imported to.

Will we ever know for certain? Only ever perhaps in the lay of the tea leaves or roll of the bones. We can but only guess the ways of the Googler as they traverse the digital world in all their etheral glory.

What does this mean in short? There are two pretty solid ways to use VLOOKUP with IMPORTRANGE. I’ll cover both in this tutorial.

Continue reading “Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function”

Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial here:

https://yagisanatode.com/2022/01/26/importing-range-data-from-one-google-sheet-to-another/

And here is a link to the sample data that I am importing if you want to play along:

Template – Project Tasks (IMPORTRANGE)

You’ve grabbed data from a source Google Sheet and used the IMPORTRANGE function to insert it into your destination sheet carefully selecting the range and sheet tab of the data that you want to display.

So you might be thinking, Great! I’ve managed to only display the content from my original Google Sheet that I want my users to see and hidden the rest from them. 

While this may be the case if you are only providing ‘View’ or ‘Comment’ permission to a Google Sheet, I do have an ugly little surprise for you if you.

via GIPHY

Are you sitting down? Are you comfortable? Do you have a support network nearby?

Let’s have a look at the IMPORTRANGE docs:

IMPORTRANGE editors can access other parts of the original Google Sheet - What to do
Click to Expand!

So basically, if you have other editors on your new sheet editing your document, they can access anything in the imported Google Sheet by copying the access granted IMPORTRANGE. All they need to do is change the Sheet tab and the range location.

Continue reading “Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet”