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. 

Using Google Apps Script over Google Sheets IMPORTRANGE

So why use Google Apps Script to import a range into another Google Sheet when there is already the very handy IMPORTRANGE function.

Well, to be honest, if all you are doing is importing a little data from one sheet to another, save yourself a bit of time and just use IMPORTRANGE.

You might want to consider using Apps Script to do this when:

1. You have a lot of data from multiple sources to import among various Google Sheets. IMPORTRANGE will slow down and may even break with a lot of data being connected live (dynamically) between different sheets. A coded approach can keep your data static improving performance while also providing a fast way through buttons or menu items or even time triggers.
2. You want to manipulate the data in transit from one Sheet to another. If you want to do some analysis or make changes to the data before it reaches the source data then using Google Apps Script is your friend.
3. You want to prevent others editors from accessing the rest of the data in your source Google Sheet. Did you know that clever editors can access other sheet tabs and data from your IMPORTRANGE source sheet in your destination sheet? There are some workarounds, but the best way to prevent editors from seeing data from your source sheet is by importing the data with Google Apps Script.
4. You are copying and pasting data from one sheet to another as a part of a larger automation project. You obviously can’t use IMPORTRANGE as a part of a larger scripting project. Here, is it is better to create a custom function and incorporate that function into your greater project.

The Code

You can simply copy and paste the code into your own project. It is recommended that you paste this code into your source sheet or an independent Google Apps Script project.

You can find out more about adding scripts to a Google Sheet here.

Quick use guide

If you are using this script as a standalone piece of code then you can copy the entire script in. Otherwise, if you are using this script as part of a larger project, then you can just copy and paste in the importRange() function.

The importRange() function takes 4 arguments as you can see in the example on lines 7-12 of the code above:

1. sourceID: e.g. “14QBa3ID3EWbK3FNReNUn5nlJwQFQR6l91zODzZTd6SA” – This is the id of the source Google Sheet. You can find the id in the URL of the sheet:
2. sourceRange: e.g. “Task List!A2:G” – This is the range in your source Google Sheet that you want to import into your destination Google Sheet. It will need to contain the name of the sheet tab (in our e.g. this is ‘Task List’) and the range inside that sheet tab (e.g. A3:G). Note that I have left G ‘open’ without a row range to allow for the range to change as data is added or removed to the rows.
3. destinationID: e.g. “14QBa3ID3EWbK3FNReNUn5nlJwQFQR6l91zODzZTd6SA” – This is the destination Google Sheet ID that you want to import into.
4. destinationRangeStart: e.g. “Sheet1!B3” – This is where you want the data to be pasted. In this example we want the data to go in the Sheet1 tab. We want the top left-most corner of the data to start in cell B3. We don’t need to complete the full range. The code will handle this.

Applying Multiple Instances of importRange()

Duplicate the function call

You can add multiple instances of the importRange() function by simply repeating the function in your code and updating the arguments:

Loop through a list

Alternatively, if you have a large number of ranges to import, you could set up a 2d array (helpful if you are keeping this data in a Google Sheet) or an array of objects and then loop through each one calling the function on each iteration.

That’s all there is to it. If you want to learn how the code works or how to add formatting to your range, read on.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

 

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Code Breakdown

We’ll just cover the importRange() function for this breakdown. The runsies() function is simply a place marker and is mostly covered in the ‘Quick use guide’ above.

This is quite a beginner-friendly block of Google Apps Script code so I will approach the explanation of it with beginners in mind.

Gather the source range values

Our first task is to call the source spreadsheet. We do this with the openById() method of the SpreadsheetApp class. This method takes one argument, the ID of the Google Sheet we want to work in. For us, this will be our sourceID. We will store the results of this method in our sourceSS variable.

Once we have initialised this method, we now can run other methods in that particular Google Sheet.

Now that our Google Sheet is set up to work in, we can select the range we want to copy (or export in our case). We can do this with the getRange() method. This method takes an A1-notation range as an argument. For example “Sheet1!A4:B”. Here we will add our sourceRange variable as our argument.

The method allows us to access and manipulate the selected range.

We store this data in our sourceRng variable. (Take a look at the chapter on ‘Adding Some Formatting’ below to see what else we can do with range data)

Finally, we collect the values of this range and store them in our sourceVals variable. This will be stored as a 2d array like this:

Gather the Destination Sheet tab and Start Cell

Our next task is to set up our destination Google Sheet.

Again we first need to call (open) the destination sheet with openById() entering our destinationID as our argument storing it in our destinationSS variable. Line 2

Next, we want to get our start range. We will be using this later to separate the start row from the column letter and also find the sheet tab we will be importing into.

Just like in our source data we call the getRange() method using our destinationStartRange as our A1-notation argument.  Line 3

This time around we need to first select the destination sheet tab that we are going to import our data into. We can do this by calling the getSheet() method on the range. This method will allow us to work on the sheet as a whole, not just the range. Line 4

Clear the sheet

Because we will be replacing all the data on the destination sheet tab with new data we should clear it out first so that we have a fresh sheet.

We can do this with the clear() method which we apply to our destSheet.

Get the full data range that we need to paste into

Now we can’t just rely on the start range to be able to import our source values. We have to get the full range of values.

If you have been paying attention, you might have noticed that we are using the same getRange()  method that we used earlier. When we call getRange() on a selected sheet tab as opposed to the entire Google Sheet workbook, we can use another approach to setting the start and end rows and columns.

In the code above we are applying 4 numerical arguments to getRange():

1. Start row (number) – For us, we get the start row by using the getRow() method on our destStartRange variable. Line 3
2. Start column (number) – We can get the start column in the same way as the start row with the getColumn() method. Line4
3. Row depth (number) – How deep the row is. We can find this out by getting the length of the outer array of the 2d array we collected in our sourceVals variable. Line 5
4. Column width (number) – How many columns are across from our start column. We can find this by getting the length of the first (or zeroeth) inner array. Line 6

Paste and flush

Our final step is to import our source values into our newly assigned destination range. We can do this with the setValues() method which takes our sourceVals as an argument.

Lastly, we want to make sure that Google Apps Script deploys our code and is not waiting for any other spreadsheet commands with the flush() method. This is particularly important when we are calling our importRange() function multiple times.

 Adding some formatting

In most cases, it is usually good practice to allow your destination sheet to dictate the formatting of your code. However, there are some cases like when you might want to apply formatting.

Fortunately for us, we can use the sourceRng and destRange to collect formatting from our source range and set it in our destination range. This means that updating our code is going to be relatively easy.

Let’s say we want to copy over the background colour, font style (normal, strikethrough, italic etc.) and font-weight (bold/normal) into our destination sheet. This is what our importRange() function would look like:

As you can see in the highlighted region, we have first collected our desired source formatting by applying the following methods to sourceRng: (Lines 15-18)

• getFontStyles()
• getFontWeights()
• getBackgrounds()

Notice the ‘s’ at the end this means it is going to collect all the formatting data over the entire range rather than just the first cell of the selected range.

Once  we have set our values in our destination Google Sheet we then set all the formatting by applying the following to our destRange: (Lines 39 – 42)

• setFontStyles(sourceFontStyle)
• setFontWeights(sourceFontWeight)
• setBackgrounds(sourceFontBackgroundColor)

It is generally good practice to keep these method calls together because Google Apps Script will try and help you out and bundle these calls together and try and do them all at once so that your script runs faster.

There are a whole bunch of other formatting ‘set’ and ‘get’ methods that you can use too. Check out the link to the docs and scroll along the sidebar to see what you can doo.

Google Apps Script: SpreadsheetApp: range docs

Adding a Time Trigger

You can set up your importRange() data to update in a similar way to the Google Sheets IMPORTRANGE function too. You can do this by adding time triggers.

The good news is that you don’t have to do any extra coding to do this. Check out my video below on how to add time triggers to your Google Apps Script:

The Video

Here’s the video tutorial.

If you want to follow along with the tutorial, here is a link to the files that I used:

• Source: Template – Project Tasks (Apps Script) | Video
• Destination: Destination (Creatively named, I know)

Conclusion

In this tutorial, we covered how to copy Google Sheet data from one spreadsheet to another using Google Apps Script essentially making our very own IMPORTRANGE.

You learnt how to add formatting to your pasted data and also how to set time triggers.

This approach overwrites the existing data in the destination sheet. But what if you want to append to the bottom of another spreadsheet or another sheet tab in the same Google Sheet? Check out the links below:

•  Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Dec 2021]
• Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet [update Jan 2022]  
• Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another 

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~ Yagi