Generating invoice from Google Sheets

Question

I have a Google spreadsheet in which I record my freelance jobs. I have it set up that each line calculates whether it is paid for. (Payments are pulled from a separate sheet.)

What I would like to do is to generate an invoice, where I would select the customer and I get a listing of all unpaid entries for that customer.

Using a arrayed filter function does the job, but I can't use that as an invoice because I need the total line underneath, and would prefer the table format matching the count of entries.

Is it possible to insert such information into a Google Doc as a table, or within Sheets, to push the lines following an array down?

I thought this would be a simple enough concept but I can't find anything that does the full deal.

Answer 1

You could try this script. I'm not sure if the final results is what you are looking for. In case it is not, it can be easily modified:

function onEdit(e) {

  //If you change the Customer in the Invoice sheet, it runs the code
  if (e.range.getA1Notation() == 'A1' && e.source.getSheetName() == 'Invoice'){
    var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
    var invoice = sprsheet.getSheetByName("Invoice");
    var times = sprsheet.getSheetByName("Times");
    
    var in_customer = invoice.getRange("A1").getValue(); //Name you selected in the dropdown menu
    var data = times.getRange("A1:H").getValues(); //All the data from the Time sheet
    
    var total = 0;
    
    //Loops through all the data looking for unpaid subtotals from that customer
    for (var i = 0; i < data.length; i++){ 
      
      /*> "i" represents the row, the second number is the column
        > The rows start at 0 since it is the first array position.
        */
      if (in_customer == data[i][2]) {
        
        if (data[i][7] == 'N'){
          
          total += Number(data[i][5]); //Accumulates each subtotal into total
          
          invoice.appendRow([data[i][0], data[i][1], data[i][3], data[i][5]]);
        }
      }
            
    }
    invoice.appendRow(["Total: ","","", total]);
    
  }
}

This results in (I changed some values to test it):

As you see I added some headers.

References:

• Range Class
• onEdit Trigger