Google Apps Script to Get Unique Values From All Sheets in Folder

Question

I would like to create a master spreadsheet for attendance in Google Sheets. There are a bunch of other worksheets in the same folder, from which I would like to pull unique names and save these unique names in the master spreadsheet. Also, it would be beneficial to count the occurrences of these unique names. But I am able to make this happen using other methods.

So far, I have found a script that has collected all data entries from files in a specified folder, but I do not have enough experience with JS/Google Apps to implement the UNIQUE function equivalent in the code.

var spreadsheets = DriveApp.getFolderById("FOLDERID").getFilesByType(MimeType.GOOGLE_SHEETS);

  var data = [];
  while (spreadsheets.hasNext()) {
    var currentSpreadsheet = SpreadsheetApp.openById(spreadsheets.next().getId());
    data = data.concat(currentSpreadsheet.getSheetByName("Sheet1").getRange("RANGE").getValues());
  }

  SpreadsheetApp.
    getActiveSheet().
    getRange(1, 1, data.length, data[0].length).
    setValues(data);

Example of desired set-up:

File 1:

John Smith      EmailJS majorJS yearJS
James Johnson   EmailJJ majorJJ yearJJ
Monty Hall      EmailMH majorMH yearMH
Stephanie C.    emailSC majorSC yearSC
Martin R.       emailMR majorMR yearMR
Jack M.         emailJM majorJM yearJM

File 2:

asa sd      dgdsv   yurntb  eryb
sdads       svgsdvf bhg     tdbs
gdfgd       sfvgs   g       tbht
jfsfsds     sfvgsd  fb      thdbt
sgsgs       sawetv4 tbh     rtrb
qssqws      qedw    qwqd    qdqwd

File 3: (A Duplicate of File 1)

John Smith      EmailJS majorJS yearJS
James Johnson   EmailJJ majorJJ yearJJ
Monty Hall      EmailMH majorMH yearMH
Stephanie C.    emailSC majorSC yearSC
Martin R.       emailMR majorMR yearMR
Jack M.         emailJM majorJM yearJM

Desired Output:

Name            Email   Major   Year   Attendance
John Smith      EmailJS majorJS yearJS 2
James Johnson   EmailJJ majorJJ yearJJ 2
Monty Hall      EmailMH majorMH yearMH 2
Stephanie C.    emailSC majorSC yearSC 2
Martin R.       emailMR majorMR yearMR 2
Jack M.         emailJM majorJM yearJM 2
asa sd          dgdsv   yurntb  eryb   1
sdads           svgsdvf bhg     tdbs   1
gdfgd           sfvgs   g       tbht   1
jfsfsds         sfvgsd  fb      thdbt  1
sgsgs           sawetv4 tbh     rtrb   1
qssqws          qedw    qwqd    qdqwd  1

Answer 1

I would do it like this-

1. Initialize an empty array to keep as memory ('memory').

For every row in every source sheet you have-

2. Form an array ('item') using the four fields; name, email, major and year.
3. Use 'memory'.includes('item') method to find the 'item' inside 'memory'
4. If not found, write the 'item' into the destination sheet with attendance '1', and push the 'item' into the memory array.
5. If 'item' is found in 'memory', just increase the attendance of that 'item' in the destination sheet by 1. You don't need to push it in the 'memory' array a second time.

I'm sure there are more efficient methods than this. But I believe this would work.