Google脚本:将列中的新值附加到另一个工作表 [英] Google Script: Append new values in column to another sheet

问题描述

我一直在从事这个项目，无法使其按我想要的方式工作.

I have been working on this project and can't get it to work quite the way I want.

我在同一列中有两张具有唯一ID的工作表.我需要确保每当在一个工作表(Sheet1)中添加新的ID时，它将被复制到另一工作表(Sheet2)的最后一个空行.如果值已经存在，请确保忽略它(以避免重复).这个想法是创建一个函数并使其每分钟左右触发一次.

I have two sheets with unique IDs in one column. I need to make sure whenever a new ID is added in one sheet (Sheet1), it is copied to the last empty row in the other sheet (Sheet2). Making sure it ignores it if the value already exist (to avoid duplicates). The idea was to create a function and have it triggered every minute or so.

下面是示例电子表格和几乎可以正常运行的脚本.唯一的问题是，不是将新值添加到底部的第一个空行，而是以怪异的顺序复制它们(请参见Sheet2了解我的意思).电子表格中的标题位于第4行中，我猜这可能与此有关吗?

Below is the example spreadsheet and the script that is almost working now. The only issue is that instead of adding new values to the first empty row at the bottom, is copying them in a weird order (see Sheet2 to see what I mean). The header in my spreadsheet is in row 4, and I'm guessing the issue has something to do with that?

电子表格示例: https://docs.google.com/电子表格/d/12Ty5pWhLULDn6GZ1Qb3RqR556TXjIL7GdWvxW2xJ7M0/edit?usp = sharing

脚本:

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.getLastRow();

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

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

  // 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 n = 1 ; n < source_data.length ; n++) {
    var keep = true;
      for(var p in target_data) {
       if (source_data[n][0] == target_data[p][0]) {
                 keep = false; break;
      }
    }
    Logger.log(keep);
 //   if(keep){ resultArray.push(source_data[n])};
//    if(keep){ resultArray.push([source_data[n][0]])};
    var columnsToKeep = [0];
    var tempData = [];
    if(keep){
      for(var c in columnsToKeep){ tempData.push(source_data[n][columnsToKeep[c]])}
    resultArray.push(tempData);
    }     
  }
  last_row++;
  Logger.log(resultArray);
  if(resultArray.length>0){
    target_sheet.getRange(last_row,1,resultArray.length,resultArray[0].length).setValues(resultArray);
  }
}

推荐答案

我相信您的目标如下.

• 您要在"Sheet1"中附加唯一值到"Sheet2"在对单元格"A2:A"进行排序之后，在"Sheet2"上通过修改脚本.

为此，该修改如何?

• 在您的情况下，如何检索单元格"A4:A"?来自 source_sheet 而不是 getDataRange()?
• 在您的脚本中，当"Sheet2"中没有任何值时，在 target_sheet.getRange(2,1，lastRow-1，lastCol)处发生错误.

• In your case, how about retrieving the cells "A4:A" from source_sheet instead of getDataRange()?
• In your script, when no values in "Sheet2", an error occurs at target_sheet.getRange(2,1, lastRow - 1, lastCol).

当以上几点反映到您的脚本中时，它如下所示.

When above points are reflected to your script, it becomes as follows.

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.getLastRow();
  if (lastRow > 1) { // <--- Added
    var r = target_sheet.getRange(2,1, lastRow - 1, lastCol);
    r.sort([{column: 1, ascending: true}]);
  }
  _updateSpreadsheet(source_sheet, target_sheet);
}

function _updateSpreadsheet(source_sheet, target_sheet) {
  var last_row = target_sheet.getLastRow();  
  var source_data = source_sheet.getRange("A4:A" + source_sheet.getLastRow()).getValues(); // <--- Modified
  var target_data = target_sheet.getDataRange().getValues();
  var resultArray = [];
  for (var n = 0 ; n < source_data.length ; n++) { // <--- Modified
    var keep = true;
      for(var p in target_data) {
       if (source_data[n][0] == target_data[p][0]) {
         keep = false; break;
       }
    }
    var columnsToKeep = [0];
    var tempData = [];
    if(keep){
      for(var c in columnsToKeep){ tempData.push(source_data[n][columnsToKeep[c]])}
      resultArray.push(tempData);
    }     
  }
  last_row++;
  resultArray = resultArray.filter(String);  // <--- Added
  if(resultArray.length>0){
    target_sheet.getRange(last_row,1,resultArray.length,resultArray[0].length).setValues(resultArray);
  }
}

• 通过此修改，当"Sheet2"没有值，标头 ID 放在第一行.第二次运行后，将忽略第一行，并对第二行下方的单元格进行排序，然后附加新的唯一值.
• By this modification, when "Sheet2" has no values, the header ID is put to the 1st row. After 2nd run, 1st row is ignored and the cells below 2nd row are sorted, and then, the new unique value is appended.
• getRange(row，列，numRows，numColumns)
• getRange(a1Notation)

这篇关于Google脚本:将列中的新值附加到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！