将不连续的选定行从电子表格复制到另一个 [英] Copy not contiguous selected rows from a spreadsheet to another

问题描述

使用该脚本可以将所选行从电子表格复制到另一行，从C列复制到E列.电子表格，在H列中显示已复制行的 sent 值.

问题是该脚本采用了连续范围内选择的行，因此没有复制的可能性.连续的选定行.

我该如何解决?

With this script is possible to copy the selected rows from a spreadsheet to another, from the column C to column E.
When the script is terminated, in the "source" spreadsheet, in column H appears the value sent for the rows copied.

The problem is that the script take the rows selected in a contiguous range and so not with the possibility to copy not contiguous selected rows.

How can I solve this?

function main() {

transfer("....", "Foglio1", "Foglio1");

}

function transfer(targetId, sourceSheetName, targetSheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var last = sourceSheet.getActiveRange().getRow();
  var height = sourceSheet.getActiveRange().getHeight();  
  var data = sourceSheet.getRange(last, 3, height, 3).getValues();

  // copy data
  var ss2 = SpreadsheetApp.openById(targetId);
  var targetSheet = ss2.getSheetByName(targetSheetName);
  //get last row
  var lastRow = targetSheet.getLastRow();
  //write data
  targetSheet.getRange(lastRow + 1, 3, data.length, data[0].length)
           .setValues(data);
  
  sourceSheet.getRange(last, 8, height).setValue('Sent');
}

推荐答案

我相信您的目标如下.

• 您要复制列"C"到"E"使用Google Apps脚本将源工作表中所选行中的选定行转移到目标工作表中.
• 在复制值时，您想将已发送"的值放在到"H"列；在资料表中.
• 在您的情况下，选定的行不仅是连续的行，而且还是离散的行.

• 在这种情况下，为了检索选择的范围，我想建议在Sheets API中使用 getActiveRangeList()和电子表格sheets.values.batchGet的方法.
  • 我认为电子表格服务也可以实现您的目标.但是我认为使用Sheets API时，过程成本会很低.因此，在这个答案中，我想提出使用Sheets API的方法.
  • In this case, in order to retrieve the selected ranges, I would like to propose to use getActiveRangeList() and the method of spreadsheets.values.batchGet in Sheets API.
    • I think that Spreadsheet service can also achieve your goal. But I thought that when Sheets API is used, the process cost will be low. So in this answer, I would like to propose the method using Sheets API.

    当以上几点反映到您的脚本中时，它如下所示.

    When above points are reflected to your script, it becomes as follows.

    在这种情况下，将修改 transfer().在使用此脚本之前，请在高级Google服务中启用Sheets API .

    In this case, transfer() is modified. Before you use this script, please enable Sheets API at Advanced Google services.

    function transfer(targetId, sourceSheetName, targetSheetName) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sourceSheet = ss.getSheetByName(sourceSheetName);
      
      // --- I modified below script.
      var ranges = sourceSheet.getActiveRangeList().getRanges().reduce((o, r) => {
        var row = r.getRow();
        var numRows = r.getNumRows();
        o.getValues.push(`${sourceSheetName}!C${row}:E${row + numRows - 1}`);
        o.setValue.push(`${sourceSheetName}!H${row}:H${row + numRows - 1}`);
        return o;
      }, {getValues: [], setValue: []});
      var data = Sheets.Spreadsheets.Values.batchGet(ss.getId(), {ranges: ranges.getValues}).valueRanges.reduce((ar, v) => ar.concat(v.values), []);
      
      data = data.map(r => !r ? Array(3).fill("") : (r.length < 3 ? r.concat(Array(3 - r.length).fill("")) : r));  // Added by OP's 3rd question in the comment.
      // ---
      
      // copy data
      var ss2 = SpreadsheetApp.openById(targetId);
      var targetSheet = ss2.getSheetByName(targetSheetName);
      //get last row
      var lastRow = targetSheet.getLastRow();
      //write data
      targetSheet.getRange(lastRow + 1, 1, data.length, data[0].length).setValues(data);  // Modified by OP's 2nd question in the comment.
      sourceSheet.getRangeList(ranges.setValue).setValue('Sent');  // Modified
    }
    

    • 在这种情况下，连续行和离散行都可以使用.
    • 在选择离散行时，在此示例中，选择顺序是值的顺序.请注意这一点.
    • getActiveRangeList()
    • 方法:sheetssheets.values.batchGet
    • getRangeList(a1Notations)

    这篇关于将不连续的选定行从电子表格复制到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！