Google Sheets – Copy and Paste Selection as Merged Cells

google sheetsgoogle-apps-script

I'm going to be honest; my biggest issue is describing what I wish to accomplish. I can't find the right word for it, so the title might not make a lot of sense. But the pictures should be clear,

I want to take this sheet:

begin

Perform some operation, and end up with this:

end

Currently this takes a lot of effort, particularly for large amounts of values. I first have to move each row down to get white rows between each row with values, and then merge them individually. Takes a lot of clicks, and I do this semi-regularly. If there is an extension that does this, or a way to do this less laboriously, I would be very happy.

Best Answer

You can use the following Google Apps Script function to do it:

function mergeFunction() {
  const spreadsheet = SpreadsheetApp.getActive();
  const range = spreadsheet.getActiveRange();
  let numRows = range.getNumRows();
  let extra = 0;
  
  let idx = 1;
  while(idx <= numRows) {
    spreadsheet.getActiveSheet().insertRowAfter(idx+extra);
    spreadsheet.getActiveSheet().getRange(idx+extra, range.getColumn(), 2, 1).activate().mergeVertically();
    spreadsheet.getActiveRangeList().setVerticalAlignment('middle'); // not necessary if you don't care about cells being aligned vertically in the middle
    extra++;
    idx++;
  }
}

You can use this function by selecting your range of values and executing the function. You can also bind the function to a macro to make it easier to execute.

This solution assumes you're only dealing with a data range consisting of 1 column

Related Topic