Yam Code

New Paste Browse

function onEditDev2(e) {
  const sheetName = 'Quan_ly_domain';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const timestamp = new Date().toLocaleString('en-US', { timeZone: 'Asia/Ho_Chi_Minh' });

// Debounce to prevent multiple triggers
  const properties = PropertiesService.getScriptProperties();
  const now = new Date().getTime();
  if (properties.getProperty('lastRun') && now - parseInt(properties.getProperty('lastRun')) < 5000) return;
  properties.setProperty('lastRun', now.toString());

// Check if edit is in the correct sheet
  const editedSheet = e.source.getActiveSheet();
  if (editedSheet.getName() !== sheetName) {
    Logger.log(`[${timestamp}] Skipped: Not ${sheetName}`);
    return;
  }

// Check row and column
  const editedRow = e.range.getRow();
  const columnStart = e.range.columnStart;
  const columnEnd = e.range.columnEnd;
  if (editedRow <= 1) {
    Logger.log(`[${timestamp}] Skipped: Header row`);
    return;
  }
  if (columnStart > 5 || columnEnd < 2) {
    Logger.log(`[${timestamp}] Skipped: Edit outside columns B to E`);
    return;
  }

const sheet1 = ss.getSheetByName(sheetName);
  const sheet2 = ss.getSheetByName('Mapping_Ladipage_NV');
  const sheet3 = ss.getSheetByName('Mapping_Domain_Alias');

if (!sheet1 || !sheet2 || !sheet3) {
    Logger.log(`[${timestamp}] Error: One or more sheets not found`);
    return;
  }

try {
    // Get row data (B to E)
    let [maDomain, tenDomain, loaiDomain, nhomSanPham] = sheet1.getRange(editedRow, 2, 1, 4).getValues()[0];
    Logger.log(`[${timestamp}] Row ${editedRow} Data: Mã Domain=${maDomain}, Tên Domain=${tenDomain}, Loại Domain=${loaiDomain}, Nhóm Sản phẩm=${nhomSanPham}`);

if (!tenDomain || !loaiDomain) {
      Logger.log(`[${timestamp}] Skipped: Tên Domain or Loại Domain empty`);
      return;
    }

// Generate Mã Domain based on formula logic
    const countLoaiDomain = sheet1.getRange(2, 4, editedRow - 1).getValues().filter(row => row[0] === loaiDomain).length + 1;
    let newMaDomain = '';
    if (loaiDomain === 'Landing Page') {
      newMaDomain = `ldp${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else if (loaiDomain === 'Alias') {
      newMaDomain = `ali${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else if (loaiDomain === 'API') {
      newMaDomain = `api${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else if (loaiDomain === 'Khác') {
      newMaDomain = `khac${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else {
      Logger.log(`[${timestamp}] Skipped: Invalid Loại Domain=${loaiDomain}`);
      return;
    }

// Update Mã Domain if it has changed
    if (maDomain !== newMaDomain) {
      maDomain = newMaDomain;
      sheet1.getRange(editedRow, 2).setValue(maDomain);
      Logger.log(`[${timestamp}] Updated Mã Domain to ${maDomain}`);
    }

// Check hash for changes
    const rowHash = Utilities.base64EncodeWebSafe((maDomain || '') + (tenDomain || '') + (loaiDomain || '') + (nhomSanPham || ''));
    const hashCell = sheet1.getRange(editedRow, 26).getValue();
    if (rowHash === hashCell) {
      Logger.log(`[${timestamp}] Skipped: No changes detected`);
      return;
    }

// Update hash in Sheet1
    sheet1.getRange(editedRow, 26).setValue(rowHash);
    Logger.log(`[${timestamp}] Updated hash in column Z`);

// Process row by key (Tên Domain for deletion, Mã Domain for update/add)
    function processRow(sheet, key, keyCol, startCol, endCol, deleteIfFound = false) {
      const data = sheet.getRange(2, startCol, Math.max(1, sheet.getLastRow() - 1), endCol - startCol + 1).getValues();
      const rowsToDelete = [];
      for (let i = 0; i < data.length; i++) {
        if (data[i][keyCol - startCol] === key) {
          const rowNum = i + 2;
          if (deleteIfFound) rowsToDelete.push(rowNum);
          else return rowNum;
        }
      }
      if (deleteIfFound && rowsToDelete.length) {
        rowsToDelete.sort((a, b) => b - a).forEach(row => {
          sheet.deleteRow(row);
          Logger.log(`[${timestamp}] Deleted row ${row} in ${sheet.getName()} for key=${key}`);
        });
      }
      return null;
    }

// Remove old entries based on Tên Domain
    processRow(sheet2, tenDomain, 3, 2, 5, true);
    processRow(sheet3, tenDomain, 3, 2, 4, true);

// Add or update new entry based on Loại Domain
    if (loaiDomain === 'Landing Page') {
      const targetSheet = sheet2;
      const existingRow = processRow(targetSheet, maDomain, 2, 2, 5);
      const row = existingRow || targetSheet.getRange("B:B").getValues().filter(String).length + 1;
      targetSheet.getRange(row, 2).setValue(maDomain);
      targetSheet.getRange(row, 3).setValue(tenDomain);
      targetSheet.getRange(row, 4).setValue(nhomSanPham);
      Logger.log(`[${timestamp}] ${existingRow ? 'Updated' : 'Added'} row ${row} in Mapping_Ladipage_NV`);
    } else if (loaiDomain === 'Alias') {
      const targetSheet = sheet3;
      const existingRow = processRow(targetSheet, maDomain, 2, 2, 4);
      const row = existingRow || targetSheet.getRange("B:B").getValues().filter(String).length + 1;
      targetSheet.getRange(row, 2).setValue(maDomain);
      targetSheet.getRange(row, 3).setValue(tenDomain);
      targetSheet.getRange(row, 5).setValue(nhomSanPham);
      Logger.log(`[${timestamp}] ${existingRow ? 'Updated' : 'Added'} row ${row} in Mapping_Domain_Alias`);
    } else if (loaiDomain === 'API' || loaiDomain === 'Khác') {
      Logger.log(`[${timestamp}] No sync needed for Loại Domain=${loaiDomain}`);
    }
  } catch (error) {
    Logger.log(`[${timestamp}] Error in onEditDev2: ${error.message}`);
  }
}

/*
function onEditDev2(e) {
  const sheetName = 'Quan_ly_domain';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const timestamp = new Date().toLocaleString('en-US', { timeZone: 'Asia/Ho_Chi_Minh' });

// Check row and column
  const editedRow = e.range.getRow();
  const columnStart = e.range.columnStart;
  const columnEnd = e.range.columnEnd;
  if (editedRow <= 1) {
    Logger.log(`[${timestamp}] Skipped: Header row`);
    return;
  }
  // Only process edits in columns B, C, D, E (2 to 5)
  if (columnStart > 5 || columnEnd < 2) {
    Logger.log(`[${timestamp}] Skipped: Edit outside columns B to E`);
    return;
  }

const sheet1 = ss.getSheetByName(sheetName);
  const sheet2 = ss.getSheetByName('Mapping_Ladipage_NV');
  const sheet3 = ss.getSheetByName('Mapping_Domain_Alias');

if (!sheet1 || !sheet2 || !sheet3) {
    Logger.log(`[${timestamp}] Error: One or more sheets not found`);
    return;
  }

try {
    // Get current row data (B to E) from Quan_ly_domain
    let [maDomain, tenDomain, loaiDomain, nhomSanPham] = sheet1.getRange(editedRow, 2, 1, 4).getValues()[0];
    Logger.log(`[${timestamp}] Current Row ${editedRow} Data: Mã Domain=${maDomain}, Tên Domain=${tenDomain}, Loại Domain=${loaiDomain}, Nhóm Sản phẩm=${nhomSanPham}`);

if (!tenDomain || !loaiDomain) {
      Logger.log(`[${timestamp}] Skipped: Tên Domain or Loại Domain empty`);
      return;
    }

// --- Dynamic Mã Domain Generation ---
    // Get the original (pre-edit) values from the sheet to determine if 'Loại Domain' changed
    const originalLoaiDomain = sheet1.getRange(editedRow, 4).getValue(); // Current value in sheet
    const originalMaDomain = sheet1.getRange(editedRow, 2).getValue(); // Current value in sheet

// Only regenerate Mã Domain if it's empty OR its prefix no longer matches the Loại Domain
    // OR if the Loại Domain itself was the cell being edited and it changed.
    if (!maDomain || !maDomain.startsWith(newMaDomainPrefix) || (e.range.getColumn() === 4 && loaiDomain !== originalLoaiDomain)) {
      const allMaDomainsInSheet1 = sheet1.getRange(2, 2, sheet1.getLastRow() - 1, 1).getValues().flat();
      let newSeqNum = 1;
      const regex = new RegExp(`^${newMaDomainPrefix}(\\d+)$`);
      const existingNumsForType = allMaDomainsInSheet1
                                   .map(id => {
                                     const match = id && id.match(regex);
                                     return match ? parseInt(match[1]) : 0;
                                   })
                                   .filter(num => num > 0);
      if (existingNumsForType.length > 0) {
        newSeqNum = Math.max(...existingNumsForType) + 1;
      }
      maDomain = `${newMaDomainPrefix}${Utilities.formatString('%02d', newSeqNum)}`;
      sheet1.getRange(editedRow, 2).setValue(maDomain); // Update Mã Domain in Sheet1
      Logger.log(`[${timestamp}] Updated Mã Domain in Sheet1 to ${maDomain} (re-generated).`);
    } else {
      Logger.log(`[${timestamp}] Keeping existing Mã Domain: ${maDomain}.`);
    }

// --- Hashing for Change Detection ---
    const newRowHash = Utilities.base64EncodeWebSafe((maDomain || '') + (tenDomain || '') + (loaiDomain || '') + (nhomSanPham || ''));
    const storedHash = sheet1.getRange(editedRow, 26).getValue();

// Skip if hash matches and edit was not in relevant columns.
    // If a relevant column (B, C, D, E) was edited, we proceed regardless of hash.
    if (newRowHash === storedHash && ![2, 3, 4, 5].includes(e.range.getColumn())) {
      Logger.log(`[${timestamp}] Skipped: No changes detected based on hash and irrelevant column edit.`);
      return;
    }
    sheet1.getRange(editedRow, 26).setValue(newRowHash); // Update hash
    Logger.log(`[${timestamp}] Updated hash in column Z.`);

// --- Cleanup when Loại Domain changes (from old mapping type to new one) ---
    // If originalLoaiDomain (before any script changes to column D) was a mapped type
    // and the current loaiDomain is a different mapped type (or API/Khac), clean up the old sheet.
    // This handles: Landing Page -> Alias, Alias -> Landing Page, or any mapped type -> API/Khac
    if (e.range.getColumn() === 4 && originalLoaiDomain && originalLoaiDomain !== loaiDomain) {
      if (originalLoaiDomain === 'Landing Page') {
        Logger.log(`[${timestamp}] Loại Domain changed from 'Landing Page'. Attempting cleanup in Mapping_Ladipage_NV for Tên Domain: ${tenDomain}.`);
        deleteRowsByKey(sheet2, tenDomain, 3, timestamp);
      } else if (originalLoaiDomain === 'Alias') {
        Logger.log(`[${timestamp}] Loại Domain changed from 'Alias'. Attempting cleanup in Mapping_Domain_Alias for Tên Domain: ${tenDomain}.`);
        deleteRowsByKey(sheet3, tenDomain, 3, timestamp);
      }
    }

// --- Processing for Sheet2 (Mapping_Ladipage_NV) ---
    // This sheet has columns: Mã Domain (B), Tên Domain (C), Nhóm Sản phẩm (D), Mã Offer (E), Mã NV (F)
    if (loaiDomain === 'Landing Page') {
      const targetSheet = sheet2;
      const maDomainCol = 2; // Column B
      const tenDomainCol = 3; // Column C
      const nhomSanPhamCol = 4; // Column D

let foundRowByMaDomain = findRowByKey(targetSheet, maDomain, maDomainCol); // Search by current Mã Domain
      let foundRowByTenDomain = findRowByKey(targetSheet, tenDomain, tenDomainCol); // Search by current Tên Domain

// Ensure that if a row is found by Ten Domain, it's either the same as Ma Domain or deleted
      if (foundRowByTenDomain && foundRowByTenDomain !== foundRowByMaDomain) {
          const maDomainInFoundRow = targetSheet.getRange(foundRowByTenDomain, maDomainCol).getValue();
          // If the entry found by Ten Domain has a different Mã Domain, delete it.
          // This happens if Mã Domain was regenerated/changed, or if Ten Domain was re-used.
          if (maDomainInFoundRow !== maDomain) {
              deleteRowsByKey(targetSheet, tenDomain, tenDomainCol, timestamp);
              Logger.log(`[${timestamp}] Cleaned up old entry in Mapping_Ladipage_NV (different Mã Domain for Tên Domain: ${tenDomain}).`);
              foundRowByTenDomain = null; // Mark as deleted for subsequent logic
          }
      }

// Main Logic for Update/Add
      if (foundRowByMaDomain) {
        // If a row exists by the *current* Mã Domain, this is an UPDATE.
        // Update only Tên Domain and Nhóm Sản phẩm, preserving other data (Mã Offer, Mã NV).
        targetSheet.getRange(foundRowByMaDomain, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(foundRowByMaDomain, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Updated row ${foundRowByMaDomain} in Mapping_Ladipage_NV for Mã Domain=${maDomain}. Kept Mã Offer/Mã NV.`);
      } else {
        // If no row found by Mã Domain, it's either truly new, or its Mã Domain changed
        // and its old entry was cleaned up. Append a new row.
        const newRow = targetSheet.getLastRow() + 1;
        targetSheet.getRange(newRow, maDomainCol).setValue(maDomain);
        targetSheet.getRange(newRow, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(newRow, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Added new row ${newRow} in Mapping_Ladipage_NV for Mã Domain=${maDomain}.`);
      }
    }
    // --- Processing for Sheet3 (Mapping_Domain_Alias) ---
    // This sheet has columns: Mã Domain (B), Tên Domain (C), (D is empty?), Nhóm Sản phẩm (E)
    else if (loaiDomain === 'Alias') {
      const targetSheet = sheet3;
      const maDomainCol = 2; // Column B
      const tenDomainCol = 3; // Column C
      const nhomSanPhamCol = 5; // Column E in Sheet3

// Ensure that if a row is found by Ten Domain, it's either the same as Ma Domain or deleted
      if (foundRowByTenDomain && foundRowByTenDomain !== foundRowByMaDomain) {
          const maDomainInFoundRow = targetSheet.getRange(foundRowByTenDomain, maDomainCol).getValue();
          if (maDomainInFoundRow !== maDomain) {
              deleteRowsByKey(targetSheet, tenDomain, tenDomainCol, timestamp);
              Logger.log(`[${timestamp}] Cleaned up old entry in Mapping_Domain_Alias (different Mã Domain for Tên Domain: ${tenDomain}).`);
              foundRowByTenDomain = null; // Mark as deleted for subsequent logic
          }
      }

if (foundRowByMaDomain) {
        // Update only Tên Domain and Nhóm Sản phẩm, preserving other data
        targetSheet.getRange(foundRowByMaDomain, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(foundRowByMaDomain, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Updated row ${foundRowByMaDomain} in Mapping_Domain_Alias for Mã Domain=${maDomain}.`);
      } else {
        const newRow = targetSheet.getLastRow() + 1;
        targetSheet.getRange(newRow, maDomainCol).setValue(maDomain);
        targetSheet.getRange(newRow, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(newRow, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Added new row ${newRow} in Mapping_Domain_Alias for Mã Domain=${maDomain}.`);
      }
    }
    // --- If Loại Domain is API or Khác, ensure it's removed from mapping sheets ---
    else if (loaiDomain === 'API' || loaiDomain === 'Khác') {
        Logger.log(`[${timestamp}] Loại Domain is API or Khác. No sync needed for this type, but checking for existing entries to remove.`);
        // Even if type didn't *just* change in this edit, if a row exists in mapping sheets for this tenDomain, remove it.
        deleteRowsByKey(sheet2, tenDomain, 3, timestamp);
        deleteRowsByKey(sheet3, tenDomain, 3, timestamp);
    }

} catch (error) {
    Logger.log(`[${timestamp}] Error in onEditDev2: ${error.message}`);
  }
}

// Helper to find a row by key in a target sheet
// Assumes keyCol is 1-indexed. Reads data from row 2 onwards.
function findRowByKey(sheet, key, keyCol) {
  // Ensure the range read is wide enough to cover all potential lookup/update columns (at least up to E/5)
  const lastDataColumn = Math.max(sheet.getLastColumn(), 5); // Assuming max relevant column is E (5)
  const data = sheet.getRange(2, 1, Math.max(1, sheet.getLastRow() - 1), lastDataColumn).getValues();
  for (let i = 0; i < data.length; i++) {
    // data[i][keyCol - 1] accesses the value in the key column (0-indexed array)
    if (data[i][keyCol - 1] === key) {
      return i + 2; // Return the actual row number (data is 0-indexed from row 2)
    }
  }
  return null; // Not found
}

// Helper to delete rows by key in a target sheet
// Assumes keyCol is 1-indexed. Deletes from row 2 onwards.
function deleteRowsByKey(sheet, key, keyCol, timestamp) {
  // Ensure the range read is wide enough to cover all potential lookup/update columns (at least up to E/5)
  const lastDataColumn = Math.max(sheet.getLastColumn(), 5); // Assuming max relevant column is E (5)
  const data = sheet.getRange(2, 1, Math.max(1, sheet.getLastRow() - 1), lastDataColumn).getValues();
  const rowsToDelete = [];
  for (let i = 0; i < data.length; i++) {
    if (data[i][keyCol - 1] === key) { // keyCol - 1 because data array is 0-indexed
      rowsToDelete.push(i + 2); // Store actual row numbers
    }
  }
  // Delete rows from bottom up to avoid index issues
  rowsToDelete.sort((a, b) => b - a).forEach(rowNum => {
    sheet.deleteRow(rowNum);
    Logger.log(`[${timestamp}] Deleted row ${rowNum} in ${sheet.getName()} for key=${key}.`);
  });
}
*/

​x
 
function onEditDev2(e) {
  const sheetName = 'Quan_ly_domain';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const timestamp = new Date().toLocaleString('en-US', { timeZone: 'Asia/Ho_Chi_Minh' });
​
  // Debounce to prevent multiple triggers
  const properties = PropertiesService.getScriptProperties();
  const now = new Date().getTime();
  if (properties.getProperty('lastRun') && now - parseInt(properties.getProperty('lastRun')) < 5000) return;
  properties.setProperty('lastRun', now.toString());
​
  // Check if edit is in the correct sheet
  const editedSheet = e.source.getActiveSheet();
  if (editedSheet.getName() !== sheetName) {
    Logger.log(`[${timestamp}] Skipped: Not ${sheetName}`);
    return;
  }
​
  // Check row and column
  const editedRow = e.range.getRow();
  const columnStart = e.range.columnStart;
  const columnEnd = e.range.columnEnd;
  if (editedRow <= 1) {
    Logger.log(`[${timestamp}] Skipped: Header row`);
    return;
  }
  if (columnStart > 5 || columnEnd < 2) {
    Logger.log(`[${timestamp}] Skipped: Edit outside columns B to E`);
    return;
  }
​
  const sheet1 = ss.getSheetByName(sheetName);
  const sheet2 = ss.getSheetByName('Mapping_Ladipage_NV');
  const sheet3 = ss.getSheetByName('Mapping_Domain_Alias');
​
  if (!sheet1 || !sheet2 || !sheet3) {
    Logger.log(`[${timestamp}] Error: One or more sheets not found`);
    return;
  }
​
  try {
    // Get row data (B to E)
    let [maDomain, tenDomain, loaiDomain, nhomSanPham] = sheet1.getRange(editedRow, 2, 1, 4).getValues()[0];
    Logger.log(`[${timestamp}] Row ${editedRow} Data: Mã Domain=${maDomain}, Tên Domain=${tenDomain}, Loại Domain=${loaiDomain}, Nhóm Sản phẩm=${nhomSanPham}`);
​
    if (!tenDomain || !loaiDomain) {
      Logger.log(`[${timestamp}] Skipped: Tên Domain or Loại Domain empty`);
      return;
    }
​
    // Generate Mã Domain based on formula logic
    const countLoaiDomain = sheet1.getRange(2, 4, editedRow - 1).getValues().filter(row => row[0] === loaiDomain).length + 1;
    let newMaDomain = '';
    if (loaiDomain === 'Landing Page') {
      newMaDomain = `ldp${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else if (loaiDomain === 'Alias') {
      newMaDomain = `ali${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else if (loaiDomain === 'API') {
      newMaDomain = `api${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else if (loaiDomain === 'Khác') {
      newMaDomain = `khac${Utilities.formatString('%02d', countLoaiDomain)}`;
    } else {
      Logger.log(`[${timestamp}] Skipped: Invalid Loại Domain=${loaiDomain}`);
      return;
    }
​
    // Update Mã Domain if it has changed
    if (maDomain !== newMaDomain) {
      maDomain = newMaDomain;
      sheet1.getRange(editedRow, 2).setValue(maDomain);
      Logger.log(`[${timestamp}] Updated Mã Domain to ${maDomain}`);
    }
​
    // Check hash for changes
    const rowHash = Utilities.base64EncodeWebSafe((maDomain || '') + (tenDomain || '') + (loaiDomain || '') + (nhomSanPham || ''));
    const hashCell = sheet1.getRange(editedRow, 26).getValue();
    if (rowHash === hashCell) {
      Logger.log(`[${timestamp}] Skipped: No changes detected`);
      return;
    }
​
    // Update hash in Sheet1
    sheet1.getRange(editedRow, 26).setValue(rowHash);
    Logger.log(`[${timestamp}] Updated hash in column Z`);
​
    // Process row by key (Tên Domain for deletion, Mã Domain for update/add)
    function processRow(sheet, key, keyCol, startCol, endCol, deleteIfFound = false) {
      const data = sheet.getRange(2, startCol, Math.max(1, sheet.getLastRow() - 1), endCol - startCol + 1).getValues();
      const rowsToDelete = [];
      for (let i = 0; i < data.length; i++) {
        if (data[i][keyCol - startCol] === key) {
          const rowNum = i + 2;
          if (deleteIfFound) rowsToDelete.push(rowNum);
          else return rowNum;
        }
      }
      if (deleteIfFound && rowsToDelete.length) {
        rowsToDelete.sort((a, b) => b - a).forEach(row => {
          sheet.deleteRow(row);
          Logger.log(`[${timestamp}] Deleted row ${row} in ${sheet.getName()} for key=${key}`);
        });
      }
      return null;
    }
​
    // Remove old entries based on Tên Domain
    processRow(sheet2, tenDomain, 3, 2, 5, true);
    processRow(sheet3, tenDomain, 3, 2, 4, true);
​
    // Add or update new entry based on Loại Domain
    if (loaiDomain === 'Landing Page') {
      const targetSheet = sheet2;
      const existingRow = processRow(targetSheet, maDomain, 2, 2, 5);
      const row = existingRow || targetSheet.getRange("B:B").getValues().filter(String).length + 1;
      targetSheet.getRange(row, 2).setValue(maDomain);
      targetSheet.getRange(row, 3).setValue(tenDomain);
      targetSheet.getRange(row, 4).setValue(nhomSanPham);
      Logger.log(`[${timestamp}] ${existingRow ? 'Updated' : 'Added'} row ${row} in Mapping_Ladipage_NV`);
    } else if (loaiDomain === 'Alias') {
      const targetSheet = sheet3;
      const existingRow = processRow(targetSheet, maDomain, 2, 2, 4);
      const row = existingRow || targetSheet.getRange("B:B").getValues().filter(String).length + 1;
      targetSheet.getRange(row, 2).setValue(maDomain);
      targetSheet.getRange(row, 3).setValue(tenDomain);
      targetSheet.getRange(row, 5).setValue(nhomSanPham);
      Logger.log(`[${timestamp}] ${existingRow ? 'Updated' : 'Added'} row ${row} in Mapping_Domain_Alias`);
    } else if (loaiDomain === 'API' || loaiDomain === 'Khác') {
      Logger.log(`[${timestamp}] No sync needed for Loại Domain=${loaiDomain}`);
    }
  } catch (error) {
    Logger.log(`[${timestamp}] Error in onEditDev2: ${error.message}`);
  }
}
​
/*
function onEditDev2(e) {
  const sheetName = 'Quan_ly_domain';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const timestamp = new Date().toLocaleString('en-US', { timeZone: 'Asia/Ho_Chi_Minh' });
​
  // Debounce to prevent multiple triggers
  const properties = PropertiesService.getScriptProperties();
  const now = new Date().getTime();
  if (properties.getProperty('lastRun') && now - parseInt(properties.getProperty('lastRun')) < 5000) return;
  properties.setProperty('lastRun', now.toString());
​
  // Check if edit is in the correct sheet
  const editedSheet = e.source.getActiveSheet();
  if (editedSheet.getName() !== sheetName) {
    Logger.log(`[${timestamp}] Skipped: Not ${sheetName}`);
    return;
  }
​
  // Check row and column
  const editedRow = e.range.getRow();
  const columnStart = e.range.columnStart;
  const columnEnd = e.range.columnEnd;
  if (editedRow <= 1) {
    Logger.log(`[${timestamp}] Skipped: Header row`);
    return;
  }
  // Only process edits in columns B, C, D, E (2 to 5)
  if (columnStart > 5 || columnEnd < 2) {
    Logger.log(`[${timestamp}] Skipped: Edit outside columns B to E`);
    return;
  }
​
  const sheet1 = ss.getSheetByName(sheetName);
  const sheet2 = ss.getSheetByName('Mapping_Ladipage_NV');
  const sheet3 = ss.getSheetByName('Mapping_Domain_Alias');
​
  if (!sheet1 || !sheet2 || !sheet3) {
    Logger.log(`[${timestamp}] Error: One or more sheets not found`);
    return;
  }
​
  try {
    // Get current row data (B to E) from Quan_ly_domain
    let [maDomain, tenDomain, loaiDomain, nhomSanPham] = sheet1.getRange(editedRow, 2, 1, 4).getValues()[0];
    Logger.log(`[${timestamp}] Current Row ${editedRow} Data: Mã Domain=${maDomain}, Tên Domain=${tenDomain}, Loại Domain=${loaiDomain}, Nhóm Sản phẩm=${nhomSanPham}`);
​
    if (!tenDomain || !loaiDomain) {
      Logger.log(`[${timestamp}] Skipped: Tên Domain or Loại Domain empty`);
      return;
    }
​
    // --- Dynamic Mã Domain Generation ---
    // Get the original (pre-edit) values from the sheet to determine if 'Loại Domain' changed
    const originalLoaiDomain = sheet1.getRange(editedRow, 4).getValue(); // Current value in sheet
    const originalMaDomain = sheet1.getRange(editedRow, 2).getValue(); // Current value in sheet
​
    let newMaDomainPrefix = '';
    if (loaiDomain === 'Landing Page') {
      newMaDomainPrefix = 'ldp';
    } else if (loaiDomain === 'Alias') {
      newMaDomainPrefix = 'ali';
    } else if (loaiDomain === 'API') {
      newMaDomainPrefix = 'api';
    } else if (loaiDomain === 'Khác') {
      newMaDomainPrefix = 'khac';
    } else {
      Logger.log(`[${timestamp}] Skipped: Invalid Loại Domain=${loaiDomain}`);
      return;
    }
​
    // Only regenerate Mã Domain if it's empty OR its prefix no longer matches the Loại Domain
    // OR if the Loại Domain itself was the cell being edited and it changed.
    if (!maDomain || !maDomain.startsWith(newMaDomainPrefix) || (e.range.getColumn() === 4 && loaiDomain !== originalLoaiDomain)) {
      const allMaDomainsInSheet1 = sheet1.getRange(2, 2, sheet1.getLastRow() - 1, 1).getValues().flat();
      let newSeqNum = 1;
      const regex = new RegExp(`^${newMaDomainPrefix}(\\d+)$`);
      const existingNumsForType = allMaDomainsInSheet1
                                   .map(id => {
                                     const match = id && id.match(regex);
                                     return match ? parseInt(match[1]) : 0;
                                   })
                                   .filter(num => num > 0);
      if (existingNumsForType.length > 0) {
        newSeqNum = Math.max(...existingNumsForType) + 1;
      }
      maDomain = `${newMaDomainPrefix}${Utilities.formatString('%02d', newSeqNum)}`;
      sheet1.getRange(editedRow, 2).setValue(maDomain); // Update Mã Domain in Sheet1
      Logger.log(`[${timestamp}] Updated Mã Domain in Sheet1 to ${maDomain} (re-generated).`);
    } else {
      Logger.log(`[${timestamp}] Keeping existing Mã Domain: ${maDomain}.`);
    }
​
    // --- Hashing for Change Detection ---
    const newRowHash = Utilities.base64EncodeWebSafe((maDomain || '') + (tenDomain || '') + (loaiDomain || '') + (nhomSanPham || ''));
    const storedHash = sheet1.getRange(editedRow, 26).getValue();
​
    // Skip if hash matches and edit was not in relevant columns.
    // If a relevant column (B, C, D, E) was edited, we proceed regardless of hash.
    if (newRowHash === storedHash && ![2, 3, 4, 5].includes(e.range.getColumn())) {
      Logger.log(`[${timestamp}] Skipped: No changes detected based on hash and irrelevant column edit.`);
      return;
    }
    sheet1.getRange(editedRow, 26).setValue(newRowHash); // Update hash
    Logger.log(`[${timestamp}] Updated hash in column Z.`);
​
    // --- Cleanup when Loại Domain changes (from old mapping type to new one) ---
    // If originalLoaiDomain (before any script changes to column D) was a mapped type
    // and the current loaiDomain is a different mapped type (or API/Khac), clean up the old sheet.
    // This handles: Landing Page -> Alias, Alias -> Landing Page, or any mapped type -> API/Khac
    if (e.range.getColumn() === 4 && originalLoaiDomain && originalLoaiDomain !== loaiDomain) {
      if (originalLoaiDomain === 'Landing Page') {
        Logger.log(`[${timestamp}] Loại Domain changed from 'Landing Page'. Attempting cleanup in Mapping_Ladipage_NV for Tên Domain: ${tenDomain}.`);
        deleteRowsByKey(sheet2, tenDomain, 3, timestamp);
      } else if (originalLoaiDomain === 'Alias') {
        Logger.log(`[${timestamp}] Loại Domain changed from 'Alias'. Attempting cleanup in Mapping_Domain_Alias for Tên Domain: ${tenDomain}.`);
        deleteRowsByKey(sheet3, tenDomain, 3, timestamp);
      }
    }
​
​
    // --- Processing for Sheet2 (Mapping_Ladipage_NV) ---
    // This sheet has columns: Mã Domain (B), Tên Domain (C), Nhóm Sản phẩm (D), Mã Offer (E), Mã NV (F)
    if (loaiDomain === 'Landing Page') {
      const targetSheet = sheet2;
      const maDomainCol = 2; // Column B
      const tenDomainCol = 3; // Column C
      const nhomSanPhamCol = 4; // Column D
​
      let foundRowByMaDomain = findRowByKey(targetSheet, maDomain, maDomainCol); // Search by current Mã Domain
      let foundRowByTenDomain = findRowByKey(targetSheet, tenDomain, tenDomainCol); // Search by current Tên Domain
​
      // Ensure that if a row is found by Ten Domain, it's either the same as Ma Domain or deleted
      if (foundRowByTenDomain && foundRowByTenDomain !== foundRowByMaDomain) {
          const maDomainInFoundRow = targetSheet.getRange(foundRowByTenDomain, maDomainCol).getValue();
          // If the entry found by Ten Domain has a different Mã Domain, delete it.
          // This happens if Mã Domain was regenerated/changed, or if Ten Domain was re-used.
          if (maDomainInFoundRow !== maDomain) {
              deleteRowsByKey(targetSheet, tenDomain, tenDomainCol, timestamp);
              Logger.log(`[${timestamp}] Cleaned up old entry in Mapping_Ladipage_NV (different Mã Domain for Tên Domain: ${tenDomain}).`);
              foundRowByTenDomain = null; // Mark as deleted for subsequent logic
          }
      }
​
      // Main Logic for Update/Add
      if (foundRowByMaDomain) {
        // If a row exists by the *current* Mã Domain, this is an UPDATE.
        // Update only Tên Domain and Nhóm Sản phẩm, preserving other data (Mã Offer, Mã NV).
        targetSheet.getRange(foundRowByMaDomain, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(foundRowByMaDomain, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Updated row ${foundRowByMaDomain} in Mapping_Ladipage_NV for Mã Domain=${maDomain}. Kept Mã Offer/Mã NV.`);
      } else {
        // If no row found by Mã Domain, it's either truly new, or its Mã Domain changed
        // and its old entry was cleaned up. Append a new row.
        const newRow = targetSheet.getLastRow() + 1;
        targetSheet.getRange(newRow, maDomainCol).setValue(maDomain);
        targetSheet.getRange(newRow, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(newRow, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Added new row ${newRow} in Mapping_Ladipage_NV for Mã Domain=${maDomain}.`);
      }
    }
    // --- Processing for Sheet3 (Mapping_Domain_Alias) ---
    // This sheet has columns: Mã Domain (B), Tên Domain (C), (D is empty?), Nhóm Sản phẩm (E)
    else if (loaiDomain === 'Alias') {
      const targetSheet = sheet3;
      const maDomainCol = 2; // Column B
      const tenDomainCol = 3; // Column C
      const nhomSanPhamCol = 5; // Column E in Sheet3
​
      let foundRowByMaDomain = findRowByKey(targetSheet, maDomain, maDomainCol); // Search by current Mã Domain
      let foundRowByTenDomain = findRowByKey(targetSheet, tenDomain, tenDomainCol); // Search by current Tên Domain
​
      // Ensure that if a row is found by Ten Domain, it's either the same as Ma Domain or deleted
      if (foundRowByTenDomain && foundRowByTenDomain !== foundRowByMaDomain) {
          const maDomainInFoundRow = targetSheet.getRange(foundRowByTenDomain, maDomainCol).getValue();
          if (maDomainInFoundRow !== maDomain) {
              deleteRowsByKey(targetSheet, tenDomain, tenDomainCol, timestamp);
              Logger.log(`[${timestamp}] Cleaned up old entry in Mapping_Domain_Alias (different Mã Domain for Tên Domain: ${tenDomain}).`);
              foundRowByTenDomain = null; // Mark as deleted for subsequent logic
          }
      }
​
      if (foundRowByMaDomain) {
        // Update only Tên Domain and Nhóm Sản phẩm, preserving other data
        targetSheet.getRange(foundRowByMaDomain, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(foundRowByMaDomain, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Updated row ${foundRowByMaDomain} in Mapping_Domain_Alias for Mã Domain=${maDomain}.`);
      } else {
        const newRow = targetSheet.getLastRow() + 1;
        targetSheet.getRange(newRow, maDomainCol).setValue(maDomain);
        targetSheet.getRange(newRow, tenDomainCol).setValue(tenDomain);
        targetSheet.getRange(newRow, nhomSanPhamCol).setValue(nhomSanPham);
        Logger.log(`[${timestamp}] Added new row ${newRow} in Mapping_Domain_Alias for Mã Domain=${maDomain}.`);
      }
    }
    // --- If Loại Domain is API or Khác, ensure it's removed from mapping sheets ---
    else if (loaiDomain === 'API' || loaiDomain === 'Khác') {
        Logger.log(`[${timestamp}] Loại Domain is API or Khác. No sync needed for this type, but checking for existing entries to remove.`);
        // Even if type didn't *just* change in this edit, if a row exists in mapping sheets for this tenDomain, remove it.
        deleteRowsByKey(sheet2, tenDomain, 3, timestamp);
        deleteRowsByKey(sheet3, tenDomain, 3, timestamp);
    }
​
  } catch (error) {
    Logger.log(`[${timestamp}] Error in onEditDev2: ${error.message}`);
  }
}
​
// Helper to find a row by key in a target sheet
// Assumes keyCol is 1-indexed. Reads data from row 2 onwards.
function findRowByKey(sheet, key, keyCol) {
  // Ensure the range read is wide enough to cover all potential lookup/update columns (at least up to E/5)
  const lastDataColumn = Math.max(sheet.getLastColumn(), 5); // Assuming max relevant column is E (5)
  const data = sheet.getRange(2, 1, Math.max(1, sheet.getLastRow() - 1), lastDataColumn).getValues();
  for (let i = 0; i < data.length; i++) {
    // data[i][keyCol - 1] accesses the value in the key column (0-indexed array)
    if (data[i][keyCol - 1] === key) {
      return i + 2; // Return the actual row number (data is 0-indexed from row 2)
    }
  }
  return null; // Not found
}
​
// Helper to delete rows by key in a target sheet
// Assumes keyCol is 1-indexed. Deletes from row 2 onwards.
function deleteRowsByKey(sheet, key, keyCol, timestamp) {
  // Ensure the range read is wide enough to cover all potential lookup/update columns (at least up to E/5)
  const lastDataColumn = Math.max(sheet.getLastColumn(), 5); // Assuming max relevant column is E (5)
  const data = sheet.getRange(2, 1, Math.max(1, sheet.getLastRow() - 1), lastDataColumn).getValues();
  const rowsToDelete = [];
  for (let i = 0; i < data.length; i++) {
    if (data[i][keyCol - 1] === key) { // keyCol - 1 because data array is 0-indexed
      rowsToDelete.push(i + 2); // Store actual row numbers
    }
  }
  // Delete rows from bottom up to avoid index issues
  rowsToDelete.sort((a, b) => b - a).forEach(rowNum => {
    sheet.deleteRow(rowNum);
    Logger.log(`[${timestamp}] Deleted row ${rowNum} in ${sheet.getName()} for key=${key}.`);
  });
}
*/

Paste Settings

Paste Title : [Optional]

Paste Folder : [Optional]

Syntax Highlighting : [Optional]

Paste Expiration : [Optional]

Paste Status : [Optional]

Password : [Optional]

Description: [Optional]

Tags: [Optional]

Encrypt Paste (?)

You are currently not logged in, this means you can not edit or delete anything you paste. Sign Up or Login

Paste Settings

Site Languages