-
Notifications
You must be signed in to change notification settings - Fork 3
/
stepOne.gs
311 lines (296 loc) · 13.5 KB
/
stepOne.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
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
var scriptTitle = "formFolio Script V1.0.7 (1/30/14)";
var scriptName = 'formFolio';
var scriptTrackingId = 'UA-43639576-1';
var waitingIconId = '0B7-FEGXAo-DGMlJaSVloVnhqRTA';
var waitingImageUrl = 'https://drive.google.com/uc?export=download&id='+this.waitingIconId;
// Written by Andrew Stillman for New Visions for Public Schools
// Published under GNU General Public License, version 3 (GPL-3.0)
// See restrictions at http://www.opensource.org/licenses/gpl-3.0.html
function onInstall() {
onOpen();
}
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var properties = ScriptProperties.getProperties();
if (!ss) {
ss = SpreadsheetApp.openById(properties.ssId);
}
var menuItems = [];
menuItems[0] = {name: "What is formFolio?", functionName: "formFolio_whatIs"};
menuItems[1] = null;
if (!properties.installed) {
menuItems.push({name: "Run initial installation", functionName: "formFolio_runInstallation"});
}
if (properties.installed=="true") {
menuItems.push({name: "Step 1: Help formFolio understand your form", functionName: "formFolio_settingsUi"});
if (properties.openStep2=="true") {
menuItems.push({name: "Step 2: Configure, Create, Refresh Folders", functionName: "formFolio_folderKeys"});
if (properties.openStep3=="true") {
menuItems.push({name: "Step 3: Set run mode", functionName: "formFolio_setRunMode"});
menuItems.push(null);
menuItems.push({name: "Package this system for others to copy", functionName: "formFolio_extractorWindow"});
}
}
}
ss.addMenu("formFolio", menuItems);
}
function formFolio_runInstallation() {
formFolio_preconfig();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var properties = ScriptProperties.getProperties();
if (!ss) {
ss = SpreadsheetApp.openById(properties.ssId);
}
var domain = Session.getActiveUser().getEmail().split("@")[1];
var formUrl = ss.getFormUrl();
var msg = "";
if (formUrl) {
var form = FormApp.openByUrl(formUrl);
if (!properties.urlQId) {
msg = "The script discovered an existing form, added a text question for the Drive resource URL";
formFolio_addDriveUrlQuestion(form);
}
if (!properties.userNameQId) {
if (domain!="gmail.com") {
form.setRequireLogin(true);
form.setCollectEmail(true);
msg = ", set your form to auto-collect username";
} else {
formFolio_addEmailQuestion(form);
msg = " and added a question to collect email.";
}
}
} else {
msg = "The script attached a new form to this Spreadsheet";
var form = FormApp.create(ss.getName() + "- Form");
var formId = form.getId();
var ssFile = DriveApp.getFileById(ss.getId());
var ssFileParents = ssFile.getParents();
var formFile = DriveApp.getFileById(formId);
while (ssFileParents.hasNext()) {
var thisParent = ssFileParents.next();
thisParent.addFile(formFile);
DriveApp.getRootFolder().removeFile(formFile);
}
form.setTitle(ss.getName() + "- Form");
if (domain!="gmail.com") {
form.setRequireLogin(true);
form.setCollectEmail(true);
msg += "set the form to collect username, ";
} else {
formFolio_addEmailQuestion(form);
msg += ", added a question to collect email";
}
var listItem = form.addListItem();
listItem.setTitle("Select Folder").setHelpText("This question was added as a default and can be modified to suit your need...").setChoiceValues(['Folder 1','Folder 2', 'Folder 3']);
listItem.setRequired(true);
ScriptProperties.setProperty('formQidsSelected', listItem.getId().toString());
msg += ", added a default list type question entitled \"Folder\"";
formFolio_addDriveUrlQuestion(form);
msg += " and added a text type question to enable users to submit Drive resource URLs.";
form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
SpreadsheetApp.flush();
var sheets = ss.getSheets();
var formSheetId = sheets[sheets.length-1].getSheetId();
ScriptProperties.setProperty('formSheetId', formSheetId);
}
ScriptProperties.setProperty('installed', 'true');
ss.toast("formFolio successfully initialized. " + msg);
onOpen();
}
function formFolio_addDriveUrlQuestion(form) {
var textQ = form.addTextItem();
textQ.setTitle("Paste the URL of the Google Drive resource you are submitting");
textQ.setHelpText("This Drive resource may be any file type or folder in Drive, but it must be shared (at least view-only) with " + Session.getEffectiveUser().getEmail() + " in order for this form to place it in the correct folder(s).");
textQ.setRequired(true);
ScriptProperties.setProperty('urlQId', textQ.getId().toString());
return;
}
function formFolio_addEmailQuestion(form) {
var textQ = form.addTextItem();
textQ.setTitle("Please provide your Google email address");
textQ.setHelpText("This will be used to notify you in case this form is unable to access your Drive submission.");
textQ.setRequired(true);
ScriptProperties.setProperty('userNameId', textQ.getId().toString());
return;
}
function formFolio_settingsUi() {
setSid();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var properties = ScriptProperties.getProperties();
if (!ss) {
ss = SpreadsheetApp.openById(properties.ssId);
}
var formUrl = ss.getFormUrl();
if (!formUrl) {
Browser.msgBox("This script requires a spreadsheet that already has a Google Form attached to it. Please come back to this step once you have a form.");
return;
}
var form = FormApp.openByUrl(formUrl);
var app = UiApp.createApplication().setTitle("Step 1: Help formFolio understand your form...");
var panel = app.createVerticalPanel().setId('panel');
var outerScrollPanel = app.createScrollPanel().setHeight("250px").setStyleAttribute('backgroundColor', 'whiteSmoke');
var innerpanel = app.createVerticalPanel().setId('innerpanel').setStyleAttribute('margin', '10px');
var formSheetLabel = app.createLabel("Select the sheet containing your form responses");
var sheets = ss.getSheets();
var formSheetSelect = app.createListBox().setName('formSheetId').setId('formSheetSelect');
var sheetIds = [];
for (var i=0; i<sheets.length; i++) {
formSheetSelect.addItem(sheets[i].getName(), sheets[i].getSheetId());
sheetIds.push(sheets[i].getSheetId());
}
if (properties.formSheetId) {
var selectedId = parseInt(properties.formSheetId);
var index = sheetIds.indexOf(selectedId);
formSheetSelect.setSelectedIndex(index);
}
innerpanel.add(formSheetLabel).add(formSheetSelect);
var formQsScrollPanel = app.createScrollPanel().setHeight("150px").setWidth("300px").setStyleAttribute('backgroundColor', 'whiteSmoke');
var formQsInnerPanel = app.createVerticalPanel();
var formQsLabel = app.createLabel("Select the form question(s) whose options will correspond to Drive folders (only multiple choice, list, checkbox questions, and username (for Apps domain users collecting username) should appear below)").setStyleAttribute('marginTop', '15px');
var formItems = form.getItems();
var numItems = 0;
if (properties.formQidsSelected) {
var selectedQids = properties.formQidsSelected.split(",");
}
for (var i=0; i<formItems.length; i++) {
if ((formItems[i].getType() == "MULTIPLE_CHOICE")||(formItems[i].getType() == "CHECKBOX")||(formItems[i].getType() == "LIST")) {
numItems++;
var thisCheckBox = app.createCheckBox(formItems[i].getTitle()).setName('checkBox-'+numItems);
if (selectedQids) {
if (selectedQids.indexOf(formItems[i].getId().toString()) != -1) {
thisCheckBox.setValue(true);
}
}
var thisHidden = app.createHidden('hidden-'+numItems, formItems[i].getId());
formQsInnerPanel.add(thisCheckBox).add(thisHidden);
}
}
var isUsernameCollected = form.collectsEmail();
if (isUsernameCollected) {
numItems++;
var thisCheckBox = app.createCheckBox('Username').setName('checkBox-'+numItems);
if (selectedQids) {
if (selectedQids.indexOf('Username') != -1) {
thisCheckBox.setValue(true);
}
}
var thisHidden = app.createHidden('hidden-'+numItems, "Username");
formQsInnerPanel.add(thisCheckBox).add(thisHidden);
}
if (numItems=="0") {
Browser.msgBox("Your form must have at least one multiple choice, checkbox, or list item question, or be set to collect username in order to work with this script.");
app.close;
return app;
}
var numHidden = app.createHidden('numItems', numItems);
innerpanel.add(formQsLabel).add(formQsInnerPanel).add(numHidden);
var textQIds = [];
for (var i=0; i<formItems.length; i++) {
if (formItems[i].getType() == "TEXT") {
textQIds.push(formItems[i].getId());
}
}
if (form.collectsEmail() == false) {
var usernameQsLabel = app.createLabel("Select the form question that collects the user's Google Email address. (only text questions will appear in the listbox below)").setStyleAttribute('marginTop', '15px');
var usernameHelpLabel = app.createLabel("Why is this necessary? Because your form is not set to collect username, you must ask the user to self-report their Google email address. This will be used to email them a notification and link to share in the event the Drive resource is not shared properly upon initial submission.").setStyleAttribute('fontSize', '10px').setStyleAttribute('color', 'grey');
var usernameListbox = app.createListBox().setName('usernameQId').setStyleAttribute('width', '300px');
for (var i=0; i<formItems.length; i++) {
if (formItems[i].getType() == "TEXT") {
usernameListbox.addItem(formItems[i].getTitle(), formItems[i].getId());
}
if (properties.userNameId) {
var index = textQIds.indexOf(parseInt(properties.userNameId));
usernameListbox.setSelectedIndex(index);
}
innerpanel.add(usernameQsLabel).add(usernameHelpLabel).add(usernameListbox);
}
}
var urlQsLabel = app.createLabel("Select the form question that collects the URL of the submitted Drive resource. (only text questions will appear in the listbox below)").setStyleAttribute('marginTop', '15px');
var urlQsListbox = app.createListBox().setName('urlQId').setStyleAttribute('width', '400px');
for (var i=0; i<formItems.length; i++) {
if (formItems[i].getType() == "TEXT") {
urlQsListbox.addItem(formItems[i].getTitle(), formItems[i].getId());
}
}
if (properties.urlQId) {
var index = textQIds.indexOf(parseInt(properties.urlQId));
urlQsListbox.setSelectedIndex(index);
}
var nonDriveUrlOption = app.createCheckBox("Email users warning when they try to submit a non-Drive URL").setName('nonDriveWarning').setValue(true);
if (properties.nonDriveWarning == "false") {
nonDriveUrlOption.setValue(false);
}
innerpanel.add(urlQsLabel).add(urlQsListbox);
innerpanel.add(nonDriveUrlOption);
outerScrollPanel.add(innerpanel);
panel.add(outerScrollPanel);
var saveHandler = app.createServerHandler('formFolio_saveSettings').addCallbackElement(panel);
var button = app.createButton("Save settings", saveHandler);
var waitingImage = app.createImage(this.waitingImageUrl);
waitingImage.setStyleAttribute('position','absolute')
.setStyleAttribute('left','35%')
.setStyleAttribute('top','20%')
.setStyleAttribute('width','150px')
.setStyleAttribute('height','150px')
.setVisible(false);
var waitingHandler = app.createClientHandler().forTargets(waitingImage).setVisible(true).forTargets(panel).setStyleAttribute('opacity', '0.5').forTargets(button).setEnabled(false);
button.addClickHandler(waitingHandler);
panel.add(button);
app.add(panel);
app.add(waitingImage);
ss.show(app);
return app;
}
function formFolio_saveSettings(e) {
var app = UiApp.getActiveApplication();
var properties = ScriptProperties.getProperties();
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (!ss) {
ss = SpreadsheetApp.openById(properties.ssId);
}
var formUrl = ss.getFormUrl();
var form = FormApp.openByUrl(formUrl);
properties.formSheetId = e.parameter.formSheetId;
var numItems = parseInt(e.parameter.numItems);
var formQidsSelected = [];
var sheetIdMappings = properties.sheetIdMappings;
if (sheetIdMappings) {
sheetIdMappings = Utilities.jsonParse(sheetIdMappings);
} else {
sheetIdMappings = '';
}
for (var i=0; i<numItems; i++) {
var isSelected = e.parameter['checkBox-'+(i+1)]
if (isSelected == "true") {
formQidsSelected.push(e.parameter['hidden-'+(i+1)]);
} else if ((sheetIdMappings != "")&&(isSelected == "false")) {
sheetIdMappings = removeSheetMapping(e.parameter['hidden-'+(i+1)], sheetIdMappings)
}
}
if (sheetIdMappings!='') {
properties.sheetIdMappings = Utilities.jsonStringify(sheetIdMappings);
}
properties.formQidsSelected = formQidsSelected.join(",");
if (form.collectsEmail()) {
properties.userNameId = "Username";
} else {
properties.userNameId = e.parameter.usernameQId;
}
properties.urlQId = e.parameter.urlQId;
properties.nonDriveWarning = e.parameter.nonDriveWarning;
ScriptProperties.setProperties(properties);
var existingHelpText = form.getItemById(e.parameter.urlQId).asTextItem().getHelpText();
if (existingHelpText.indexOf("This Drive resource")==-1) {
var newHelpText = existingHelpText + " This Drive resource must be shared (at least view-only) with " + Session.getEffectiveUser().getEmail() + " in order for this form to place it in the correct folder(s).";
}
form.getItemById(e.parameter.urlQId).setHelpText(newHelpText);
app.close();
if (!properties.openStep2) {
properties.openStep2 = "true";
ScriptProperties.setProperties(properties);
onOpen();
formFolio_folderKeys();
}
return app;
}