How to add cell values across multiple separate Google Sheet files, when each file also has several worksheets?

Question

To implement an exercise in my upcoming Winter Quarter course, to reduce the burden on students and myself, I need to be able to add the value of a given cell across every student's file and post the value in a separate (class aggregate) file.

Each student file will itself contain several worksheets, and one cell value I need to aggregate may be on the top worksheet, another might be on, say, the third worksheet.

I'm hoping someone can provide an example of how to reference a given cell across separate Google Sheet files, so I can achieve the above. Once I write the formula, I am assuming, that since as instructor I will be the owner of all the files, the aggregate file will be dynamically updated as a student file changes.

Without this feature I would have to restrict what students can do, just to make my own task of then constructing the resulting aggregate easier.

The toy example below assumes that 3 students, each maintain a 2-worksheet Google Sheets file, as in

https://docs.google.com/spreadsheets/d/1pn9D6s382lvnsY-GoMr92aFGFmL_11N2g4tci0tWyzQ/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1aqrGEJQUFg8cdpJwOaj7ZO64iOm9op0U4kXsBn72JMI/edit?usp=sharing

https://docs.google.com/spreadsheets/d/18ixwhnSVesDDfba2AvSN7v-TPiemGEtavkAlw9GS02c/edit?usp=sharing

and I want to add up numbers in the second column of the first worksheet in each Google Sheets file, to get a "class aggregate" file, as in

https://docs.google.com/spreadsheets/d/18XnZ7ZaVGfxOTiaT11FH_t5s5RWQQevfZFWrUWb9gPM/edit?usp=sharing

without having to do a manual copy and paste and add (which is what I did above). Given that the student-maintained Google Sheets are weblinks (and I'll be the owner of those files as well as I'm the instructor), is there a simple way to use a longer path name and dynamically update the class aggregate file.

I hope I have explained what I want to do. Please let me know if you have any questions. Thank you for your time and attention.

Answer 1

Both Cooper and Tanaike above have offered suggestions using scripting, which I realize are much better answers than what I got from pursuing a blog.google link that I was sent by a colleague. Clearly scripting offers more flexibility, and will let me do far more things. My immediate goal was to see whether I could generate the numbers in my Aggregate file without using manual copy and paste.

The link https://blog.google/products/g-suite/g-suite-pro-tips-how-sync-one-spreadsheet-another-google-sheets/ taught me how to reference a tab in a worksheet, and then I first tried the function SUM. That does not seem to work across separate Google Sheet files. Then I tried the same Importrange function shown in an example in the link, and that did work. So I achieved what I wanted by then doing what I really wanted to do by first using IMPORTRANGE, and then using SUM inside the same tab. I checked that it would dynamically update, by changing one number 10 in the Sheet_10 toy file to 12. And the aggregate also changed.

What I did is shown in https://docs.google.com/spreadsheets/d/18XnZ7ZaVGfxOTiaT11FH_t5s5RWQQevfZFWrUWb9gPM/edit#gid=0

Given my winter quarter will start soon, and I am definitely not clever in coding, to get ready for class I will probably go with the less elegant solution. This Google Sheets problem arose in the context of wanting each student to maintain his own portfolio, but to show them aggregate performance in addition to other information they find.

Google Sheets has 400+ functions, but only some work across different files. SUM does not, IMPORTRANGE does. I've learnt a lot today. As I find time, I will try to use the scripting suggestions given here. Thanks for your time, and your help.

Answer 2

How about this without knowing anything else about your spreadsheets:

function ttttttttt(obj) {
  const ss = SpreadsheetApp.getActive();
  ss.getSheets().forEach(sh => sh.getRange(obj.a1not).setValue(obj.value));
}