解决下拉列表中importRange问​​题的脚本解决方法 [英] Script workaround to solve importRange problems for drop down list

问题描述

我分享这个希望我解决了我面临的这个问题的解决方案。
我正在使用数据验证来获取下拉菜单的值。这些值来自我称为计算的工作表中的范围（A1：A40）。因为我有大约50个使用相同下拉值的电子表格，模式，我只是决定使用importRange函数填充Sheet（Calculation）。Range（A1：A50）中的信息。 importRange正在从我为此目的专门设计的单独电子表格中获取列表。

这样做的目的仅仅是因为这是我想更新和更改的类别列表，我希望下拉菜单考虑到这些更改而不必手动更改50+电子表格中的值。

现在理论上这可以毫无问题地工作，但importRange函数非常具有气质，它显示了一个#REF随机电子表格发生错误。我看到有同样问题的投诉帖子，所以我放弃了希望importRange将是一个可行的解决方案。

我希望你的意见在一个很好的解决方法，以避免这种问题。

我想到的一个解决方案是将脚本从一个表单复制到所有这些表单，并且每天都有这个脚本工作，但我不知道如何复制整个表单从一个电子表格到另一个电子表格的范围，坦率地说，我不确定这个解决方案是否最好。

我使用跨电子表格脚本的经验是，从50页以上的单元格获取数据的脚本可能需要5分钟以上的时间，并最终停止超过时间允许。

解决方案

尝试添加一个将导致刷新函数运行的菜单项。
我发现这个函数导致 importRange 有效。

  function onOpen（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var menuEntries = []; 
 menuEntries.push（{name：Refresh，functionName：SheetFlush}）; 
 ss.addMenu（Menu，menuEntries）; 
} 
 
函数SheetFlush（工作表）{
工作表=工作表|| SpreadsheetApp.getActive（）; 
 var sheets = worksheet.getSheets（）; 
 SpreadsheetApp.flush（）; 
} 
  

I am sharing this with hopes I workaround solution to this problem I am facing. I am using data validation to get values of a drop down menu. The values come from Range("A1:A40") in a sheet I called "calculation"

Since I have about 50 spreadsheets that use the same drop down values and same pattern, I simply decided to use an importRange function to fill the information in Sheet("Calculation").Range("A1:A50"). The importRange was taking the list from a separate spreadsheet that I dedicated for that purpose.

The purpose of doing that was simply because this is a list of categories that I want to update and change, and I want the drop down menu to take into consideration these changes without have to manually change the values in the 50+ spreadsheets.

Now theoretically this would work with no problem , but the importRange function is so temperamental and it shows a #REF error that happens with random spreadsheets. I saw posts with complains from the same problem so I gave up hope the importRange would be a viable solution.

I would like your opinion in a good workaround to avoid such problem.

One solution I thought of is to have a script copying values from one sheet to all these sheets and have this script work every day but I did not know how to copy entire range from one spreadsheet to another, and frankly I am not sure if this solution is the best.

My experience working with cross-spreadsheet scripts is that a script getting data from couple of cells in 50+ sheets would probably take more than 5 minutes and eventually stop for exceeding the time allowed.

解决方案

Try to add a menu item that will cause a flush function to run. I found that this function causes the importRange to work.

function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
    menuEntries.push({name: "Refresh", functionName: "SheetFlush"});
    ss.addMenu("Menu", menuEntries);
}

function SheetFlush(worksheet) {
    worksheet = worksheet || SpreadsheetApp.getActive();
    var sheets = worksheet.getSheets();
    SpreadsheetApp.flush();
}

这篇关于解决下拉列表中importRange问​​题的脚本解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！