How to Copy Data from One Sheet to Another in Google Sheets Using Apps Script

by

Google Apps Script is a powerful tool for automating tasks in Google Sheets. One common task is copying data from one sheet to another without creating duplicates. In this post, we’ll walk through a script that accomplishes this using Google Apps Script.

Scenario

Imagine you have a sheet named “Orders” that contains statements of orders. You want to copy these statements to another sheet named “Consolidated Orders” but ensure no duplicates are added.

Step-by-Step Guide

  1. Open Google Sheets and Access Apps Script:
    • Open your Google Sheet.
    • Click on Extensions > Apps Script.
  2. Create the Script:

Here’s a script that copies data from the “Orders” sheet to the “Consolidated Orders” sheet, ensuring no duplicates are copied.

function copyUniqueOrders() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Orders');
  const targetSheet = ss.getSheetByName('Consolidated Orders');

  if (!sourceSheet || !targetSheet) {
    Logger.log('One or both of the sheets are missing.');
    return;
  }

  const sourceData = sourceSheet.getDataRange().getValues();
  const targetData = targetSheet.getDataRange().getValues();
  const targetDataFlat = targetData.flat();

  let uniqueOrders = [];

  // Start from the second row to avoid headers
  for (let i = 1; i < sourceData.length; i++) {
    const orderRow = sourceData[i];

    // Check if the order (considering the first column as unique identifier) already exists in target
    if (!targetDataFlat.includes(orderRow[0])) {
      uniqueOrders.push(orderRow);
    }
  }

  if (uniqueOrders.length > 0) {
    targetSheet.getRange(targetData.length + 1, 1, uniqueOrders.length, uniqueOrders[0].length).setValues(uniqueOrders);
  } else {
    Logger.log('No new unique orders to copy.');
  }
}

Explanation

  1. Get the Active Spreadsheet and Sheets:const ss = SpreadsheetApp.getActiveSpreadsheet(); const sourceSheet = ss.getSheetByName('Orders'); const targetSheet = ss.getSheetByName('Consolidated Orders');
  2. Check if Sheets Exist:javascriptCopy codeif (!sourceSheet || !targetSheet) { Logger.log('One or both of the sheets are missing.'); return; }
  3. Get Data from Both Sheets:const sourceData = sourceSheet.getDataRange().getValues(); const targetData = targetSheet.getDataRange().getValues(); const targetDataFlat = targetData.flat();
  4. Find Unique Orders:
    • Loop through the source data starting from the second row to avoid headers.Check if the order (using the first column as a unique identifier) already exists in the target data.
    let uniqueOrders = []; for (let i = 1; i < sourceData.length; i++) { const orderRow = sourceData[i]; if (!targetDataFlat.includes(orderRow[0])) { uniqueOrders.push(orderRow); } }
  5. Copy Unique Orders to the Target Sheet:if (uniqueOrders.length > 0) { targetSheet.getRange(targetData.length + 1, 1, uniqueOrders.length, uniqueOrders[0].length).setValues(uniqueOrders); } else { Logger.log('No new unique orders to copy.'); }

Running the Script

To run the script:

  1. Save your script by clicking the disk icon or pressing Ctrl + S.
  2. Click the play button (▶️) to execute the copyUniqueOrders function.

You can also set up a trigger to run this script automatically at regular intervals or based on specific events (e.g., when the sheet is edited).