If cells of Column A are merge, exact cells to be merged in Column E

1
votes

My sheet consists of details of working hours of crew in shifts. Column A is serial no. Column E is total duty hours. One day duty consists of smaller shifts and some details like S.No, Name, crew id gets repeated.

Initial data

I want to merge column with same cell values (Column A & Column E). I have been able to merge Column A of S.No (thanks to @Tanaike from this Forum) and want to do same thing for Column E.

Achieved so far

What i want

Condition - If Column A is merged, exactly no of cells should merge in Column E. So, if A11, A12 are merged = E11, E12 should merge; A13 not merged = E13 not merged; A14, A15, A16, A17 are merged = E14, E15, E16, E17 should merge. Thanks.

Relevant Code so far -

   // merge columns vertically for same cell value for Column A
  var start = 10; //data starts from row 10
  var c = {};
  var k = "";
  var offset = 0;

  // Retrieve values of column A
  var data = destSheet.getRange(start, 1, lastRow-2, 1).getValues().filter(String);

  // Retrieve the number of duplication values.
  data.forEach(function(e){c[e[0]] = c[e[0]] ? c[e[0]] + 1 : 1;});

  // Merge cells.
  data.forEach(function(e){
    if (k != e[0]) {
      destSheet.getRange(start + offset, 1, c[e[0]], 1).merge();
      offset += c[e[0]];
    }
    k = e[0];
  });
1
google-apps-scriptgoogle-sheets
Can you give a bit more detailed information about what yoou are trying to do? Are cells in column A merged in the original or destination spreadsheet? And where do you want to merge cells in column E accordingly? Can you reduce your provided code to what is relevant for your specific question? And maybe provide a test spreadsheet for visualization? – ziganotschka
@ziganotschka thankyou so much for the interest. I have edited my original question as suggested by you. Please have a look and let me know what more information do you require. – Abhay Singh Chauhan

1 Answers

1
votes

Two approaches to solve your issue

  1. Apps Script offers methods like isPartOfMerge() and mergeVertically() which would allow you to transfer the merge formatting from column A to column E.
  • You can do it by looping through all merged ranges in column A, retreiving their start and end row, and merge the respective ranges in column E:
  var range = destSheet.getRange(1,1, destSheet.getLastRow(), 1);
  var mergedRanges = range.getMergedRanges();
  for (var i = 0; i < mergedRanges.length; i++) {
    Logger.log(mergedRanges[i].getA1Notation());
    var start = mergedRanges[i].getRow();
    var end = mergedRanges[i].getLastRow();
    var destinationRange =  destSheet.getRange(start, 5, end - start + 1, 1);
    destinationRange.mergeVertically();
  }
  1. You can copy the formatting from the entire column A to column E - this will copy the merging
  var range = destSheet.getRange(1,1, destSheet.getLastRow(), 1);
  var destinationRange = destSheet.getRange("E1");
  range.copyTo(destinationRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);