'Trying to copy merged cells from one sheet to another

I have a script that creates a new file, names it and copies data from a template. The problem I am having is getting all of the formatting over such as merged cells. Here is the code I have so far.

function createNewSheet(e) {
  const sh = e.range.getSheet();
  var name = sh.getRange(e.range.rowStart, 1).getValue();
  var crNew = SpreadsheetApp.create("Job Traveler-" + name);
  var ssNew = SpreadsheetApp.openByUrl(crNew.getUrl()).getId();
  importRange("1X4iOzc_shcOR8UM7OEM4F1-eR62YZmwKWyyEuu59Pf4","Job!A1:D", ssNew,"Sheet1!A1");
  copiedFormat();

}

function importRange(sourceId,sourceRange,destinationID,destinationRangeStart){

  const sourceSS = SpreadsheetApp.openById(sourceId);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVals = sourceRng.getValues();

  // Get formatting data
  const sourceFontStyle = sourceRng.getFontStyles();
  const sourceFontWeight = sourceRng.getFontWeights();
  const sourceFontBackgroundColor = sourceRng.getBackgrounds();

  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destinationSS.getSheetByName(destStartRange.getSheet().getName());

  destSheet.clear();

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVals.length,
    sourceVals[0].length
  );

  destRange.setValues(sourceVals);

   // Set formatting
  destRange.setFontStyles(sourceFontStyle);
  destRange.setFontWeights(sourceFontWeight);
  destRange.setBackgrounds(sourceFontBackgroundColor);

  SpreadsheetApp.flush();
  
}

Best

google-apps-script google-sheets

Solution 1:^[1]

Description

I've only provided the addtional steps to duplicate merged cells from one range to another. I leave it to you to incorporate the relavent code to your script. I've included a debugging step 'merged.forEach()' which you can remove for production.

Script

function createNewSheet() {
  try {
    // in my case source and destination are the same spreadsheet but different sheets
    // and I have made the ranges arbitrary
    importRange("Sheet5","Sheet5!C6:L11","Sheet6","Sheet6!E11")
  }
  catch(err) {
    console.log(err)
  }
}

function importRange(sourceId,sourceRange,destinationID,destinationRangeStart) {
  try {
    var sourceSS = SpreadsheetApp.getActiveSpreadsheet();
    var sourceRng = sourceSS.getRange(sourceRange);
    var sourceVals = sourceRng.getValues();

    var destinationSS = sourceSS;
    var destStartRange = destinationSS.getRange(destinationRangeStart);
    var destSheet = destinationSS.getSheetByName(destStartRange.getSheet().getName());
    var row = destStartRange.getRow();
    var col = destStartRange.getColumn();
    var rows = sourceVals.length;
    var cols = sourceVals[0].length;
    destSheet.getRange(row,col,rows,cols).setValues(sourceVals);

    var merged = sourceRng.getMergedRanges();
    merged.forEach( range => console.log(range.getA1Notation()));
    var rowOffset = destStartRange.getRow()-sourceRng.getRow();
    var columnOffset = destStartRange.getColumn()-sourceRng.getColumn();
    for( var i=0; i<merged.length; i++ ) {
      row = merged[i].getRow()+rowOffset;
      col = merged[i].getColumn()+columnOffset;
      rows = merged[i].getNumRows();
      cols = merged[i].getNumColumns();
      destSheet.getRange(row,col,rows,cols).merge();
    }
  }
  catch(err) {
    console.log(err)
  }
}

Reference

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	TheWizEd

'Trying to copy merged cells from one sheet to another

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]