Automatically Merge Group of Cells Below a Text Value

Question

I'm building out a scheduling assistant; the look is great, but it is very tedious to Merge & highlight each schedule block individually, so naturally I turned to Apps Script.

I'm entering different Names (text values) on the schedule that may or may not be different every time. The catch is that, if the text = "test 1", "test 2" or "test 3", I'm looking to merge that cell with ONE cell below; however, if there is any other text value, I want to merge that cell with four cells below.

My goal is to use a "Transform" button to run a script that would take any cell that has a text value on the schedule, evaluate it, and merge & center that text value with the correct # of cells.

My plan is to use Conditional Formatting to highlight these blocks after.

Thanks to @Bas van der Linden, I had a script that was working perfectly before I realized I needed to edit. Logically, I want my code to read like this:

If the cell is blank, do nothing;

If the cell value = "test 1", "test 2", or "test 3", merge vertically with a single cell below;

If the cell value = any other word, merge vertically with 4 cells below it;

I'm very confused with the cell variable, I thought this would work. This is my new attempt:

function Merge2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Create Schedule');
  const range = s.getRange("W4:AC22");
  
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();

  for (let i = 1; i <= numCols; i++) {
    for (let j = 1; j <= numRows; j++) {
      const cell = range.getCell(j, i)
      
      if (!cell.isBlank()){
      
      if(cell == 'test' or 'test 2' or 'test 3')
        cell.offset(0,0,2,1).activate().mergeVertically(){
        
      if(cell <= ""
         cell.offset(0, 0, 5, 1).activate().mergeVertically(){        
    }
  }
}

I've also linked a sample sheet, because I'm sure this sounds very confusing without seeing what I've done thus far:

https://docs.google.com/spreadsheets/d/1KSYihfSaqDxl58J6CLubEsFFeAhltq__CV__wXT9whU/edit#gid=0

Answer 1

Answer to the original question

I've created the following function:

function Merge() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('Schedule');
    const range = s.getRange("B3:H20");

    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();

    for (let i = 1; i <= numCols; i++) {
        for (let j = 1; j <= numRows; j++) {
            const cell = range.getCell(j, i)
            if (!cell.isBlank()) {
                cell.offset(0, 0, 7, 1).activate().mergeVertically()
            }
        }
    }
}

You can assign this script to your button.

In my example the range is B3:H20, but you might want to increase the range depending on the number of rows you want. The range you pick should not include the table headers or the column that has the times in it. The script assumes you don't have cells spanning 2 or more columns.

Once you've specified the range the script will check every cell in the range. If the cell is not empty it will try to merge the active cell with a number of cells below it.

Updated implementation after update original post

function Merge() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('Schedule');
    const range = s.getRange("W4:AC22");

    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();

    for (let i = 1; i <= numCols; i++) {
        for (let j = 1; j <= numRows; j++) {
            const cell = range.getCell(j, i);
            if (!cell.isBlank()) {
                const cellValue = cell.getValue();
                if (
                    cellValue == 'test' ||
                    cellValue == 'test 2' ||
                    cellValue == 'test 3'
                ) {
                    cell.offset(0, 0, 2, 1).activate().mergeVertically();
                } else {
                    cell.offset(0, 0, 5, 1).activate().mergeVertically();
                }
            }
        }
    }
}

This updated implementation fixes some problems in the updated post.

The reference to a cell is not the same as the value of that cell. So if you want to create an if statement using the value of a cell you need to call the getValue method on that cell reference.

Other problems were just syntax errors. In Javascript an or operator is written like this: ||.

If there are some ranges or offsets you want to change, you are of course free to do so.