Google脚本:将值从一张纸复制到另一张纸以确定范围 [英] Google Script: copy values from one sheet to another determining the range

问题描述

我在同一列中有两张具有唯一ID的工作表.我需要确保每当在一个工作表(Sheet1)中添加新的ID时，它将被复制到另一工作表(Sheet2)的最后一个空行. IMPORTRANGE不能以动态方式工作，否则在另一张工作表中添加的任何静态信息都将与相应的ID无关.

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). The IMPORTRANGE won't work as being dynamic any static information added in the other sheet would be irrelevant to the respective ID.

在另一个线程[1]中，我得到了开发此脚本的帮助，该脚本将准确地完成此任务.但是，此脚本会将数据插入到A列的另一张工作表中.如何修改脚本，以便可以确定要脚本插入数据的具体范围(在工作表2中).

In another thread [1], I got help developing this script that will do exactly that. However, this script will insert the data in the other sheet in Column A. How can I modify the script so I can decide the specific range where I want the script to insert the data (in Sheet 2).

更新:我以创建此电子表格为例.我试图确保脚本从"Sheet2"中的cel C10开始进行计算(即，添加在第一个可用的空行中未重复的vlaues).如果我需要以某种方式可以更改该范围，那也将非常好: https://docs.google.com/spreadsheets/d/1abESAXFrOHoqRQRNqQGfmAFxlbD10wlprAf1tca4y7o/edit#gid=132361488

Update: I created this spreadsheet as an example. I'm trying to ensure that the script does the calculation (ie. adding vlaues that are not duplicated in the first available empty row), starting in cel C10 in "Sheet2". It would be also great if I can somehow change that range if I need to: https://docs.google.com/spreadsheets/d/1abESAXFrOHoqRQRNqQGfmAFxlbD10wlprAf1tca4y7o/edit#gid=132361488

谢谢！

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

[1] Google脚本:附加新值在另一张纸上的列中

推荐答案

target_sheet.getRange(last_row,1,resultArray.length,resultArray[0].length)
.setValues(resultArray);

此行

• 取出存储在变量target_sheet
中的工作表
• 访问定义的范围
• 设置值

• Takes the sheet stored in the variable target_sheet
• Accesses the defined range
• Sets values

访问代码中使用的范围的语法为 getRange(行，列，numRows，numColumns)

The syntax to access a range used in your code is getRange(row, column, numRows, numColumns)

其中起始列是数字-在您的情况下，它是1，它对应于列A.

whereby the start column is numerical - in your case it's 1 which corresponds to column A.

如果要将起始列从A修改为B:

If you want to modify the start column from A to B:

只需更改您的范围定义

getRange(last_row,1,resultArray.length,resultArray[0].length)

到

getRange(last_row,2,resultArray.length,resultArray[0].length)

这篇关于Google脚本:将值从一张纸复制到另一张纸以确定范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！