Find row and set value in column google app scripts

I have a 2d array:

const arr = [ [ 'John Doe',
    'john@something.com',
    'Some text here',
    '' ],
  [ 'Steven Smith',
    'steven@something.com',
    'Another irrelevant text here',
    '' ],
]

I want to find a row in the tab that matches the name in each array in this list and add a text value in the last column (where the ” is).

For example:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const range = sheet.getDataRange()
const data = range.getValues()
const people = data.slice(1)

if a name in arr is found in people, add the text ‘Found’ in the last column in people for that name.

people looks like this:

[ [ 'John Doe',
    'john@something.com',
    'Some text here',
    '' ] ]

I was able to find the match using this:

const peopleTarget = arr.map(person => people.find(el => el[0] === person[0]))

However, I need the row number to be able to set a value.
I know I need the getRange to be able to use setValue but I’m having difficulty grabbing the row number based on matching value between two arrays.

Any help is appreciated.

>Solution :

Create a set of names from arr, iterate over data and modify them in-place. Then setValues the modified array.

const arr = [
    ['John Doe', 'john@something.com', 'Some text here', ''],
    [
      'Steven Smith',
      'steven@something.com',
      'Another irrelevant text here',
      '',
    ],
  ],
  arrSet = new Set(arr.map((el) => el[0]));
data.forEach((people) =>
  arrSet.has(people[0]) ? (people[3] = 'found') : null
);
range.setValues(data);

Leave a Reply