How to Merge Cells/Columns Horizontally With Formatting in Google Sheets?

Question

I've found a manual way to merge cells with formatting using the following several steps and formulas:

Youtube video example below:

And the Sheet2 here:

How to Merge cells horizontally with formatting in Google Sheets?

Conditional formatting column E (range E1:E33):

=IFS(AND(C1="",D1=""),"$",AND(C1<>"",D1=""),C1&"#",AND(C1="",D1<>""),D1&"*")

Text is exactly:

—> set Background color to White

Text contains:

—> set Background color to Red

Text contains:

—> set Background color to Green

Conditional formatting column F (range F1:F33):

=RIGHT(E1:E,1)="$"

—> set Background color to White

=RIGHT(E1:E,1)="#"

—> set Background color to Red

=RIGHT(E1:E,1)="*"

—> set Background color to Green

Delete "$", "#" and "*" in range F1:F33.

My question is:

How to make the process simpler and automated with a script? possibly with less steps?

Thanks a lot for your help and ideas!

EDIT:

Answering the suggested answer

Google Spreadsheet script to merge cells in column A containing 'Hello' with the adjacent cell in column B

How my question is different?

If my understanding it correct, the .mergeAcross() action works to merge cells to keep only the top left content of the left column cell (column A) into the output cell (the merged result).

In my case that would not work to merge 2 cells and keep the content of the right column cell into the merged result.

For example:

When A1 is blank (A1="") and B1 is not blank (B1<>"" / B1=1) have the output cell return B1 content (C1 return "1").

Also it doesn't seem to address the formatting needed criteria.

For example:

If A1="", and B1<>"" / B1=1, and B1 cell background is Red, return B1 content and formatting in the output cell (C1 return 1 with red as cell background color).

But thanks a lot for the suggestion about .mergeAcross() action. I didn't know about it and it sure is valuable to know.

user_7108653 · Answer

You want to achieve the following situation using Google Apps Script.
- You want to obtain the column "H" by merging the values and formats of the column "C" and "D".

If my understanding is correct, how about this sample script? The flow of this script is as follows.

Retrieve values and background colors from the column "C" and "D".
Merge the retrieved values and background colors in a loop.
Put the values and background colors.

Sample script:

function myFunction() {
  var destColumn = "H"; // Please set the destination column.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  var range = sheet.getRange("C1:D" + sheet.getLastRow());
  var values = range.getValues();
  var backgrounds = range.getBackgrounds();
  var obj = values.reduce(function(o, e, i) {
    if (e[0] && !e[1]) {
      o.values.push([e[0]]);
      o.backgrounds.push([backgrounds[i][0]]);
    } else if (!e[0] && e[1]) {
      o.values.push([e[1]]);
      o.backgrounds.push([backgrounds[i][1]]);
    } else {
      o.values.push([""]);
      o.backgrounds.push([""]);
    }
    return o;
  }, {values: [], backgrounds: []});
  var destRange = sheet.getRange(destColumn + "1:" + destColumn + sheet.getLastRow());
  destRange.setValues(obj.values);
  destRange.setBackgrounds(obj.backgrounds);
}

When you use this script, please copy and paste this to the script editor of the shared Spreadsheet. And run the function of myFunction.
In this script, the result is put to the column "H" using the column "C" and "D" of "Sheet2".

Note:

In this script, when both cells of column "C" and "D" at the same row have values, the empty value is put. If you want to modify this, please modify the script.
This sample script is for your shared Spreadsheet. If you want to use this for other situation, this script is required to be modified. Please be careful this.

References:

If I misunderstood your question and this was not the result you want, I apologize.