Google script - copy columnC data from Sheet21 to columnC Sheet2, if not present in Sheet2 already

Question

I am writing one of my first scripts, and have tried to look at other similar questions.

I have two sheets: sheet1: new data (Only Column C is of interest, everything else can ignore in other columns) sheet2: old data (but needs to be updated with sheet1 new data if not already there). The data to be added should be at the end of Column C after the existing data.

The code I have has the following compiling error. I need to - get the last row of Column C Sheet2. Then check if Column C sheet1 is present in ColumnC sheet2, if not present- copy over from sheet1 to sheet2 column C.

UPDATED CODE:

function updateSheet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = "Sheet1"; 
var destinationSheet = "Sheet2";
var source_sheet = ss.getSheetByName(sourceSheet);
var target_sheet = ss.getSheetByName(destinationSheet);
var last_row = CountColC();


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

//Note the use of an array
r.sort([{column: 3, ascending: true}]);


  // Process sheet
  _updateSpreadsheet(source_sheet, target_sheet);

}

//gets last row in Column C
function CountColC(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][2] != null && data[i][2] != ''){
      return i+1 ;
    }
  }
}

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

  for (var n = 1 ; n < source_data.length ; n++) {
    var keep = true;
    for(var p = 1 ; p < target_data.length ; p++) {
      if (new Date(source_data[n][2]).getTime() == new Date(target_data[p][2]).getTime()) {
        keep = false; break;
      }
    }
    Logger.log(keep);
    if(keep){ resultArray.push([source_data[n][2]])};
  }
  last_row++;
  Logger.log(resultArray);
  target_sheet.getRange(last_row,1,resultArray.length,resultArray[2].length).setValues(resultArray);
//  target_data.push(n);
}

Thanks in advance :)

   function updateSheet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = "Sheet1"; 
var destinationSheet = "Sheet2";
var source_sheet = ss.getSheetByName(sourceSheet);
var target_sheet = ss.getSheetByName(destinationSheet);
var lastCol = target_sheet.getLastColumn();
var lastRow = target_sheet.lastColumn();


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

//Note the use of an array
r.sort([{column: 3, ascending: true}]);


  // Process sheet
  _updateSpreadsheet(source_sheet, target_sheet);

}

//gets last row in Column C
function CountColC(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][2] != null && data[i][2] != ''){
      return i+1 ;
    }
  }
}
function _updateSpreadsheet(source_sheet, target_sheet) {
  var last_row = target_sheet.CountColC();  
  var source_data = source_sheet.getDataRange().getValues();
  var target_data = target_sheet.getDataRange().getValues();
  var resultArray = [];

  for (var n = 1 ; n < source_data.length ; n++) {
    var keep = true;
    for(var p = 1 ; p < target_data.length ; p++) {
      if (new Date(source_data[n][2]).getTime() == new Date(target_data[p][2]).getTime()) {
        keep = false; break;
      }
    }
    Logger.log(keep);
    if(keep){ resultArray.push([source_data[n][2]])};
  }
  last_row++;
  Logger.log(resultArray);
  target_sheet.getRange(last_row,1,resultArray.length,resultArray[2].length).setValues(resultArray);
//  target_data.push(n);
}

@Eugene I'm not sure if the rest of the lines are any good. but row 35 is the first error I get row 35 "var last_row = target_sheet.CountColC(); " TypeError: Cannot call method "CountColC" of undefined. — user3858022

Eugene Eugene · Accepted Answer · 2016-07-28T07:21:22

Instead call

var last_row = target_sheet.CountColC();

Please use

var last_row = CountColC();

Google script - copy columnC data from Sheet21 to columnC Sheet2, if not present in Sheet2 already

1 Answers