Google Apps Script - How to Update Code to Get Unique Values From Column And Filter Based On Them In-CodePudding

I currently have some code (pasted below) that I use in order to take subsets of a dataset and paste them in separate tabs in the same Google Sheets file. I'm currently manually inputting the values with which I filter the dataset in a list and then looping though each value in the list. I would like to update the code to look through the column and pick up on the unique values in the column and turn the unique values into a list that I would then look through using the rest of the code. I'm having trouble figuring out how to do this though.

Here is a link to the image of my dataset: enter image description here

Below is my code. I would really like to update the const list = "" part to not be manually inputted anymore but to grab the unique values from the Product Type column (column # 4).

function getSubsetDataComplaints() {
  const shName = "RawData";
  const list = ["Toy Cars", "Barbie Dolls", "Videogames", "Role Playing Games","Trading Card Games","Food"];

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [headers, ...values] = ss.getSheetByName(shName).getDataRange().getValues()

  list.forEach(elem => {
    const result = [headers, ...values.filter(r => r[3].includes(elem))]
    const sheet = ss.insertSheet(elem);
    sheet.getRange(1,1, result.length, result[0].length).setValues(result);
  })
}

CodePudding user response：

Try

  const shName = "RawData";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [headers, ...values] = ss.getSheetByName(shName).getDataRange().getValues()
  const list = values.map(r => r[3]).flat().filter(onlyUnique).sort()

and add this function

function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}

your complete code

function getSubsetDataComplaints() {
  const shName = "RawData";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [headers, ...values] = ss.getSheetByName(shName).getDataRange().getValues()
  const list = values.map(r => r[3]).flat().filter(onlyUnique).sort()
  list.forEach(elem => {
    try {
      if (elem != '') {
        const result = [headers, ...values.filter(r => r[3].includes(elem))]
        const sheet = ss.insertSheet(elem);
        sheet.getRange(1, 1, result.length, result[0].length).setValues(result);
      }
    } catch (e) {
      Browser.msgBox(e)
    }
  })
}

function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}