Import Range Values from one Google Sheet into another with Google Apps Script

Google Apps Script

If you're looking to automate the process of transferring data between Google Sheets, Google Apps Script provides a powerful solution. In this tutorial, we'll guide you through creating a custom importRange() function using Google Apps Script. This script allows you to copy a specific range of data from one Google Sheet to another effortlessly, offering distinct advantages over the conventional IMPORTRANGE function.

Why Choose Google Apps Script?

While the IMPORTRANGE function is a built-in feature in Google Sheets, Google Apps Script offers greater flexibility and customization. With Apps Script, you can not only copy data but also apply specific formatting and even set up a time trigger to automate the process.

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

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.

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.

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.

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


/**
 * This is a placemarker function used to call the importRange.
 * You can call importRange() from any function or just use this  one for your project.
@annt
 */
function runsies() {

  importRange(
    "yourGoogleSheetSourceID",  //Source ID -  e.g. "14QBa3ID3EWbK3FNReNUn5nlJwQFQR6l91zODzZTd6SA"
    "yourSourceSheetTab!A2:G", // Source Range - e.g. "Task List!A2:G"
    "yourGoogleSheetDestinationID", // Destination ID - e.g. "14QBa3ID3EWbK3FNReNUn5nlJwQFQR6l91zODzZTd6SA"
    "yourDestinationSheetTab!B3" // Destination Range Start - e.g. "Sheet1!B3"
  );

};

/**
 * Imports range data from one Google Sheet to another.
 * @param {string} sourceID - The id of the source Google Sheet.
 * @param {string} sourceRange - The Sheet tab and range to copy.
 * @param {string} destinationID - The id of the destination Google Sheet.
 * @param {string} destinationRangeStart - The destintation location start cell as a sheet name and cell. 
 */
function importRange(sourceID, sourceRange, destinationID, destinationRangeStart){

  // Gather the source range values
  const sourceSS = SpreadsheetApp.openById(sourceID);
  const sourceRng = sourceSS.getRange(sourceRange)
  const sourceVals = sourceRng.getValues();

  // Get the destiation sheet and cell location.
  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destStartRange.getSheet();

  // Clear previous entries. 
  destSheet.clear();

  // Get the full data range to paste from start range.
  const destRange = destSheet.getRange(
      destStartRange.getRow(),
      destStartRange.getColumn(),
      sourceVals.length,
      sourceVals[0].length
    );
  
  // Paste in the values.
  destRange.setValues(sourceVals);

  SpreadsheetApp.flush();
};