CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom Sawkins
Tom Sawkins

Reputation: 361

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

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

Upvotes: 1

Views: 630

Answers (2)

user3105233
user3105233

Reputation: 71

Try this:

=unique(filter(importrange("URL", "sheetName!cellRange"),importrange("URL","sheetName!cellRange") = "conditionToBeMet"))

Upvotes: 0

Tanaike
Tanaike

Reputation: 201553

  • 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.

  1. Retrieve the values from the sheet.
  2. Calculate the frequency.
    • Create an object and convert it to an array. And in this case, the array is sorted.
  3. 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.

Upvotes: 2

Related Questions