Create a loop for each cell in array, returning a lookup value from another sheet

user_5603816 0
Viewed 41

My main goal here is to run a lookup for each cell based on the value in column b from another Google sheet and if there are multiple entries return whether there is a match or not. Eg look up the occupant on the application and check to see if it matches information held on the "data base"

Response Sheet

"Database"

I have tweaked the above script which returns the first value almost like a Vlookup (the cell ranges aren't dynamic either) however I am only just learning Javascript and cant work out what is needed to achieve the desired result here.

Any help would be appreciated as I am keen to learn.

function CtLookups() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();     

 var data = SpreadsheetApp.openById('1OZ-oNcx-ZeDqNpxthzH7lxbPZUR88WJC3wmfvHx-yHc').getSheetByName("Report 1").getRange('A:B').getValues()

 var searchValue = s.getRange("B3").getValue();

 var dataList = data.map(x => x[0])

 var index = dataList.indexOf(searchValue);

 if (index === -1) {

    throw new Error('Value not found')

 } else {

      var foundValue = data[index][1]

     s.getRange("D3").setValue(foundValue);

 }
}
google-apps-script google-sheets javascript
1 Answers

Probably it could be boiled down to this custom function:

function get_name_by_ref(ref) {
  var base = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('base').getDataRange().getValues();
  try { return base.find(x => x[0] == ref)[1] }
  catch(e) { return 'none' };
}

Active sheet:

enter image description here

Sheet 'base':

enter image description here

Here is my sheet.

Except a custom function can't open another spreadsheet. It works only within the same spreadsheet.

If your 'base' is another spreadsheet you need a bit more complicated solution. It could be something like this:

function fill_column_d() {
  var id = '###'; // put ID of your 'base' spreadsheet here
  var sheet_name = 'base'; // change it with your sheet name
  var base = SpreadsheetApp.openById(id).getSheetByName(sheet_name).getDataRange().getValues();

  // make an object with keys from column A and values from column B
  // {ref1:name1, ref2:name2, ref3:name3, ...etc}
  var obj = {};
  for (let [ref, name] of base) obj[ref] = name;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var [header, ...data] = sheet.getDataRange().getValues();

  for (let i in data) {
    var ref = data[i][1]; // take ref number from the second column (B)
    data[i][3] = (ref in obj) ? obj[ref] : 'Value not found'; // fill 3rd column (D)
  }
  
  sheet.getRange(2,1,data.length,data[0].length).setValues(data);
}

The function fill_column_d() fills column D with names based on the reference numbers from column B and the list of refs-names from the 'base' sheet of another spreadsheet.

It's not clear yet how you're supposed to run this function. For now you can run this function from Script Editor. Or you can add a custom menu or a 'button' to run this function from the sheet.

user_142654690
Related