How to fix “Loading…” error in multi-row, dynamic, dependent drop-down list in google sheets?

Question

I want to reduce human error & increase staff productivity by implementing multi-row, dynamic, dependent drop-down in google sheets. Check sample sheet here.

This is for a transport company. We receive orders daily that need to be tracked.

In column B in Orders sheet ("Consignor"), data validation is enforced by a simple drop-down populated from column A in Consignee sheet.

I want to enable my staff to make new entries (those not available in drop-down) in column B (orders sheet) itself, and from the subsequent rows, the newly added entry should be an option in the drop-down.

To that end, I entered the following formula in cell A2 in Consignees sheet, and it works: SORT(UNIQUE(Orders!B2:B),1,TRUE).

Now, in Orders sheet, I want a drop-down in column C based on option selected in column B. And similar to column B, I also want to enable the staff make new entries (those not available in drop-down) in column C itself, and from the subsequent rows, the newly added entry should be an option in the drop-down.

To achieve that, I did the following:

Entered this formula in cell B1 in Consignees sheet: TRANSPOSE(A2:A).

Entered this formula in cell B2 in Consignees sheet: SORT(FILTER(Orders!$C$2:$C,Orders!$B$2:$B=B1),1,TRUE).

Copied and pasted cell B2 horizontally to cell Z2.

This achieves the objectives of creating the data for dependent drop-down, and allowing the staff to make new entries simultaneously.

We have a lot of existing data that I want to paste (>2000 rows) & a lot more will be added (>50 rows) daily.

I tried using array formula with Indirect function, but it is tedious, limited & slows the sheets considerably.

So, I realized we need google apps script. But I am not a coder and tried learning the script of creating dynamic drop-down from this link.

It's an amazing tutorial & I copied and tried customizing the code for my purpose. Here's the code:

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);
    }
  }
}

The problem is that the drop-down sign that comes in column C once I select something in column B shows "Loading...". It doesn't show the options.

I know there's some issue with my customization of code, but I can't figure it out since I don't know coding.

I also want to do similar things in columns E & F in orders sheet and have created Destinations sheet for the same.

Please let me know the solution & how can I expand it to columns E & F too.

Thanks a lot!

Is there any Java in this? Or did you mean JavaScript. Update the tag if it's not related to Java. — Roger Gustavsson

Andres Duarte Andres Duarte · Accepted Answer · 2019-08-20T10:52:30

I replicated your problem with the drop-down in the C column and managed to fixed it replacing the following 2 lines:

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

For

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

And

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

For

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

In both cases as you're not taking the Range from the 1st row/column but from the 2nd, you need to subtract 1 to the number of rows/columns you want, so that there are no blank values in the resulting array.

How to fix “Loading…” error in multi-row, dynamic, dependent drop-down list in google sheets?

1 Answers