将多列添加到谷歌脚本Vlookup功能

function finderLookUPReturnArray_(Sheet,Range,lookupItem,IndexOffset,IndexOffsetReturn)   // finderLookUP(Ref Sheet, Ref Sheet Range, Current lookup Item, Ref Sheet Column for Current lookup Item , Ref Sheet Column for qualified Current lookup Item RETURN)
{
var newArray = new Array();
var data = SpreadsheetApp.getActive().getSheetByName(Sheet).getRange(Range).getValues();             //Syncs sheet by name and range into var 
for(var i = 0; i<lookupItem.length; i++)
{
for(nn=0;nn<data.length;nn++)
{
  var Sending = "";
if (data[nn][IndexOffset]==lookupItem[i])
   {
    var Sending = data[nn][IndexOffsetReturn];
    newArray.push(Sending);
    break;
  }
}
if(isEmpty_(Sending)==true)
{
  var Sending = "#N/A";
  newArray.push(Sending);
}
}
  return newArray;
}
//------------------
function isEmpty_(string) 
{
if(!string)             return true;         
if(string == '')        return true;
if(string === false)    return true; 
if(string === null)     return true; 
if(string == undefined) return true;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^'s's*/, '').replace(/'s's*$/, ''))) return true;       
return false;        
}

@AD：AM 我认为在数组公式中使用多列调用时需要*sign(Row(A2:A))。 但我刚刚用一张全新的床单尝试了一下，看起来你是对的！我刚刚尝试过，但新工作表仍然不支持数组之外的 Vlookup 多列调用。

看：https://github.com/preactive/GAS-Helper-Function/blob/master/Lookup_.js &https://docs.google.com/a/costco.com/spreadsheets/d/1nDOFWeXfeFEGvCjhH0YHunjDuh4atCVMpiEiRV7wAFM/edit#gid=0

也只是想说，自从我开始进入谷歌应用世界以来，我已经从你那里学到了很多东西。我会从你那里寻找文章，因为我知道我可以改编，因为它写得很清楚！

但我没有放弃，这就是我想出的。

理由：

1：基于脚本的版本是由于要引用大量数据以减少一致的计算和减慢工作表。

2：数据库，如导入范围，与Vlookup属性。

用法：

var LocNum = spreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('J2:J').getValues();

FinderLookUpReturnArrayRange_(LocNum,0,'Data','A:G',[1,3,4,7],'test',1,1);

设置在目标工作表的位置 1，1

或

FinderLookUpReturnArrayRange_(LocNum,0,'Data','A:G',[1,3,4,7],'test',"Next",1);

在目标工作表的下一个未占用行（数据库样式）处设置

//-------------------------------------------------

function FinderLookUpReturnArrayRange_(Search_Key,SearchKey_Ref_IndexOffSet,Ref_Sheet,Ref_Range,IndexOffSetForReturn,Set_Sheet,Set_PosRow,Set_PosCol)   
{
var twoDimensionalArray = [];
var data = SpreadsheetApp.getActive().getSheetByName(Ref_Sheet).getRange(Ref_Range).getValues();         //Syncs sheet by name and range into var
for (var i = 0; i<Search_Key.length; i++)  // i = number of rows to index and search  
{
var Sending = [];
var newArray = [];
for (nn=0;nn<data.length;nn++)  // nn = number of row data is found at
{
  if (data[nn][SearchKey_Ref_IndexOffSet]==Search_Key[i]) //if statement is triggered when the search_key is found.
  {
    for (cc=0;cc<IndexOffSetForReturn.length;cc++) //cc = numbers of columns to ref
    {
      var iosr = IndexOffSetForReturn[cc];
      var Sending = data[nn][iosr];
      if(isEmpty_(Sending)==true) //if statement for if one of the returned cells is blank
      {
      var Sending =  "#N/A";
      }
      if (IndexOffSetForReturn.length>1) //if statement for multi-Column returns
      {
        newArray.push(Sending);
        if(IndexOffSetForReturn.length-1 == cc) // if statement for pulling all columns into larger array
        {
          twoDimensionalArray.push(newArray);  
          break;
        }
      }
      else if (IndexOffSetForReturn.length<=1) //if statement for single-Column returns
      {
        twoDimensionalArray.push(Sending);
        break;
      }
    }
  }
  if(data.length-1==nn && isEmpty_(Sending)==true) //following if statement is for if the current item in lookup array is not found.  Nessessary for data structure.
  {
    for(na=0;na<IndexOffSetForReturn.length;na++) //looping for the number of columns to place "#N/A" in to preserve data structure
    {
      var Sending = "#N/A";
      newArray.push(Sending);
    }
    twoDimensionalArray.push(newArray);  
  }
}
}
if(typeof Set_PosRow != "number")
{
var Set_PosRow = getFirstEmptyRowUsingArray_(Set_Sheet);    //for usage in a database like entry without having to manually look for the next level.
}
for (var l = 0; l < Search_Key.length; l++)                                    //Builds 2d Looping-Array to allow choosing of columns at a future point
{
if (IndexOffSetForReturn.length<=1)  //checks to see if it's a single column return
{
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_PosRow + l,Set_PosCol).setValue(twoDimensionalArray[l]);
}
}
if (IndexOffSetForReturn.length>1)  //checks to see if it's a multi column return
{
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_PosRow,Set_PosCol,twoDimensionalArray.length,twoDimensionalArray[0].length).setValues(twoDimensionalArray);
}
}

//-------------------------------------------------

function isEmpty_(string) 
{
if(!string)             return true;         
if(string == '')        return true;
if(string === false)    return true; 
if(string === null)     return true; 
if(string == undefined) return true;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^'s's*/, '').replace(/'s's*$/, ''))) return true;       
return false;        
}

//-------------------------------------------------

function getFirstEmptyRowUsingArray_(sheetname) 
{
  var data = SpreadsheetApp.getActive().getSheetByName(sheetname).getDataRange().getValues();
  for(var n = data.length ; n<0 ;  n--)
  {
    if(isEmpty_(data[n][0])=false)
    {
      n++;
      break;
    }
  }
  n++
    return (n);
}