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;
}
