Fill empty merged cells in range with top-left cell data that merged range

Admin 1
Viewed 82
+--------+-----+-----+
|   A    |  D  |  F  |
+--+--+--+--+--+--+--+
|  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+

From values merged ranges array:

[[A, , ], [D, ], [F, ]]

to this:
[[A, A, A], [D, D], [F, F]]

But looks like this code not work for me. Where is problem or is there better solution ?

let SpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
let Sheet = SpreadSheet.getSheets()[0];  
let CategoryRange = Sheet.getRange(1,1,1,Sheet.getLastColumn());
let CategoryMergedRanges = CategoryRange.getMergedRanges();
let CategoryData = CategoryRange.getValues();

  
for (let i = 0; i < CategoryMergedRanges.length; i++) {
    let CategoryName = "";
    for (let j = CategoryMergedRanges[i].getColumn(); j < CategoryMergedRanges[i].getColumn() + CategoryMergedRanges[i].getNumColumns(); j++) {
      if (CategoryData[0][j - 1].toString() != "") {
        CategoryName = CategoryData[0][i - 1];
      } 
    }    
    for (let j = CategoryMergedRanges[i].getColumn(); j < CategoryMergedRanges[i].getColumn() + CategoryMergedRanges[i].getNumColumns(); j++) {
        CategoryData[0][i - 1] = CategoryName;
    }
  }
Logger.log(CategoryData);
google-apps-script google-sheets javascript
1 Answers

Explanation:

  • According to your explanation: A, D and F are merged into 3,2 and 2 number of cells respectively.

Meaning that the merged ranges would be:

[[A, , ], [D, ], [F, ]]

Assuming:

  • merged A1:C1 with value A in cell A1
  • merged D1:E1 with value D in cell D1
  • merged F1:G1 with value F in cell F1

In order to fill in the empty cells using the first value of each array you can use the fill() method.

The following script will give you the result in the screenshot.

Solution:

function myFunction() {
  
let SpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
let Sheet = SpreadSheet.getSheets()[0];  
let CategoryRange = Sheet.getRange(1,1,1,Sheet.getMaxColumns());
let CategoryMergedRanges = CategoryRange.getMergedRanges();
let CategoryData = CategoryRange.getValues();
  
fAr = [];

CategoryMergedRanges.forEach(cmr=>
   { 
       let tempA=cmr.getValues().flat();
       fAr.push(tempA.fill(tempA[0],1));
   }
);

let ffAr = [fAr.flat()];
Sheet.getRange(2,1,1,ffAr[0].length).setValues(ffAr);
}

Note that:

fAr returns [[A, A, A], [D, D], [F, F]] which should be the desired format that you want.

ffAr returns [[A, A, A, D, D, F, F]] which is what I use to set the values back to the sheet (Optional).

example

user_112252910
Related