Merging identical cells with Google Spreadsheet script

Question

I have a table with columns partner , id , email , status , type , group , details . There are duplicate values in the columns id , email , status , type , group .

 <!-- language: lang-none --> | partner | id | email | status | type | group | details | |---------|-----------|---------------------|--------|------|-------|----------------------| | YESTKBW | Wednesday | wednesday@gmail.com | YES | 0 | 11 | kgplqolgbmdxenwpehyn | | ZBSDCUY | Wednesday | wednesday@gmail.com | YES | 0 | 11 | upujkpekm tmqfzpca | | RBGBHNK | Wednesday | wednesday@gmail.com | YES | 0 | 11 | iky k gtlffdyukngwlo | | UXJGHLW | Saturday | saturday@gmail.com | NO | 0 | 11 | mjdmodggygxkydxifxny | | UZHNECM | Friday | friday@gmail.com | YES | 1 | 22 | wpboajbemueskbuyjqeg | | XACTUOK | Tuesday | tuesday@gmail.com | NO | 2 | 33 | rxnwkpmegng bobjdqlz | | EIOBZXS | Tuesday | tuesday@gmail.com | NO | 2 | 33 | rwqxxwoeanxqsbpbwx j |

I'm trying to make a script that will, with the same consecutive id values, merge cells in the columns id , email , status , type , group . Values partner and details do not need to touch.

An example of what should happen

 <!-- language: lang-none --> | partner | id | email | status | type | group | details | |---------|-----------|---------------------|--------|------|-------|----------------------| | YESTKBW | Wednesday | wednesday@gmail.com | YES | 0 | 11 | kgplqolgbmdxenwpehyn | | ZBSDCUY | | | | | | upujkpekm tmqfzpca | | RBGBHNK | | | | | | iky k gtlffdyukngwlo | | UXJGHLW | Saturday | saturday@gmail.com | NO | 0 | 11 | mjdmodggygxkydxifxny | | UZHNECM | Friday | friday@gmail.com | YES | 1 | 22 | wpboajbemueskbuyjqeg | | XACTUOK | Tuesday | tuesday@gmail.com | NO | 2 | 33 | rxnwkpmegng bobjdqlz | | EIOBZXS | | | | | | rwqxxwoeanxqsbpbwx j |

I understand that it is necessary to use the Range class's mergeVertically() method, but I do not have enough experience to solve this problem.

Community spirit ♦ one · Accepted Answer · 2016-08-21T15:07:24

OK. This question is no different from this. Hide rows with criteria on click .

Suppose that we have some method of merging cells:

 function verticallyMerger(sheet, blocks){ /* { columns: [], rows: [[index, count],[index, count]] } */ for (var i = 0; i < blocks.rows.length; i++){ for (var j = 0; j < blocks.columns.length; j++){ sheet.getRange(blocks.rows[i][0], blocks.columns[j], blocks.rows[i][1]).mergeVertically(); } } return sheet; }

Everything is pretty transparent:

go through the settings
take turns

It remains to add a more or less useful constructor tuners. For example,

 function blockBilder(sheet, initialBlocks){ var blocks = initialBlocks; blocks.rows = []; var values = sheet.getRange(1, blocks.mergerColumn, sheet.getLastRow()).getValues(); var isNew = true; var prevVal; for(var i = 0; i < values.length; i++){ if(prevVal === values[i][0]){ if(isNew){ blocks.rows.push([i , 2]); isNew = false; } else { blocks.rows[blocks.rows.length - 1][1]++; } } else { prevVal = values[i][0]; isNew = true; } } return blocks; }

Here, of course, without explanation, not everything is obvious. initialBlocks must represent an object like this

 { mergerColumn : 2 }

mergerColumn - column number, on the basis of which the conclusion about the need to merge will be made

Now it can be run

 function run(){ var sheet = SpreadsheetApp.getActiveSheet(); var blocks = blockBilder(sheet, {mergerColumn : 2, columns: [2, 3, 4, 5, 6]}); verticallyMerger(sheet, blocks); }

In blocks , one more property is added that is necessary for verticallyMerger - these are columns . It contains an array that lists the columns to be combined.

Here you can get a copy of the working example and the code in full.

Throws an error: TypeError: Cannot read property "rows" from undefined.
@MaksymKatsovets, see what you are initialBlocks as a block or initialBlocks .

Merging identical cells with Google Spreadsheet script

1 answer 1

More articles: