'Nested loop of array of dicts doesn't work in Google Sheets script
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
