javascriptfor-loopgoogle-apps-scriptgoogle-sheetsconditional-statements

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 trackOriginSheet = SpreadsheetApp.getActiveSpreadsheet().getName();
var getMirSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Miranda");

//Set a new object to hold conditional data
var holdingData = new Object();

//Create function to get values from origin sheet
var returnedValues = function (trackOriginSheet) {

//Load dynamic variables into an object via returnedValues()
    if (trackOriginSheet === getMirSheet) {

        var startMirRow = 2; // First row of data to process
        var numRowsMir = 506; // Number of rows to process

        // Fetch the range of cells A2:Z506
        var dataRangeMir = getMirSheet.getRange(startMirRow, 1, numRowsMir, 26);

        // Fetch values for each cell in the Range.
        var dataMir = dataRangeMir.getValues();

        for (var k in dataMir) {
            var secondRowMir = dataMir[k];
            var intRefDescMir = secondRowMir[3];
            var intAdminActionsMir = secondRowMir[4];

            //Push returned data to holdingData Object
            holdingData.selectedData = secondRowMir;
            holdingData.refDesc = intRefDescMir;
            holdingData.adminActions = intAdminActionsMir;
        }
    }
}

Here's a copy of the sheet I'm working on

What I need to have happened here first, is track the origin sheet, then create an object to hold data returned from the returnedValues() function. Later, I'll call the properties of this object into a send email function.

The problem is that I need to be able to pull data from the selected sheet dynamically (the "Miranda" sheet in this case.) In other words, when a user selects the "Yes" option in column I of the Miranda sheet, the first thing this script needs to do is pull the values of the variables at the top of the for loop within the same row that the user selected "Yes." Then, I'm pushing that data to a custom object to be called later.

It's apparent to me, that I'm doing it wrong. There's, at least, something wrong with my loop. What have I done? :)

EDIT: After reviewing the suggestion by VyTautas, here's my attempt at a working loop:

for (var k = 0; k < dataMir.length; k++) {
            var mirColI = dataMir[k][8];
            var mirRefDesc = dataMir[k][2];
            var mirAdminActions = dataMir[k][3];
            var mirDates = dataMir[k][4];
            if (mirColI === "Yes") {
              var activeRowMir = mirColI.getActiveSelection.getRowIndex();
              //Pull selected values from the active row when Yes is selected
              var mirRefDescRange = getMirSheet.getRange(activeRowMir, mirRefDesc);
              var mirRefDescValues = mirRefDescRange.getValues();
              var mirAdminActionsRange = getMirSheet.getRange(activeRowMir, mirAdminActions);
              var mirAdminActionsValues = mirAdminActionsRange.getValues();
              var mirDatesRange = getMirSheet.getRange(activeRowMir, mirDates);
              var mirDatesValues = mirAdminActionsRange.getValues();
              var mirHoldingArray = [mirRefDescValues, mirAdminActionsValues, mirDatesValues];
              //Push mirHoldingArray values to holdingData
              holdingData.refDesc = mirHoldingArray[0];
              holdingData.adminActions = mirHoldingArray[1];
              holdingData.dates = mirHoldingArray[2];
            }
        }

Solution

  • 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 process
       var numRowsMir = getMirSheet.getLastRow(); // Number of rows to process
    
    // Fetch the range of cells A2:Z506
       var 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.