Google Apps Script：按多税率匹配数组含税/不含税金额对

我们在Google Apps Script开发场景中，有一个从发票提取的货币金额数组，另有一个存储适用VAT/税率的数组。需求是为每个税率，在金额数组中找到对应的**含税金额（inc tax amount）与不含税金额（ex tax amount）**配对。

示例输入 #

values = [4000, 2500, 2066.12, 2000, 1834.86];
taxRates = [9, 21];

期望输出 #

taxAmounts = [[2000, 2500],[1834.86, 2066.12]];

说明：taxAmounts[0][0]为税率9%的含税金额，taxAmounts[1][0]为对应不含税金额；taxAmounts[0][1]为税率21%的含税金额，taxAmounts[1][1]为对应不含税金额。

用户原实现代码 #

function findBtwPairs(btwRates, amountsArr, incBtwArr, exBtwArr){
  for(m=0 ; m < btwRates.length ; m++){
    skipIndex = 0;
    for (i=0;i<amountsArr.length;i++){
      for (j=0;j<amountsArr.length;j++){
        if (i!=j){
          cond1 = (roundTo((amountsArr[i]/amountsArr[j]),2) == (1+btwRates[m]/100));
            cond2 = (roundTo((amountsArr[i]/amountsArr[j]),2) == (btwRates[m]/100));
          if (cond1 || cond2){          //inc btw and ex btw specified
              if(cond1){incBtw = amountsArr[i]; exBtw = amountsArr[j];}else{incBtw = amountsArr[i]+amountsArr[j]; exBtw = amountsArr[j];};
            if(roundTo(incBtw,0) == roundTo(amountsArr[0],0)){              //if found amount is highest number
              return [[incBtw], [exBtw]];
            }else{                      //if found amount NOT highest number
                incBtwArr = [incBtw]; exBtw = [exBtw];      //convert to arrays
              functionOutput = findBtwPairs(btwRates, amountsArr, incBtw, exBtw);
              if(functionOutput != undefined){
                incBtwArr = incBtwArr + functionOutput[0]; console.log("functionOutput[0] =" + functionOutput[0]);
                exBtwArr = exBtwArr + functionOutput[1]; console.log("functionOutput[1] =" + functionOutput[0]);
                }           //add found inc. and ex. btw values (e.g. 9% inc/ex value) to 'incBtw' and 'exBtw' Arrays
              if (roundTo(sumArray(incBtwArr),0) == roundTo(amountsArr[0]),0){
                return [incBtwArr, exBtwArr]
                }
              } // end of if/else
            } // end of if(cond1 || cond2){
          } // end of if (i!=j){
        } // end of j loop
      } // end of i loop
    } //end of m loop
    console.log( "BTW RATES NOT FOUND USING RATES IN 'btwRates' Array" );
  } // end of fn 'findBtwPairs'

//$$ Helper functions $$

function sumArray(array){
    return array.reduce(add, 0); // with initial value to avoid when the array is empty
    }

function add(accumulator, a) {
  return accumulator + a;
}

function roundTo(num, decimals) {
  return ( +num.toFixed(decimals));
}

原代码问题分析 #

• 未声明变量（m、i、j等），导致全局变量污染，逻辑混乱
• 递归逻辑错误：递归调用时传入单个值而非数组，数组拼接用+会转为字符串而非数组
• 条件判断失效：roundTo(sumArray(incBtwArr),0) == roundTo(amountsArr[0]),0)中的逗号运算符导致条件错误
• 未处理重复匹配：找到配对后未移除已使用金额，可能导致重复配对

优化后的实现代码 #

function findVatPairs(taxRates, amounts) {
  // 复制数组避免修改原数据，按降序排序提升匹配效率
  const remainingAmounts = [...amounts].sort((a, b) => b - a);
  const result = [[], []]; // [含税金额数组, 不含税金额数组]

  // 遍历每个税率
  for (const rate of taxRates) {
    let found = false;
    const multiplier = 1 + rate / 100;

    // 查找当前税率对应的金额配对
    for (let i = 0; i < remainingAmounts.length; i++) {
      for (let j = i + 1; j < remainingAmounts.length; j++) {
        const larger = remainingAmounts[i];
        const smaller = remainingAmounts[j];
        
        // 检查两种比例情况：含税/不含税 或 不含税/含税
        if (Math.round(larger / smaller * 100) / 100 === multiplier) {
          result[0].push(larger);
          result[1].push(smaller);
          // 移除已匹配的金额，避免重复使用
          remainingAmounts.splice(j, 1);
          remainingAmounts.splice(i, 1);
          found = true;
          break;
        } else if (Math.round(smaller / larger * 100) / 100 === multiplier) {
          result[0].push(smaller);
          result[1].push(larger);
          remainingAmounts.splice(j, 1);
          remainingAmounts.splice(i, 1);
          found = true;
          break;
        }
      }
      if (found) break;
    }

    if (!found) {
      console.log(`未找到税率${rate}%对应的金额配对`);
    }
  }

  return result;
}

// 测试示例
const values = [4000, 2500, 2066.12, 2000, 1834.86];
const taxRates = [9, 21];
console.log(findVatPairs(taxRates, values)); // 输出 [[2000, 2500], [1834.86, 2066.12]]

优化说明 #

• 规范变量作用域：使用const/let声明变量，避免全局污染
• 简化匹配逻辑：直接通过税率计算乘数，检查金额比例是否符合含税/不含税关系
• 避免重复匹配：找到配对后从剩余数组中移除已匹配项
• 清晰结果结构：直接构建符合需求的二维数组，无需复杂递归
• 提升鲁棒性：添加未找到配对的提示，排序数组优化匹配效率

内容的提问来源于stack exchange，提问作者Maarten -Monica for president