I made the following function in apps script to recreate a vlookup that is done in google sheets and insert the results into a new cell:

function doLookUP() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsCombine = ss.getSheetByName('Results Data');
  const wsAlliance = ss.getSheetByName('VlookUp Data');
  const Avals = wsCombine.getRange("A1:A").getValues();
  const Alast = Avals.filter(String).length;

  const imageData = wsAlliance.getRange(2,1,wsAlliance.getLastRow()-1,2).getValues()
  const searchValues = wsCombine.getRange(2,4,wsCombine.getLastRow()).getValues()

  const matchingImage = searchValues.map(searchRow => {
    searchRow[0]
    const matchRow = imageData.find(r => r[2] == searchRow[0])
    return match = matchRow ? [matchRow[2]] : [" "]
  })


wsCombine.getRange(2,5,Alast,1).setValues(matchingImage)
  

}

The Data in the sheets looks like this:

Results Data:

VlookUp Data

At the moment the code is doing the look up in one column (ID) and returning values, however I would like to do the same only that if the look up bring data as blank to look into the the other columns that has data(numbericID) and to bring results like this:

EDIT: I've made a change to the table where the Result data is as I can't make the column into a single array and do a lookup based on that as there may be data on the column instead of blank, however on the VlookUp Data table there's only one ID.

CodePudding user response：

Here is an example of how to compare a value with more than one column value.

Notice that Array.some() is like break when the condition is met.

I did not address if the value is not found in the lookup table.

Code.gs

function doLookUp() {
  try {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let wsCombine = ss.getSheetByName('Results Data');
    let wsAlliance = ss.getSheetByName('VlookUp Data');
    let imageData = wsAlliance.getRange(2,1,wsAlliance.getLastRow()-1,2).getValues();
    let searchValues = wsCombine.getDataRange().getValues();
    searchValues.shift(); // remove headers
    let find = [["CT"]];
    searchValues.map( (row,index) => {
        find.push(["not found"]);  //  added
        imageData.some( image => { 
            if( ( row[1] === image[0] ) || ( row[2] === image[0] ) ) {
              find[index 1] = [image[1]];  //  updated
              return true;
            }
            return false;
          }
        );
      }
    );
    console.log(find);
    wsCombine.getRange(1,5,find.length,1).setValues(find);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

10:36:33 AM Notice  Execution started
10:36:35 AM Info    [ [ 'CT' ], [ 'CR' ], [ 'TK' ], [ 'HK' ], [ 'US' ] ]
10:36:35 AM Notice  Execution completed

Reference

• Array.shift()
• Array.map()
• Array.some()
• Array.push()