Using Apps Script to Transfer Data Between Google Sheets

This article shows how to create a simple Google Apps Script to import data from one Google Sheet to another Google Sheet. This capability can be used automate data transfers, keep data in sync, and simplify data aggregation for reporting and analysis, making it easier to keep your data up-to-date and centralized.

Transfer Data Between Google Sheets

Create a Simple Apps Script in Google Sheets

  1. Prepare Your Google Sheets
    Before diving into Google Apps Script, ensure that you have two Google Sheets ready:
    • Source Sheet: This is the external Google Sheet that contains the data you want to import. Make sure it’s accessible to your Google account and has the data organized with clear headers.
    • Destination Sheet: This is the Google Sheet where you want to import the data. It should have a structure that matches the data you’re importing or be ready to accommodate the incoming information.
  2. Access Google Apps Script
    • To begin, open your destination Google Sheet and navigate to the “Extensions” menu, then select “Apps Script.” This opens the Google Apps Script editor, where you’ll write your custom script.
  3. Script Development
    • In the Apps Script editor, write a custom script that specifies how to retrieve data from the source Google Sheet and insert it into the destination sheet (sample script below). The script involves identifying the source sheet, selecting the data to be imported, and pasting it into the destination sheet. You can also include error handling and data formatting as needed.
    • You can use methods from the Google Class Spreadsheet to construct code that processes the data however you need.
  4. Testing and Execution
    • Once you’ve developed the script, save it, and run it directly from the Apps Script editor. Be prepared to authorize the script to access your Google Sheets. Your script is associated with the Google Sheet you’re working in and can always be accessed from the Extensions menu or at https://script.google.com.

Sample Apps Script to Import Data from Another Google Sheet

function importData() {
  // Replace with the ID of the source spreadsheet
  const sourceSpreadsheetId = "###############";
  // Replace with the name of the source sheet
  const sourceSheetName = "Sheet1";
  // Replace with the range of data to import (e.g., "A1:B10")
  const sourceRange = "A1:D";
  // Access the source spreadsheet
  const ss = SpreadsheetApp.openById(sourceSpreadsheetId);
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  // Get the data from the source range
  const sourceValues = sourceSheet.getRange(sourceRange).getValues();
  // Access the destination spreadsheet (the current one)
  const destSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Replace with the name of the destination sheet
  const destSheetName = "Sheet2";
  const destSheet = destSpreadsheet.getSheetByName(destSheetName);
  // Clear the existing data in the destination range (optional)
  destSheet.getRange(sourceRange).clearContent();
  // Set the imported data to the destination range
  destSheet.getRange(sourceRange).setValues(sourceValues);
}

Summary

Google Apps Script opens up a world of possibilities for automating data between Google Sheets. By following the steps outlined above, you can streamline your data management and reporting tasks, ensuring that your information is always up-to-date and readily accessible. Dive in today and begin harnessing the potential of this versatile tool to simplify your workflow and transform your data-management workload.