Google脚本-将Sheet21中的columnC数据复制到columnC Sheet2，如果Sheet2中还没有columnC数据

Question

我正在写我的第一个脚本之一，并试图研究其他类似的问题。

我有两张表：Sheet1：新数据（只有C列感兴趣，其他列中的所有内容都可以忽略）Sheet2：旧数据（但如果尚未存在，则需要使用Sheet1新数据更新）。要添加的数据应该在存量数据之后的C列末尾。

我拥有的代码有以下编译错误。我需要-获取C列Sheet2的最后一行。然后检查列C表1是否存在于列C表2中，如果不存在-从表1复制到表2列C。

更新代码：

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);
}

感谢提前：）

   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);
}

Answer 1

而是打电话

var last_row = target_sheet.CountColC();

请使用

var last_row = CountColC();