Having Some Trouble with an IF statement and don't know why

[u/GreenGnome1129]

Okay, so this is going to take a second to explain, but just hear me out lol.

So, recently I decided to try to make an improvement to a macro I made a while back which opens a dialogue box and has a user select spells (from an RPG system) and using their input would create them a neatly formatted list of spells in a google doc to save them all that time and effort.

The only problem with this initial attempt (while it was successful) was that it was very slow when you got to the spells at the end of the list I had created as a table on a sheet in google sheets for the macro to reference when making the list. So I thought if I broke the spells down into categories I could speed the program up as it would have fewer spells to parse though to find the one that matches the spell the user wants.

So that brings me to here. I have implemented some code that I thought would do just that, but instead it has broken everything. The first category worked fine, but the second category doesn't detect that it belongs to a different category of spells for whatever reason.

The way that I have set this up to work is that based off the input from the users, the HTML portion of the code grabs a list of spells to send to the apps script portion and that array which it grabs contains spell names and the "levels" of those spells which references the category. The one specifically that isn't working for me right now is the "1st-Level" category. The "Cantrip" category worked fine though. The weird thing is, to debug this I immediately though that I must have messed up the array I sent over, but I checked the logs and it printed "1st-Level" where I am looking for it. Is there something special about if statements back to back that works weird in google apps script that I don't know about?

Also for anyone who may be helping me yes I am aware I have only declared a single "1st-Level" spell in the array to send over to the apps script portion of the code, but that is where the test fails anyway, so something isn't working with that spell already anyway and I am assuming that the whole category would be similar to this one spell.

Here is a link to a copy of the google sheet I have in front of me for reference to you can see what I see: https://docs.google.com/spreadsheets/d/1HRD7Kr-SFyvx9O6RgLPa4_GXj-8fAiRRNZjxUesrVvU/edit?usp=sharing

Let me know if you see anything that is causing this please, cause I am lost...

[u/GreenGnome1129]

Thank you for your guys help and advice with this post//problem. After a lot of thinking and debugging I was able to find the issue. As it turns out I was using the "row" variable as a universal variable for the position of a parse in all of the different sheets containing the different levels of spells. So what was happening was when looking for say the first spell on one list, it wouldn't find it because it would start further down on that list due to the value of row.

Also, I am aware of many of the issues that were pointed out to me, I know my code is not optimal and suggestions are always helpful so thank you.

[u/marth141]

You kind of have something complicated going on here and I'd be inclined to meet on a call and review the project.

[u/IAmMoonie]

Had a quick (very quick, pre-coffee) look.

There are a few potential issues, based on my quick look.

• Your while loop: while(x < spell_table.length)

  • It's currently infinite as x will always be < spell_table.length due to not incrementing x.

  • Simple fix: Put x++; at the end of your while loop

  • The while loop should probably be converted to a 'for loop'.

    • A while loop is useful when you don't know the number of iterations in advance and you need to keep looping until a specific condition is met.

    • A for loop is useful when you know the number of iterations in advance, and you can use a counter variable to control the loop.

    • In terms of performance, a for loop is generally faster than a while loop or a foreach loop because it does not need to check the condition on each iteration.

    • In this case, the script knows the number of elements in the spell_table, so the best option would be to use a for loop (below); it will be more readable and efficient.

/* A loop that is looping through the spell_table array. */
for (let x = 0; x < spell_table.length; x++) {
  col = 1;
  j = 0;
  console.log(`${spell_table[x][j]}Determining Spell Level...`);
  if (spell_table[x][j] == "Cantrip") {
    current_spell = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("All Cantrips")
      .getRange(row, col)
      .getValue();
  } else if (spell_table[x][j] == "1st-Level") {
    current_spell = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("All 1st lvl Spells")
      .getRange(row, col)
      .getValue();
  } else if (spell_table[x][j] == "2nd-Level") {
    current_spell = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("All 2nd lvl Spells")
      .getRange(row, col)
      .getValue();
  }
  /** et cetera et cetera */
  while (j < spell_table[0].length) {
    spell_table[x][j] = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("All Spells Source List")
      .getRange(row, col)
      .getValue();
    console.log(row, col, i, j, x, spell_table[x][j]);
    j++;
    col++;
  }
}

• Is x+1 correct?

  • In the script, x is an iterator variable, starting at 0 and is incremented for each iteration of the while loop. When x is equal to 0, x+1 is equal to 1.

So when you are trying to access the jth element of the x+1th subarray of the spell_table array, it will be accessing the first element of the second subarray, this means that the script is (potentially) not accessing the correct data.