Home > Software engineering >  Google Apps Script FOR Loop failing to automatically iterate with nested IF statements
Google Apps Script FOR Loop failing to automatically iterate with nested IF statements

Time:07-03

Overview I'm constructing a script to assign individuals to groups based on first-come-first-served ranked preference input data. The script constructs a response order from the data array using two For Loops, selecting student1, and then choice 1, 2, 3. It then checks to see which group matches the student's first choice, and if that group is below current capacity, it will copy student1 to that group and move onto student2 choice 1, 2, 3.

Issue ..The need to include a i loop iteration trigger (line 46) - my understanding is that after meeting all of the required criteria for the loop, it would iterate by itself. Removal of i at line 46 breaks the script.

A consequence of having this iteration trigger is that at i = respCount (number of student responses), after assigning the last student, the i will still iterate the counter, and then cause

"TypeError: Cannot read property '0' of undefined"

as it tries to read outside of the bounds of the responses array.

What I've Tried In order to prevent this error, I have added a check to see if the response array is undefined (line 21), however this merely hides the problem.

I am new to coding, and desire to write clear and maintainable code.

Example Spreadsheet: enter image description here

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    const respCount = sheet.getRange("A1:A").getValues().filter(String).length //Number of responses to parse
    const responses = sheet.getRange("A1:D" (respCount 1)).getValues() //All students & responses as an array. ( 1) gets accurate range size due to header row
    console.log(respCount);
    console.log(responses);
  }
  catch(err) {
    console.log(err);
  }
}

2:10:50 PM  Notice  Execution started
2:10:51 PM  Info    5
2:10:51 PM  Info    [ [ 'A', 1, '', '' ],
  [ 'B', 2, '', '' ],
  [ 'C', 3, '', '' ],
  [ '', 4, '', '' ],
  [ '', 5, '', '' ],
  [ 'D', 6, '', '' ] ]
2:10:51 PM  Notice  Execution completed

Prefered method IMHO. It makes one call to get all values and filters out rows starting with blank. I don't need 2 arrays to work with. It also get only rows that contain data.

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    let responses = sheet.getDataRange().getValues();
    responses = responses.filter( row => row[0] !== "" );
    console.log(responses);
  }
  catch(err) {
    console.log(err);
  }
}

2:12:23 PM  Notice  Execution started
2:12:24 PM  Info    [ [ 'A', 1 ],
 [ 'B', 2 ],
 [ 'C', 3 ],
 [ 'D', 6 ],
 [ 'E', 7 ] ]
2:12:24 PM  Notice  Execution completed
  • Related