CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Matt92
Matt92

Reputation: 1

Google script - copy values to a new sheet except values that already exists

I'm trying to copy the values of data to a new sheet. I want to copy all the information from Column AB - AZ but I want it to only copy unique values based on the values that are in column AC. For example, if the value 12345 is in column AC on Sheet1 and is also in Col C on Sheet2, then I want it to copy everything else except that row.

Here's the code I have so far

function updateSheet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = "Sheet9"; 
var destinationSheet = "Sheet10";
var source_sheet = ss.getSheetByName(sourceSheet);
var target_sheet = ss.getSheetByName(destinationSheet);
var lastCol = target_sheet.getLastColumn();
var lastRow = target_sheet.getLastRow();

//assumes headers in row 1
var r = target_sheet.getRange(2,27);

  // Process sheet
  _updateSpreadsheet(source_sheet, target_sheet);

}


function _updateSpreadsheet(source_sheet, target_sheet) {
  var last_row = target_sheet.getLastRow();  
  var source_data = source_sheet.getDataRange().getValues();
  var target_data = target_sheet.getDataRange().getValues();
  var resultArray = [];

  for (var ac in source_data) {
  var keep = true;
   for(var c in target_data) {
      if (source_data[ac][0] == target_data[c][0]) {
                 keep = false; break;
      }
    }
    Logger.log(keep);
 //   if(keep){ resultArray.push(source_data[ac])};
//    if(keep){ resultArray.push([source_data[ac][0]])};
    var columnsToKeep = [27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51];
    var tempData = [];
    if(keep){
      for(var c in columnsToKeep){ tempData.push(source_data[ac][columnsToKeep[c]])}
    resultArray.push(tempData);
    }     
  }
  last_row++;
  Logger.log(resultArray);
  if(resultArray.length>0){
    target_sheet.getRange(last_row,2,resultArray.length,resultArray[0].length).setValues(resultArray);
  }
}

It's copying the data across properly but it's failing to recognize when the values are equal to each other and therefore avoid that row. Any help would be greatly appreciated

Upvotes: 0

Views: 421

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

If I understood you correctly, you want to copy the rows in which the value in column AC (Sheet1) doesn't exist in any row of column C (Sheet2).

If that's the case, you can use this:

function _updateSpreadsheet(source_sheet, target_sheet) {
  var last_row = target_sheet.getLastRow();
  var firstRow = 1; // First row to check
  var firstCol = 28; // Column AB
  var numRows = source_sheet.getLastRow() - firstRow + 1; // Number of rows to check
  var numCols = 25; // Number of columns to keep
  var source_data = source_sheet.getRange(firstRow, firstCol, numRows, numCols).getValues(); // Data corresponding to columns 28-52  
  var indexColumnToCheck = 1; // Column AC, corresponding to index 1 of source_data rows
  var target_column = 2; // Column C
  var target_data = target_sheet.getDataRange().getValues();
  var resultArray = source_data.filter(source_row => {
    return !target_data.some(target_row => target_row[target_column] === source_row[indexColumnToCheck]);
  });
  if (resultArray.length > 0) {
    target_sheet.getRange(last_row + 1, 2, resultArray.length, resultArray[0].length).setValues(resultArray);
  }
}

This function filters the source_data, checking, for each row, whether there is any row in the target_data in which column C matches column AC from source row. filter and some are used for that.

Note:

  • You have to enable V8 for this script to run.

Reference:

Upvotes: 1

Related Questions