CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Ethan
Ethan

Reputation: 837

How Can I Change My Range to Include Merged Cells?

I am trying to copy and paste "A4:J4" and "A5:H5" but I am getting an error because A5 is merged. Also "STAGE 3" only pastes in "G4" in Sheet2. How can I copy and paste this data to Sheet2 with merged cells? 

function hi()
{
   var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
   var yourRange = sheet.getRange("A4");
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var destSheet = ss.getSheetByName('Sheet2');
   var yourLastColumn=sheet.getRange("A4:4").getLastColumn();
   var range = ss.getRangeByName("A4");
   var yourLastRange=sheet.getRange("A4").offset(0, yourLastColumn-1);
   var rsltRange1 = yourLastRange.getNextDataCell(SpreadsheetApp.Direction.PREVIOUS);
   var rsltRange2 = sheet.getRange("A4:" + rsltRange1.getA1Notation()); 
  if (rsltRange2.isPartOfMerge()) {
    var rangeTest = rsltRange2.getMergedRanges()[0];
  } else {
  }
   Logger.log(rangeTest.getA1Notation());
   var destRange = destSheet.getRange("A4");
   rsltRange2.copyTo(destRange, {contentsOnly: false});
 }

enter image description here

Upvotes: 0

Views: 99

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

Here is a simplified version of your code that should copy your range correctly no matter if the range is merged or not:

function hi(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var destSheet = ss.getSheetByName('Sheet2');
  var startColumn=1;
  var startRow=4;
  var numRows=2;
  var numColumns=sheet.getLastColumn()-startColumn+1;
  var range=sheet.getRange(startRow, startColumn, numRows, numColumns);
  var destRange = destSheet.getRange("A4");
  range.copyTo(destRange);
 }

Upvotes: 1

Related Questions