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

Import range values from one Google Sheet into another with Google Sheet

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.

Hire a Google Workspace Developer for your Business Needs

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: Google Drive Spreadsheet ID in URL
  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.

 

Create and Publish a Google Workspace Add-on with Apps Script Course

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.

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

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)

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)

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:

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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:

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:

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

~ Yagi

13 thoughts on “Copy and Paste Range Values from one Google Sheet into another with Google Apps Script”

  1. Hey there,

    This is awesome! Thank you. I’m trying the code where I can loop through a list. I am getting an error “importRange is not defined (line 31)” – Is there something I need to be adding outside of my spreadsheet Id’s and ranges? Thanks!

    1. Hi Brett,

      From the error message, it sounds like the function importRange isn’t available in your script. are you calling import range from another function? It’s difficult to see what is going wrong without seeing your code. Feel free to share a copy or a simplified version with the error and if I have time, I will take a look.

      ~Yagi

      1. Thanks for the reply! I’m just grabbing that initial code from your array script:
        I’m replacing the sheet ID’s and I get that error for line 31 “importRange is not defined (line 31, file “Code”)”
        My goal is to get data from about 100 sheets into one sheet. All those 100 are formatted the same way.

        function runsies() {

        const arrayOfImports = [
        [
        “1ee5sz_wz9fMMoS_J4KlXb3Ui0AzUif5AlYF7k05zt1Y”,
        “Data!AD2:AP4”,
        “1ZHztPZnbd1U9og_evbV_fAYU138M0pn4IA10CwiWU90”,
        “Variance!A1”
        ],
        [
        “1KXar0SOck36-X1S68pnxgp4B7lqpqfLy1Mrc6z2Ic7A”,
        “Data!AD2:AP4”,
        “1ZHztPZnbd1U9og_evbV_fAYU138M0pn4IA10CwiWU90”,
        “Variance!A4”
        ],
        [
        “1u_dp4W1Wv_lAh6mi6KLG64l_P9vspM9czT6TZPrYHWI”,
        “Data!AD2:AP4”,
        “1ZHztPZnbd1U9og_evbV_fAYU138M0pn4IA10CwiWU90”,
        “Variance!A7”
        ]
        ]

        arrayOfImports.forEach(importSequence => importRange(…importSequence));

        const arrayObjOfImports = [
        {
        sourceID: “1ee5sz_wz9fMMoS_J4KlXb3Ui0AzUif5AlYF7k05zt1Y”,
        sourceRng: “Data!AD2:AP4”,
        destID: “1ZHztPZnbd1U9og_evbV_fAYU138M0pn4IA10CwiWU90”,
        destStart: “Variance!A1″
        },
        {
        sourceID:”1KXar0SOck36-X1S68pnxgp4B7lqpqfLy1Mrc6z2Ic7A”,
        sourceRng:”Data!AD2:AP4″,
        destID:”y1ZHztPZnbd1U9og_evbV_fAYU138M0pn4IA10CwiWU90″,
        destStart:”Variance!A4″
        },
        {
        sourceID:”1u_dp4W1Wv_lAh6mi6KLG64l_P9vspM9czT6TZPrYHWI”,
        sourceRng:”Data!AD2:AP4″,
        destID:”1ZHztPZnbd1U9og_evbV_fAYU138M0pn4IA10CwiWU90″,
        destStart:”Variance!A7″
        }
        ]

        arrayObjOfImports.forEach(importSeq => importRange(importSeq.sourceID, importSeq.sourceRng, importSeq.destID, importSeq.destStart));

        };

        1. Hi Brett, you currently don’t seem to have your importRange function in the code. You will also need to copy that function in from The Code chapter.

          ~Yagi

  2. This is great man! Love it.

    I do have a question pls.

    My database is full of holes (blanks and errors) so i get code gs47 error but when i test with a full data set (no blanks / errors) it works well.

    Is there any way to modify your code to grab data based on auto number of columns (based on headers in row 1) and auto number of rows (based on data in column A)? The first row and first column are always fully populated so they are perfect to define range boundaries for my data set. Thanks in advance.
    KK

  3. Hi Scott,

    Thanks for the tutorial!
    I am curious…how can I use Google Apps Script to copy data from a source Google Drive sheet into dynamically generated subject-specific sheets, allowing flexibility in selecting subjects and class names while ensuring the script can adapt to various combinations of subjects and class names?

    1. Hi Kevin,

      I don’t have any tutorials that specifically match your requests. However, it is definitely achievable.
      You could start off by creating a dashboard in a separate Google Sheet tab in your core Spreadsheet. You can add and modify the settings that you need there. You could even level this up to a dialog box or even a separate web app.

      Next you could create a template sheet and set it up to take in the variations that you need. From here you can call the template sheet with say SpreadsheeetApp.openById() and either use SpreadsheetApps inbuilt copy method or if you wish to direct the new file to a specific file pay, use the DriveApp Class makeCopy method.

      From there you can grab your source data. If it is a single range you can use getRange or if it is a collection of ranges, I recommend using getRangeList() for better performance. Then, you can set the values in the new locations in the newly generated spreadsheet that you created from the template.

      ~ Yagi

      1. Thank you for the detailed suggestions, Yagi!

        I have successfully generated sheets based on my subjects selection using checkboxes for class selection, a specific naming convention system, and apps script. However, I haven’t been able to figure out how to copy data from a sheet inside Google Drive into my generated sheet based on subjects and class selection. I’m unsure if this is possible.

        1. Hi Kevin,
          Yes it is possible. When you generate a new Sheet, it will return the sheet constructor class. Here you can access the Sheet and Range class methods and set Values, formulas, validation and formatting to the newly created Sheet based on what you require to be displayed.

          You can use a dynamic FILTER or QUERY to create the data for the new Sheet in a tab in your core Sheet, or use Apps Script to do this after selecting the full data range from you core and filtering out what you need.

        2. Hello Yogi,

          I wanted to let you know that I’ve made some adjustments to my Google Apps Script based on your suggestion to utilize getRangeList(). These changes have enabled me to successfully copy data from a Google Drive sheet into my newly generated subjects sheet.

          I truly appreciate your assistance! Thank you!

          1. Great to hear and glad to help. Best of luck with the next stage of your project.

  4. I have a Jot form to Google Sheets automation set up where when I update an existing Jot form submission, it will update the existing Google Sheets entry. However, that means whenever I edit a current submission, it will erase the previous entry. I want to keep that entry. We need an automation that would create a new row somewhere on a Google Sheet whenever it senses an update. So it keeps the previous entry and creates a new one.

    For example, a submission comes in, data gets recorded, but going back and making an edit to that submitted data also gets recorded on a new line or shows an edit history etc.

    Here is what we tried:
    Grabbed an extension called sheet automation and created another sheet called source sheet and set the options to watch for changes and copy the row to another sheet called target.
    Now new data comes from jot form from the form submission then into that new source using =IMPORTRANGE> pulls data> extension script watches for changes> target gets copy of new row and target becomes immutable on NEW submissions.

    The problem is this method relies on =importrange to read data, but old data changed on this side does not get recorded since it would “overwrite” the target sheet’s data. I need a copy to a new row option if that even exists?

    To summarize, the automation currently just updates the current submission on Google Sheets, thus erasing the previous entry. Can’t edit the form submissions directly, so using import range on another sheet to run my manual changes, but I also want to make sure the previous entry doesn’t disappear.
    Is there any way to make what we want function, so we can retain our entry changes while updating them at the same time?

    1. Hi V,

      This one is quite a bit beyond the scope of this tutorial.

      However, you could use Jot’s webhooks in conjunction with a Google Apps Script doPost() trigger function. When the webhook receives the form data, updated or otherwise, it appends the data in the sheet rather than replacing it. Feel free to send me a message via my Hire Me page should you require further consultation.

      ~ Yagi

Leave a Reply