#javascript #google-apps-script #google-sheets
#javascript #google-apps-script #google-таблицы
Вопрос:
Следующие коды показывают ошибки из-за ограничения 500. Я пытался решить эту проблему и просмотрел множество документов, но потерпел неудачу. Есть ли какой-либо способ решить эту проблему?
var spreadsheet = SpreadsheetApp.getActive();
var dashboard = spreadsheet.getSheetByName("Dashboard");
var wsOptions = spreadsheet.getSheetByName("Master");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,6).getDisplayValues();
function onEdit(e){
var as = e.source.getActiveSheet();
var val = e.range.getValue();
var val_not = e.range.getA1Notation();
if (val_not =='F5' amp;amp; as.getName() == "Dashboard"){
if(val === "All"){
dashboard.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");
dashboard.getRange('G5').clearDataValidations();
}
else{
var filteredOptions = options.filter(function(o){return o[5] === val});
var listToApply = filteredOptions.map(function(o){return o[0]}).sort().reverse();
var cell = dashboard.getRange('G5');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).setAllowInvalid(false).build();
cell.clearContent();
cell.clearDataValidations();
cell.setDataValidation(rule);
}
}
}
Ответ №1:
Объяснение:
Как следует из ошибки:
Правило проверки данных содержит больше элементов, чем ограничение в 500. Вместо этого используйте критерий «Список из диапазона».
вы должны использовать requireValueInRange() .
Чтобы использовать последнее, вам необходимо определить диапазон элементов проверки данных. В следующем подходе я создаю диапазон этих элементов opt_range и использую его в качестве аргумента для requireValueInRange() функции.
Решение 1:
function onEdit(e){
var as = e.source.getActiveSheet();
var val = e.range.getValue();
var val_not = e.range.getA1Notation();
var spreadsheet = SpreadsheetApp.getActive();
var dashboard = spreadsheet.getSheetByName("Dashboard");
var wsOptions = spreadsheet.getSheetByName("Master");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,6).getDisplayValues();
if (val_not =='F5' amp;amp; as.getName() == "Dashboard"){
if(val === "All"){
dashboard.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");
dashboard.getRange('G5').clearDataValidations();
}
else{
var filteredOptions = options.filter(function(o){return o[5] === val});
var listToApply = filteredOptions.map(function(o){return o[0]}).sort().reverse();
var listToApply2D = listToApply.map(ta=>[ta]);
var jSize = wsOptions.getRange('J:J').getValues().filter(String).length;
if (jSize>0){ wsOptions.getRange(1,10,jSize,1).clearContent()};
wsOptions.getRange(1,10,listToApply2D.length,listToApply2D[0].length).setValues(listToApply2D);
var opt_range = wsOptions.getRange(1,10,listToApply2D.length,listToApply2D[0].length);
var cell = dashboard.getRange('G5');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(opt_range).build();
cell.clearContent();
cell.clearDataValidations();
cell.setDataValidation(rule);
}
}
}
Решение 2 (рекомендуется):
Предполагая, что у вас нет 500 уникальных элементов в списке, вы все равно можете использовать опубликованный вами код, но возьмите уникальный список элементов:
function onEdit(e){
var spreadsheet = SpreadsheetApp.getActive();
var dashboard = spreadsheet.getSheetByName("Dashboard");
var wsOptions = spreadsheet.getSheetByName("Master");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,6).getDisplayValues();
var as = e.source.getActiveSheet();
var val = e.range.getValue();
var val_not = e.range.getA1Notation();
if (val_not =='F5' amp;amp; as.getName() == "Dashboard"){
if(val === "All"){
dashboard.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");
dashboard.getRange('G5').clearDataValidations();
}
else{
var filteredOptions = options.filter(function(o){return o[5] === val});
var listToApply = filteredOptions.map(function(o){return o[0]}).sort().reverse();
var uniqueList = listToApply.filter((v, i, a) => a.indexOf(v) === i); // <= New code
var cell = dashboard.getRange('G5');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(uniqueList).setAllowInvalid(false).build();
cell.clearContent();
cell.clearDataValidations();
cell.setDataValidation(rule);
}
}
}
Комментарии:
1. К сожалению, это не работает. Не могли бы вы проверить данную ссылку на лист. Спасибо
2. @TamjidTaha извините, попробуйте еще раз. Я уже обновил код в файле. Имейте в виду, что код может быть выполнен только пользователем, имеющим доступ к основной таблице. Я предполагаю, что у вас есть доступ. Дайте мне знать иначе.
3. Это решение, но я не могу его использовать, так как загрузка занимает много времени, и когда я открываю электронную таблицу в первый раз, часто это не работает. Ваше предыдущее решение было лучшим, но из-за ограничения в 500 оно не имеет значения. Есть ли какой-либо другой способ сделать это действительно быстро, как ваше предыдущее решение?
4. Можем ли мы ограничить эту строку для 1-го 500 записей, таких как var listToApply = filteredOptions.map(function(o){return o[0]}).sort.reverse(); Мне не нужны все даты для каждой темы, если я могу получить 6-10 последних дат, это нормально. Итак, как мы можем это сделать?
5. @TamjidTaha можете ли вы попробовать второе решение в моем обновленном ответе? Это должно быть очень быстро и без ошибок 🙂
Ответ №2:
Вместо использования requireValueInList use requireValueInRange . Это означает, что вы должны добавить значения в списке в диапазон.
Ресурсы
Комментарии:
1. Это не работает. Я изменил код как var rule = SpreadsheetApp.newDataValidation().requireValueInRange(listToApply, false).build();