ABCDEFGHIJKLMNOPQRS
1
Paste Round 1 LinkPaste Round 2 LinkPaste Round 3 LinkPaste Round 4 LinkPaste Round 5 LinkLink must start with HTTPS
function Clear1() {
function Clear1() {
2
Round 1Round 2Round 3Round 4Round 5ALLFALSE
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
3
How To Use
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
4
1. Right Click on the tab for this Sheet
var lr = scr.getLastRow();
var lr = scr.getLastRow();
5
1a. Bottom of Screen, tab titled "Clear All"
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
6
};};
7
2. Select "Copy To"
8
2a. I suggest copying to your Master Scoresheet, but you can create a new one if you want.
9
function Uncheck1() {
function Uncheck1() {
10
3. When copied, click "Open Spreadsheet"
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
11
3a. If you copied to an existing spreadsheet, when you open it, it will NOT be on the Clear All sheet. Click on the appropriate tab to continue reading these instructions in your new copy.
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
12
#REF!#REF!#REF!#REF!#REF!#REF!3b. Right Click on "Copy of Clear All" and rename so it doesn't say "Copy of" - unless that doesn't bother you. I hate it.
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
13
rl.uncheck();rl.uncheck();
14
4. Paste Links to Individual Round Answer Sheets in A1 - E1 as indicated.};};
15
4a. If all links are inserted properly, G1 and G2 will change.
16
function Wipe1() {
function Wipe1() {
17
5. Right Click on G2 - the YELLOW cell - and "Copy"
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
18
5a. I suggest opening a blank Notepad file and pasting the code in it just in case you accidentally copy something else before needing it.
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
19
var lr = scr.getLastRow();
var lr = scr.getLastRow();
20
6. Go to Tools > Script Editor
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
21
#REF!#REF!#REF!#REF!#REF!6a. If it's your first time running Script Editor on this Spreadsheet you may have to either Authorize it, or go to Tools > Script Editor multiple times, as it initializes the first time without opening.
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
22
UncheckedUncheckedUncheckedUncheckedUncheckedALL UNCHECKED!rl.uncheck();rl.uncheck();
23
7. By default you will have a code page called "Code.gs" which should have 4 lines pre-written for you (function myFunction() etc). Erase that all, then paste the code you copied from G2.
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
24
};};
25
8. Go to File > Save. It will prompt you to name the project. I suggest something simple like "Wipe." After submitting that, go to File > Save one more time.
26
8a. If you get an error when trying to save, select the entire "H" column, right click, and COPY. Paste this into the "Code.gs" project, and save that.
27
function Clear2() {
function Clear2() {
28
9. On this sheet, in your Master Scoresheet, if you currently see #REF! errors underneath any of the buttons, click on one of the errors, and in the little pop-up, click "Allow Access." (Sometimes you have to allow each error individually, sometimes allowing one fixes them all).
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
29
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
30
10. Finally, click one one of the buttons. I'd suggest just the very first one - Round 1, Clear Answers - as you can open that answer sheet and revert to an earlier edition if you need to restore the deleted data.
var lr = scr.getLastRow();
var lr = scr.getLastRow();
31
10a. The very first time you do this, you will almost certainly get an error saying you need to authorize the script. "A script attached to this document needs your permission to run." This only happens the FIRST TIME. Click on Continue.
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
32
#REF!#REF!#REF!#REF!#REF!#REF!10c. Sign in with the account you use to handle these Spreadsheets.};};
33
10d. It will warn you that the app isn't verified. If you trust me, click "Advanced" followed by "Go to <Name of your project>" (it may still say Untitled Project (unsafe)) - it's the same one.
34
10e. You need to click "Allow," which gives the script access to "See, edit, create, and delete" your spreadsheets. This is what it was made for, so...
35
10f. After clicking "Allow," click the button again (Round 1 - Clear Answers).
function Uncheck2() {
function Uncheck2() {
36
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
37
11. At the top of your screen it should say "Running Script" followed by "Script Finished." If there was data in there before, the cell below the button will now read "Cleared"
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
38
var rl = sub.getRange('A:AZ');
var rl = sub.getRange('A:AZ');
39
12. Click "Enable Mobile" to the left of this cell. It may ask for authorization to run a script. It's creating a trigger that allows you to utilize these functions via mobile, using the dropdown round titles.rl.uncheck();rl.uncheck();
40
};};
41
13. Assuming everything has worked properly, click the button to the left of this cell. This button will delete all the extra coding needed to prepare your script, as well as this How To, making the Sheet nice and tidy. On the original Spreadsheet that you copied this from, you can go to the Example tab to see how it should look when you're done.
42
13a. If you encounter any errors, contact Jason or come directly to the Trivia Writers' Co-Op Discord where we will attempt to walk you through it!
function Wipe2() {
function Wipe2() {
43
13b. Please read the notes below before clearing everything!
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
44
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
45
NOTES
var lr = scr.getLastRow();
var lr = scr.getLastRow();
46
Buttons do not work on mobile. The Round Title cells are dropdown menus that allow you to run the scripts from mobile devices.
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
47
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
48
Buttons do not require confirmation. If you click it, it happens, so click carefully!rl.uncheck();rl.uncheck();
49
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
50
Unchecked/Not Unchecked shouldn't, but may, return a false negative ("Not Unchecked") in some instances where "TRUE" is a submitted, but not correct, answer. This should only happen if you are using the "Uncheck Boxes" button but are not clearing answers.};};
51
52
If you don't Enable Mobile before Cleaning everything up, it will be a little trickier to enable it later, but still possible. You will also wind up with an extra button that you need to delete. You can do this by right clicking on it, then clicking on the three-dot menu in the top right corner, and selecting Remove Image. Because it's on a black background, you might not be able to see the dots - click carefully!
function Clear3() {
function Clear3() {
53
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
54
In some instances, the sheet may error out or not finish the clearing sequence if you do something else on the computer. This does not always occur - if it does, the text validations will still show "Not Cleared" and "Not Unchecked," letting you know you need to run the script again.
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
55
var lr = scr.getLastRow();
var lr = scr.getLastRow();
56
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
57
};};
58
59
60
function Uncheck3() {
function Uncheck3() {
61
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
62
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
63
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
64
rl.uncheck();rl.uncheck();
65
};};
66
67
function Wipe3() {
function Wipe3() {
68
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
69
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
70
var lr = scr.getLastRow();
var lr = scr.getLastRow();
71
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
72
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
73
rl.uncheck();rl.uncheck();
74
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
75
};};
76
77
function Clear4() {
function Clear4() {
78
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
79
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
80
var lr = scr.getLastRow();
var lr = scr.getLastRow();
81
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
82
};};
83
84
85
function Uncheck4() {
function Uncheck4() {
86
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
87
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
88
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
89
rl.uncheck();rl.uncheck();
90
};};
91
92
function Wipe4() {
function Wipe4() {
93
var ss = SpreadsheetApp.openById("");
var ss = SpreadsheetApp.openById("
");
94
var scr = ss.getSheetByName('Form Responses 1');
var scr = ss.getSheetByName('Form Responses 1');
95
var lr = scr.getLastRow();
var lr = scr.getLastRow();
96
var sub = ss.getSheetByName('Answer Validator');
var sub = ss.getSheetByName('Answer Validator');
97
var rl = sub.getRange('A:Z');
var rl = sub.getRange('A:Z');
98
rl.uncheck();rl.uncheck();
99
if (lr > 1) scr.deleteRows(2, lr);
if (lr > 1) scr.deleteRows(2, lr);
100
};};