Limit Query Results to 2 Google Sheets / Google Apps Script

0
votes

Question Updated for Clarity

I have the below formula running as intended. However I would like to limit the results found in A2:C to 2 of each SKU found. the below table has A11134 which has 4 results, but I want to only see the first 2. so I would see (A11134 E01D1 48) & (A11134 F06C2 48) 

function PreparePrepPivotTable() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('PrepSheet');
sheet.getRange('G2').activate().setFormula('=QUERY({\'LOCATIONS\'!$A$2:$C},"SELECT * WHERE Col1 
MATCHES \'" & JOIN("|",FILTER(O2:O, NOT(ISBLANK(O2:O)))) & "\' ")');
}

Queryresults

Thanks In Advance

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

1 Answers

2
votes

NEW ANSWER

Ok in this case I would suggest using a script to obtain as much as SKU you want. First you use this function to filter the O coulmn and get your matching set. This set will contain a counter initially set to 0.

function GetNSKUS() {
  var ss =SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('PrepSheet');
  //Building the matching set.
  var matchingValues = ss.getRange("O2:O").getValues().filter(value => value[0] != '').map(nonBlankValue => [nonBlankValue[0], 0]);
  //Filtering out the desired number of values
  var values = sheet.getRange("LOCATIONS!$A$2:$C").getValues().filter(value => advancedFilter(value[0], matchingValues));

  let cols = values[0].length;
  let rows = values.length;
  //Printing out the found rows starting from G2
  sheet.getRange(2, 7, rows, cols).setValues(values);

}

You will then filter your LOCATION Sheet range with this function. When a match is done the counter will increment allowing you to filter up to the desired values.

function advancedFilter(value, toMatch) {
  let matched = toMatch.filter(couple => couple[0] === value);
  if (matched.length > 0) {
    if (matched[0][1]<2) { //Here you can decide when to stop returning SKUs
      matched[0][1] += 1;
      return true;
    }
  }
  return false;
}

OLD ANSWER

You can use limit to reduce your query output. In your case if you want only to return the first 2 rows matching your query you can use the following:

function PreparePrepPivotTable() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('PrepSheet');
  sheet.getRange('G2').activate().setFormula('=QUERY({\'LOCATIONS\'!$A$2:$C},"SELECT * WHERE Col1 MATCHES \'" & JOIN("|",FILTER(O2:O, NOT(ISBLANK(O2:O)))) & "\' LIMIT 2")');
}

References:

Limit