CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Jomar Sevillejo
Jomar Sevillejo

Reputation: 1698

Returning a Range with some rows having merged columns in Google Appscript

I am to create a custom function for Google Sheets using Google AppScript. I have a set of data that my custom function will summarize and display the summary on another sheet.

I am able to create my on algorithm for the summary and return a range like this:

Current

enter image description here

Now I understand that an Appscript function returns a two dimensional array, but my problem is how do I merge some of the cells Using Appscript? Whenever there's a month? Like this:

Target

enter image description here

For the example above, the range I will be returning from my custom function will be

[
    ['January', ''],
    ['Project 1', '10 Hours'],
    ['Project 2', '20 Hours'],
    ['Project 3', '30 Hours'],
    ['Project 4', '40 Hours'],
    ['Project 5', '50 Hours'],
    ['February', ''],
    ['Project 1', '10 Hours'],
    ['Project 2', '20 Hours'],
    ['Project 3', '30 Hours'],
    ['Project 4', '40 Hours'],
    ['Project 5', '50 Hours']
]

So how do I tell AppScript to tell GooglSheets that ['January', ''], and ['February', ''], are cells to be merged?

If there's a function in Excel that can do this, I am also open to other methods. :)

Thanks

Upvotes: 4

Views: 1560

Answers (2)

tagplus5
tagplus5

Reputation: 336

This example copy table from Sheet1 to Sheet2 to 3 row and 4 column.

function myFunction() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet1 = ss.getSheets()[0];
  var data = sheet1.getDataRange().getValues();   
  var mergeRows = [];  
  for(var i = 0; i < data.length; i++) {    
    if (data[i][1] == "") {
      mergeRows.push(i);      
    }
  }  
  var startRow = 3;  
  var startCol = 4;  
  var sheet2 = ss.getSheets()[1];  
  sheet2.getRange(startRow, startCol, data.length, data[0].length).setValues(data);  
  for(var i = 0; i < mergeRows.length; i++) {    
    sheet2.getRange(startRow + mergeRows[i], startCol, 1, 2).merge().setHorizontalAlignment('center');
  }  
}

Upvotes: 2

Tovly Deutsch
Tovly Deutsch

Reputation: 663

You would have to use the merge() or mergeAcross() method. Something like

sheet.getRange('A1:B1').merge();
sheet.getRange('A7:B7').merge();

Upvotes: 1

Related Questions