Pulling data from one sheet and matching it by TWO keys to another sheet in a different spreadsheet…faster
Clash Royale CLAN TAG#URR8PPP
Pulling data from one sheet and matching it by TWO keys to another sheet in a different spreadsheet…faster
I'm sure there is an easy answer, I'm just haven't learned yet.
My code is working, it is just extremely slow (as in times out) and I know someone out there who is smarter than me knows a better way to accomplish this task.
What I'm trying to accomplish:
I have many multiple grade book spreadsheets (one for each class at a Jr High) and each one has a list of students listed by student ID and their grade, behavior indicator, number of missing assessments, and number of excused assessments.The administration wants all the data for all the grade books collected in one master student data sheet for easy reference.
In order to accomplish this task, I need to match the student ID and the class ID with the student ID and the Class ID of the master and then copy over the data. Both must match. One student ID will have multiple class IDs and one class ID will have multiple student IDs.
In both the grade book and the master, there is one student ID per row and each row has a different student ID.
In the grade book, the class ID is in one cell D1. In the master, that same class ID will be on the same row as the student ID number (when found) but could be in several different columns depending on which teacher, semester, and period of the day the student has that particular class.
Here are the possible columns: I, N, S, X, AC, AH, AM, AR, AW, BB, BG, BL, BQ, BV, CA, CF, CK, CP, CU, CZ, DE, DJ, DO, DT, DY, ED, EI, EN, ES, EX, FC, FH, FM, FR, FW. In case I messed up on this list, it is every fifth column starting with "I" and ending with "FW."
My thinking was to find the row the student is on first, then find the column that class ID is in, then copy the info to the next 4 columns after the class ID.
As I mentioned, my code works, but in all the time it took me to write this plea for help, the script is still running and then it will exceeds max execution time. LOL
Any advice is gratefully accepted. :)
Please be specific in a response as the following code taxes my abilities and I'm not sure I will know how to implement whatever solution you might offer.
function gbToMaster()
//get source spreadsheet
var ss = SpreadsheetApp.openById('16YVy9rC-9g00rem4rfoQdqxgiGNzYD7mucJsO9udPrQ'); //source spreadsheet ID
var gb = ss.getSheetByName('Grade Book'); //source sheet
var classID = gb.getRange(2, 4).getValue(); //get the class ID to find in the student data master
//get the target spreadsheet
var sdm = SpreadsheetApp.openById('1nYgKwslsX8Tqw9rqbFsVa7q2Itm2RMoeORWjG7LFOyU');
var sdmt = sdm.getSheetByName('Master Database'); //target sheet student data master target
// get the last row in the student database master...currently 397
var lastRow = sdmt.getLastRow();
Logger.log(lastRow);
//Get each student ID from source and check it against the list of student IDs in master to find a match
for (var si=6; si<104; si++)
//get value of student ID
var studentId = gb.getRange(si,65).getValue();
//find the row in student database master that contains the student ID
for(var trow =3; trow < lastRow+1; trow++ )
//get target sheet student ID value to compair
var cellValue = sdmt.getRange(trow,5).getValue();
//*****Working until this point*****
if(cellValue == studentId)
//get all the values we need to transfer
var gradeNum = gb.getRange(si,74).getValue();
var behavior = gb.getRange(si,6).getValue();
var missingAssessment = gb.getRange(si,7).getValue();
var excusedAssessment = gb.getRange(si,60).getValue();
//search in row to find class id
for(var tcol=9; tcol<198; tcol++)
var classIDT = sdmt.getRange(trow, tcol).getValue();
//check if current classIdTarget matches the key classID
if(classIDT == classID)
//copy values from grade book sourse sheet into student data master target
sdmt.getRange(trow, tcol+1).setValue(gradeNum);
sdmt.getRange(trow, tcol+2).setValue(behavior);
sdmt.getRange(trow, tcol+3).setValue(missingAssessment);
sdmt.getRange(trow, tcol+4).setValue(excusedAssessment);
I've done a bit more research and figured out that I probably need arrays to loop through instead of getValues, but I am brand new to arrays I'll keep looking for tutorials to help while I wait to see if someone can help me get directly to what I need. I probably need an example so I can see how it will look and work.
– JLBJones
Aug 12 at 2:07
Implement an index - right now you essentially forget each row / column each time you read it. Odds are that you can reuse known positions, i.e. multiple students take the same class, and the same student takes multiple classes. Having a student -> row computation and a class -> column computation will save considerable time.
– tehhowch
Aug 12 at 4:07
How would you do that...sorry, I'm so new to all this. There will be multiple students in this sheet that are all in the same class, but the class ID will be found on a different row for each student (the same row the student ID is found in). Each student ID will only appear once. Each class ID will appear once for each student, in the same column as the last student and on the same row as the student ID is found. Please help further.
– JLBJones
Aug 12 at 19:29
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
I improved one little piece. In the last for loop, I changed the increment to tcol+=5 instead of tcol++. That makes the script SLIGHTLY faster to run but it still times out.
– JLBJones
Aug 12 at 0:42