 const folderId = ''; // put your folder id here or leave '' for root
const listTabName = 'Sheet1'; // tab that has the file names
const keepTabNames = ['Emails']; // tabs to keep in each new file

function createSheetFilesFromTemplate() {
  const masterSS = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = masterSS.getSheetByName(listTabName);

  if (!listSheet) {
    throw new Error(`Tab "${listTabName}" not found.`);
  }

  const db = new GSheetDB().connect().sheet(listTabName);
  const rows = db.getAll();

  const folder = folderId ? DriveApp.getFolderById(folderId) : null;
  const masterFile = DriveApp.getFileById(masterSS.getId());

  let count = 0;

  const fileExists = (name) => {
    const safeName = String(name).replace(/"/g, '\\"');
    const files = DriveApp.searchFiles(`title = "${safeName}"`);
    return files.hasNext();
  };

  const randomString = (length = 6) => {
    const chars = 'abcdefghijklmnopqrstuvwxyz0123456789';
    let result = '';
    for (let i = 0; i < length; i++) {
      result += chars.charAt(Math.floor(Math.random() * chars.length));
    }
    return result;
  };

  rows.forEach((row) => {
    const baseName = String(row.name || '').trim();
    const status = String(row.status || '').trim().toLowerCase();

    if (!baseName || status === 'created') return;

    let finalName = baseName;

    if (fileExists(finalName)) {
      do {
        finalName = `${baseName}-${randomString(6)}`;
      } while (fileExists(finalName));
    }

    // Make a copy of the current spreadsheet
    const copiedFile = folder
      ? masterFile.makeCopy(finalName, folder)
      : masterFile.makeCopy(finalName);

    const newSS = SpreadsheetApp.openById(copiedFile.getId());
    const sheets = newSS.getSheets();

    // Make sure all keep tabs exist in the template
    keepTabNames.forEach((tabName) => {
      if (!newSS.getSheetByName(tabName)) {
        throw new Error(`Tab "${tabName}" not found in the template file.`);
      }
    });

    // Delete all tabs except the ones listed in keepTabNames
    sheets.forEach((sheet) => {
      if (!keepTabNames.includes(sheet.getName())) {
        newSS.deleteSheet(sheet);
      }
    });

    db.update(
      { name: baseName },
      {
        status: 'Created',
        url: newSS.getUrl(),
        final_name: finalName
      }
    );

    count++;
  });

  SpreadsheetApp.getUi().alert(`✅ Done! ${count} files created`);
}