Inner For Loop not executing in App Script

Question

Context

In google app script for gsheets I am running this function that searches the names of items in a comma separated list within a single cell per row, against a list of item name and ids in 2 columns.

The function is called within another function that iterates through each row of data in the Data Table.

The goal is to replace the names with the ids in a comma separated list.

The Problem

The nested loop just doesn't run! When I set up training outputs to test where the code gets blocked it's at the inner for loop, it breaks at that line and doesn't execute at all. I cannot figure out why that would be as I can't see a problem with my syntax.

Any ideas where I could be going wrong please?

The data looks like this:


Item Directory Table:

Item ID | Item Name
--------------------
1234    | Item 1
46976   | Item 2
3526    | Item 3
32255   | Item 4
425     | Item 5  


Data table:

Data Name | Items
--------------------
Row 1     | Item 1, Item 2, Item 5
Row 2     | Item 5, Item 2
Row 3     | Item 4
Row 4     | 
Row 5     | Item 1

Desired output:

Row 1 = [1234, 46976, 425] // updated item 2
Row 2 = [425, 46976]
Row 3 = [32255]
Row 4 = []
Row 5 = [1234]

This is my function:

function findItems(x) {
  var items = [];
  var j = 0;
  var i = 0;
  var itemNames = Items.getRange(2 ,11).getValue().split(', ');
  var itemIDs = Items.getRange(3 ,11).getValue().split(', ');
  
  if (data.getRange(x, 6).getValue() != '') {
    items = data.getRange(x, 6).getValue().split(', ');
    for (i = 0; i < items.length; i++) {
      output.getRange(2, 1).setValue(items[i]);
      for (j = 0; j < itemNames.length; j++) {
       if (items[i] === itemNames[j]) {
          items[i] = itemIDs[j];
        }
      }
    }
  }
  return items
}

If the inner loop is not executing it just means the condition part of the for-loop is not satisfied. — TheMaster
– TheMaster, Commented Dec 15, 2020 at 13:15
I am not against GAS, but I think in your scenario a google formula would be more suitable unless you don't have other option rather than using GAS. — Marios
– Marios, Commented Dec 15, 2020 at 13:17
Your desired output is easily obtained with an object that converts item names to IDs and a single loop — Cooper
– Cooper, Commented Dec 15, 2020 at 15:07
If your question was solved, please push the accept button. Other people who have the same problem with you can also base your question as a question which can be solved. If you don't find the button, feel free to tell me. stackoverflow.com/help/accepted-answer — NightEye
– NightEye, Commented Dec 16, 2020 at 17:37

NightEye · Accepted Answer · 2020-12-15 16:12:47Z

What I will provide below is a one-time run for all rows. Feel free to modify to get your expected behavior, you can add a parameter or call the function as formula to populate the cells one by one.

function findItems() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var ids = sheet.getRange('A2:A' + lastRow).getValues().flat();
  var names = sheet.getRange('B2:B' + lastRow).getValues().flat();
  var data = sheet.getRange('C2:C' + lastRow).getValues().flat();
  var items = sheet.getRange('D2:D' + lastRow).getValues();

  items.forEach(function(item, count){
    var elems = item[0].split(",");
    for(var i = 0; i < elems.length; i++){
      for(var j = 0; j < names.length; j++){
        if(elems[i].trim() == names[j].trim()){
          elems[i] = ids[j];
          break;
        }
      }
    }    
    // separated the formatting so you can easily modify here
    // followed the formatting in your post
    var output = data[count] + ' = [' + elems.join(', ') + ']';
    sheet.getRange('E' + (count + 2)).setValue(output);
  });
}

This is the expected output from this code.

If you want to return something, you might want to modify the code above as this only sets the string output as the cell's value.

Logic is pretty straight forward, but if you have any clarifications/questions, feel free to comment below.

Collectives™ on Stack Overflow

Inner For Loop not executing in App Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related