Hi I am building an app for our school. We have a positive reward system in place and I want to allow staff to submit points on the “Reward Submissions” sheet for spending (Bucks) and house points (similar to the House system in Harry Potter movies). I need a formula/function that will take staff submissions from the “Rewards Submissions” sheet and add them to individual student’s Buck column and House Points column on the “Students” sheet. Can you help me?
@Christie_Orich-Matth Private message me a link to your sheet or a clone of it and I’ll write the function to do what you want.
1 Like
Wow! Thank you.
This is a copy. I took out the student and staff private info. If I need to add anything back in let me know.
I really appreciate this.
Christie
OK here it is. I bit more work because of the dropdowns in col B, C, D as I could not write back the entire sheet into a single array. I put in plenty of comments so hopefully you can follow it if you need a change. Also do plenty of testing on sample data to make sure it is doing what you want. Any questions ask away.
// Created for Christie by George B.. Enjoy!
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Apply Reward Points ✌",
functionName : "applyBuckAndHousePoints"
}];
sheet.addMenu("✍Custom Functions", entries);
};
// Note 1: Arrays are used for speed. All the sheet data is loaded into the arrays
// and the changing of the data is done in the arrays and the written back
// to the respective sheets in a few calls
// Note 2: Only rows that have data are processed but since two sheets are involved
// the min number of the two sheets are processed. They should have the same number
// . of rows aka students
function applyBuckAndHousePoints() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetStudents = ss.getSheetByName("Students");
var sheetRewards = ss.getSheetByName("Reward Submissions");
var lastRowMin = Math.min(sheetStudents.getLastRow(), sheetRewards.getLastRow()) -1;
/* !!!!!!*********************************************!!!!!!!
if you change the position of any of these columns on the
respective sheets you will need to change these column numbers
AND TEST TEST TEST on COPIES of the spreadsheet
*/
var colNumStudentsName = 1; // col A
var colNumStudentsHouse = 6;
var colNumStudentsBuck = 7;
var colNumRewardsName = 1;
var colNumRewardsHouse = 7;
var colNumRewardsBuck = 8;
// Note that the array is zero based so when used -1 will
// be applied to the index position of the array
// !!!!!!*********************************************!!!!!!!
// I had to use individual arrays because of the
// dropdown choice cells in column B, C, and D
var rangeStudentsName = sheetStudents.getRange(2, colNumStudentsName, lastRowMin, 1);
var rangeStudentsHouse = sheetStudents.getRange(2, colNumStudentsHouse, lastRowMin, 1);
var rangeStudentsBuck = sheetStudents.getRange(2, colNumStudentsBuck, lastRowMin, 1);
var rangeRewardsName = sheetRewards.getRange(2, colNumRewardsName, lastRowMin, 1);
var rangeRewardsHouse = sheetRewards.getRange(2, colNumRewardsHouse, lastRowMin, 1);
var rangeRewardsBuck = sheetRewards.getRange(2, colNumRewardsBuck, lastRowMin, 1);
var dataStudentsName = rangeStudentsName.getValues();
var dataStudentsHouse = rangeStudentsHouse.getValues();
var dataStudentsBuck = rangeStudentsBuck.getValues();
var dataRewardsName = rangeRewardsName.getValues();
var dataRewardsHouse = rangeRewardsHouse.getValues();
var dataRewardsBuck = rangeRewardsBuck.getValues();
// walk through the Students data
for (var i = 0; i < dataStudentsName.length; i++){
// find the student in the Rewards array
for (var j = 0; j < dataRewardsName.length; j++){
if (dataRewardsName[j][colNumRewardsName-1] == dataStudentsName[i][colNumStudentsName-1]){
// found it; add the points
dataStudentsHouse[i][0] += dataRewardsHouse[j][0];
dataStudentsBuck[i][0] += dataRewardsBuck[j][0];
// reset the points in the Rewards sheet
dataRewardsHouse[j][0] = 0;
dataRewardsBuck[j][0] = 0;
// no need to look further so break out of the inner "j" loop
break;
}
}
}
// apply all the changes back to the respective sheets
rangeRewardsHouse.setValues(dataRewardsHouse);
rangeRewardsBuck.setValues(dataRewardsBuck);
rangeStudentsHouse.setValues(dataStudentsHouse);
rangeStudentsBuck.setValues(dataStudentsBuck);
}
BTW you need to put this code in the script. Tools/Script Editor. Once saved you will have to reload the sheet for the menu to show up and you will have to authorize the script. Just follow the screens that Google presents and keep clicking on links that move you forward like Advance and Go to script and Allow.
Hi George,
Thank you for time you’ve already taken to help me. I’ve added the script and saved. Done the permissions. But I’m stuck at how to apply the custom functions to the cells. I have a very basic understanding of code and none as to how to apply that to the spreadsheet. I googled and it said to type: =(name of custom function) into the cell that I want to apply it to but I keep getting an error. So I’m missing something.
Thanks,
Christie
No problem. I made it very simple, but it’s a forest for the trees type of thing for you. Make sure you close and reopen the spreadsheet. Once it refreshes you should see a new menu item at the top of the sheet next to Help called 
Custom Functions. Click that and then click “Apply Reward Points 
” . That is what that first function does, the onOpen() one. It adds that menu item when the spreadsheet is first opened. And when you select it, it runs that function called “applyBuckAndHousePoints”.
You know it just occurred to me that I made an assumption about how you wanted those “points” to be applied. I had helped another teacher with a similar reward type of thing and what he was after was to apply those reward points on a monthly basis to each student, and then reset the totals. He had another column on the same sheet for the student that was used to accumulate points during the month and then once a month he would apply the points to the students grand total. It was this gentlemen https://www.mrhebert.org/
Here is the YouTube where he talked about it. https://www.youtube.com/watch?v=YPmCjxy66vY&t=2137s
It seem to be the classic example of understanding the problem before trying to solve it, lol. The mistake I made in this case unfortunately. But all is not lost and it gave me the opportunity to learn a few things when I coded what I did. In my defense there was no place on the rewards sheet to indicate a teachers name, the one that was giving the reward, and I assumed that there would be a one for one relationship between the two sheets. And if that were the case it didn’t seem to make sense to have the two sheets at all. Now that I look closer I see columns for other student names on that rewards spreadsheet so I think I need more info in order to help you achieve what you want. Try walking me through the process of rewarding points and we will come up with what you want.
I’m sort of doing the same thing again, with poking at the problem but…
If the plan is to have multiple entries on your Rewards Submissions sheet for each student then this formula should work. Put it in cell F2 on sheet Students. Make sure all the cells to the end of that row are blank.
=arrayformula(if(isblank(A2:A),"",sumif('Reward Submissions'!$A$2:$A,"="&A2:A,'Reward Submissions'!$G$2:$G)))
Put the same formula in G2 but change all the G’s at the end of it to H. The work is done by the sumif function. The arrayformula wrapping around it is just applies it all the way down the rest of the column but only if there is something in the A column of the sheet.
Hi George,
Mr. Hebert is popular among teachers that want to gamify their classrooms. He shared the Youtube video on a teacher Facebook group over the summer and that’s where this started. When my principals suggested we create house teams (like Harry Potter) I knew this is where I could apply the idea. You must be the help he referred to when he said he reached out … small world. Really glad you found me.
So on to the app …
The plan was to create an app that teachers could make submissions to that recorded both their Kisi Bucks (the nickname for our school and the reward system) and House Points. The Kisi Bucks are for spending in our store for tangible prizes. The house points are the individual contributions that students earn for their respective houses.
The way I have it setup on the app is that teachers fill out a form and submit student earnings for good deeds. We want it to run all year and have staff reward students on the app for their good deeds and behaviours. I did a few screenshots and gave the details in the images. I hope this help.
Those extra students make it a bit more complex. I noticed in your spreadsheet you show 5 students but the form you showed has only 4. In any event the following formulas seem to be working for me in my mock up of some data. Copy the first one into cell F2 the second in G2. Let me know how your tests go and if I need to fix something. Oh and yeah that was me who helped Scott.
// this goes in the Student sheet F2
=arrayformula(if(isblank(A2:A),"",
sumif('Reward Submissions'!$A$2:$A,"="&A2:A,'Reward Submissions'!$G$2:$G) +
sumif('Reward Submissions'!$B$2:$B,"="&A2:A,'Reward Submissions'!$G$2:$G) +
sumif('Reward Submissions'!$C$2:$C,"="&A2:A,'Reward Submissions'!$G$2:$G) +
sumif('Reward Submissions'!$D$2:$D,"="&A2:A,'Reward Submissions'!$G$2:$G) +
sumif('Reward Submissions'!$E$2:$E,"="&A2:A,'Reward Submissions'!$G$2:$G)
))
// this one in G2
=arrayformula(if(isblank(A2:A),"",
sumif('Reward Submissions'!$A$2:$A,"="&A2:A,'Reward Submissions'!$H$2:$H) +
sumif('Reward Submissions'!$B$2:$B,"="&A2:A,'Reward Submissions'!$H$2:$H) +
sumif('Reward Submissions'!$C$2:$C,"="&A2:A,'Reward Submissions'!$H$2:$H) +
sumif('Reward Submissions'!$D$2:$D,"="&A2:A,'Reward Submissions'!$H$2:$H) +
sumif('Reward Submissions'!$E$2:$E,"="&A2:A,'Reward Submissions'!$H$2:$H)
))
1 Like
Here is the spreadsheet with the formulas in it. You should delete the script as it is no longer needed. Just open up the script and delete all the code. The menu will go away as well.
It works!!! Thank you so much. I tested it a few times and yay! It’s ready to go 
Now to make the student app. Wish me luck
Great. You now know where to find help if you need it. And not just from me, there are many other very knowledgable people around here that chip in to help all the time. All you need to do is ask.
1 Like
Ok. I will. Thank you