VLookup的Google脚本版本(更有效的方法?) [英] Google Script version of VLookup (More Efficient Method?)

问题描述

我正在尝试组合一个函数，该函数将允许我根据关键列将一列的信息从一张纸拉到另一张纸.这将类似于excel/google中的索引匹配或vlookup.

I'm trying to put together a function that will allow me to pull a column's info from one sheet to another based on a key column. This would work similar to an index match or vlookup in excel/google.

样本数据:

我尝试过的事情:

function vlookup(importFromSht, importToSht, importFromCompCol, importToCompCol,importFromCol, importToCol){
  var lastImportFromRN = importFromSht.getLastRow();
  var lastImportToRN = importToSht.getLastRow();
  var importFromCompArr =  importFromSht.getRange(2, importFromCompCol, lastImportFromRN, 1).getValues();
  var importToCompArr =  importToSht.getRange(2, importToCompCol, lastImportToRN, 1).getValues();
  var importFromArr =  importFromSht.getRange(2, importFromCol, lastImportFromRN, 1).getValues();
  var importToArr = [];

  for (var i in importToCompArr) {
    for (var j in importFromCompArr){
      if (importToCompArr[i].toString() == importFromCompArr[j].toString()) { 
        importToArr.push(importFromArr[j]);
      }
    }
  }

  //Paste to column
  importToSht.getRange(2,importToCol,importToArr.length,1).setValues(importToArr);
}

已定义参数

• importFromSht-我们正在从中获取值的表.
• importToSht-工作表值将被复制到.
• importFromCompCol-具有匹配值的列(数字).
• importToCompCol-具有匹配值的列(数字).
• importFromCol-包含需要复制的值的列(数字).
• importToCol-将值复制到的列(数字).

要求:

• 至少有超过6K的行，并且可能会更多.执行速度很重要.我的方法是正确的方法还是有更有效的方法?我的脚本使执行时间增加了大约30秒.
• 我想看到它在本项目和其他项目的其他领域中使用时，将其称为函数.

推荐答案

问题:

• 慢速脚本:

  • O(n ² ):对于数组1中的每个元素，数组2从顶部到底部进行迭代.即使在数组2中找到匹配项后，循环也不会中断( break )，但是内部循环会完成，直到不必要地在array2结束为止.

  • O(n²): For every element in array 1, the array 2 is iterated from top to bottom. Even after finding a match in array 2, the loop is not broken(break), but the inner loop is completed until the end of the array2 unnecessarily.

  getValues().与电子表格联系非常昂贵.因此，限制它是必要的.

  getValues() is requested twice for two columns of the same sheet. Contact with spreadsheet is costly. So, limiting it is necessary.

  一种实现 O(n)的可能解决方案:

  One possible solution to achieve O(n):

  • 使用键1作为要查找的值"的数组1创建一个新对象.这样就有可能每次都为数组2中的每个值直接访问此对象中的值.

  const ss = SpreadsheetApp.getActive();
  /**
   * @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
   * @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
   * @param {Number} fromCompCol -Column number of fromSht to compare
   * @param {Number} toCompCol -Column number of toSht to compare
   * @param {Number} fromCol -Column number of fromSht to get result
   * @param {Number} toCol -Column number of toSht to get result
   */
  function vlookup_2(
    fromSht = ss.getSheetByName('Sheet1'),
    toSht = ss.getSheetByName('Sheet2'),
    fromCompCol = 1,
    toCompCol = 1,
    fromCol = 2,
    toCol = 2
  ) {
    const toShtLr = toSht.getLastRow();
    const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
    const fromArr = fromSht.getDataRange().getValues();
    fromCompCol--;
    fromCol--;
  
    /*Create a hash object of fromSheet*/
    const obj1 = fromArr.reduce((obj, row) => {
      let el = row[fromCompCol];
      el in obj ? null : (obj[el] = row[fromCol]);
      return obj;
    }, {});
  
    //Paste to column
    toSht
      .getRange(2, toCol, toShtLr - 1, 1)
      .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));
  }
  
  

  性能:

  • 在工作表1中的10000行和工作表2中的10000行的〜5s
  • 时间复杂度
  • 哈希图

  这篇关于VLookup的Google脚本版本(更有效的方法?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！