如何将范围从一张纸复制到另一张纸的区域，然后重复复制到下一行 [英] How to copy a range from one sheet to a range in another sheet, then repeat copying to the next row down

问题描述

我正在寻找一个Google Apps脚本，将一个工作表中的单元格区域复制到另一个工作表中，然后当用户更改了第一个范围值时，将相同的范围(新值)复制到从第一.这是我到目前为止所拥有的:

I am looking for a google apps script to copy a range of cells in one sheet to another sheet, then when the first range values are user changed, copy the same range (new values), to the next row down from the first one. Here is what i have so far:

function copyToDatasheet() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s1 = ss.getSheetByName('Mobile Form');
     var s2 = ss.getSheetByName('Data'); 

     s1.getRange('B2').copyTo(s2.getRange('P6'), {contentsOnly:true})
     s1.getRange('B4').copyTo(s2.getRange('Q6'), {contentsOnly:true})
     s1.getRange('B6').copyTo(s2.getRange('R6'), {contentsOnly:true})
     s1.getRange('B8').copyTo(s2.getRange('S6'), {contentsOnly:true})
     s1.getRange('B10').copyTo(s2.getRange('O6'), {contentsOnly:true})
}

所有这些操作就是将s1范围复制到s2范围，从第6行开始，这是我希望它开始的方式，但是在下一个条目上，我希望它向下移动到第7行，依此类推.重置s1范围并输入新值，然后将它们复制到s2的下一行中，使它们在下一次运行时动态下移.希望这是有道理的，我对应用程序脚本不了解很多，所以如果可能的话，解释一些功能会有所帮助.感谢任何人都可以提供的帮助.

All this does is copy s1 ranges to s2 ranges starting on row 6, which is how I want it to start but on the next entry I want it to move down to row 7 and so on. s1 ranges are reset and new values entered, copy them to s2 in next row down Having them dynamically move down on next run. Hope this makes sense, I do not have a ton of knowledge about apps script so explaining what some functions do would be helpful if possible. Thanks for any assistance anyone can offer.

推荐答案

对Sheet1进行编辑后，这将获得相同的数据范围.然后，所有这些数据将被附加到Sheet2.

This will get the same data range when there is an edit to the Sheet1. Then all of that data will then be appended to the Sheet2.

function getMultipleValues(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  var range = sheet.getRange(2,1,4,2); //getRange(starting row, starting column, ending row, ending column)
  var data = range.getValues();
  putMultipleValues(data)
}

function putMultipleValues(data){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet2');
  data.forEach(function(i){
    SpreadsheetApp.flush();
    sheet.appendRow(i)
  })
}

不要忘记设置触发器.在菜单=>编辑=>当前项目的触发器中.

Don't forget to set the triggers. In the menu => Edit => Current project's triggers.

这篇关于如何将范围从一张纸复制到另一张纸的区域，然后重复复制到下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！