首页 文章

在Google文档中自动将数据从一个工作表移动到另一个工作表

提问于
浏览 次
1

我有一个电子表格，我跟踪我需要做的任务，一旦完成我在最后一列输入日期 . 我想要的是将完成的任务转移到第2页 .

目前我有一张名为SUD_schedule的表1，我希望将完成的数据行移动到名为SUD_archive的表2 . 我已经浏览了论坛帖子，我尝试过各种各样的脚本，但到目前为止还没有运气 . 我最接近的是这个脚本：

function onEdit() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();//Original sheet
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];//target sheet
   // to act on only one sheet, check the sheet name here:
  //If it si not first sheet, it will do nothing
  if (sheet1.getSheetName() != "SUD_schedule") {
    return; 
  }
  //Get Row and column index of active cell.
  var rowIndex = sheet1.getActiveRange().getRowIndex();
  var colIndex =  sheet1.getActiveRange().getColumnIndex();

  //If the selected column is 10th and it is not a header row
  if (colIndex == 16 && rowIndex > 1) {
    //Get the data from the current row
   var data = sheet1.getRange(rowIndex,1,1,9).getValues();
   var lastRow2; 
    (sheet2.getLastRow()==0)?lastRow2=1:lastRow2=sheet2.getLastRow()+1;
    //Copy the data to the lastRow+1th row in target sheet
    sheet2.getRange(lastRow2,1,1,data[0].length).setValues(data);
  }
}

列P（16）是任务完成日期，第1行被冻结并包含列 Headers .

任何人都可以帮助显示我出错的地方 .

亲切的问候Den

google-apps-script google-sheets

3 回答

  • 0

    我不确定您在下面使用的语法是完全正确的 .

    (sheet2.getLastRow()==0)?lastRow2=1:lastRow2=sheet2.getLastRow()+1;
sheet2.getRange(lastRow2,1,1,data[0].length).setValues(data);

    我所知道的将是肯定的，如果你一起省略变量lastRow2并使用它 .

    sheet2.getRange(getLastRow+1,1,1,data[0].length).setValues(data);
    回复于
  • 0

    您的代码不是通用的，您的目标更加复杂 . 以下将解决您的需求 .

    function onEdit(){

     var ss = SpreadsheetApp.getActiveSpreadsheet();

     var sheet1 = ss.getSheetByName('SUD_schedule');
     var sheet2 = ss.getSheetByName('SUD_archive');

     var dateColumn = "16";
     var array = []

     var range = sheet1.getRange(1, 1, sheet1.getLastRow(), dateColumn); 

      for (var i = 2; i <= sheet1.getLastRow(); i++) //i iterates from 2 as you say R1 is header
      {
        if(isValidDate(range.getCell(i, dateColumn).getValue()) == true) //checking if any values on column16 is valid date
        {
          data = sheet1.getRange(i, 1, 1, dateColumn).getValues(); //Getting the range values of particular row where C16 is date

          for (var j = 0; j < dateColumn; j++) //Adding the row in array
          {
          array.push(data[0][j]);
          }

        }
        if(array.length > 0)
        {
        sheet2.appendRow(array); //Appending the row in sheet2
        array = [];
        sheet1.deleteRow(i); //deleting the row in sheet as you said you want to move, if you copy remove this and next line
        i=i-1; //managing i value after deleting a row.
        }

      }


    }

//Below function return true if the given String is date, else false

    function isValidDate(d) {
      if ( Object.prototype.toString.call(d) !== "[object Date]" )
        return false;
      return !isNaN(d.getTime());
    }
    回复于
  • 0

    为了补充Joachin 's answer, here is how you can adapt that code if you don' t在最后一行中有日期 . 在下面显示的部分代码中，将 Lastcolumnumber 替换为您的上一列 .

    //Getting the range values of particular row where C16 is date
data = sheet1.getRange(i, 1, 1, LASTCOLUMNNUMBER).getValues(); 

//Adding the row in array
for (var j = 0; j < LASTCOLUMNNUMBER; j++)
    回复于

相关问题