PDF Invoice Generator

var folderId = 'YOUR "Public" FOLDER ID HERE';

var newFileId = '';

var invNo = '';

function doPost(e) {

 var ss = SpreadsheetApp.getActive();

 var shd = ss.getSheetByName("Data");

 var data = JSON.parse(e.postData.contents);

 var newData = data.slice(0,9);

 var content = data.slice(9, data.length);

 for (var i=0;i < content.length; i++) {

    newData.push(JSON.stringify(content[i]));

 }

 shd.appendRow(newData);

 SpreadsheetApp.flush();

 return ContentService.createTextOutput(setInvoice());

}

function setInvoice() {

//sets variables and gets ranges/values for the last entry in the data sheet

var ss = SpreadsheetApp.getActive();

var shd = ss.getSheetByName("Data");

var shi = ss.getSheetByName("Invoice");

var lr = shd.getLastRow();

var rng = shd.getRange("A" + lr + ":J" + lr).getDisplayValues();

var itm = shd.getRange("K" + lr + ":AD" + lr).getDisplayValues();

invNo = shd.getRange("G" + lr).getDisplayValue();

//sets cell values on the Invoice from the last entry in the Data sheet

shi.getRange("B9").setValue(rng[0][0]);

shi.getRange("B10").setValue(rng[0][1]);

shi.getRange("B11").setValue(rng[0][2]);

shi.getRange("B12").setValue(rng[0][3] + " " + rng[0][4] + " " + rng[0][5]);

shi.getRange("G9").setValue(rng[0][6]);

shi.getRange("F4").setValue(rng[0][7]);

shi.getRange("G12").setValue(rng[0][8]);

shi.getRange("B32").setValue(rng[0][9]);

//handles the json array data for each item, and applies it to the invoice

for (var i = 0; i < itm[0].length; i++) {

 if (itm[0][i] != "") {

   var item = JSON.parse(itm[0][i]);

   shi.getRange("B" + (16 + i)).setValue(item[0]);

   shi.getRange("E" + (16 + i)).setValue(item[1]);

   shi.getRange("F" + (16 + i)).setValue(item[2]);

 }

}

SpreadsheetApp.flush();

//create pdf and return the invoice number and fileId for the naming of the pdf to be downloaded

return InvoiceSheetToPDF(invNo);

}

 //create pdf of invoice sheet.

 //ref: https://stackoverflow.com/questions/39690232/using-google-apps-script-to-save-a-single-sheet-from-a-spreadsheet-as-pdf-in-a-s

function InvoiceSheetToPDF(invNo) {

var sheetName = "Invoice";

var folderID = folderId; // Folder id to save in a folder.

var pdfName = "Invoice-" + invNo + ".pdf";

var sourceSpreadsheet = SpreadsheetApp.getActive();

var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);

var folder = DriveApp.getFolderById(folderID);

//copy whole spreadsheet

var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

//delete redundant sheets

var sheets = destSpreadsheet.getSheets();

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

if (sheets[i].getSheetName() != sheetName){

destSpreadsheet.deleteSheet(sheets[i]);

}

}

var destSheet = destSpreadsheet.getSheets()[0];

//replace cell values with text (to avoid broken references)

var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());

var sourcevalues = sourceRange.getValues();

var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());

destRange.setValues(sourcevalues);

//save to pdf

var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);

newFileId = folder.createFile(theBlob).getId();

//Delete the temporary sheet

DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

clearRanges();

return invNo + ',' + newFileId;

}

function clearRanges() {

var ss = SpreadsheetApp.getActive();

var shd = ss.getSheetByName("Invoice");

shd.getRange("B9:B12").clearContent();

shd.getRange("F4").clearContent();

shd.getRange("G9").clearContent();

shd.getRange("G12").clearContent();

shd.getRange("B16:F30").clearContent();

shd.getRange("B32").clearContent();

SpreadsheetApp.flush();

}