google-apps-script - 在脚本内使用公式

Question

我想在自定义函数中使用公式，例如:

function myFunction(range, value) {
    var countNumber = COUNTIF(range; value); // COUNTIF is a formula that can be used in the spreadsheet
    if (countNumber > 0) {
        return "RESULT";
    } else {
        return "OTHER RESULT";
    }
}

然后:

=MYFUNCTION(A1:A5,"VALUETOTEST")

<小时/>

我想简化一个巨大的公式:

类似于:

=IF(SUM(COUNTIFS(G182:G186;"ERROR";H182:H186;"62");COUNTIFS(G182:G186;"ERROR";H182:H186;"ALL"))>0;"ERRO";IF(SUM(COUNTIFS(G182:G186;"RETEST";H182:H186;"62");COUNTIFS(G182:G186;"RETEST";H182:H186;"TODOS"))>0;"RETEST";IF(COUNTIF(G182:G186;"UNIMPLEMENTED")>0;"UNIMPLEMENTED";"SOLVED")))

Answer 1

您可以通过三种方式执行这些操作。

1. 将工作表公式添加到工作表本身所需的范围内。然后使用 GAS 函数从结果单元格(无论您将其设置为写入的位置)读取数据。然后您可以使用结果执行进一步的处理。

2. 使用 GAS 函数将工作表公式写入工作表中。然后使用更多的 GAS 来读取结果并处理数据。可以在这里找到此方法:https://developers.google.com/apps-script/reference/spreadsheet/range#setFormula(String)

3. 您可以使用 GAS 创建自定义工作表公式，然后在工作表中使用该公式。然后，GAS 可以读取该结果并处理该信息。这需要对 JS 进行一些整体研究，以了解如何重新创建、组合和执行您需要工作表中的数据执行的操作。

您可以在此处找到制作自定义公式的指南:https://developers.google.com/apps-script/guides/sheets/functions

还有 JS 指南:http://www.w3schools.com/js/default.asp

W3 Schools 有一本相当全面的 JS 指南。 GAS使用所有原生JS方法，因为它是一个JS编码环境。检查 GAS 引用，了解更多关于 GAS 特定方法的信息，这些方法可以执行您所需要的操作。

如果您需要检查条件和/或迭代行，请尝试如下操作:

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange(startRow, startColumn, numRows, numColumns);
var values = range.getValues(); //This is a 2D array; iterate appropriately
  for (i = 0; i < values.length; i++) {
    if (values[i] == conditionToCheck) {
      //perform code..OR
      //continue; <- This works to skip the row if the condition is met
    } else {
      //perform alternate code if condition is not met
    }
  }
}

正如我所提到的，.getValues() 创建一个二维数组。如果您需要迭代列和行，则需要 2 个 for() 循环，如下所示:

for (i = 0; i < values.length; i++) { //iterates through the rows
  for(j = 0; j < values[i].length; j++) { //iterates through the columns in that current row

值得一提的是 GAS 如何处理 2D 数组。 values[i][j] 表示有多少 i 行和 j 列。您可以像这样想象:

值 = [[A1, B1, C1],[A2, B2, C2],[A3, B3, C3]]

这是一个数组的数组，其中外部数组是行数组，而内部数组是该行中按列的单元格值数组。