时间戳

首页 > 解决方案 > 如何修复谷歌表格中多行、动态、相关下拉列表中的“正在加载...”错误？

google-apps-script - 如何修复谷歌表格中多行、动态、相关下拉列表中的“正在加载...”错误？

问题描述

我想通过在谷歌表格中实现多行、动态、相关的下拉菜单来减少人为错误并提高员工的工作效率。在此处查看样本表。

这是给运输公司的。我们每天都会收到需要跟踪的订单。

在订单表（“发货人”）的 B 列中，数据验证通过从收货人表的 A 列填充的简单下拉列表强制执行。

我想让我的员工能够在 B 列（订单表）本身中创建新条目（下拉列表中不可用的条目），并且从后续行中，新添加的条目应该是下拉列表中的一个选项。

为此，我在收货人表的单元格 A2 中输入了以下公式，它可以工作：SORT(UNIQUE(Orders!B2:B),1,TRUE).

现在，在订单表中，我希望根据 B 列中选择的选项在 C 列中进行下拉。与 B 列类似，我还希望使员工能够在列中进行新条目（下拉列表中不可用的条目） C 本身，以及从后续行来看，新添加的条目应该是下拉列表中的一个选项。

为了实现这一点，我做了以下事情：

  1. 在收货人表的单元格 B1 中输入此公式： TRANSPOSE(A2:A)。

  2. 在收货人表的单元格 B2 中输入此公式： SORT(FILTER(Orders!$C$2:$C,Orders!$B$2:$B=B1),1,TRUE)。

  3. 将单元格 B2 水平复制并粘贴到单元格 Z2。

这实现了为相关下拉列表创建数据并允许员工同时进行新条目的目标。

我们有很多我想要粘贴的现有数据（> 2000 行）并且每天都会添加更多数据（> 50 行）。

我尝试使用带有间接函数的数组公式，但它很乏味、有限且大大减慢了工作表的速度。

所以，我意识到我们需要谷歌应用脚​​本。但我不是编码员，并尝试从此链接学习创建动态下拉列表的脚本。

这是一个很棒的教程，我复制并尝试为我的目的自定义代码。这是代码：

function onEdit() {
  var tabLists = "Consignees";
  var tabValidation = "Orders";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

  var activeCell = ss.getActiveCell();

  if(activeCell.getColumn() == 2 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation) {

    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var consignor = datass.getRange(1, 2, 1, datass.getLastColumn()).getValues();

    var makeIndex = consignor[0].indexOf(activeCell.getValue()) + 2;

    if(makeIndex != 0) {
      var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      activeCell.offset(0, 1).setDataValidation(validationRule);
    }
  }
}

问题是，一旦我在 B 列中选择了某些内容，C 列中的下拉符号就会显示“正在加载...”。它不显示选项。

我知道我的代码自定义存在一些问题，但我无法弄清楚，因为我不知道编码。

我也想在订单表的 E 和 F 列中做类似的事情，并为此创建了 Destinations 表。

请让我知道解决方案以及如何将其扩展到 E & F 列。

非常感谢！

标签： google-apps-scriptgoogle-sheets

解决方案

我用 C 列中的下拉菜单复制了您的问题，并设法修复了它，替换了以下 2 行：

var consignor = datass.getRange(1, 2, 1, datass.getLastColumn()).getValues();

为了

var consignor = datass.getRange(1, 2, 1, datass.getLastColumn()-1).getValues();

和

  var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());

为了

  var validationRange = datass.getRange(2, makeIndex, datass.getLastRow()-1);

在这两种情况下，由于您不是从第 1 行/列而是从第 2 行获取 Range，因此您需要将所需的行/列数减去 1，以便结果数组中没有空白值。

推荐阅读