javascript - 在不同电子表格之间传输数据 google SpreadSheets

Question

/* 
 **** Move a row onEdit determined by a specific Cell value***
 */
 
// Names of sheets
var sourceSheet = "datasource"
var destinationSheet = "datadestiny"
 
/* col: the column to watch,  
 * changeVal: what value you want to change,
 * del: do you want to delete after the change?
 */
var check = {
  "col":22,
  "changeVal": "OK",
  "del": true
  };
 
/* What you want to paste into the other sheet.
 * start: start column
 * cols: how many columns you want to copy
 */
var pasteRange = {
  "start": 1, 
  "cols": 2
  };
 
 
function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  
  if(sheet.getName() === sourceSheet){
    
    //Get active cell
    var cell = sheet.getActiveCell();
    var cellCol = cell.getColumn();
    var cellRow = cell.getRow(); 
    
    if(cellCol === check.col){
      if(cell.getValue() === check.changeVal){
        
        //Select the range you want to export
        var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
        
        
        //Select the past destination
        var dest = SpreadsheetApp.openById('1sMenz31IMkCsa20ORue09EVMKyNujHCqgnM6C5HD_yo');

        var pasteDestination = dest.getSheetByName(destinationSheet);
        var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;
       
        //Copy the row to the new destination
        exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                           SpreadsheetApp.CopyPasteType.PASTE_VALUES);
        

        //If delete is true delete after copying
        if(check.del){
          sheet.deleteRow(cellRow);
        };
      };
    };
  };
 };

我需要一些帮助，我正在尝试将数据从一个电子表格传输到另一个 googleSpreadSheets 电子表格

电子表格 - 来源 - https://docs.google.com/spreadsheets/d/1ki_EKHYVifJzm3pWnSs_GlnJPrIzN2bEZHQmexPgF_M/edit#gid=0

电子表格 - 命运 - https://docs.google.com/spreadsheets/d/1sMenz31IMkCsa20ORue09EVMKyNujHCqgnM6C5HD_yo/edit#gid=0

onEdit() 函数无法传输数据。

Answer 1

尝试这样:

第一个问题是您需要一个可安装的触发器。您可以手动或使用软件创建它。你的选择。第二个问题是复制仅适用于同一个电子表格。因此，您可以使用 getValues() 和 setValues() 来代替，就像我在下面所做的那样:

function testonEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=='datasource') {
    if(e.range.columnStart==22 && e.value=="OK"){
      var dss=SpreadsheetApp.openById('11f9yyZZs5Ret6YoXinvne0eCXWWaXdj12t6MnrT43vM');
      var dsh=dss.getSheetByName('datadestiny');
      var v=sh.getRange(e.range.rowStart,1,1,2).getValues();
      dsh.getRange(dsh.getLastRow(),1,v.length,v[0].length).setValues(v);
      sh.deleteRow(e.range.rowStart);
    }
  }
}

我对代码进行了一些修改以适应我的测试。我尝试让 onEdit() 函数尽可能简单，因为您希望它们尽可能快，因为您只有 30 秒的时间来完成它们。