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

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.

CodePudding user response：

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));
}

CodePudding user response：

I believe your goal is as follows.

From your following goal.

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

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
You want to retrieve the values from the column "A" and "B" from 1st sheet in 3 Google Spreadsheet. And, you want to sum the value at the column "B" and, want to put the result values to the 1st sheet in "class aggregate" Spreadsheet.
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  // These are from your question.
  const srcSpreadsheetUrls = [
    "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"
  ];

  // This is from your question.
  const dstSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/18XnZ7ZaVGfxOTiaT11FH_t5s5RWQQevfZFWrUWb9gPM/edit?usp=sharing";

  // Retrieve values from the source Spreadsheets and calcucate and put as the 2 dimensional array.
  const obj = srcSpreadsheetUrls.reduce((o, url) => {
    const values = SpreadsheetApp.openByUrl(url).getSheets()[0].getDataRange().getValues()
    values.forEach(([a, b]) => o[a] = o[a] ? o[a]   b : b);
    return o;
  }, {});
  const values = Object.entries(obj);
  
  // Put the array to "class aggregate" Spreadsheet.
  const sheet = SpreadsheetApp.openByUrl(dstSpreadsheetUrl).getSheets()[0];
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Of course, you can retrieve the Spreadsheet by the Spreadsheet ID. But from your question, when the Spreadsheet URL is used, I thought that it might help to understand the script.

Sample script:

References: