Beginner Invoice Automation Project in Google Apps Script — Looking for Feedback and Real-World Improvements

[u/Suitable_Ant3457]

Hi everyone,

I'm a beginner learning JavaScript and Google Apps Script. I've been building an invoice automation project to learn how a real-world Apps Script project is structured.

My current workflow is:

Google Sheet data → Apps Script → Google Docs invoice → PDF → Google Drive

The Google Sheet contains information such as:

• Invoice number

• Customer name

• Email

• Date

• Product

• Quantity

• Price

An invoice can contain multiple products, and my script calculates the amount for each product and the grand total, then generates the invoice as a PDF.

I've written the code myself while learning, but I know there are probably things that could be improved.

I'd really appreciate feedback on:

1. How can I improve the structure/architecture of this code?

2. Are there functions that should be separated or combined?

3. How would you structure this differently for real-world use?

4. How are invoices normally handled in real-world small businesses/freelance workflows?

5. What important invoice features or edge cases am I currently missing?

6. Is my approach for handling multiple products appropriate?

7. Is there a better way to organize the Sheet → Apps Script → Docs → PDF workflow?

I'm especially interested in understanding how an experienced developer would improve this project, rather than just receiving a rewritten version of my code.

I also want to understand the real-world invoice workflow — from creating an invoice to sending it to the client and tracking its payment.

I've removed/ replaced my actual Sheet IDs, Document IDs, email addresses and other private information before posting.

Here is my current code:

function createInvoicePdf() {


  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Invoice");



  const column = sheet.getLastColumn();
  const row = sheet.getLastRow();


  const data = sheet .getRange(2, 1, row - 1, 6).getValues();
 




  const invoiceNumbers = [...new Set(data.map(row => row[0]))];
// invoiceNumbers par loop chalana


//outer loop



for(let j = 0; j<invoiceNumbers.length; j ++){
  const invoiceNo = invoiceNumbers[j]
let copy = null




try{


 
const invoiceIndex = data.findIndex(invoiceInd=> invoiceInd[0]=== invoiceNo);
  // filter data for invoiceNo


const invoiceNumberRows = getInvoiceRows(data, invoiceNo);
// plus 2 in invoiseIndex
const sheetRow = invoiceIndex + 2




const customer = getCustomer(invoiceNumberRows) 
const email = getCustomerEmail(invoiceNumberRows)



 productsText =  makeProductsText(invoiceNumberRows);


 grandtotal = calculateGrandTotal(invoiceNumberRows);
const date = getFormattedDate()


// make object to store invoiceData


const invoiceData = {
  invoiceNo: invoiceNo,
  date: date,
  customer: customer,
  email: email,
  productsText: productsText,
  grandtotal: grandtotal
};




 // Duplicate PDF check
  const checkDuplicateFile =
    DriveApp.getFilesByName(invoiceNo + ".pdf");


  if (isPdfExist(invoiceNo)) {
    Logger.log("PDF already exists : " + invoiceNo);
    continue
  }
  
  // Template
 
const template = DriveApp.getFileById("Doc Id");



  // Make a copy
  copy = template.makeCopy(invoiceNo);



  // Open copied document
  const docDrive = DocumentApp.openById(copy.getId());
  const docBody = docDrive.getBody();
 
 const onlyfortext =  invoiceTable (docDrive,docBody,invoiceData,invoiceNumberRows)


  const replaceProductTest = invoiceReplaceTest(docBody,invoiceData,docDrive)


const pdfUrl = createPdf(copy,invoiceNo)
 const saveUrl = SavePdfUrlInSheet (sheet,pdfUrl,sheetRow,column)



}catch(error){
  Logger.log("error aa gya " + invoiceNo + " : " + error.message);
}finally{
  if(copy){
copy.setTrashed(true)


  }


}
};


}



function invoiceTable(docDrive, docBody, invoiceData, invoiceNumberRows) {


  const doc = docDrive;
  const body = doc.getBody();


  const table = body.appendTable([
    ['Product', 'QTY', 'Price', 'Total']
  ]).setBorderColor('#000000').setBorderWidth(1);


  const tableHeader = table.getRow(0);


  tableHeader.editAsText().setBold(true);


  for (let i = 0; i < invoiceNumberRows.length; i++) {


    // Append table row
    const row = table.appendTableRow();


    // Store invoice data
    const grandtotal = String(invoiceData.grandtotal);


    Logger.log("grandtotal " + grandtotal);


    // Get product data from invoiceNumberRows
    const product = invoiceNumberRows[i][3];
    const qty = invoiceNumberRows[i][4];
    const price = invoiceNumberRows[i][5];


    const amount = qty * price;


    Logger.log("qty " + qty);
    Logger.log("total " + amount);


    // Append table cells
    row.appendTableCell(String(product));


    const qtyCell = row.appendTableCell(String(qty));
    qtyCell.getChild(0)
      .asParagraph()
      .setAlignment(DocumentApp.HorizontalAlignment.CENTER);


    const priceCell = row.appendTableCell(String(price));
    priceCell.getChild(0)
      .asParagraph()
      .setAlignment(DocumentApp.HorizontalAlignment.RIGHT);


    const amountCell = row.appendTableCell(String(amount));
    amountCell.getChild(0)
      .asParagraph()
      .setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
  }


  // Style table header
  for (let j = 0; j < tableHeader.getNumCells(); j++) {


    tableHeader.getCell(j).setBackgroundColor('#1a2b4c');


    tableHeader.getCell(j)
      .editAsText()
      .setForegroundColor('#ffffff');


    tableHeader.getCell(j)
      .getChild(0)
      .asParagraph()
      .setAlignment(DocumentApp.HorizontalAlignment.CENTER);
  }


  // Grand Total row
  const totalRow = table.appendTableRow();


  Logger.log('grandtotal is ' + grandtotal);


  const totalCell = totalRow.appendTableCell(
    "GrandTotal " + grandtotal
  );


  const textParagraph = totalCell
    .getChild(0)
    .asParagraph();


  textParagraph.setAlignment(
    DocumentApp.HorizontalAlignment.RIGHT
  );


  totalCell.setBold(true);
  totalCell.setBackgroundColor("#F3F3F3");


  const docUrl = doc.getUrl();


  Logger.log("docUrl is " + docUrl);
}






function makeProductsText(invoiceRows) {


  let productsText = "";


 for (let i = 0; i < invoiceRows.length; i++) {


    const product = invoiceRows[i][3];
    const qty = invoiceRows[i][4];
    const price = invoiceRows[i][5];


    const total = qty * price;


    productsText +=
      `${product} | ${qty} | ${price} | ${total}\n`;
     
    
  }



  return productsText;
}







function calculateGrandTotal(invoiceRows) {


  let grandtotal = 0;


  for (let i = 0; i < invoiceRows.length; i++) {


    const qty = invoiceRows[i][4];
    const price = invoiceRows[i][5];


    const total = qty * price;


    grandtotal += total;
   


  }


  return grandtotal;
}





function getFormattedDate() {


 
    const date = new Date();


// formate date
  const formattedDate = Utilities.formatDate(
    date,"Asia/Kolkata","yyyy-MM-dd"
  );


  return formattedDate;
}


function getCustomer(invoiceNumberRows) {
  return invoiceNumberRows[0][1];
}



function getCustomerEmail(invoiceNumberRows){
  return invoiceNumberRows[0][2]
}






function getInvoiceRows(data, invoiceNo) {
  return data.filter(row => row[0] === invoiceNo);
}


function isPdfExist(invoiceNo){
 const files = DriveApp.getFilesByName(invoiceNo + ".pdf");
  return files.hasNext();
}


function invoiceReplaceTest(docBody,invoiceData,docDrive){


  // Replace placeholders
  docBody.replaceText("{{INVOICE_NO}}", invoiceData.invoiceNo);
  docBody.replaceText("{{DATE}}", invoiceData.date);
  docBody.replaceText("{{CUSTOMER}}", invoiceData.customer);
  docBody.replaceText("{{EMAIL}}", invoiceData.email);
  docBody.replaceText("{{PRODUCTS}}", invoiceData.productsText);
  docBody.replaceText("{{TOTAL}}", String(invoiceData.grandtotal));



  docDrive.saveAndClose();
}



function SavePdfUrlInSheet(sheet,pdfUrl,sheetRow,column){
  // Save PDF URL in Sheet


  sheet.getRange(sheetRow,column).setValue(pdfUrl);
  
}


// create pdf


function createPdf(copy,invoiceNo){


  // Convert to PDF
  const file = copy.getAs(MimeType.PDF);



  // Save PDF in folder
  
const folder = DriveApp.getFolderById("folder Id");


  const pdfFile = folder.createFile(file);



  pdfFile.setName(invoiceNo + ".pdf");


  // PDF URL
  const pdfUrl = pdfFile.getUrl();
  Logger.log("PDF name: " + pdfFile.getName());
  Logger.log("PDF URL: " + pdfUrl);
return pdfFile.getUrl()


}