Automated Invoicing from Spreadsheets

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Outright")
    .addItem("Create PDF Links", "createPdfLinks")
    .addItem("Send PDFs", "sendPdfs")
    .addToUi();

  createHeadersIfNeeded();
}

function createHeadersIfNeeded() {
  const properties = PropertiesService.getScriptProperties();
  const headersSet = properties.getProperty("headersSet");

  if (!headersSet) {
    createHeaders();
    properties.setProperty("headersSet", "true");
  }
}

function createHeaders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headers = [
    "INVOICE_DATE", "DUE_DATE", "INVOICE_NUMBER", "Contact person",
    "contact_company", "Country", "my_company_details", "ITEM_1", "cost_1",
    "ITEM_2", "cost_2", "ITEM_3", "cost_3", "Total_cost", "my_bank_details",
    "Email", "status", "Final invoice"
  ];

  const existingHeaders = sheet.getRange("1:1").getValues()[0];
  const headersToSet = headers.map(header => 
    existingHeaders.includes(header) ? existingHeaders[existingHeaders.indexOf(header)] : header
  );

  sheet.getRange(1, 1, 1, headersToSet.length).setValues([headersToSet]);
  const headerRange = sheet.getRange(1, 1, 1, headersToSet.length);
  headerRange.setFontWeight("bold");
  headerRange.setFontSize(14);
}

function getTemplateId() {
  const templateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("invoice templates");
  if (!templateSheet) throw new Error('Sheet "invoice templates" not found');

  const templateUrl = templateSheet.getRange("B2").getValue();
  const templateId = templateUrl.match(/[-\w]{25,}/);
  if (!templateId) throw new Error('Template ID not found in the provided URL');

  return templateId[0];
}

function createPdfLinks() {
  try {
    const copiedSheet = createMonthlySheetCopy();
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const masterSheet = ss.getSheetByName("master-clients");
    const sheet = copiedSheet;

    const dataValues = sheet.getDataRange().getValues();
    const headers = dataValues[0];
    const templateFileId = getTemplateId();
    const templateDoc = DriveApp.getFileById(templateFileId);
    const templateName = templateDoc.getName();
    const folder = DriveApp.createFolder("Generated Documents");

    const currentDate = new Date();
    const currentMonth = new Intl.DateTimeFormat('en-US', { month: 'long' }).format(currentDate);
    const currentDay = ('0' + currentDate.getDate()).slice(-2);

    for (let i = 1; i < dataValues.length; i++) {
      const row = dataValues[i];

      // Generate invoice number
      const clientNumber = row[headers.indexOf("INVOICE_NUMBER")];
      const invoiceNumber = `${currentDate.getFullYear()}.${('0' + (currentDate.getMonth() + 1)).slice(-2)}.${clientNumber}`;
      row[headers.indexOf("INVOICE_NUMBER")] = invoiceNumber;

      // Set invoice date
      let invoiceDate = row[headers.indexOf("INVOICE_DATE")] || currentDate;
      row[headers.indexOf("INVOICE_DATE")] = invoiceDate;
      sheet.getRange(i + 1, headers.indexOf("INVOICE_DATE") + 1).setValue(
        Utilities.formatDate(invoiceDate, Session.getScriptTimeZone(), "dd MMM yyyy")
      );
      masterSheet.getRange(i + 1, headers.indexOf("INVOICE_DATE") + 1).setValue(
        Utilities.formatDate(invoiceDate, Session.getScriptTimeZone(), "dd MMM yyyy")
      );

      // Set due date (first of next month)
      let dueDate = row[headers.indexOf("DUE_DATE")] || (() => {
        const nextMonth = new Date(invoiceDate);
        nextMonth.setMonth(nextMonth.getMonth() + 1);
        nextMonth.setDate(1);
        return nextMonth;
      })();
      row[headers.indexOf("DUE_DATE")] = dueDate;
      sheet.getRange(i + 1, headers.indexOf("DUE_DATE") + 1).setValue(
        Utilities.formatDate(dueDate, Session.getScriptTimeZone(), "dd MMM yyyy")
      );
      masterSheet.getRange(i + 1, headers.indexOf("DUE_DATE") + 1).setValue(
        Utilities.formatDate(dueDate, Session.getScriptTimeZone(), "dd MMM yyyy")
      );

      // Calculate total cost
      const cost1 = parseFloat(row[headers.indexOf("cost_1")]) || 0;
      const cost2 = parseFloat(row[headers.indexOf("cost_2")]) || 0;
      const cost3 = parseFloat(row[headers.indexOf("cost_3")]) || 0;
      const totalCost = cost1 + cost2 + cost3;
      row[headers.indexOf("Total_cost")] = `${totalCost} USD`;
      sheet.getRange(i + 1, headers.indexOf("Total_cost") + 1).setValue(`${totalCost} USD`);
      masterSheet.getRange(i + 1, headers.indexOf("Total_cost") + 1).setValue(`${totalCost} USD`);

      // Create PDF from template
      const templateCopy = templateDoc.makeCopy();
      const doc = DocumentApp.openById(templateCopy.getId());
      const body = doc.getBody();
      headers.forEach((header, index) => {
        let value = row[index];
        if (value instanceof Date) value = Utilities.formatDate(value, Session.getScriptTimeZone(), "dd MMM yyyy");
        body.replaceText(`{{${header.toLowerCase()}}}`, value);
      });
      doc.saveAndClose();

      const copiedFile = DriveApp.getFileById(templateCopy.getId());
      const newFileName = `${templateName} - ${invoiceNumber}`;
      copiedFile.setName(newFileName);

      const finalPdf = DriveApp.createFile(copiedFile.getAs(MimeType.PDF));
      finalPdf.setName(`${newFileName}.pdf`);
      const pdfLink = finalPdf.getUrl();

      sheet.getRange(i + 1, headers.indexOf("Final invoice") + 1).setValue(pdfLink);
      sheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Created");
      masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Created");

      copiedFile.setTrashed(true);
    }

    SpreadsheetApp.getUi().alert("PDF Links successfully created.");
  } catch (error) {
    SpreadsheetApp.getUi().alert("Error: " + error.message);
    Logger.log("Error: " + error.message);
  }
}

function sendPdfs() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName("master-clients");
  const currentDate = new Date();
  const currentMonth = new Intl.DateTimeFormat('en-US', { month: 'long' }).format(currentDate);
  const lastTwoDigitsOfYear = String(currentDate.getFullYear()).slice(-2);
  const newSheetName = `${currentMonth} ${lastTwoDigitsOfYear} Invoices`;
  const copiedSheet = ss.getSheetByName(newSheetName);

  if (!copiedSheet) throw new Error('Copied sheet not found');

  const dataValues = copiedSheet.getDataRange().getValues();
  const headers = dataValues[0];
  const baseInvoiceName = `${currentMonth} ${('0' + currentDate.getDate()).slice(-2)} Invoice from Outright Systems`;

  for (let i = 1; i < dataValues.length; i++) {
    const row = dataValues[i];
    const email = row[headers.indexOf("Email")];
    const pdfLink = row[headers.indexOf("Final invoice")];

    if (email && pdfLink) {
      try {
        const fileId = pdfLink.match(/[-\w]{25,}/);
        if (fileId) {
          const pdfFile = DriveApp.getFileById(fileId[0]).getAs(MimeType.PDF);
          MailApp.sendEmail({
            to: email,
            subject: baseInvoiceName,
            body: "Please find attached your invoice.",
            attachments: [pdfFile]
          });
          copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Sent");
          masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Sent");
        } else {
          copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Invalid link");
          masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Invalid link");
        }
      } catch (e) {
        copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Failed to send");
        masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Failed to send");
        Logger.log(`Failed to send email to ${email}: ${e.message}`);
      }
    } else {
      copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("No email found");
      masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("No email found");
    }
  }

  SpreadsheetApp.getUi().alert("PDFs successfully sent.");
}

function createMonthlySheetCopy() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName("master-clients");
  if (!masterSheet) throw new Error('Sheet "master-clients" not found');

  const currentDate = new Date();
  const currentMonth = new Intl.DateTimeFormat('en-US', { month: 'long' }).format(currentDate);
  const lastTwoDigitsOfYear = String(currentDate.getFullYear()).slice(-2);
  const newSheetName = `${currentMonth} ${lastTwoDigitsOfYear} Invoices`;

  const existingSheet = ss.getSheetByName(newSheetName);
  if (existingSheet) ss.deleteSheet(existingSheet);

  return masterSheet.copyTo(ss).setName(newSheetName);
}