Google Apps Script - Using For Loop To Pull Data From Range Based On Condition

I'm having an issue pulling the correct values out of a for loop in Google Sheets. Here's my code: Note: this is a snippet from a larger function function sendEmails() { var trackO

Solution 1:

Where did all that whitespace go in the actual script editor? :D

You already correctly use .getValues() to pull the entire table into an array. What you need to do now is have a for loop go through dataMir[k][8] and simply fetch the data if dataMir[k][8] === 'Yes'. I also feel that it's not quite necessary to use for (var k in dataMir) as for (var k = 0; k < dataMir.length; k++) is a lot cleaner and you have a for loop that guarantees control (though that's probably more a preference thing).

You can also reduce the number of variables you use by having

holdingData.selectedData = mirData[k]
holdingData.refDesc = mirData[k][2] //I assume you want the 3rd column for this variable, not the 4th
holdingData.adminActions = mirData[k][3] //same as above

remember, that the array starts with 0, so if you mirData[k][0] is column A, mirData[k][1] is column B and so on.

EDIT: what you wrote in your edits seems like doubling down on the code. You already have the data, but you are trying to pull it again and some variables you use should give you an error. I will cut the code from the if, although I don't really see why you need to both get the active sheet and sheet by name. If you know the name will be constant, then just always get the correct sheet by name (or index) thus eliminating the possibility of working with the wrong sheet.

var titleMirRows = 1; // First row of data to processvar numRowsMir = getMirSheet.getLastRow(); // Number of rows to process// Fetch the range of cells A2:Z506var dataRangeMir = getMirSheet.getRange(titleMirRows + 1, 1, numRowsMir - titleMirRows, 26); // might need adjusting but now it will only get as many rows as there is data, you can do the same for columns too// Fetch values for each cell in the Range.var dataMir = dataRangeMir.getValues();

   for (var k = 0; k < dataMir.length; k++) {
     if (dataMir[k][7] === 'Yes') {    //I assume you meant column i
       holdingData.refDesc = dataMir[k] //this will store the entire row
       holdingData.adminActions = dataMir[k][3] //this stores column D
       holdingData.dates = dataMir[k][4] //stores column E
     }
   }

Double check if the columns I have added to those variables are what you want. As I understood the object stores the entire row array, the value in column called Administrative Actions and the value in column Dates/Periods if Applicable. If not please adjust accordingly, but as you can see, we minimize the work we do with the sheet itself by simply manipulating the entire data array. Always make as few calls to Google Services as possible.