Skip to content

Instantly share code, notes, and snippets.

@gregorynicholas

gregorynicholas/email-spreadsheet-as-pdf-invoice.js

Last active April 14, 2023 22:18
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save gregorynicholas/9008572 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save gregorynicholas/9008572 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. gregorynicholas revised this gist Feb 14, 2014. 1 changed file with 63 additions and 20 deletions.
    83 changes: 63 additions & 20 deletions email-spreadsheet-as-pdf-invoice.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,25 +1,68 @@
    function sendInvoice(inv, sepData, subject, body) {
    var invid = "0AuZZaL8vZt8GdHdMMlhHeVhqUm1NUmlXQnd2NjJqSVE";
    /** Returns a PDF object based on the contents of the 'invoicing' sheet */
    function invoiceToPDF(invDetails)
    {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    ssID = spreadsheet.getId();
    var sheet = spreadsheet.getSheetByName(INVOICES_SHEETNAME);
    var gid = sheet.getSheetId();

    // &gid=x at the end of above url if you only want a particular sheet
    var url2 = "http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + ssID +
    "&gid=" + gid +
    "&fmcmd=12&size=7&fzr=true&portrait=true&fitw=true&locale=en&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=true";

    // AUTH TOKEN required to access the UrlFetchApp call below. You can receive it
    // from https://appscripts.appspot.com/getAuthToken
    var AUTH_TOKEN = "your_token_goes_here";
    var auth = "AuthSub token=\"" + AUTH_TOKEN + "\"";

    var advancedArgs = {bcc:"[email protected]"};
    var pdf = spreadsheetToPDF(invid, "JS Invoice "+inv+".pdf");
    advancedArgs["attachments"] = pdf;
    var res = UrlFetchApp.fetch(url2, {headers: {Authorization: auth}}).getBlob();
    return res;
    }



    /**
    * Creates an invoice in the Invoices drive folder
    */
    function createInvoiceAsGDriveDocsFile(pdfBlob)
    {
    var details = getInvoiceDetails();
    filename = "Invoice_xyz";

    body = "Dear "+sepData[0][3]+",\n\nPlease find attached an invoice for " + sepData[0][1]+".\n\n"+body;
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    // get or create the base folder
    var theFolder = getInvoicesBaseFolder();

    var htmlText = "<table><tr><td><img src='cid:seplogo' height=62 width=270><td><B><font size=+2>Jan and John Stringer</B><br>11+ Tutoring and Mock Exams</TR></TABLE>"+
    "<P>"+body.replace(/\n/g,"<BR>")+"</P>Regards,<BR><BR>John.<BR><BR>"+
    "<TABLE WIDTH=80% ALIGN=CENTER><TR><TD><B>Jan Stringer<BR><B>John Stringer</B><TD>(01722) 782599<BR>(01722) 782827<TD>http://janstringer.com<BR>http://salisburyelevenplus.co.uk</TR></TABLE>";
    var seplogoBlob = UrlFetchApp.fetch("http://www.salisburyelevenplus.co.uk/uploads/9/1/4/3/9143834/3120226_orig.jpg").getBlob().setName("seplogoBlob");
    var body = body + "Regards,\n\nJohn.";"\n\nJohn Stringer.\nhttp://salisburyelevenplus.co.uk\nhttp://janstringer.com";

    var recipient = sepData[0][5];
    // var recipient = "[email protected]";
    if (sepData[0][8] != "")
    { advancedArgs["cc"] = sepData[0][8];
    // Create the invoice file based on the filename
    try
    {
    var matchingFileList = theFolder.find(filename);

    // guard against duplicates
    if (matchingFileList == undefined || matchingFileList.length == 0)
    {
    pdfBlob.setName(filename);
    var f = theFolder.createFile(pdfBlob);
    // check file created ok
    if (f != undefined)
    {
    f.setDescription("TBD some blurb from the invoice perhaps");
    spreadsheet.toast("Created invoice " + f.getName() + " " + f.getSize() + " bytes" + " in " + theFolder.getName());
    }
    else
    {
    Browser.msgBox("ERROR: creating document: " + filename);
    }
    }
    else
    {
    Browser.msgBox("ERROR: New invoice not created. " + filename + " already exists. " + filename);
    }
    }
    catch (fileCreateError)
    {
    Browser.msgBox("ERROR: couldn't create invoice pdf " + filename);
    }
    advancedArgs["htmlBody"] = htmlText;
    advancedArgs["inlineImages"] = {seplogo: seplogoBlob};

    MailApp.sendEmail(recipient, subject, body, advancedArgs);
    }
  2. gregorynicholas created this gist Feb 14, 2014.
    25 changes: 25 additions & 0 deletions email-spreadsheet-as-pdf-invoice.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    function sendInvoice(inv, sepData, subject, body) {
    var invid = "0AuZZaL8vZt8GdHdMMlhHeVhqUm1NUmlXQnd2NjJqSVE";

    var advancedArgs = {bcc:"[email protected]"};
    var pdf = spreadsheetToPDF(invid, "JS Invoice "+inv+".pdf");
    advancedArgs["attachments"] = pdf;

    body = "Dear "+sepData[0][3]+",\n\nPlease find attached an invoice for " + sepData[0][1]+".\n\n"+body;

    var htmlText = "<table><tr><td><img src='cid:seplogo' height=62 width=270><td><B><font size=+2>Jan and John Stringer</B><br>11+ Tutoring and Mock Exams</TR></TABLE>"+
    "<P>"+body.replace(/\n/g,"<BR>")+"</P>Regards,<BR><BR>John.<BR><BR>"+
    "<TABLE WIDTH=80% ALIGN=CENTER><TR><TD><B>Jan Stringer<BR><B>John Stringer</B><TD>(01722) 782599<BR>(01722) 782827<TD>http://janstringer.com<BR>http://salisburyelevenplus.co.uk</TR></TABLE>";
    var seplogoBlob = UrlFetchApp.fetch("http://www.salisburyelevenplus.co.uk/uploads/9/1/4/3/9143834/3120226_orig.jpg").getBlob().setName("seplogoBlob");
    var body = body + "Regards,\n\nJohn.";"\n\nJohn Stringer.\nhttp://salisburyelevenplus.co.uk\nhttp://janstringer.com";

    var recipient = sepData[0][5];
    // var recipient = "[email protected]";
    if (sepData[0][8] != "")
    { advancedArgs["cc"] = sepData[0][8];
    }
    advancedArgs["htmlBody"] = htmlText;
    advancedArgs["inlineImages"] = {seplogo: seplogoBlob};

    MailApp.sendEmail(recipient, subject, body, advancedArgs);
    }