Apps Script - compare strings in two arrays

Question

I am a stuck on this piece of code. I have an Example sheet with two tabs. The first tab is new items. A new item is comprised of two pieces, a attribute code (string), and an item ID (number). In the other tab "Locations" there are a bunch of empty locations. Each location has primary attribute (string), and a set of secondary attribute codes in a longer string.

I have assigned these two ranges to two unique arrays.

function matchcodes() {

var locss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Locations');
var lastlocRow = locss.getLastRow();
var newitems = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('New Items');
var lastNIRow = newitems.getLastRow();
var itemcodes = newitems.getRange("A1:B" + lastNIRow).getValues();
var locations = locationssheet.getRange("A2:D" + lastlocationRow).getValues();
Logger.log(itemcodes)
Logger.log(locations)}

What I am attempting to do is compare itemcodes[i][0] to locations[j][2] (match item attribute with location primary attribute). If the strings match I want to copy itemcodes[i][1] (ItemID) and set it as the value of locations[j][1]. If the strings do not match check the next iteration of locations[j][2]. If no matching attributes are found in locations[j][2], I would like to see if it is contained as a substring in locations[j][3] (starting back at the top and iterating through the whole list of secondary attributes. If the substring code is contained in loactions[j][3] I would like take the same action in the first IF condition.

Once a new item is matched, the loop can break, and the next item can be located itemcodes[i+1][0]. If no match is found in the primary or secondary search, also iterate to the next new item.

Where I'm struggling is writing the condition statements to compare both strings and substrings within strings.

//for (var i = 0; i < itemcodes.length; i++) {
   //for (var j = 0; j < locations.length; j++) {
    //if (itemcodes[i][0] == locations[j][2]) {
      // I want set the value of locations[j][1] with itemcodes[i][1] 
    }
     // if no match is found in entire [j][2] column, search for substring in locations[j][3] column
    //if item match is found, or no match is found in all of [j][2] or [j][3] break loop and iterate to [i+1][0] and start the next loop

Input (3 iterations) Results

Any help would be much appreciated. Or if you can point me to a similar thread. (I've not had any success finding a similar example) Thanks in advance!

I have to apologize for my poor English skill. Unfortunately, I cannot imagine your goal from match item attribute with location primary attribute. In order to correctly understand your question, can you provide the sample input and output situations you expected as the image? — Tanaike
– Tanaike, Commented Jun 18, 2022 at 1:33
I added the photos for sample input and output. In the first iteration (itemID 4728) it would see the attribute "xafx" and match it with the first location it sees with a matching primary attribute ("xafx"). Same for the 2nd iteration (itemID 33692). However, the third itemID (25691) has no locations with matching primary attributes ("xaex") so in this instance it loops through the secondary column and copies the ID in the first location where the substring "xaex" is found — Nick Eddy
– Nick Eddy, Commented Jun 18, 2022 at 1:48
Thank you for replying and adding more information. For example, when the cell "C2:D2" is seen, the values are empty and xaex xbcex xbdex, respectively. In this case, you want to check xaex of xaex xbcex xbdex. And, if xaex is not found, you want to check xbcex. Is my understanding correct? If my understanding is correct, when no values are found from the columns "C" and "D", what result will you obtain? — Tanaike
– Tanaike, Commented Jun 18, 2022 at 1:54
It may help to provide some real world perspective. In my example the attributes (i.e. xaex) are unique codes that are short hand notation for properties of that product (refrigerated, fragile, organic). I am trying to assign products to locations, but locations are only capable of having items with specific attributes. We want to check all the primary location attributes first (col C). This would be the "ideal" location to place this item. However if there are no locations with a matching primary attribute, I want to check col D. "xaex xbcex xbdex" is one string of 3 attribute codes. — Nick Eddy
– Nick Eddy, Commented Jun 18, 2022 at 2:00
If that code matches any of the 3 attribute codes in that string, it is ok to assign that item to that location (its not ideal, but still ok) So I want to check for a match in Col C, and if no match is found, check if code is contained as substring in Col D. If no matches are found the item cannot be assigned, and we move onto the next item — Nick Eddy
– Nick Eddy, Commented Jun 18, 2022 at 2:01

NEWAZA · Accepted Answer · 2022-06-18 02:24:51Z

1

Try:

function matchCodes() {

  const newItems = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`New Items`)
  const locations = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`Locations`)

  const newItemsValues = newItems.getDataRange().getValues()
  const locationsValues = locations.getDataRange().offset(1, 0).getValues()

  newItemsValues.forEach(([attribute, id]) => {

    const primaryTarget = locationsValues.findIndex(row => row[2] === attribute && row[1] === ``)
    if (primaryTarget !== -1) return locationsValues[primaryTarget][1] = id

    const secondaryTarget = locationsValues.findIndex(row => row[3].includes(attribute) && row[1] === ``)
    if (secondaryTarget !== -1) return locationsValues[secondaryTarget][1] = id

  })

  locations.getDataRange().offset(1, 0).setValues(locationsValues)

}

Learn More:

edited Jun 18, 2022 at 2:24

answered Jun 18, 2022 at 2:19

NEWAZA

1,6202 gold badges6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nick Eddy Over a year ago

This worked great. Familiarizing myself with => syntax, and findindex() I should be able to keep adding conditions with && in each of the primaryTarget and scondaryTarget declarations correct? Thanks for the help!

NEWAZA Over a year ago

Correct, be sure to checkout Array Methods if you haven't studied up on them. If you have any other questions feel free to ask!

Nick Eddy Over a year ago

Thanks for the pointers, there is a lot of good stuff here. Would you be able to show the primaryTarget = locationValues.findIndex() with traditional function() syntax? Just so I can track how its working. The examples I see have different parameter declarations, so im trying to track what you're doing with the row => row[2] portion. Thanks!

Nick Eddy Over a year ago

A better way of phrasing my question. Using the article you provided, array.findIndex(row) => row[2] === attribute. I am confused at how the element (row) is being used to reference the location row{2]. findIndex((element) => { /* ... */ } )

Yuri Khristich Over a year ago

I suppose in 'classic notation' it would be:

const primaryTarget = locationsValues.findIndex( function(row) { if (row[2] === attribute && row[1] === ``) return row } )

Basically it returns an index of the first row of a given array that (the row) meets some conditions (cell2 == attribute, and cell1 is empty). It there is no such row it returns '-1'

|

Collectives™ on Stack Overflow

Apps Script - compare strings in two arrays

1 Answer 1

Try:

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Try:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related