/
Código.gs
78 lines (62 loc) · 2.6 KB
/
Código.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
/**
* @OnlyCurrentDoc
*/
function onOpen() {
SpreadsheetApp.getUi().createMenu('breakApart')
.addItem('Break apart selected ranges', 'breakApart')
.addToUi();
}
/**
* Menu command
*/
function breakApart() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
// Range to unmerge in B1
breakApartAux(ss.getActiveSheet(), ss.getActiveSheet().getRange(ss.getRange('B1').getValue()));
}
/**
* Breaks apart merged cells in range
* @param {sheet} sheet Sheet of range
* @param {range} rangeToSplit Cell or cells that intersect with whole merged range
*/
function breakApartAux(sheet, rangeToSplit) {
// Current spreadsheet & sheet
let ss = SpreadsheetApp.getActiveSpreadsheet();
let activeSSId = ss.getId();
let activeSId = sheet.getSheetId();
// Get sheet's merges using advanced sheets service
let merges = Sheets.Spreadsheets.get(activeSSId).sheets.find(s => s.properties.sheetId == activeSId).merges;
if (merges == undefined) SpreadsheetApp.getUi().alert('No merged cells found in sheet.');
else {
// Logger.log(merges);
// Cells to merge R/C
let rowS = rangeToSplit.getRow();
let rowE = rowS + rangeToSplit.getNumRows() - 1;
let colS = rangeToSplit.getColumn();
let colE = colS + rangeToSplit.getNumColumns() - 1;
// Find overlapping merged range
// Advanced service merged ranges start in 0 and are right-open [..)
let merge = merges.find(m => {
let mRowS = m.startRowIndex + 1;
let mRowE = m.endRowIndex;
let mColS = m.startColumnIndex + 1;
let mColE = m.endColumnIndex;
// Check for overlapping
return ((rowS >= mRowS && rowS <= mRowE) ||
(rowE <= mRowE && rowE >= mRowS) ||
(rowS < mRowS && rowE > mRowE)) &&
((colS >= mColS && colS <= mColE) ||
(colE <= mColE && colE >= mColS) ||
(colS < mColS && colE > mColE));
})
// Overlapping range?
if (merge == undefined) SpreadsheetApp.getUi().alert('No merged cells found in specified range.');
else {
// Break apart whole range
ss.getActiveSheet().getRange(merge.startRowIndex + 1,
merge.startColumnIndex + 1,
merge.endRowIndex - merge.startRowIndex,
merge.endColumnIndex - merge.startColumnIndex).breakApart();
}
}
}