Find row and set value in column google app scripts-CodePudding

I have a 2d array:

const arr = [ [ 'John Doe',
    '[email protected]',
    'Some text here',
    '' ],
  [ 'Steven Smith',
    '[email protected]',
    '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',
    '[email protected]',
    '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.

CodePudding user response：

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

const arr = [
    ['John Doe', '[email protected]', 'Some text here', ''],
    [
      'Steven Smith',
      '[email protected]',
      '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);