Google sheet SalesSync

function insertWeeklySummary() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const startCell = sheet.getRange("D5");
  const startRow = startCell.getRow();
  const startCol = startCell.getColumn();
  const endCol = sheet.getRange("AA4").getColumn();
  const doanhThuCol = 7;
  const thuTuTuanCol = 5;
  const dateCol = 4;
  const thuCol = 6;
  const originalLastRow = sheet.getLastRow();
  const sundayRows = [];
  let weekStartRow = startRow;

  const firstDate = sheet.getRange(startRow, dateCol).getValue();
  if (!(firstDate instanceof Date)) {
    SpreadsheetApp.getUi().alert("❌ Dữ liệu tại D" + startRow + " không phải định dạng ngày.");
    return;
  }

  const year = firstDate.getFullYear();
  const month = firstDate.getMonth();
  const daysInMonth = new Date(year, month + 1, 0).getDate();

  for (let r = sheet.getLastRow(); r >= startRow; r--) {
    const val = sheet.getRange(r, dateCol).getValue();
    if (val instanceof Date && (val.getMonth() !== month || val.getFullYear() !== year)) {
      sheet.deleteRow(r);
    }
  }

  let validDayRows = 0;
  for (let r = startRow; r <= sheet.getLastRow(); r++) {
    const val = sheet.getRange(r, dateCol).getValue();
    if (val instanceof Date && val.getMonth() === month && val.getFullYear() === year) {
      validDayRows++;
    }
  }

  const delta = daysInMonth - validDayRows;

  if (delta > 0) {
    for (let i = 0; i < delta; i++) {
      const newDate = new Date(year, month, validDayRows + i + 1);
      const insertAt = sheet.getLastRow();
      sheet.insertRowAfter(insertAt);
      sheet.getRange(insertAt + 1, dateCol).setValue(newDate);
      sheet.getRange(insertAt + 1, thuCol).setFormula(
        `=CHOOSE(WEEKDAY(D${insertAt + 1});"CHỦ NHẬT";"THỨ HAI";"THỨ BA";"THỨ TƯ";"THỨ NĂM";"THỨ SÁU";"THỨ BẢY")`
      );
    }
  }

  const finalValidRow = sheet.getLastRow();
  for (let row = startRow; row <= finalValidRow; row++) {
    const value = sheet.getRange(row, thuCol).getValue();
    if (typeof value === "string" && value.trim().toUpperCase() === "CHỦ NHẬT") {
      sundayRows.push(row);
    }
  }

  let offset = 0;
  let weekCounter = 1;
  const tongKetTuanRows = [];

  sundayRows.forEach(sundayRowOriginal => {
    const sundayRow = sundayRowOriginal + offset;
    const insertPos = sundayRow + 1;
    sheet.insertRowAfter(sundayRow);
    sheet.getRange(insertPos, startCol).setValue("TỔNG KẾT TUẦN");
    sheet.getRange(insertPos, thuTuTuanCol).setValue("Tuần " + weekCounter);
    sheet.getRange(insertPos, doanhThuCol).setFormula(`=SUM(G${weekStartRow}:G${sundayRow})`);
    sheet.getRange(insertPos, startCol, 1, endCol - startCol + 1).setFontWeight("bold").setBackground("#f2f2f2");
    tongKetTuanRows.push(insertPos);
    weekCounter++;
    weekStartRow = sundayRow + 2;
    offset += 1;
  });

  const lastRowCheck = sheet.getLastRow();
  const lastValue = sheet.getRange(lastRowCheck, startCol).getValue();
  if (lastValue !== "TỔNG KẾT TUẦN" && lastValue !== "TỔNG KẾT THÁNG") {
    const insertPos = lastRowCheck + 1;
    sheet.insertRowAfter(lastRowCheck);
    sheet.getRange(insertPos, startCol).setValue("TỔNG KẾT TUẦN");
    sheet.getRange(insertPos, thuTuTuanCol).setValue("Tuần " + weekCounter);
    sheet.getRange(insertPos, doanhThuCol).setFormula(`=SUM(G${weekStartRow}:G${lastRowCheck})`);
    sheet.getRange(insertPos, startCol, 1, endCol - startCol + 1).setFontWeight("bold").setBackground("#f2f2f2");
    tongKetTuanRows.push(insertPos);
  }

  const finalRow = sheet.getLastRow() + 1;
  sheet.insertRowAfter(sheet.getLastRow());
  sheet.getRange(finalRow, startCol).setValue("TỔNG KẾT THÁNG");
  sheet.getRange(finalRow, startCol, 1, endCol - startCol + 1).setFontWeight("bold").setBackground("#d9ead3");

  const sumCells = tongKetTuanRows.map(r => `G${r}`).join(";");
  if (sumCells) {
    sheet.getRange(finalRow, doanhThuCol).setFormula(`=SUM(${sumCells})`);
  }

  const tongKetThangCell = `G${finalRow}`;
  sheet.getRange("B3").setFormula(`=${tongKetThangCell}/B1`);
  sheet.getRange("B4").setFormula(`=B1-${tongKetThangCell}`);

  SpreadsheetApp.flush();
  SpreadsheetApp.getUi().alert("✅ Yến Ngọc ơi! Xong rồi nè.");
}