0

I would like to write a script that copies the formatting, and the conditional formatting, that is applied to cell A1, and applies it to all of the other cells in the sheet.

However, I do not want to copy the merged-cell-status of A1.

For example, if A3 and B3 are merged, and if A1 is not merged with any other cell, then I want all of the formatting of A1 (both conditional and non-conditional) to be copied to the other cells, including A3 and B3, but in a way that preserves the merged status of A3 and B3 (or any other merged cells).

   A    B    C   
 _______________
1|   |    |    |
2|   |    |    |
3|        |    |
4|   |    |    |

I've tried the following functions, but both of these end up unmerging A3 and B3.

function copyFormat() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var source = ss.getRange('A1'); var destination = ss.getRange('A2:A'); source.copyTo(destination, {formatOnly:true}); }

and

function setFormat(){ var ss = SpreadsheetApp.getActive() var sheet = ss.getActiveSheet() var firstCell = sheet.getRange(1, 1, 1, 1) var dataRange = sheet.getDataRange() firstCell.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 1, dataRange.getNumRows()) }

1

This works. It applies the formatting from cell A1 to the rest of the cells in the spreadsheet upon any edit to those cells. See the commented lines for some configuration options:

function onEdit(e){           //This is activated each time a modification happens in the sheet
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();

  //var dataRange = sheet.getDataRange(); // This is the fastest option; only the cells that have content are reformatted
  //var dataRange = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()); // This applies to all columns and rows in sheet, regardless of whether they have data; may slow things down
  var dataRange = sheet.getRange(1,1,sheet.getLastRow()+2,sheet.getLastColumn()+2); // This applies the formatting to the active range with a 2 row and column buffer on all sides; hopefully not too slow

  var masterFormatCell = sheet.getRange(1, 1, 1, 1); // In this case, the Master Format Cell is A1 
  var mergedRanges = dataRange.getMergedRanges();    // Get list of merged ranges

  masterFormatCell.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 1, dataRange.getNumRows()); //Copy the Master Format Cell (i.e., A1) to the rest of the cells

  for (var i = 0; i < mergedRanges.length; i++) {    
    sheet.getRange(mergedRanges[i].getA1Notation()).merge(); // Re-merge the previously merged cells
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.