'data validation rule limit of 500' error on google script

Question

I'm trying to implement the script to the spreadsheet which will get all folder list and all files list in subfolder of Google drive api with this code,

[I found on this forum]

//GLOBAL VARIABLES

var ss = SpreadsheetApp.getActiveSpreadsheet()
  .getSheets()[0];
var listCell = 'B1';
var idRange = 'A2:B2';
var idCell = 'B2';

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Get Files From Folder')
    .addItem('STEP 1: Get my folders', 'getAllFolders')
    .addItem('STEP 2: Get ID of selected folder', 'getId')
    .addItem('STEP 3: Get all Files', 'getAllFiles')
    .addToUi();
}

function getAllFolders() {
  var arr = [];
  var all = DriveApp.getFolders();
  while (all.hasNext()) {
    var folder = all.next();
    arr.push(folder.getName())
  }
  arr.sort(function(first, next) {
    first.toLowerCase(),
      next.toLowerCase();
    return first < next ? -1 : first > next ? 1 : 0;
  });
  var cell = ss.getRange(listCell);
  cell.setDataValidation(SpreadsheetApp.newDataValidation()
      .requireValueInList(arr, true)
      .build())
    .setVerticalAlignment('middle');
  cell.offset(0, -1)
    .setValue('FOLDERS: ')
    .setBackground('#50803c')
    .setFontColor('White')
    .setFontWeight('Bold')
    .setHorizontalAlignment('center')
    .setVerticalAlignment('middle');
  ss.setRowHeight(1, 50);
}
//STEP2

function getId() {
  ss.getRange(idRange)
    .setValues([
      ["ID: ", DriveApp.getFoldersByName(ss.getRange(listCell)
          .getValue())
        .next()
        .getId()
      ]
    ])
    .setVerticalAlignment('middle')
    .setFontColor('grey');
}


//STEP3

function getAllFiles() {
  var id = ss.getRange(idCell).getValue();
  ss.getRange(4, 1, ss.getLastRow(), ss.getLastColumn()).clear();
  var arr = [
    ["FILENAME", "URL"]
  ];
  var f = DriveApp.getFolderById(id).getFiles()
  while (f.hasNext()) {
    var file = f.next();
    var name = file.getName()
    arr.push([name, '=HYPERLINK("' + file
      .getUrl() + '"; "' + name + '")'
    ]);
  }
  ss.getRange(4, 1, arr.length, arr[0].length).setValues(arr);
}

but I got an error

The data validation rule has more items than the limit of 500. Use the "List from a range" criteria instead.

I have no clue why I got this error, probably too many subfolders

Is there anyway to edit code to get 3 level of subfolder like

root folder > subfolder1 >subfolder2>subfolder3

or anything that can solve this error ,Thanks

abielita abielita · Accepted Answer · 2017-07-09T15:14:37

It is an issue exactly as the error message says. You may check this documentation. Apps Script services impose daily quotas and hard limitations on some features. If you exceed a quota or limitation, your script will throw an exception and terminate execution.

'data validation rule limit of 500' error on google script

2 Answers