CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user13509354
user13509354

Reputation:

Copy not contiguous selected rows from a spreadsheet to another

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');
}

Upvotes: 0

Views: 182

Answers (2)

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to copy the columns "C" to "E" of the selected rows in the source sheet to the destination sheet using Google Apps Script.
  • When the values are copied, you want to put the value of "Sent" to the column "H" in the source sheet.
  • In your case, the selected rows are not only the continuous rows, but also the discrete rows.

Modification points:

  • 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.
  • In order to put the value of "Sent", I used the range list.

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

Modified script:

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
}
  • In this case, both the continuous rows and the discrete rows can be used.
  • When the discrete rows are selected, in this sample, the order of select is the order of the values. Please be careful this.

References:

Upvotes: 3

ziganotschka
ziganotschka

Reputation: 26796

In addition to the elegant solution proposed by Tanaike, I would like to add for completion's sake a simple solution:

  • You are defining var height = sourceSheet.getActiveRange().getHeight();

  • The method getHeight() returns the height of an (adjacent) range.

  • If instead you want to copy all the range until the last data containing row - including empty rows, you can define instead:

    var height = sourceSheet.getActiveRange().getLastRow() - last + 1;

  • Now you will select and copy al the rows between the actively selected one and the last data containing row - including empty rows inbetween.

  • Mind that for large amounts of data, Tanaike's solution will be more efficient.

Upvotes: 2

Related Questions