Use =UNIQUE from another spreadsheet in WorkBook - Google Apps Script

Question

I am trying to use a function (=UNIQUE ? ) to return a list of names in a range and how many names are in that range - in another worksheet. I understand this function will do this - if the data is in SAME worksheet.

Here is the link to the spreadsheet example so you can see what I mean https://docs.google.com/spreadsheets/d/1Fro28JD-a-ZVcWIGkzCqgNM_QEljm64FPRIVztvJTmM/edit#gid=0

I have tried query then importrange the unique but no good

Thanks in Advance

Tanaike Tanaike · Accepted Answer · 2020-01-28T04:39:42

You want to retrieve the frequency of values at the cells "A2:A" in the sheet of CURRENT PALLETS AWAITING PROCESSING in your shared Spreadsheet.
- For example, in the case of value of Pacific Magazines - Nov 2019 Donation, you want to put Pacific Magazines - Nov 2019 Donation to the column "A" and the frequency of 45 to the column "B" at the other Spreadsheet.
You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

The flow of this sample script is as follows.

Retrieve the values from the sheet.
Calculate the frequency.
- Create an object and convert it to an array. And in this case, the array is sorted.
Put the values.

Sample script:

In this script, the result values are put to the sheet of destinationSheetName in the Spreadsheet of destinationSpreadsheetId. Before you run the script, please set these variables.

function myFunction() {
  var destinationSpreadsheetId = "###";  // Please set the destination Spreadsheet ID.
  var destinationSheetName = "Sheet1";  // Please set the destination sheet name of the Spreadsheet.

  var destinationSS = SpreadsheetApp.openById(destinationSpreadsheetId)
  var destinationSheet = destinationSS.getSheetByName(destinationSheetName);
  var sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = sourceSS.getSheetByName("CURRENT PALLETS AWAITING PROCESSING");

  // Retrieve the values from the sheet.
  var values = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, 1).getValues();

  // Calculate the frequency.
  var obj = values.reduce(function(o, [a]) {
    // o[a] = a in o ? o[a] + 1 : 1;
    if (a) o[a] = a in o ? o[a] + 1 : 1;  // Modified
    return o;
  }, {});
  var ar = Object.keys(obj).map(function(e) {return [e, obj[e]]});

  // Put the values.
  destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1, ar.length, ar[0].length).setValues(ar);
}

If you want to sort the result array, please put ar.sort(function(a, b) {return(b[1] - a[1])}) after var ar = Object.keys(obj).map(function(e) {return [e, obj[e]]});.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Use =UNIQUE from another spreadsheet in WorkBook - Google Apps Script

2 Answers

Flow:

Sample script:

References: