Last active
September 29, 2018 22:26
-
-
Save sramam/2a9d17216f31ed7285808b8a773f3211 to your computer and use it in GitHub Desktop.
Script to setup tryout spreadsheet from registration data. setting-sheet.tsv contains all the externalities that can be changed and must be put on a sheet called "Settings" (note capitalization). Also the registration information exported as a .csv file, is assumed to be present in a sheet called "CSV" (again, note capitalization).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function createForm() { | |
// global constants, that drive the script | |
var G = { | |
Settings: 'Settings', | |
CSV: 'CSV', | |
Grade: {} | |
}; | |
var SS = SpreadsheetApp.getActive(); | |
var Sheets = { | |
Settings: SS.getSheetByName(G.Settings), | |
CSV: SS.getSheetByName(G.CSV), | |
}; | |
var startRow = 2; | |
var startCol = 1; | |
var maxRows = 1; | |
var maxCols = 1; | |
var name = Sheets.Settings.getRange(startRow, startCol, maxRows, maxCols).getValues()[0][0]; | |
var grades = Sheets.Settings.getRange(startRow=2, column=2, maxRows=2, maxCols = 1).getValues().map(function (v) { return v[0] }); | |
Logger.log(name); | |
Logger.log(grades); | |
var cityRange = Sheets.Settings.getRange('C2:D10').getValues(); | |
var zipRange = Sheets.Settings.getRange('E2:F10').getValues(); | |
var pinnieRange = Sheets.Settings.getRange('G2:G1000'); | |
var shortsSizeRange = Sheets.Settings.getRange('H2:H1000'); | |
var jerseySizeRange = Sheets.Settings.getRange('I2:I1000'); | |
var heightRange = Sheets.Settings.getRange('J2:J1000'); | |
var dataValidationRules = { | |
addrValidation: SpreadsheetApp.newDataValidation().requireValueInList(['Needs Validation', 'Exception Granted', 'Validated']).build(), | |
jersey: SpreadsheetApp.newDataValidation().requireValueInRange(jerseySizeRange).build(), | |
shorts: SpreadsheetApp.newDataValidation().requireValueInRange(shortsSizeRange).build(), | |
height: SpreadsheetApp.newDataValidation().requireValueInRange(heightRange).build(), | |
pinnie: SpreadsheetApp.newDataValidation().requireValueInRange(pinnieRange).build(), | |
}; | |
Logger.log(cityRange); | |
Logger.log(zipRange); | |
// process data by grade | |
grades.map(function (grade) { | |
var sheetName = grade +' Grade'; | |
var gradeSheet = SS.getSheetByName(sheetName); | |
Logger.log(sheetName); | |
// create sheet if needed | |
if (gradeSheet == null) { | |
gradeSheet = SS.insertSheet(); | |
gradeSheet.setName(sheetName); | |
} | |
// populate with filtered data | |
var A1 = gradeSheet.getRange(/*row*/1, /*column*/1) | |
// =QUERY(CSV!A:Z, "select * where (E='7th') order by A, B", 1) | |
A1.setFormula('=QUERY(\'' + G.CSV + '\'!A:Z, "select A,B,C,E,H,M,N,P where (E=\'' + grade + '\') order by A, B label A \'FirstName\',B \'LastName\',C \'DOB\',E \'Grade\',H \'Returning\',M \'Street\',N \'City\',P \'Zip\'", 1)'); | |
var maxCol = 200; | |
var ColI = gradeSheet.getRange('I1:I' + maxCol); | |
var ColJ = gradeSheet.getRange('J1:J' + maxCol); | |
var ColJersey = gradeSheet.getRange('K1:K' + maxCol); // jerseySize | |
var ColShorts = gradeSheet.getRange('L1:L' + maxCol); // shortsSize | |
var ColHeight = gradeSheet.getRange('M1:M' + maxCol); // height | |
var ColPinnie = gradeSheet.getRange('N1:N' + maxCol); // pinneNumber | |
var formatRules = gradeSheet.getConditionalFormatRules(); | |
[ | |
SpreadsheetApp.newConditionalFormatRule() | |
.whenTextEqualTo('Needs Validation') | |
.setBackground("#ea9999") | |
.setRanges([ColJ]) | |
.build(), | |
SpreadsheetApp.newConditionalFormatRule() | |
.whenTextEqualTo('Validated') | |
.setBackground("#cceacc") | |
.setRanges([ColJ]) | |
.build(), | |
SpreadsheetApp.newConditionalFormatRule() | |
.whenTextEqualTo('Exception Granted') | |
.setBackground("#cceacc") | |
.setRanges([ColJ]) | |
.build(), | |
].map(function (rule) { | |
formatRules.push(rule); | |
}); | |
gradeSheet.setConditionalFormatRules(formatRules); | |
ColI.getValues() | |
.map(function (val, idx) { | |
Logger.log('idx' + idx); | |
var cell = ColI.getCell(idx+1, 1); | |
var name = gradeSheet.getRange('A' + (idx+1)).getValues().toString().toLowerCase(); | |
if (idx == 0) { | |
cell.setValue("Needs Validation?") | |
var [j,k,l,m, n] = [ | |
ColJ.getCell(idx+1, 1), | |
ColJersey.getCell(idx+1, 1), | |
ColShorts.getCell(idx+1, 1), | |
ColHeight.getCell(idx+1, 1), | |
ColPinnie.getCell(idx+1, 1), | |
]; | |
j.setValue('Validated?'); | |
k.setValue('Jersey Size'); | |
l.setValue('Shorts Size'); | |
m.setValue('Height'); | |
n.setValue('Pinnie #'); | |
} else if (name != '') { | |
cell.setValue('OK'); | |
cell.setBackground('#cceacc'); | |
var addrValidation = ColJ.getCell(idx+1, 1); | |
addrValidation.setDataValidation(dataValidationRules.addrValidation).setValue('Validated'); | |
ColJersey.getCell(idx+1, 1).setDataValidation(dataValidationRules.jersey); | |
ColShorts.getCell(idx+1, 1).setDataValidation(dataValidationRules.shorts) | |
ColHeight.getCell(idx+1, 1).setDataValidation(dataValidationRules.height) | |
ColPinnie.getCell(idx+1, 1).setDataValidation(dataValidationRules.pinnie) | |
var city = gradeSheet.getRange('G' + (idx+1)).getValues().toString().toLowerCase(); | |
var zip = gradeSheet.getRange('H' + (idx+1)).getValues().toString().toLowerCase(); | |
if (city == '' || zip == '') { | |
cell.setValue('Validate'); | |
cell.setBackground('#ea9999'); | |
addrValidation.setValue('Needs Validation'); | |
} else { | |
cityRange.map(function(cval) { | |
if (cval[0].toLowerCase() == city && cval[1].toLowerCase() == 'validate') { | |
cell.setValue('Validate'); | |
cell.setBackground('#ea9999'); | |
addrValidation.setValue('Needs Validation'); | |
} | |
}); | |
zipRange.map(function(zval) { | |
if (zval[0].toString().toLowerCase() == zip && zval[1].toString().toLowerCase() == 'validate') { | |
cell.setValue('Validate'); | |
cell.setBackground('#ea9999'); | |
addrValidation.setValue('Needs Validation'); | |
} | |
}); | |
} | |
} | |
}); | |
gradeSheet.getRange('A1:Z1').setFontWeight("bold").setWrap(true); | |
gradeSheet.autoResizeColumns(1,20); | |
// keep references to the sheet and it's name, so we can find it. | |
Sheets[sheetName] = gradeSheet; | |
G.Grade[grade] = G.Grade[grade] || {}; | |
G.Grade[grade]["CSV"] = sheetName; | |
}); | |
} |
We can make this file beautiful and searchable if this error is corrected: Illegal quoting in line 2.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Name Grades Cities Zips Pinnies Shorts Sizes Jersey Sizes Height | |
BD1-2018-WSJ 7th Alviso OK 95117 OK 1 Youth-XS Youth-XS 2' 7" | |
8th Campell OK 95126 Validate 2 Youth-S Youth-S 2' 8" | |
Cupertino OK 95128 OK 3 Youth-M Youth-M 2' 9" | |
Santa Clara OK 95129 OK 4 Youth-L Youth-L 2' 10" | |
Milpitas Validate 95130 OK 5 Adult-XS Adult-XS 2' 11" | |
San Jose OK 95131 Validate 6 Adult-S Adult-S 2' 12" | |
95134 OK 7 Adult-M Adult-M 3' 0" | |
8 Adult-L Adult-L 3' 1" | |
9 3' 2" | |
10 3' 3" | |
11 3' 4" | |
12 3' 5" | |
13 3' 6" | |
14 3' 7" | |
15 3' 8" | |
16 3' 9" | |
17 3' 10" | |
18 3' 11" | |
19 4' 0" | |
20 4' 1" | |
21 4' 2" | |
22 4' 3" | |
23 4' 4" | |
24 4' 5" | |
25 4' 6" | |
26 4' 7" | |
27 4' 8" | |
28 4' 9" | |
29 4' 10" | |
30 4' 11" | |
31 5' 0" | |
32 5' 1" | |
33 5' 2" | |
34 5' 3" | |
35 5' 4" | |
36 5' 5" | |
37 5' 6" | |
38 5' 7" | |
39 5' 8" | |
40 5' 9" | |
41 5' 10" | |
42 5' 11" | |
43 6' 0" | |
44 6' 1" | |
45 6' 2" | |
46 6' 3" | |
47 6' 4" | |
48 6' 5" | |
49 6' 6" | |
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment