Apps Scripts Code

Copy Code

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];
  let headersToSet = [];


  headers.forEach((header, index) => {
    if (!existingHeaders.includes(header)) {
      headersToSet.push([header]);
    } else {
      headersToSet.push([existingHeaders[existingHeaders.indexOf(header)]]);
    }
  });


  sheet.getRange(1, 1, 1, headersToSet.length).setValues([headersToSet.flat()]);
  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();  // Assuming the template URL is in cell B2
  const templateId = templateUrl.match(/[-\w]{25,}/);  // Extract the ID from the URL
  if (!templateId) {
    throw new Error('Template ID not found in the provided URL');
  }
  return templateId[0];
}


function createPdfLinks() {
  try {
    const copiedSheet = createMonthlySheetCopy(); // Create a copy of the master sheet before creating PDF links


    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var masterSheet = ss.getSheetByName("master-clients");
    var sheet = copiedSheet;
    if (!sheet) {
      throw new Error('Copied sheet not found');
    }


    var dataRange = sheet.getDataRange();
    var dataValues = dataRange.getValues();
    var headers = dataValues[0]; // Get the headers from the first row
    var templateFileId = getTemplateId();  // Get the template ID from the "invoice templates" sheet
    var templateDoc = DriveApp.getFileById(templateFileId);
    var templateName = templateDoc.getName();
    var folder = DriveApp.createFolder("Generated Documents");


    var currentDate = new Date();
    var currentMonth = new Intl.DateTimeFormat('en-US', { month: 'long' }).format(currentDate);
    var lastTwoDigitsOfYear = String(currentDate.getFullYear()).slice(-2);
    var currentDay = ('0' + currentDate.getDate()).slice(-2);
    var invoiceName = currentMonth + ' ' + currentDay + ' Invoice from Outright Systems';


    for (var i = 1; i < dataValues.length; i++) { var row = dataValues[i]; var clientNumber = row[headers.indexOf("INVOICE_NUMBER")]; var invoiceNumber = currentDate.getFullYear() + '.' + ('0' + (currentDate.getMonth() + 1)).slice(-2) + '.' + clientNumber; row[headers.indexOf("INVOICE_NUMBER")] = invoiceNumber; var invoiceDate = row[headers.indexOf("INVOICE_DATE")]; if (!invoiceDate) { invoiceDate = 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")); } var dueDate = row[headers.indexOf("DUE_DATE")]; if (!dueDate) { var nextMonthDate = new Date(invoiceDate); nextMonthDate.setMonth(nextMonthDate.getMonth() + 1); nextMonthDate.setDate(1); row[headers.indexOf("DUE_DATE")] = nextMonthDate; sheet.getRange(i + 1, headers.indexOf("DUE_DATE") + 1).setValue(Utilities.formatDate(nextMonthDate, Session.getScriptTimeZone(), "dd MMM yyyy")); masterSheet.getRange(i + 1, headers.indexOf("DUE_DATE") + 1).setValue(Utilities.formatDate(nextMonthDate, Session.getScriptTimeZone(), "dd MMM yyyy")); } var cost1 = parseFloat(row[headers.indexOf("cost_1")]) || 0; var cost2 = parseFloat(row[headers.indexOf("cost_2")]) || 0; var cost3 = parseFloat(row[headers.indexOf("cost_3")]) || 0; var 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"); var templateCopy = templateDoc.makeCopy(); var doc = DocumentApp.openById(templateCopy.getId()); var body = doc.getBody();
headers.forEach((header, index) => {
        var placeholder = "{{" + header.toLowerCase() + "}}";
        var value = row[index];
        if (typeof value === 'object' && value instanceof Date) {
          value = Utilities.formatDate(value, Session.getScriptTimeZone(), "dd MMM yyyy");
        }
        body.replaceText(placeholder, value);
      });


      doc.saveAndClose();


      var newFileName = templateName + " - " + invoiceNumber;
      var copiedFile = DriveApp.getFileById(templateCopy.getId());
      copiedFile.setName(newFileName);


      var pdfFile = copiedFile.getAs(MimeType.PDF);
      var finalPdf = folder.createFile(pdfFile);
      finalPdf.setName(newFileName + ".pdf");


      var pdfLink = finalPdf.getUrl();
      sheet.getRange(i + 1, headers.indexOf("Final invoice") + 1).setValue(pdfLink);


      // Update status to "Created" in both sheets
      sheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Created");
      masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Created");


      Logger.log("PDF created: " + pdfLink);


      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() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getSheetByName("master-clients");
  if (!masterSheet) {
    throw new Error('Sheet "master-clients" not found');
  }
  var currentDate = new Date();
  var currentMonth = new Intl.DateTimeFormat('en-US', { month: 'long' }).format(currentDate);
  var lastTwoDigitsOfYear = String(currentDate.getFullYear()).slice(-2);
  var currentDay = ('0' + currentDate.getDate()).slice(-2);
  var newSheetName = currentMonth + " " + lastTwoDigitsOfYear + " Invoices";
  var copiedSheet = ss.getSheetByName(newSheetName);
  if (!copiedSheet) {
    throw new Error('Copied sheet not found');
  }


  var dataRange = copiedSheet.getDataRange();
  var dataValues = dataRange.getValues();
  var headers = dataValues[0]; // Get the headers from the first row
  var baseInvoiceName = currentMonth + ' ' + currentDay + ' Invoice from Outright Systems';


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


    Logger.log("Processing row: " + (i + 1));
    Logger.log("Email: " + email);
    Logger.log("PDF Link: " + pdfLink);


    if (email && pdfLink) {
      try {
        var fileId = pdfLink.match(/[-\w]{25,}/);
        if (fileId) {
          var pdfFile = DriveApp.getFileById(fileId[0]).getAs(MimeType.PDF);
          MailApp.sendEmail({
            to: email,
            subject: baseInvoiceName,
            body: "Please find attached your invoice.",
            attachments: [pdfFile]
          });
          Logger.log("Email sent to: " + email);
          copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Sent");
          masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Sent");
        } else {
          Logger.log("Invalid PDF link: " + pdfLink);
          copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Invalid link");
          masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Invalid link");
        }
      } catch (e) {
        Logger.log("Failed to send email to: " + email + " with error: " + e.message);
        copiedSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Failed to send");
        masterSheet.getRange(i + 1, headers.indexOf("status") + 1).setValue("Failed to send");
      }
    } else {
      Logger.log("No email address or PDF link found for row: " + (i + 1));
      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() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getSheetByName("master-clients");
  if (!masterSheet) {
    throw new Error('Sheet "master-clients" not found');
  }
  var currentDate = new Date();
  var currentMonth = new Intl.DateTimeFormat('en-US', { month: 'long' }).format(currentDate);
  var lastTwoDigitsOfYear = String(currentDate.getFullYear()).slice(-2);
  var newSheetName = currentMonth + " " + lastTwoDigitsOfYear + " Invoices";
  var existingSheet = ss.getSheetByName(newSheetName);
  if (existingSheet) {
    ss.deleteSheet(existingSheet);
  }
  var copiedSheet = masterSheet.copyTo(ss).setName(newSheetName);
  return copiedSheet;
}