Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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);
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading