Copy Data from Source Sheet to Target Sheet IF SourceSheet: Column A data does not exists in Target Sheet

Question

I want to copy data from Source Sheet to Target Sheet by using the following code taken from other thread of this site. How ever its not helping with desired output.

I want to copy data from source sheet to target sheet. Source sheet data is changing dynamically hence, I want to copy Source sheet rows if Source sheet Column A data does not exists in Target sheet Column A.

please help me to modify the code.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("Source");
const targetSheet = ss.getSheetByName("Target");
const startRow = 2; 
const APPENDED = 'APPENDED';

function appendToRecords() {
  const numRows = sourceSheet.getLastRow() - 1; // Number of rows to process
  const dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
  const sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
  const lastColumn = sourceData[0].length;

//if (dataRange > 2) {
  for (var i = 0; i < sourceData.length; ++i) {
    var row = sourceData[i];
    if (row[1]=="" && row[lastColumn-1] ==APPENDED) { 
      sourceSheet.getRange(startRow + i, 9 ).setValue("");
    }
    else if (row[lastColumn-1] != APPENDED) {
      ///you should not do this line by line, but all at oncw with an array
      //row[lastColumn-1] = APPENDED; 
      row[8] = APPENDED;                         // to avoid incremental appending
      var lastRow = targetSheet.getLastRow();
      targetSheet.getRange(lastRow + 1, 1, 1, row.length).setValues([row]);
     sourceSheet.getRange(startRow + i, 9 ).setValue("APPENDED");  
    }
  }
}

Sample data sheet https://docs.google.com/spreadsheets/d/13NGHmUjnISK76wri8x9PoyU34KPLdW2TrOXVvoY5kuM/edit#gid=2100307022

Iamblichus · Accepted Answer · 2022-04-27T14:16:34.737

0

Issue:

You want to copy all rows from the source sheet in which column A is not found in target data.

Solution:

Filter the source rows according to whether column A value is found in any row in the target sheet, using filter and some.
Add the filtered rows to the target sheet via Range.setValues.

Code sample:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("Source");
const targetSheet = ss.getSheetByName("Target");
const startRow = 2; 
const CHECK_COLUMN = 1; // Column A

function appendToRecords() {
  const numRows = sourceSheet.getLastRow() - 1; // Number of rows to process
  const numCols = sourceSheet.getLastColumn();
  const lastTargetRow = targetSheet.getLastRow();
  const dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
  const sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
  let targetData = [];
  if (lastTargetRow > 1) targetData = targetSheet.getRange(startRow, 1, lastTargetRow-startRow+1, numCols).getValues();
  const newData = sourceData.filter(sourceRow => {
    const columnA = sourceRow[CHECK_COLUMN-1];
    return !targetData.some(targetRow => targetRow[CHECK_COLUMN-1] === columnA);
  });
  if (newData.length) {
    targetSheet.getRange(lastTargetRow+1, 1, newData.length, newData[0].length).setValues(newData);
  }
}

The script above assumes there's a header row in the target sheet. If that's not the case, replace:

if (lastTargetRow > 1) targetData = targetSheet.getRange(startRow, 1, lastTargetRow-startRow+1, numCols).getValues();

With this:

if (lastTargetRow > 0) targetData = targetSheet.getRange(1, 1, lastTargetRow, numCols).getValues();

Note:

Avoid using setValue in a loop, this slows down your script a lot. See Use batch operations.

edited Apr 27 '22 at 14:16

answered Apr 27 '22 at 10:06

Iamblichus

18,540
2
11
27

Thanks Iamblichus. Can we append data in target sheet with cellvalue "=GOOGLEFINANCE("NSE:"&A1,price)" for all rows A1, A2, A3, ... – NSW Apr 27 '22 at 10:43
Getting following error after second execution 5:15:55 PM Error TypeError: Cannot read property 'length' of undefined CopySheet.gs:39 – NSW Apr 27 '22 at 11:46
Actually On first execution it copied data (two rows) to target sheet. On second execution it copied one duplicate row. On third execution I got above error. – NSW Apr 27 '22 at 11:55
@NSW My apologies, I forgot to handle the edge case in which there's no new data. I updated my answer accordingly. About the duplicate row, I cannot reproduce this. Are you sure both column A values match? – Iamblichus Apr 27 '22 at 12:01
Are you sure both column A values match? Yes. Observation: If there is no change in source sheet during second execution, it copies the first row. – NSW Apr 27 '22 at 14:02
@NSW I see what you mean. I assumed the target sheet would have a header row, so the first row is ignored. That's the cause of this issue. I'll update the answer now for the case in which the target sheet doesn't have a header row. – Iamblichus Apr 27 '22 at 14:14
@NSW I updated the answer, describing the line you'd have to change if your target sheet doesn't have a header row. – Iamblichus Apr 27 '22 at 14:17
Please check thread https://stackoverflow.com/questions/72031020/appending-googlefinance-while-copy-data-from-source-sheet-to-target-sheet – NSW Apr 28 '22 at 02:41
@NSW For documentation purposes, please consider accepting this answer by clicking the checkmark icon below the vote buttons next to the answer https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work. Please notice that people might be less prone to answer your questions if you don't accept them. – Iamblichus Apr 28 '22 at 10:54

Copy Data from Source Sheet to Target Sheet IF SourceSheet: Column A data does not exists in Target Sheet

1 Answers1

Issue:

Solution:

Code sample:

Note: