Script workaround to solve importRange problems for dropdown

Question

I share this with the hope that I am facing a workaround for this problem. I use data validation to get the values ​​of the dropdown menu. The values ​​are taken from the range ("A1: A40") in the sheet, which I called "calculation"

Since I have about 50 spreadsheets that use the same dropdown values ​​and the same template, I just decided to use the importRange function to populate the information in the Sheet ("Calculation"). Range ("A1: A50"). ImportRange accepted the list from a separate table, which I dedicated for this purpose.

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

Now, theoretically, this will work without problems, but the importRange function is so temperamental and shows the #REF error that occurs with random spreadsheets. I saw messages complaining of the same problem, so I gave up hope that importRange would be a viable solution.

I would like your opinion to be a good solution to avoid such a problem.

One solution that I was thinking about is to have a script copy values ​​from one sheet to all these sheets and work with this script every day, but I did not know how to copy the entire range from one table to another, and to be honest, I not sure if this is the best solution.

My experience with scripts with transverse tables is that a script to retrieve data from a pair of cells in 50+ sheets will probably take more than 5 minutes and, ultimately, will cease to exceed the allowable time.

Answer 1

How many of your 50 tables will get this error? Also, does the user have access to the source spreadsheet so that he can run importRange ()?

A script might not be ideal for your case, and you would be better off chasing importRange and making it work.

Answer 2

We had problems with the reliability of the import function. In our case, we needed information for automatic updates, but once a day was good for our application.

Our solution was a script that copied and pasted values ​​once a night. We also included a menu item in which the user can force an update on demand.

We did not have 50 sheets, so the script entry was direct. Depending on your situation, you could maintain the identifiers in one place and have a script loop through each spreadsheet, or you could have your own script in each spreadsheet.

Note. We collected a public report from the information on a personal sheet. The report was displayed on the website using embedded charts. Charts often display an error that has been fixed by returning to a public worksheet and allowing the import function to be updated.

Answer 3

Try adding a menu item that will trigger the flash function. I found that this function causes importRange 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(); } 

Answer 4

Could you please indicate how you are trying to access your spreadsheet? I don’t think that there is such a thing as a temperamental function ... it is usually something that is not well defined, which causes random errors.

Answer 5

Force recalculation of import variables by restoring cell formula

It is required:

Imported data is your goal where everything will be copied.

Imported data configuration - contains the following fields:

  + --------------------------------------------- + --- ----------------------------- +
 |  A |  B |
 + --------------------------------------------- + --- ----------------------------- +
 |  Import Data from Spreadsheet with the key: |  key |
 |  Import Data from Spreadsheet between range: |  A: AA |
 |  Imported Data select columns: |  SELECT * |
 |  Imported Data criteria: |  WHERE Col15 contains 'Offered' |
 |  Imported Data should by ordered by column: |  ORDER BY Col1 |
 + --------------------------------------------- + --- ----------------------------- +

Script:

 function onOpen() { try { var ss = SpreadsheetApp.getActiveSpreadsheet(); } catch(err) { Browser.msgBox(err); } var configsheet = ss.getSheetByName("Imported Data Config"); var configkey = configsheet.getRange("B1").getValue(); var configrange = configsheet.getRange("B2").getValue(); var configselect = configsheet.getRange("B3").getValue(); var configwhere = configsheet.getRange("B4").getValue(); var configorderby = configsheet.getRange("B5").getValue(); var importedsheet = ss.getSheetByName("Imported Data"); importedsheet.getRange("A1").setValue('=QUERY(IMPORTRANGE("' + configkey + '","' + configrange + '"),"' + configselect + ' ' + configwhere + ' ' + configorderby + '")'); SpreadsheetApp.flush(); // Solution of sourcecode is: http://stackoverflow.com/questions/13631584/how-can-i-force-a-recalculation-of-cell-using-importrange-function-in-a-google-s // OnOpen Trigger: https://developers.google.com/apps-script/understanding_triggers // Active Spreadsheet solution: https://productforums.google.com/forum/#!topic/docs/XIY0WNX0uL8 Browser.msgBox("Sync Complete!"); } 

This allows you to change the formula without editing the script and simplify the transfer of the script to different sheets.