将动态范围从一个电子表格复制到另一个使用getfileid调用的电子表格 [英] copy dynamic range from one spreadsheet to another spreadsheet called with getfileid

问题描述

我正在尝试执行以下操作:

I'm trying to do the following:

• 浏览客户"中的"A"列，并获取相应的工作表名称.
• 获取工作表并将工作表中所有使用的范围(例如(A; B; C; etc))复制到B列中引用的特定文件中.

这是我到目前为止编写的代码:

Here's the code I've written so far:

function updatefile() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('CUSTOMER')
  var data = sh.getRange("A2:B12").getValues(); // get the range of non empty cells
  for (var i = 1; i < data.length; i++) {
    var source = ss.getSheetByName(data[i][0]);
    var range = source.getRange('C3:M1003'); //assign the range you want to copy
    var data = range.getValues();

    var destination = SpreadsheetApp.openById(data[i][1]); // open the corresponding file by using the file id in the second column 
    var destbodya = destination.getSheetByName('UPDATE ON ORDERS');
    destbodya.getRange(ts.getLastRow()+1, 1,49,7).setValues(data); //you will need to define the size of the copied data see getRange()
  }
}

链接到我的示例电子表格

推荐答案

经过大量的搜索和试验，我终于可以使用它了.对于那些面临这样的挑战的人.干杯

I finally got this to work after a lot of searching and trials. for those who comes by such challenges. Cheers

function updatefile() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('CUSTOMER')
  var data = sh.getRange("A2:B73").getValues(); // get the range of non emmpty cells
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] != '') {
      var source = ss.getSheetByName(data[i][0]);
      var range = source.getRange('C3:M1003'); //assign the range you want to copy
      var valuedata = range.getValues();

      //var destination = SpreadsheetApp.openById("xxxxxxxxxxxxrrrrrsomeID"); // enter file ID directly or the nest line
      var destination = SpreadsheetApp.openById(data[i][1]); // Iterate through the range and open the corresponding file by using the file id in the secound column 
      var dest_1body = destination.getSheetByName('blah blah sheetname'); // first sheet destination to copy data to
      dest_1body.getRange("A2:K1002").setValues(valuedata); //you will need to define the size of the copied data see getRange()

      var destination = SpreadsheetApp.openById(data[i][1]); // open the coresponding file by using the file id in the secound column
      var dest_2body = destination.getSheetByName('blah blah sheetname'); // second sheet to copy same data to

      dest_2body.getRange("A28:K1028").setValues(valuedata); //you will need to define the size of the copied data see getRange()
    }
  }
}

这篇关于将动态范围从一个电子表格复制到另一个使用getfileid调用的电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！