-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathdropDownListsComplete.gs
252 lines (241 loc) · 11.7 KB
/
dropDownListsComplete.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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
function onOpen() {
var m = SpreadsheetApp.getUi().createMenu('Drop Down');
m.addItem('TEMPLATE','DROPDOWNLISTS').addToUi();
m.addItem('RUN DROP', 'onEdit').addToUi();
}
function depDrop_(range, sourceRange){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
}
function onEdit (){
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 1 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
else if (aColumn == 2 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
//if (aColumn == 8 && SpreadsheetApp.getActiveSheet()){
//var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
//var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
//depDrop_(range, sourceRange);
//}
//else if (aColumn == 9 && SpreadsheetApp.getActiveSheet()){
//var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
//var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
//depDrop_(range, sourceRange);
//}
}
function DROPDOWNLISTS() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('N:N').activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
spreadsheet.getRange('101:101').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().setValue('GROUPS');
spreadsheet.getRange('B1:D1').activate()
.mergeAcross();
spreadsheet.getCurrentCell().setValue('SUBGROUPS');
spreadsheet.getRange('E1:M1').activate()
.mergeAcross();
spreadsheet.getCurrentCell().setValue('SUB-SUBGROUPS');
spreadsheet.getRange('1:1').activate();
spreadsheet.getActiveRangeList().setFontWeight('bold')
.setHorizontalAlignment('center');
spreadsheet.getRange('A2').activate();
spreadsheet.getCurrentCell().setValue('MAIN');
spreadsheet.getRange('B2').activate();
spreadsheet.getCurrentCell().setValue('AAAA');
spreadsheet.getRange('C2').activate();
spreadsheet.getCurrentCell().setValue('BBBB');
spreadsheet.getRange('D2').activate();
spreadsheet.getCurrentCell().setValue('CCCC');
spreadsheet.getRange('E2').activate();
spreadsheet.getCurrentCell().setValue('AAA');
spreadsheet.getRange('F2').activate();
spreadsheet.getCurrentCell().setValue('AAB');
spreadsheet.getRange('G2').activate();
spreadsheet.getCurrentCell().setValue('AAC');
spreadsheet.getRange('H2').activate();
spreadsheet.getCurrentCell().setValue('BBB');
spreadsheet.getRange('I2').activate();
spreadsheet.getCurrentCell().setValue('BBC');
spreadsheet.getRange('J2').activate();
spreadsheet.getCurrentCell().setValue('BBD');
spreadsheet.getRange('K2').activate();
spreadsheet.getCurrentCell().setValue('CCC');
spreadsheet.getRange('L2').activate();
spreadsheet.getCurrentCell().setValue('CCD');
spreadsheet.getRange('M2').activate();
spreadsheet.getCurrentCell().setValue('CCE');
spreadsheet.getRange('A3').activate();
spreadsheet.getCurrentCell().setValue('AAAA');
spreadsheet.getRange('A4').activate();
spreadsheet.getCurrentCell().setValue('BBBB');
spreadsheet.getRange('A5').activate();
spreadsheet.getCurrentCell().setValue('CCCC');
spreadsheet.getRange('B3').activate();
spreadsheet.getCurrentCell().setValue('AAAA')
.setValue('AAA');
spreadsheet.getRange('B4').activate();
spreadsheet.getCurrentCell().setValue('AAB');
spreadsheet.getRange('B5').activate();
spreadsheet.getCurrentCell().setValue('AAC');
spreadsheet.getRange('C3').activate();
spreadsheet.getCurrentCell().setValue('BBB');
spreadsheet.getRange('C4').activate();
spreadsheet.getCurrentCell().setValue('BBC');
spreadsheet.getRange('C5').activate();
spreadsheet.getCurrentCell().setValue('BBD');
spreadsheet.getRange('D3').activate();
spreadsheet.getCurrentCell().setValue('CCC');
spreadsheet.getRange('D4').activate();
spreadsheet.getCurrentCell().setValue('CCD');
spreadsheet.getRange('D5').activate();
spreadsheet.getCurrentCell().setValue('CCE');
spreadsheet.getRange('E3').activate();
spreadsheet.getCurrentCell().setValue('A1');
spreadsheet.getRange('E4').activate();
spreadsheet.getCurrentCell().setValue('A2');
spreadsheet.getRange('E5').activate();
spreadsheet.getCurrentCell().setValue('A3');
spreadsheet.getRange('F3').activate();
spreadsheet.getCurrentCell().setValue('A4');
spreadsheet.getRange('F4').activate();
spreadsheet.getCurrentCell().setValue('A5');
spreadsheet.getRange('F5').activate();
spreadsheet.getCurrentCell().setValue('A6');
spreadsheet.getRange('G3').activate();
spreadsheet.getCurrentCell().setValue('A7');
spreadsheet.getRange('G4').activate();
spreadsheet.getCurrentCell().setValue('A8');
spreadsheet.getRange('G5').activate();
spreadsheet.getCurrentCell().setValue('A9');
spreadsheet.getRange('H3').activate();
spreadsheet.getCurrentCell().setValue('b1')
.setValue('B1');
spreadsheet.getRange('H4').activate();
spreadsheet.getCurrentCell().setValue('B2');
spreadsheet.getRange('H5').activate();
spreadsheet.getCurrentCell().setValue('B3');
spreadsheet.getRange('I3').activate();
spreadsheet.getCurrentCell().setValue('B4');
spreadsheet.getRange('I4').activate();
spreadsheet.getCurrentCell().setValue('B5');
spreadsheet.getRange('I5').activate();
spreadsheet.getCurrentCell().setValue('B6');
spreadsheet.getRange('J3').activate();
spreadsheet.getCurrentCell().setValue('B7');
spreadsheet.getRange('J4').activate();
spreadsheet.getCurrentCell().setValue('B8');
spreadsheet.getRange('J5').activate();
spreadsheet.getCurrentCell().setValue('B9');
spreadsheet.getRange('K3').activate();
spreadsheet.getCurrentCell().setValue('C1');
spreadsheet.getRange('K4').activate();
spreadsheet.getCurrentCell().setValue('C2');
spreadsheet.getRange('K5').activate();
spreadsheet.getCurrentCell().setValue('C3');
spreadsheet.getRange('L3').activate();
spreadsheet.getCurrentCell().setValue('C4');
spreadsheet.getRange('L4').activate();
spreadsheet.getCurrentCell().setValue('C5');
spreadsheet.getRange('L5').activate();
spreadsheet.getCurrentCell().setValue('C6');
spreadsheet.getRange('M3').activate();
spreadsheet.getCurrentCell().setValue('C7');
spreadsheet.getRange('M4').activate();
spreadsheet.getCurrentCell().setValue('C8');
spreadsheet.getRange('M5').activate();
spreadsheet.getCurrentCell().setValue('C9');
spreadsheet.getRange('A:A').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveSheet().setColumnWidths(1, 13, 50);
spreadsheet.getActiveSheet().setColumnWidths(1, 13, 70);
spreadsheet.getActiveRangeList().setHorizontalAlignment('center')
.setFontWeight(null)
.setFontWeight('bold');
spreadsheet.getRange('A1').activate();
spreadsheet.getActiveSheet().setFrozenRows(2);
spreadsheet.getRange('A1:A5').activate();
spreadsheet.getActiveRangeList().setBackground('#00ff00');
spreadsheet.getRange('B1:D1').activate();
spreadsheet.getActiveRangeList().setBackground('#ffff00');
spreadsheet.getRange('E1:M1').activate();
spreadsheet.getActiveRangeList().setBackground('#00ffff');
spreadsheet.getRange('B2:D5').activate();
spreadsheet.getActiveRangeList().setBackground('#d5a6bd');
spreadsheet.getRange('E2:G5').activate();
spreadsheet.getActiveRangeList().setBackground('#fce5cd');
spreadsheet.getRange('H2:J5').activate();
spreadsheet.getActiveRangeList().setBackground('#ff9900');
spreadsheet.getRange('K2:M5').activate();
spreadsheet.getActiveRangeList().setBackground('#ff00ff');
spreadsheet.setNamedRange('MAIN', spreadsheet.getRange('A3:A100'));
spreadsheet.setNamedRange('AAAA', spreadsheet.getRange('B3:B100'));
spreadsheet.setNamedRange('BBBB', spreadsheet.getRange('C3:C100'));
spreadsheet.setNamedRange('CCCC', spreadsheet.getRange('D3:D100'));
spreadsheet.setNamedRange('AAA', spreadsheet.getRange('E3:M3'));
spreadsheet.setNamedRange('AAB', spreadsheet.getRange('F3:F100'));
spreadsheet.setNamedRange('AAC', spreadsheet.getRange('G3:G100'));
spreadsheet.setNamedRange('BBB', spreadsheet.getRange('H3:H100'));
spreadsheet.setNamedRange('BBC', spreadsheet.getRange('I3:I100'));
spreadsheet.setNamedRange('BBD', spreadsheet.getRange('J3:J100'));
spreadsheet.setNamedRange('CCC', spreadsheet.getRange('C3:C100'));
spreadsheet.getRange('C3:C100').activate();
spreadsheet.setNamedRange('CCC', spreadsheet.getRange('K3:K100'));
spreadsheet.setNamedRange('CCD', spreadsheet.getRange('L3:L100'));
spreadsheet.setNamedRange('CCE', spreadsheet.getRange('M3:M100'));
spreadsheet.getRange('E3:M3').activate();
spreadsheet.setNamedRange('AAA', spreadsheet.getRange('E3:E100'));
spreadsheet.insertSheet(1);
spreadsheet.getRange('D:D').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
spreadsheet.getRange('101:101').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('Sheet2!A1:A100').setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(true)
.requireValueInRange(spreadsheet.getRange('Sheet1!$A$3:$A'), true)
.build());
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
};