So I want to loop a column Text in a Google sheet and assign values to columns main_category, item_category and item based on the value of Text which maps to an array of dictionaries for each rule applied. I'm struggling at setting up the nested loop for the dictionary.

Sheet structure / source data:

Text     main_category  item_category   item
-------------------------------------------------
Banana   
Tomato  
Choco    

Desired outcome:

Text     main_category  item_category   item
-------------------------------------------------
Banana   Fruits         Yellow          Banana
Tomato   Vegetables     Red             Tomato
Choco    Sweets         Brown           Choco

My approach:

function process_actuals() {

  // Initiating an array of dicts
  var rules_array = [
    {contains: 'Bana', main_category: 'Fruits', item_category: 'Yellow', item: 'Banana'},
    {contains: 'Tomato', main_category: 'Vegetables', item_category: 'Red', item: 'Tomato'},
    {contains: 'Choco', main_category: 'Sweets', item_category: 'Brown', item: 'Choco'},
  ];

  // Get active Spreadsheet
  var s = SpreadsheetApp.getActiveSpreadsheet();

  // Get sheet "Source"
  var sht = s.getSheetByName('Source')

  // Get the range where data is present in sht
  var drng = sht.getDataRange();

  // Get the range size 
  var rng = sht.getRange(2,1, drng.getLastRow(),drng.getLastColumn());

  // Get an array of values within rng
  var rngA = rng.getValues();

  // Set up loop
  for (var i = 2; i < rngA.length; i  ) {

    // Get the search string
    let search_cell = sht.getRange(i, 1)
    search_string = search_cell.getValue()

      // Loop array of dicts and check if search string is within "contains" key
      for (var dict in rules_array) {

        // If search string is in contains key, populate row
        if (search_string in dict['contains']) {  // breaks here
          
          // Populate rows
          var target_cell = sht.getRange([i][2]) // main_category
          target_cell.setValue(dict['main_category'])
        }
      }
  }
}

I receive the error TypeError: Cannot use 'in' operator to search for 'Banana' in undefined.

CodePudding user response：

Modification points:

• When your rules_array is used, dict of for (var dict in rules_array) { is 0, 1, 2,,,. I thought that this might be the reason for your issue. This has already been mentioned in the comments.
• When setValue is used in the loop, the process cost will become high.

When these points are reflected in a sample script, how about the following sample script?

Sample script:

function process_actuals() {
  var rules_array = [
    { contains: 'Bana', main_category: 'Fruits', item_category: 'Yellow', item: 'Banana' },
    { contains: 'Tomato', main_category: 'Vegetables', item_category: 'Red', item: 'Tomato' },
    { contains: 'Choco', main_category: 'Sweets', item_category: 'Brown', item: 'Choco' },
  ];
  
  // 1. Retrieve values from "Source" sheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source');
  var [[,...header], ...values] = sheet.getDataRange().getValues();

  // 2. Create an array for putting to the sheet.
  var v = values.map(([a]) => {
    var t = rules_array.filter(o => a.includes(o.contains));
    return t.length == 1 ? header.map(h => t[0][h]) : Array(3).fill("");
  });

  // 3. Put the array to "Source" sheet.
  sheet.getRange(2, 2, v.length, v[0].length).setValues(v);
}

• In this sample script, an array for putting to Spreadsheet is created using the retrieved values and rules_array, and put it to the sheet of Source.

Note:

• In this sample script, from your question, it supposes that the header row of your script is Text, main_category, item_category, item. Please be careful about this.

Reference:

• map()

Added:

From your following replying,

However when I log v it is empty, thus the macro doesn't populate anything. Do I have to further enrich your sample script before? Re process cost, we aretalking about a couple of thousand rows only

In this case, I'm worried that your Spreadsheet might not have the header row. If my understanding is correct, how about the following sample script?

Sample script:

function process_actuals() {
  var rules_array = [
    { contains: 'Bana', main_category: 'Fruits', item_category: 'Yellow', item: 'Banana' },
    { contains: 'Tomato', main_category: 'Vegetables', item_category: 'Red', item: 'Tomato' },
    { contains: 'Choco', main_category: 'Sweets', item_category: 'Brown', item: 'Choco' },
  ];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source')
  var values = sheet.getDataRange().getValues();
  var header = ["main_category", "item_category", "item"];
  var v = values.map(([a]) => {
    var t = rules_array.filter(o => a.includes(o.contains));
    return t.length == 1 ? header.map(h => t[0][h]) : Array(3).fill("");
  });
  sheet.getRange(1, 2, v.length, v[0].length).setValues(v);
}

• In this sample script, it supposes that your Spreadsheet has no header row. And, the order of values is given by var header = ["main_category", "item_category", "item"];.