javascript - Google 脚本 - 在另一个函数中调用函数时出错

Question

我将发布下面的全部代码。

我正在使用 Google 脚本，从不断变化的工作表中提取一些数据，执行计算，然后重新填充该工作表。

数据正在被调用到应用程序中，因此我试图尽快进行计算(因此，如果有人可以告诉我更有效的方法来执行此操作，那也会很有帮助)。

当我尝试调用 invoicePrice() 函数或 tiers() 函数时，出现 TypeError:找不到函数。

function invoicePrice() {
      // define row to do calculations on
      var AVals = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange("A1:A1000").getValues();
      var ALast = AVals.filter(String).length;

      // Set order No. here

      var orderNoPrev = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast - 1, 3).getValue();
      var orderNo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 3).setValue(orderNoPrev + 1);
      var total = 0;

      // invoicePrice the total number of items
      var itemRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 4, 1, 14).getValues()[0];

      // set the values of the constants on the Products page

      var priceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Products").getRange("F1:F1000").getValues();
      var pLast = priceRange.filter(String).length;
      var pGrab = priceRange.splice(0,pLast);

      var invoicePrice = 0;

      // set weight of case values

      var weightRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Products").getRange("V1:V1000").getValues();
      var wLast = weightRange.filter(String).length;
      var wGrab = weightRange.splice(0,wLast);


      for (var i = 0, len = 13; i <= len; i++) {
          // SUBTOTAL THE ORDER AMOUNT
          var orderAmount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, i + 4).getValue();

          total += orderAmount;

          // SET/KG PRICES
          var perKGInvoice = pGrab[i + 1].map(Number);

          // SET WEIGHTS
          var weights = wGrab[i + 1].map(Number);

          // SET CASE PRICE
          var casePrice = perKGInvoice * weights;

          // SUM OF PRODUCT PRICE * ORDER AMOUNT * WEIGHT OF CASE
          invoicePrice += orderAmount * perKGInvoice * weights;


      }
    Logger.log(invoicePrice, ALast, pGrab, wGrab);
    return [invoicePrice, ALast, pGrab, wGrab];



  }

  function tiers() {
      var invoicePriceCall = invoicePrice();
      var invoicePrice = invoicePriceCall[0];
      var ALast = invoicePriceCall[1];

      var beefDiscount = 0;
      var sfDiscount = 0;
      var currentTier = ""

      // Set the invoice order value required ($ amount)

      var tier1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(3, 3).getValue();
      var tier2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(4, 3).getValue();
      var tier3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(5, 3).getValue();
      var tier4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(6, 3).getValue();
      var tier5 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(7, 3).getValue();
      var minNotmet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(10, 3).getValue();

      // Set the discount tier levels for beef (% discount)
      var tier1BeefDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(3, 4).getValue();
      var tier2BeefDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(4, 4).getValue();
      var tier3BeefDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(5, 4).getValue();
      var tier4BeefDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(6, 4).getValue();
      var tier5BeefDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(7, 4).getValue();

      var tier1SfDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(3, 5).getValue();
      var tier2SfDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(4, 5).getValue();
      var tier3SfDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(5, 5).getValue();
      var tier4SfDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(6, 5).getValue();
      var tier5SfDiscount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DiscountGrid").getRange(7, 5).getValue();




      if (invoicePrice < tier1) {
          beefDiscount = tier1BeefDiscount;
          sfDiscount = tier1SfDiscount;
          currentTier = "Minimum Not Met"
      }
      else if (invoicePrice < tier2) {
          beefDiscount = tier1BeefDiscount;
          sfDiscount = tier1SfDiscount;
          currentTier = "Tier 1"
      }
      else if (invoicePrice < tier3) {
          beefDiscount = tier2BeefDiscount;
          sfDiscount = tier2SfDiscount;
          currentTier = "Tier 2"
      }
      else if (invoicePrice < tier4) {
          beefDiscount = tier3BeefDiscount;
          sfDiscount = tier3SfDiscount;
          currentTier = "Tier 3";
      }
      else if (invoicePrice < tier5) {
          beefDiscount = tier4BeefDiscount;
          sfDiscount = tier4SfDiscount;
          currentTier = "Tier 4";
      }
      else {
          beefDiscount = tier5BeefDiscount;
          sfDiscount = tier5SfDiscount;
          currentTier = "Tier 5";
      }


      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 20).setValue(beefDiscount);
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 21).setValue(sfDiscount);
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 22).setValue(currentTier);

      return [beefDiscount, sfDiscount, tier1];

  }


  function calcsOnColumns2() {

      // call price range and weight from previous function 
      var invoicePriceCall = invoicePrice();
      var invoicePrice = invoicePriceCall[0];
      var ALast = invoicePriceCall[1];
      var priceRange = invoicePriceCall[2];
      var weightRange = invoicePriceCall[3];

      // call tiers function
      var tiers = tiers();
      var beefDiscount = tiers[0];
      var sfDiscount = tiers[1];
      var tier1 = tiers[2];

      // set sums to 0

      var beefSum = 0;
      var sfSum = 0

      for (var i = 0, len = 13; i <= len; i++) {
          // SUBTOTAL THE ORDER AMOUNT
          var orderAmount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, i + 4).getValue();

          // SET/KG PRICES
          var perKGInvoice = priceRange[i + 1].map(Number);

          // SET WEIGHTS
          var weights = weightRange[i + 1].map(Number);

          // SET CASE PRICE
          var casePrice = perKGInvoice * weights;

          // Split up seafood and beef with if statement:

          if (i < 9) {
              // set discount case price
              var discountCaseBeef = perKGInvoice - (perKGInvoice * beefDiscount);
              // set the total price of the discounted cases in the order (ie, order price * discount)
              var beefCaseOrder = discountCaseBeef * orderAmount * weights;
              beefSum += beefCaseOrder;
              // Nice and Formatted sheet
              var casePrice = perKGInvoice * weights;

              if (orderAmount) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, i + 32).setValue(orderAmount + " cases @" + discountCaseBeef.toFixed(2) + "/kg = ~$" + beefCaseOrder.toFixed(2)); }

          }

          else {
              var discountCaseSF = perKGInvoice - (perKGInvoice * sfDiscount);
              // set the total price of the discounted cases in the order (ie, order price * discount)
              var sfCaseOrder = discountCaseSF * orderAmount * weights;
              sfSum += sfCaseOrder;
              // Nice and Formatted sheet
              var casePrice = perKGInvoice * weights;
              if (orderAmount) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, i + 32).setValue(orderAmount + " cases @" + discountCaseSF.toFixed(2) + "/kg = ~$" + sfCaseOrder.toFixed(2)); }

          }



          if (i == len) {
              SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 24).setValue(beefSum);
              SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 25).setValue(sfSum);
              // if statement to go here if min order size not met

              var sumOfSums = beefSum + sfSum;
              // swap out 75 with referenced value
              if (sumOfSums > tier1) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 27).setValue(sumOfSums) }
              // swap out phrase with changable phrase
              else { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 27).setValue("Minimum order size not met") }

              var totalDiscount = invoicePrice - sumOfSums

              SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 29).setValue(totalDiscount);
              SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubtotalDisplay").getRange(ALast, 30).setValue(invoicePrice);


          }



      }


  }

Answer 1

问题

正确声明的函数 (invoicePrice) 在从另一个函数 (calcsOnColumns2) 调用时是未定义。

重现步骤

假设您有三个名为 A、B、C 的函数，然后调用 B 会导致错误:

function A() {
  var A = 0;
  return [A];
}

function C() {
  var a = A();
  var A = a[0];
}

function B() {
  A();
  C();
}

您可能注意到，这就是您的函数的归结 - 行为是由于提升:

1. 函数B调用A，它返回一个第一个元素为0的Array；
2. [从未发生]函数C调用函数A并覆盖A以输出A；<

相反，在第二步中，A 被声明为等于 a 的第一个元素，然后才调用 A。由于 a 通过调用 A 获取其值，因此 A 将是 undefined，结果是: [undefined]() ->“不是函数”

优化

1. 保持干燥(不要重复自己) - 当您看到看起来相同或具有相同结构的代码时，请考虑使其可重用(使其成为函数、类、对象、变量 - 这取决于情况)。它使您的代码可读且更易于调试，但很多时候它可以节省您对 API 的额外调用、重新计算以及时间(特别是在重复 I/O 时)。
2. 除非绝对必要，否则不要使用注释(这会大大降低可读性 - 如果您需要其他人帮助调试代码或自己完成代码，这一点至关重要[可能听起来违反直觉，但您会感到惊讶])。
3. 除非别无选择，否则不要将 I/O(getRange()、getValue())放入循环中 - 输入/输出总是很慢，因此通常最好将所有内容加载到内存中并在那里处理数据。
4. 保持一致的范围:如果声明一个变量，每个访问器都应该在同一范围内或嵌套(例如，不要在 if...else 内声明两个同名的变量，将其移至外部范围)。
5. 使用 TypeScript 或至少 JSDoc - 将防止您在尝试理解为什么您的函数需要一种类型并收到另一种类型时发疯(就像您的情况一样)[UPD:我还在解决方案中添加了 JSDOC 注释]。

我对您的脚本进行了一些优化，请看一下(请在使用前检查，因为我们没有示例数据，所以无法测试它)。您还可以做更多事情(特别是关于 calcsOnColumns2 中的循环)，但这应该是一个开始:

/**
 * [Your decription here]
 * @returns {Number[]}
 */
function invoicePrice() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var subtotalDisplaySheet = ss.getSheetByName('SubtotalDisplay');

    // define row to do calculations on
    var AVals = subtotalDisplaySheet.getRange("A1:A1000").getValues();
    var ALast = AVals.filter(String).length;

    // Set order No. here
    var orderNoPrev = subtotalDisplaySheet.getRange(ALast - 1, 3).getValue();
    var orderNo = subtotalDisplaySheet.getRange(ALast, 3).setValue(orderNoPrev + 1);

    // invoicePrice the total number of items
    var itemRange = subtotalDisplaySheet.getRange(ALast, 4, 1, 14).getValues()[0];

    // set the values of the constants on the Products page
    var productsSheet = ss.getSheetByName("Products");

    var priceRange = productsSheet.getRange("F1:F1000").getValues();
    var pLast = priceRange.filter(String).length;
    var pGrab = priceRange.splice(0, pLast);

    var outputInvoicePrice = 0;

    var weightRange = productsSheet.getRange("V1:V1000").getValues();
    var wLast = weightRange.filter(String).length;
    var wGrab = weightRange.splice(0, wLast);

    var orderAmounts = subtotalDisplaySheet.getRange(Alast, 17).getValues();

    var total = 0;
    for (var i = 0, len = 13; i <= len; i++) {
        var nextI = i + 1;

        var orderAmount = orderAmounts[0][i + 4];

        total += orderAmount;

        var perKGInvoice = pGrab[nextI].map(Number);
        var weights = wGrab[nextI].map(Number);

        var casePrice = perKGInvoice * weights;

        outputInvoicePrice += orderAmount * casePrice;
    }

    Logger.log(outputInvoicePrice, ALast, pGrab, wGrab);
    return [outputInvoicePrice, ALast, pGrab, wGrab];
}

/**
 * Gets values from 5 rows in 
 * a column starting from row 3
 * @param {Sheet} sheet 
 * @param {Number} column 
 * @returns {*[]}
 */
function getGridValues(sheet, column) {
    var range = sheet.getRange(3, column, 5, 1);
    return range
        .getValues()
        .map(function (row) {
            return row[0];
        });
}

/**
 * [Your decription here]
 * @returns {Number[]}
 */
function tiers() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var discountGridSheet = ss.getSheetByName("DiscountGrid");

    var invoicePriceCall = invoicePrice();
    var outputInvoicePrice = invoicePriceCall[0];
    var ALast = invoicePriceCall[1];

    var beefDiscount = 0;
    var sfDiscount = 0;
    var currentTier = "";

    var tierValues = getGridValues(discountGridSheet, 3);
    var tier1 = tierValues[0];
    var tier2 = tierValues[1];
    var tier3 = tierValues[2];
    var tier4 = tierValues[3];
    var tier5 = tierValues[4];

    var minNotmet = discountGridSheet.getRange(10, 3).getValue();

    var tierBeefDiscountValues = getGridValues(discountGridSheet, 4);
    var tier1BeefDiscount = tierBeefDiscountValues[0];
    var tier2BeefDiscount = tierBeefDiscountValues[1];
    var tier3BeefDiscount = tierBeefDiscountValues[2];
    var tier4BeefDiscount = tierBeefDiscountValues[3];
    var tier5BeefDiscount = tierBeefDiscountValues[4];

    var tierSfDiscountValues = getGridValues(discountGridSheet, 5);
    var tier1SfDiscount = tierSfDiscountValues[0];
    var tier2SfDiscount = tierSfDiscountValues[1];
    var tier3SfDiscount = tierSfDiscountValues[2];
    var tier4SfDiscount = tierSfDiscountValues[3];
    var tier5SfDiscount = tierSfDiscountValues[4];

    if (outputInvoicePrice < tier1) {
        beefDiscount = tier1BeefDiscount;
        sfDiscount = tier1SfDiscount;
        currentTier = "Minimum Not Met";
    }
    else if (outputInvoicePrice < tier2) {
        beefDiscount = tier1BeefDiscount;
        sfDiscount = tier1SfDiscount;
        currentTier = "Tier 1";
    }
    else if (outputInvoicePrice < tier3) {
        beefDiscount = tier2BeefDiscount;
        sfDiscount = tier2SfDiscount;
        currentTier = "Tier 2";
    }
    else if (outputInvoicePrice < tier4) {
        beefDiscount = tier3BeefDiscount;
        sfDiscount = tier3SfDiscount;
        currentTier = "Tier 3";
    }
    else if (outputInvoicePrice < tier5) {
        beefDiscount = tier4BeefDiscount;
        sfDiscount = tier4SfDiscount;
        currentTier = "Tier 4";
    }
    else {
        beefDiscount = tier5BeefDiscount;
        sfDiscount = tier5SfDiscount;
        currentTier = "Tier 5";
    }

    var subtotalDisplaySheet = ss.getSheetByName("SubtotalDisplay");
    var subtotalDisplayRange = subtotalDisplaySheet.getRange(ALast, 20, 3, 1);
    subtotalDisplayRange.setValues([
        [beefDiscount],
        [sfDiscount],
        [currentTier]
    ]);

    return [beefDiscount, sfDiscount, tier1];
}

/**
 * Foramts amount string
 * @param {Number} amount 
 * @returns {Function}
 */
function formatAmount(amount) {
    return function (discount, caseOrder) {
        return amount + " cases @" + discount.toFixed(2) + "/kg = ~$" + caseOrder.toFixed(2);
    };
}

/**
 * Counts sum by discount, amount and weights
 * @param {Number} amount 
 * @param {Number} weights 
 * @returns {Number}
 */
function countSum(amount, weights) {
    return function (discount) {
        return discount * amount * weights;
    };
}

/**
 * Counts discount case per Kg
 * @param {Number} perKG 
 * @returns {Number}
 */
function countDiscountCase(perKG) {
    return function (discount) {
        return perKG - (perKG * discount);
    };
}

/**
 * [Your decription here]
 */
function calcsOnColumns2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var subtotalDisplaySheet = ss.getSheetByName("SubtotalDisplay");

    var invoicePriceCall = invoicePrice();
    var outputInvoicePrice = invoicePriceCall[0];
    var ALast = invoicePriceCall[1];
    var priceRange = invoicePriceCall[2];
    var weightRange = invoicePriceCall[3];

    var tiers = tiers();
    var beefDiscount = tiers[0];
    var sfDiscount = tiers[1];
    var tier1 = tiers[2];

    var beefSum = 0;
    var sfSum = 0;

    var orderAmounts = subtotalDisplaySheet.getRange(ALast, 17).getValues();

    for (var i = 0, len = 13; i <= len; i++) {
        var orderAmount = orderAmounts[0][i + 4];

        var nextI = i + 1;

        var perKGInvoice = priceRange[nextI].map(Number);
        var weights = weightRange[nextI].map(Number);

        var casePrice = perKGInvoice * weights;

        var subtotalRange = subtotalDisplaySheet.getRange(ALast, i + 32);

        var formatAmountOrder = formatAmount(orderAmount);
        var countDisountSum = countSum(orderAmount, weights);
        var countPerKGCase = countDiscountCase(perKGInvoice);

        // Split up seafood and beef with if statement:
        if (i < 9) {
            var discountCaseBeef = countPerKGCase(beefDiscount);
            var beefCaseOrder = countDisountSum(discountCaseBeef);
            beefSum += beefCaseOrder;
        } else {
            var discountCaseSF = countPerKGCase(sfDiscount);
            var sfCaseOrder = countDisountSum(discountCaseSF);
            sfSum += sfCaseOrder;
        }

        if (orderAmount) {
            subtotalRange.setValue(
                i < 9 ?
                    formatAmountOrder(discountCaseBeef, beefCaseOrder) :
                    formatAmountOrder(discountCaseSF, sfCaseOrder)
            );
        }

        if (i == len) {
            subtotalDisplaySheet.getRange(ALast, 24).setValue(beefSum);
            subtotalDisplaySheet.getRange(ALast, 25).setValue(sfSum);
            // if statement to go here if min order size not met

            var subtotalRange = subtotalDisplaySheet.getRange(ALast, 27);

            var sumOfSums = beefSum + sfSum;

            subtotalRange.setValue(sumOfSums > tier1 ? "Minimum order size not met" : sumOfSums);

            var totalDiscount = outputInvoicePrice - sumOfSums;

            subtotalDisplaySheet.getRange(ALast, 29).setValue(totalDiscount);
            subtotalDisplaySheet.getRange(ALast, 30).setValue(outputInvoicePrice);
        }
    }

}

注释

1. 虽然现代 JavaScript 可以帮助您避免这个问题，但您应该永远不要覆盖任何变量名称，除非您确切知道为什么正在这样做(i++ 是一个例子，但是函数式风格将说即使这样也是有问题的)。
2. 在 SO 上发布时，切勿为了可读性而 chop 错误消息/代码 - 了解到底发生了什么对于我们为您提供帮助非常宝贵(invoicePrice 是 undefined 这么说更多)。

引用文献

1. Hoisting在 JavaScript 中