时间戳

首页 > 解决方案 > 在 Google Apps 脚本中重复执行自定义函数

google-apps-script - 在 Google Apps 脚本中重复执行自定义函数

问题描述

我在 Google Apps 脚本中创建了一个自定义函数来查找与给定范围内的“描述”字符串关联的“批号”。该函数按预期输出，但我在日志中注意到该函数似乎每隔几分钟就会被调用一次。

重复调用自定义函数的日志截图

这对于函数来说是正常的还是会影响我的电子表格的性能？这是我的项目的完整源代码。

/**
     * @param {range} descriptions The range containing descriptions from Ledger
     * @return The lot numbers associated with each description
     * @customfunction
     */
    function findLots(descriptions) {
      Logger.log(input + typeof(input))
      const ss = SpreadsheetApp.getActiveSpreadsheet()
      let thisSheet = ss.getSheetByName("Lots for Descriptions")
      
      const LedgerData = ss.getSheetByName("Ledger").getDataRange().getValues()
      const ledgerMap =  { 
                          date:0,sku:1,description:2,coa:3,expiration:4,
                          quantity:5,packUnit:6, section:7,lot:8,vendor:9,
                          price:10,job:11,comment:12,componentType:13, allocated:14,
                        }
      
      let output = []
      descriptions.forEach(function(entry) {
        let description = entry[0]
        let workingData = LedgerData.map(x => x)
        let lotsFound = []
        while (true){
          let matchingDescIndex = workingData.findIndex(el => el[ledgerMap.description] == description)
          // description doesn't exist
          if (matchingDescIndex === -1) {
            lotsFound = lotsFound.filter(x => x != "")
            // Logger.log("description not found")
            break
          }
          else {
            let thisLot = workingData[matchingDescIndex][ledgerMap.lot]
            if (!lotsFound.includes(thisLot)){
              lotsFound.push(thisLot)
              // Logger.log("added: " + thisLot)
            }
          }
          
          // blank out this row to continue search in next loop iteration
          workingData.splice(matchingDescIndex, 1)
        }
        output.push(lotsFound)
        Logger.log(description + ": " + lotsFound)
      })
      return output
      
    }

标签： google-apps-scriptgoogle-sheets-formula

解决方案

我显然无法对此进行测试，但我认为它与您的功能试图做的事情相同。但如果它有效，它应该运行得更快。它完全消除了while循环。您还做了一些不必要的操作，例如使用拼接从原始数据中删除线。

function findLots(descriptions) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Ledger");
  const vs = sh.getDataRange().getValues()
  const dvs = sh.getRange(1, 3, sh.getLastRow()).getValues().flat();
  const ledgerMap = { date: 0, sku: 1, description: 2, coa: 3, expiration: 4, quantity: 5, packUnit: 6, section: 7, lot: 8, vendor: 9, price: 10, job: 1, comment: 12, componentType: 13, allocated: 14 }
  let lotsFound = [];
  descriptions.forEach(function (entry) {
    let idx = dvs.indexOf(entry[0]);
    if (~idx && !~lotsFound.indexOf(vs[idx][ledgerMap.lot])) {
      lotsFound.push(vs[idx][ledgerMap.lot]);
    }    
  });
  return lotsFound;
}

推荐阅读