0

I have 50 google sheet files for 50 students. They need to key in their answers in their own google sheet file when they are asked to do so. I have a main google sheet to consolidate their data by using IMPORTRANGE formula. This is my formula:

=QUERY({IMPORTRANGE(...);IMPORTRANGE(...);IMPORTRANGE(...);...},"Select * where Col1 is not null")

I will have 50 IMPORTRANGE in the formula. So as expected, the main google sheet is very lag when the 50 students start to key in their answers at the same time. Sometimes, the formula will show #Value when all the students started to answer the questions at their own google sheet file. I need to keep refreshing the main google sheet so that the data will come out, but it will disappear again in a short while then I need to refresh it again (although it will settle down once most of the students finished answering the questions).

I know that using IMPORTRANGE is really not an efficient way to consolidate their answers in main google sheet file but I don't have other better way.

I tried to write a script so that they can send in their data by clicking the button assigned with the script. However, all the students need to go through the authorization process when they run the script for the first time. They don't know how to proceed when they saw the authorization process (not very good in using computer).

May I know is there any ways or tricks that I can use to solve the IMPORTRANGE issue? Or there are some way to write the script where we are not required to go through the authorization process when we run the script for the first time?

Hope to get some advice and help on this as I couldn't find a better way from Google already. Any help will be greatly appreciated!

weizer
  • 1,009
  • 3
  • 16
  • 39

2 Answers2

1

Using that many IMPORTRANGE formulas is definitely a bad idea. What I'd suggest you to do:

  1. keep a list of all your student spreadsheet in your main document
  2. write a script that will browse through all of the spreadsheets from that list and copy/paste values into your main document
  3. create a time based trigger that will run the script every X minutes (or hours), depending on how accurate you want the results to be

This is a simple solution, but efficient. Depending on the amount of data and number of students/spreadsheets you may consider other solutions (like writing a cloud function that will do the same as the script) but I think this will work for your use case

GoranK
  • 1,628
  • 2
  • 12
  • 22
  • Thinks to take in consideration: Witch cells are you gonna get data from? Are the sheetnames always the same/Sheet in first position? you need to map the name of the student based on the spreadsheet id (or use a vlookup in the sheet itself). So why not use google forms and let the answers go into a sheet? – RemcoE33 Aug 29 '21 at 19:39
  • based on the information in question posted, it seems that there is a single range per spreadsheet that needs to be read from. For 50 students copying the range would work even with the apps script. Of course, the whole range would be read and write in a single operation. Using the forms is also good idea but I think there must be a reason why weizer decided to use sheets instead – GoranK Aug 29 '21 at 20:06
1

If I understood correctly what you are looking is that your spreadsheet show in real-time the data being entered simultaneously on 50 different spreadsheets. I'm afraid that Google Sheets is not the right tool for what you are trying to do the way that you are trying to do it. Basically you have two options : change it or use a different tool.

It's not a good idea to have and array of multiple IMPORTRANGE functions that are being edited simultaneously because while the official docs says that IMPORTRANGE functions are updated every 30 minutes when the source and the spreadsheet having formula are opened at the same time the import is done practically immediately and could happen multiple times during the recalculation making causing it to start over an over again.

Replacing the above array by script might help only if you are open to not have the destination spreadsheet updated on real time as scripts are slow.

Replacing the above array by a program that uses the Google Sheets API also might help only if you are open to not have the destination spreadsheet updated on real time as the spreadsheet refresh.

Regarding running a script without requiring authorization that is only possible when using simple triggers and / or removing all the scopes that require authorization to run. Please bear in mind that you might create installable triggers to run other using the authorization of the user who creates them.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166