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 increamental 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

CodePudding user response：

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.