Copying cells from sheet1 to sheet2 if it does not exist in sheet2

Question

I am still fairly new to Apps Script and am trying to do the following:

Loop through Sheet1 Column C, which contains user_names. I want to compare the list of user_names in Sheet1 Column C to the list contained in Sheet 2 Column A. If there are user_names that exist in Sheet 1 Column C that do not exist in Sheet 2 Column A, I want to copy them to the end of the list in Sheet 2 Column A. If there are user_names that exist in both lists, I do not want to copy them.

I want the order of the user_names in Sheet 2 Column A to stay the same regardless of how Sheet1 Column C changes. I want any new additions to the list in Sheet1 Column C copied to the end of the list in Sheet 2 Column A, even if the new additions to Sheet1 Column C are found in the middle of that list (see examples below).

The order of the user_names in Sheet1 Column C changes daily.

Examples:
Given:
Sheet1 Column C | Sheet 2 Column A
-----------------------------------
username1       |
username2       |
username3       |

I want:
Sheet1 Column C | Sheet 2 Column A
-----------------------------------
username1       | username1
username2       | username2
username3       | username3

The next day, when the usernames update:
Given:
Sheet1 Column C | Sheet 2 Column A
-----------------------------------
username1       | username1
username3       | username2
username5       | username3
username4       |
username2       |

I want:
Sheet1 Column C | Sheet 2 Column A
-----------------------------------
username1       | username1
username3       | username2
username5       | username3
username4       | username5  
username2       | username4

(in the last example username4 and username 5 can be in any order)

Here's a draft of a function -- I've played around with many versions but none of them worked. I used this question as a reference.

function copy_new_names() {
var spreadsheet = SpreadsheetApp.openById('MY ID IS HERE -- THIS IS A PLACEHOLDER');
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var sheet2 = spreadsheet.getSheetByName('Sheet2');
  var startingrow = 2;
  var endrow = sheet.getLastRow();
  var endcol = sheet.getLastColumn();
  var endrow2 = sheet2.getLastRow();
  var endcol2 = sheet2.getLastColumn();
  var data = sheet.getDataRange().getValues();
  var data2 = sheet2.getDataRange().getValues();

  var resultArray = [];
  for (var n in data){
    var keep = true
    for (var p in data2){
      if( data[n][2] == data2[p][0]){
        keep=false ; break ;
      }
    }
     Logger.log(keep);
    if(keep){ resultArray.push(data[n])};
  sheet2.getRange(endrow2 + 1, 1,resultArray.length,resultArray[0].length).setValues(resultArray);

Logger.log(resultArray);
}
}

Any help would be great!! Thanks so much!

Answer 1

I had found a solution a little different but still works ...who can most can least ;) If you want test...

    // You can activate a trigger on function copy_new_names()
    // Assuming that row 1 in each sheet is not included in the values to be processed...

    function copy_new_names() {
      var spreadsheet = SpreadsheetApp.openById('spreadsheet_id');
      var sheet = spreadsheet.getSheetByName('Sheet1');
      var sheet2 = spreadsheet.getSheetByName('Sheet2');
      var endrow = sheet.getLastRow(), endcol = sheet.getLastColumn();
      var endrow2 = sheet2.getLastRow(), endcol2 = sheet2.getLastColumn();
      // assuming that row 1 is not included in the values to be processed
      var valuesSheet1 = sheet.getRange(2, 3, endrow-1, 1).getValues(), valuesSheet2 = [];
      if ( endrow2 > 1 ) valuesSheet2 = sheet2.getRange(2, 1, endrow2-1, 1).getValues();

      var newlist = checkList(valuesSheet1, valuesSheet2);
      //Below to paste new list in sheet2 
      sheet2.getRange(2, 1, newlist.length, 1).setValues(newlist);
    }

    function checkList(valuesSheet1, valuesSheet2) {
      var values1 = valuesSheet1.map(function(d) { return d.join()}), values2 = [];
      if ( valuesSheet2.length ) values2 = valuesSheet2.map(function(d) { return d.join()});
      var newList = [];
        for ( var i in values1 ) {
          if ( values2.indexOf(values1[i]) === -1 ) newList.push([values1[i]]);
        }  
      newList = valuesSheet2.concat(newList);
      //Logger.log(newList)
      return newList;
    }

Answer 2

This code does what you want. I've tested it, and it works:

function copy_new_names() {
  var spreadsheet, sheet, sheet2, endrow, endcol,endrow2,endcol2,data, data2,resultArray,n,l,l2,p,doesNotExist,
      sheetOneValue, numberOfMissingValues, innerArray,i,thisValue;

  spreadsheet = SpreadsheetApp.openById('');

  sheet = spreadsheet.getSheetByName('Sheet1');
  sheet2 = spreadsheet.getSheetByName('Sheet2');

  endrow = sheet.getLastRow();
  endcol = sheet.getLastColumn();
  endrow2 = sheet2.getLastRow();
  endcol2 = sheet2.getLastColumn();

  data = sheet.getRange(1, 3, endrow, 1).getValues();
  //getRange(start row, start column, number of Rows, number of Columns)
  data2 = sheet2.getRange(1, 1, endrow2, 1).getValues();
  data2 = data2.toString().split(","); //Flatten the 2D array to one D

  resultArray = [];
  innerArray = [];
  l = data.length;
  l2 = data2.length;

  for (n=0;n<l;n+=1) {
    sheetOneValue = data[n][0];
    innerArray = []; //Reset

    for (p=0;p<l2;p+=1) {
      doesNotExist = data2.indexOf(sheetOneValue) === -1;//If the value is NOT found, indexOf returns -1  
      if (!doesNotExist) break; //If it exists in the list, no need to go any further
      if (doesNotExist) {//If the value in the cell from data one does NOT exist in data two, add it to the array

        innerArray.push(sheetOneValue)
        resultArray.push(innerArray);
        Logger.log('sheetOneValue: ' + sheetOneValue);
        break ;
      };
    };
  };

  endrow2 = 0;
  for (i=0;i<l;i+=1) {
    thisValue = data2[i];
    if (thisValue==="" || thisValue=== undefined) {//There is nothing in this cell
      continue;
    };
    endrow2+=1;
  };
  //Append the data in the array to the bottom of the data two list
  sheet2.getRange(endrow2+1, 1,resultArray.length,1).setValues(resultArray);

  Logger.log(resultArray);  
};