javascriptgoogle-chromegoogle-apps-scriptgoogle-sheetsgsheets

Copy selected/highlighted rows

This script is intended to copy the column values of rows which i highlighted/selected and paste to another googlesheet. However instead of copying column values of the rows which i selected, it is copying the first row column values instead.How can i fix it to copy and paste the rows which i selected/highlighted instead?

function copySelectedRows() {
  var sourceSpreadsheetId = "SOURCE_SPREADSHEET_ID";
  var targetSpreadsheetId = "TARGET_SPREADSHEET_ID";
  
  var sourceSheetName = "SourceSheetName";
  var targetSheetName = "TargetSheetName"
  
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
  
  var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
  var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
  
  var selection = sourceSheet.getSelection();
  var selectedRanges = selection.getActiveRangeList().getRanges();
  
  var targetData = [];
  
  selectedRanges.forEach(function (range) {
    var startRow = range.getRow();
    var numRows = range.getNumRows();
    var sourceRange = sourceSheet.getRange(startRow, 2, numRows, 4); // Assuming column B is index 2, column C is index 3, and column D is index 4
    var sourceValues = sourceRange.getValues();
    
    sourceValues.forEach(function (row) {
      targetData.push([row[0], "", "", row[1], row[2], row[3]]); // Copying values from column B, C, D to E, A, and F respectively
    });
  });
  
  var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, targetData.length, targetData[0].length); // Starting from column A (1)
  targetRange.setValues(targetData);
}
Solution

  • About your current issue of However instead of copying column values of the rows which i selected, it is copying the first row column values instead., I thought that the reason for your current issue is due to openById of var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);. In this case, please modify as follows.

    From:

    var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);

    To:

    var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // or SpreadsheetApp.getActive()

    References: