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);