Reputation: 445
I've been working on this side project in Google sheets for a while now. (link to copy) When it works, it would take a conditional argument in column I (yes or no) of the Individual sheets (Miranda, Piper, Lowes, and Golden) and send an email off to addresses in column B and Q of the Master sheet, as well as whatever email is returned by running through the switch statement at the end of the function. Through suggestions I've gotten through this site, I've been able to drastically improve my code, and I am very grateful! Here's my latest hurdle -
Code: The full thing is over here at JSFiddle
for (var ii = 0; ii < holdingData.masterStudNames.length; ii++) {
if (holdingData.masterStudNames[ii] === holdingData.selectedStudName) {
//Push Master column B to holdingData
holdingData.selectedTeacherEmail = holdingData.masterTeacherEmail[ii].toLowerCase();
//Push Master Column Q for selected student to holdingData
holdingData.selectedIEP = holdingData.allIEPContents[ii].toLowerCase();
}
}
for (var j = 0; j < holdingData.selectedIEP.length; j++) {
switch (holdingData.masterIEP[j]) {
case "dreier": holdingData.selectedIEPEmail = "[email protected]"; break;
case "forbes": holdingData.selectedIEPEmail = "[email protected]"; break;
case "green": holdingData.selectedIEPEmail = "[email protected]"; break;
case "marlo": holdingData.selectedIEPEmail = "[email protected]"; break;
case "mcDonald": holdingData.selectedIEPEmail = "[email protected]"; break;
case "nakkour": holdingData.selectedIEPEmail = "[email protected]"; break;
case "wright": holdingData.selectedIEPEmail = "[email protected]"; break;
default: break;
}
}
The Issue: The for loop that looks through holdingData.selectedIEP [line 79 in JSFiddle] is getting held up. I believe it may be using its [j] counter wrong, or maybe one of the other loops is getting in the way. I admit, having 3 for loops in a function seems like too many to me, but I couldn't think of a different way to do it. Is there anything glaringly obvious that I'm missing?
I have already cut the code down by.....a lot. So I'm fully aware that there is probably room for more improvement (I'm still learning:) If you have any suggestions, I'd love to hear them!
UPDATE: By using the debugger, One of the issues I've found is that my for loops are only pulling the last value in the selected column. For example, in the first loop:
for (var h = 0; h < dataMaster.length; h++) {
//Push all student first names from master sheet for later use
var masterStudFirstName = masterSheetDataLooped[h][4];
}
This sets masterStudentFirstName as "test" which is the last cell in the 4th column. What I need is to grab all of the values in that column, and store them in an array that I can call later.
I feel like I missed something simple here... :)
Upvotes: 1
Views: 817
Reputation: 445
After some troubleshooting, and a look at a debugger as suggested by Karl_S (Thank you!) I now have refined the issue I was having, and gotten it solved.
The problem with my code, was that the for loops were not pulling the correct data. I have 4 tabs on this sheet, and each of them contain some data I need to pull. In the first case (the "Master" tab) I need to pull the values of column E "Student First Name" and store them in an array object within my master "holdingData" object.
My earlier approach was to just loop through that column:
for (var h = 0; h < dataMaster.length; h++) {
//Push all student first names from master sheet for later use
var masterStudFirstName = masterSheetDataLooped[h][4];
}
But I found that, while this is useful, it would require me to write conditions to make other parts of the code functional. So what I ended up doing is this (posting a few more variables for context):
//Set a new object to hold data
var holdingData = new Object();
holdingData.studFirstNames = [];
holdingData.allSheetsNamesAndIds = [[sheets[1], sheets[1].getSheetName(),
543328548], [sheets[3], sheets[3].getSheetName(), 1377446903], [sheets[4],
sheets[4].getSheetName(), 748028814], [sheets[5], sheets[5].getSheetName(),
431951580], [sheets[6], sheets[6].getSheetName(), 193755985]];
//Skippng a few lines....
//Load the master sheet, get its entire range, and then get all values
SpreadsheetApp.setActiveSheet(holdingData.allSheetsNamesAndIds[0][0]);
var numRowsMaster = masterSheet.getLastRow();
var dataRangeMaster = masterSheet.getRange(2, 1, numRowsMaster - 1, 26);
var dataMaster = dataRangeMaster.getValues();
//Loop dataMaster and pull the relevant columns
for (var h = 0; h < dataMaster.length; ++h) {
holdingData.studFirstNames.push(dataMaster[h][4]);
}
Basically, this loops through every row of the Master tab. Then every time we get to the 4th column, we push the contents of that column within the specific row to the holdingData array.
To sum up, the loop is saying "go through dataMaster row by row." then we say "every row you loop through push the contents of the 4th column within that row to holdingData.studFirstNames"
You could certainly take a different approach at how you store data (variable) For this application, it's easier for me pass data upstairs to a holding Object. I realize I'm mostly explaining this to myself :) but I hope in the future someone comes across this post and finds a quicker answer than I did.
Upvotes: 1