Google Spreadsheets（应用程序脚本）将一行信息复制到另一个电子表格 [英] Google Spreadsheets (app-script) copying a row of information to another spreadsheet

问题描述

如果有任何信息输入到第5列，我需要将该行数据复制到另一个电子表格。我的函数中的第一个if（）是用于其他的东西，它会在任何信息输入到第4列的时候对数据进行排序，但是我需要另一个if（）来工作，但它没有，我看过其他问题，但我找不到一个答案，将在我的情况。

  function onEdit（event）{
 var sheet = event.source.getActiveSheet（）; 
 var editedCell = sheet.getActiveCell（）; 
 var columnToSortBy = 4; 
 var columnToArchive = 5; 
 var tableRange =A2：F！; 
 
 if（editedCell.getColumn（）== columnToSortBy）{
 var range = sheet.getRange（tableRange）; 
 range.sort（{column：columnToSortBy}）; 
} 
 else {
 if（editedCell.getColumn（）== columnToArchive）{
 var rowToBeMoved = editedCell.getRow（）; 
 var rangeToBeMoved = sheet.getRange（A+ rowToBeMoved +：F+ rowToBeMoved）; 
 var values = rangeToBeMoved.getValues（）; 
 var archiveSpreadSheet = SpreadsheetApp.openById（0AroBvchobu2edHNXQ3ZUQjI5TWJtWWZwa1UtcExPNnc）; 
 var archiveSheet = archiveSpreadSheet.getSheetByName（archive）; 
 archiveSheet.appendRow（values）; 
} 
} 
} 
  

我发现问题与第二个if块中的最后三行一致。看起来，在var values = rangeToBeMoved.getValues（）;这一行之后，脚本停止运行，因为如果我在该行之后或之前的任何地方放置了Browser.msgBox（hi），则会显示该消息，但如果我把它放在下一行之后，它不会出现。问题是与.openById（）？ HELP！

解决方案

这段代码正在工作： $ b

UPDATE：10-01-2013

您需要使用columnIndex而不是getColumn：

  function onEdit（event）{
 //设置当前电子表格的项目
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sh = ss.getActiveSheet（）; 
 var row = sh.getActiveRange（）。getRowIndex（）; 
 var col = sh.getActiveRange（）。getColumnIndex（）; 
 var columnToSortBy = 4; 
 var columnToArchive = 5; 
 var tableRange =A2：F！; 
 
 if（col == columnToSortBy）{
 var range = sh.getRange（tableRange）; 
 range.sort（{column：columnToSortBy}）; 
 
 
 if（col == columnToArchive）{
 var rangeToBeMoved = sh.getRange（A+ row +：F+ row）; 
 var values = rangeToBeMoved.getValues（）; 
 var archiveSpreadSheet = SpreadsheetApp.openById（your key）; 
 var archiveSheet = archiveSpreadSheet.getSheetByName（Sheet1）; 
 archiveSheet.appendRow（values [0]）; 
 
 
  

appendRow 只接受一维数组。 getRange 将产生一个二维数组。添加一个零（在方括号之间）将使其成为一维数组。

I need to, if any information is entered into column 5, copy that row of data to another spreadsheet. The first if() in my function is for something else, it sorts the data when any info is entered into column 4, which works, but I need the other if() to work, but it doesn't, I have looked at other questions, but I can't find an answer that will work in my situation.

function onEdit(event) {
  var sheet=event.source.getActiveSheet();
  var editedCell=sheet.getActiveCell();
  var columnToSortBy=4;
  var columnToArchive=5;
  var tableRange= "A2:F!";

  if(editedCell.getColumn() == columnToSortBy){
    var range=sheet.getRange(tableRange);
    range.sort( {column : columnToSortBy} );
  }
  else{
    if(editedCell.getColumn() == columnToArchive){
      var rowToBeMoved=editedCell.getRow();
      var rangeToBeMoved=sheet.getRange("A" + rowToBeMoved + ":F" + rowToBeMoved);
      var values=rangeToBeMoved.getValues();
      var archiveSpreadSheet=SpreadsheetApp.openById("0AroBvchobu2edHNXQ3ZUQjI5TWJtWWZwa1UtcExPNnc");
      var archiveSheet=archiveSpreadSheet.getSheetByName("archive");
      archiveSheet.appendRow(values);
    }
  }
}

I have identified that the problem is with the last three lines within the second if block. It seems that after the line "var values=rangeToBeMoved.getValues();", the script stops running because if a I put a Browser.msgBox("hi") after that line or anywhere before, that message appears, but if I put it after the next line, it does not appear. The problem is with the .openById()? HELP!

解决方案

This piece of code is working:

UPDATE: 10-01-2013
You need to use the columnIndex and not the getColumn:

function onEdit(event) {
  // set items for current spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var col = sh.getActiveRange().getColumnIndex();
  var columnToSortBy=4;
  var columnToArchive=5;
  var tableRange= "A2:F!";

  if(col == columnToSortBy){
    var range=sh.getRange(tableRange);
    range.sort( {column : columnToSortBy} );
  }

  if(col == columnToArchive){   
    var rangeToBeMoved=sh.getRange("A" + row + ":F" + row);
    var values=rangeToBeMoved.getValues();
    var archiveSpreadSheet=SpreadsheetApp.openById("your key");
    var archiveSheet=archiveSpreadSheet.getSheetByName("Sheet1");        
    archiveSheet.appendRow(values[0]);
  }
}    

The appendRow only accepts a 1D array. The getRange will result in a 2D array. Added a zero (between square brackets) will make it a 1D array.

这篇关于Google Spreadsheets（应用程序脚本）将一行信息复制到另一个电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！