Google Apps Script - Data Manipulation-CodePudding

I have a dataset with 3 columns. The data contains car data and I want to use a google apps script to split the dataset by car manufacturer into several datasets. Each subset would then need to be pasted into a separate worksheet within the google sheets script. I'm having trouble with dynamically getting the car manufacturer subset. Is there an effective way to get the subset and copy over to separate datasets? The other challenge is that the car manufacturer names have numbers tacked on at the end that vary in length. I would want all of the instances to be copy pasted into the right car manufacturer worksheet, so is there a way to identify the car manufacturer in the id and paste all the necessary rows? Also, is there a way to take the car manufacturer name and use that to rename the worksheet name when copying over the dataset?

Below is an example of what I'm hoping to do. My dataset looks like this in my worksheet:

Manufacturer	Year	Purchase Timestamp
Ford250	2018	5/19/2022 13:10:25
Ford4560	2019	6/03/2022 13:10:25
Ford12389	2020	8/22/2022 13:10:25
Toyota1	2011	9/07/2022 13:10:25
Tesla45678	2009	7/03/2022 13:10:25
Toyota89	2009	3/15/2022 13:10:25

In the above sample google sheets dataset, I'm hoping to get 3 subset datasets split by manufacturer (Ford, Toyota, Tesla) that would then be saved in separate worksheets within the Google sheets file:

Subset Dataset 1 (Ford):

Manufacturer	Year	Purchase Timestamp
Ford250	2018	5/19/2022 13:10:25
Ford4560	2019	6/03/2022 13:10:25
Ford12389	2020	8/22/2022 13:10:25

Subset Dataset 2 (Toyota):

Manufacturer	Year	Purchase Timestamp
Toyota1	2011	9/07/2022 13:10:25
Toyota89	2009	3/15/2022 13:10:25

Subset Dataset 3 (Tesla):

Manufacturer	Year	Purchase Timestamp
Tesla45678	2009	7/03/2022 13:10:25

Additionally, I'm trying to perform automation using google apps script, so I want to avoid making a column manually and use something like regex.

CodePudding user response：

I believe your goal is as follows.

You have a sheet of "dataset" including various data of 3 columns as shown in your question.
You want to split the data by each manufacturer (Ford, Toyota, Tesla).
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the variable of datasetSheetName and list for your actual situation. And, please run myFunction.

function myFunction() {
  const datasetSheetName = "dataset"; // Please set the sheet name of dataset.
  const list = ["Ford", "Toyota", "Tesla"]; // Please set "manufacturer" list you want to retrieve.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [, ...sourceValues] = ss.getSheetByName(datasetSheetName).getDataRange().getValues();
  const obj = sourceValues.reduce((o, r) => {
    const t = list.find(e => r[0].toLowerCase().includes(e.toLowerCase()));
    if (t) o[t] = o[t] ? [...o[t], r] : [r];
    return o;
  }, {});
  list.forEach(e => {
    const sheet = ss.getSheetByName(e) || ss.insertSheet(e);
    sheet.getRange(sheet.getLastRow()   1, 1, obj[e].length, obj[e][0].length).setValues(obj[e]);
  });
}

Note:

I think taht when this script is run, your goal is obtained. But, if an error occurs, in that case, I think that it is required to know your Spreadsheet.

References:

Added:

About your following new request,

is there a way to update the code above so that it works even if the manufacturer column is the 2nd column in the dataset and not the first?

In this case, please modify above script as follows.

From:

const t = list.find(e => r[0].toLowerCase().includes(e.toLowerCase()));

To:

const t = list.find(e => r[1].toLowerCase().includes(e.toLowerCase()));

CodePudding user response：

Try

function myFunction() {
  const list = ["Ford", "Toyota", "Tesla"];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [headers, ...values] = ss.getSheetByName("Sheet1").getDataRange().getValues();
  list.forEach(elem => {
    const result = [headers, ...values.filter(r => r[0].includes(elem))]
    const sheet = ss.insertSheet(elem);
    sheet.getRange(1, 1, result.length, result[0].length).setValues(result);
  })
}

filter

forEach