I'm using the following code to turn tabs in a Google Sheets file into separate Google Sheets files. One challenge that I have is that there's some tabs that have similar names and I don't want to have separate Google Sheets files for them.

For ex: Say there's 5 tabs in the Google Sheets file: tab1: 'Toys Check', tab2: 'Toys Checked', tab3: 'Clothes Check', tab4: 'Video Games Check', tab5: 'Video Games Checked'. Right now, the below code will create 5 separate Google Sheet's files for them. However, I want the code to create 3 separate Google Sheets files instead. The first Google Sheets file would contain 2 tabs: tab1: 'Toys Check' and tab2: 'Toys Checked'. The second Google Sheets file would just have 'Clothes Check' tab. The third Google Sheets file would also contain 2 tabs: tab1: 'Video Games Check' and tab2: 'Video Games Checked'.

Below is a visual of the above example:

How can the below code be updated to ensure that tabs that have the same name (minus the 'check' or 'checked' part of the tab name) would end up being split into the same Google Sheets file? For the file name, is it possible to make it so that it is the same as the unique category 'Summary'. So in the above example, the file name for the Google Sheets file that contains 'Toys Check' and 'Toys Checked' tabs would be 'Toys Summary'.

function copySheetsToFolder() {
  var ss = SpreadsheetApp.getActive();
  var folderId = DriveApp.getFileById(ss.getId()).getParents().next().getId();
  for (var n in ss.getSheets()) {
    var sheet = ss.getSheets()[n];
    var name = sheet.getName();
    if (name != 'ControlTab' && name != 'RawData') {
      var alreadyExist = DriveApp.getFilesByName(name);
      while (alreadyExist.hasNext()) {
        alreadyExist.next().setTrashed(true);
      }
      var newSS = Drive.Files.insert({ title: name, mimeType: MimeType.GOOGLE_SHEETS, parents: [{ id: folderId }] }, null, { supportsAllDrives: true });
      var copy = SpreadsheetApp.openById(newSS.id);
      sheet.copyTo(copy).setName(name);
      copy.deleteSheet(copy.getSheets()[0]);
    }
  }
}

CodePudding user response：

I believe your goal is as follows.

• You want to achieve a goal shown in your question using Google Apps Script.
• The suffix of the sheet names are always Check or Checked.
• You want to give the new Spreadsheet names like ### Summary.
• From your showing script, you want to remove the existing Spreadsheets in the parent folder of the active Spreadsheet.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const exclude = ['ControlTab', 'RawData']; // This is from your showing script.
  const suffix = ["Checked", "Check"]; // This is from your comment.
  const newSuffix = "Summary"; // This is from your question.

  // Retrieve active Spreadsheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Create an object for creating new Spreadsheets by splitting sheets.
  const obj = ss.getSheets().reduce((o, s) => {
    const name = s.getSheetName();
    if (!exclude.includes(name)) {
      const n = name.replace(new RegExp(suffix.join("|")), "").trim();
      o[n] = o[n] ? [...o[n], s] : [s];
    }
    return o;
  }, {});

  // Retrieve files from the parent folder and create a file object.
  const folder = DriveApp.getFileById(ss.getId()).getParents().next();
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const fileObj = {};
  while (files.hasNext()) {
    const f = files.next();
    fileObj[f.getName()] = f;
  }

  // Create new Spreadsheets including several sheets.
  Object.entries(obj).forEach(([ssName, sheets]) => {
    const newSSName = `${ssName} ${newSuffix}`;
    if (fileObj[newSSName]) {
      fileObj[newSSName].setTrashed(true);
    }
    const newss = SpreadsheetApp.create(newSSName);
    sheets.forEach(s => s.copyTo(newss).setName(s.getName()));
    newss.deleteSheet(newss.getSheets()[0]);
    DriveApp.getFileById(newss.getId()).moveTo(folder);
  });
}

• The variables exclude, suffix and newSuffix are from your question and your comment.
• When this script is run, several new Spreadsheets including several sheets are created in the same folder of the active Spreadsheet. Each sheet in a Spreadsheet is retrieved from the sheet name.

References:

• reduce()
• forEach()