Last active
February 21, 2022 15:03
-
-
Save theGove/2e4da32b8ea2ce47301b7a75dd594409 to your computer and use it in GitHub Desktop.
A complete example to build an invoice and fetch data from an API endpoint
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function launch_show_form(){ | |
/*Jade.listing:{"name":"Show invoice form","description":"Displays a form to manage the invoice example."}*/ | |
show_form() | |
} | |
function fetch_json(params){ | |
if(!params.on_fail){params.on_fail=fetch_failed} | |
fetch(params.url) | |
.then((response) => response.json()) | |
.then((data) => params.on_success(params,data)) | |
.catch((error) => params.on_fail(params, error)) | |
} | |
function html_get_4x(){ | |
return ` | |
<div id="div-4x" style="border: 1px solid; box-shadow: 5px 5px darkgrey;border-radius:.5rem;max-width:400px; background-color:white;display:none;margin:2rem;padding:1rem"> | |
<div style="margin-bottom:1rem;font-size:1.5rem;font-weight:bold">Foreign Exchange</div> | |
Convert this invoice to currency of the customer's country.<br><br>This requires a free API key from <a href="https://www.alphavantage.co/" target="_blank">Alpha Vantage</a>.<br><br> | |
API Key: <input id="api-key-4x" size="10" /> <br><br><button onClick="configure_4x()">Convert</button> | |
</div>` | |
} | |
function html_toggle_4x(){ | |
return ` | |
<div id="div-4x-toggle" style="border: 1px solid; box-shadow: 5px 5px darkgrey;border-radius:.5rem;max-width:400px; background-color:white;display:none;margin:2rem;padding:1rem"> | |
<div style="margin-bottom:1rem;font-size:1.5rem;font-weight:bold">Foreign Exchange</div> | |
Convert this invoice to currency of the customer's country.<br><br>This invoice is already configured to show it's values in USD or the customer's country currency. <br><br><button onClick="configure_4x()">Toggle Currency</button></div>` | |
} | |
function html_invoice(){ | |
return ` | |
<div style="border: 1px solid; box-shadow: 5px 5px darkgrey;border-radius:.5rem;max-width:400px; background-color:white;display:inline-block;margin:2rem;padding:1rem"> | |
<div style="margin-bottom:1rem;font-size:1.5rem;font-weight:bold">Client Invoice</div> | |
Covefront Laboratories conducts DNA analyses for clients all over the world. Generate a sample invoice for a client.<br><br><button onClick="fill_in_invoice()">Build invoice</button> | |
</div>` | |
} | |
function show_form(){ | |
let html=[] | |
html.push(html_invoice()) | |
html.push(html_toggle_4x()) | |
html.push(html_get_4x()) | |
Excel.run(async function(excel){ | |
const sheet = excel.workbook.worksheets.getActiveWorksheet() | |
const inv_rng = sheet.getRange("H1") | |
const cur_rng = sheet.getRange("F11:I13") | |
inv_rng.load("values") | |
cur_rng.load("values") | |
await excel.sync() | |
Jade.open_canvas("forex", html.join("")) | |
if(inv_rng.values[0][0]==="INVOICE"){ | |
if(cur_rng.values[1][0] !== "USD"){ | |
if(cur_rng.values[1][3]){ | |
// already have exchange rate | |
tag("div-4x-toggle").style.display="inline-block" | |
}else{ | |
tag("div-4x").style.display="inline-block" | |
if(window["api_key_4x"]){ | |
tag("api-key-4x").value=window["api_key_4x"] | |
} | |
} | |
} | |
} | |
}) | |
} | |
function configure_4x(){ | |
if(tag("api-key-4x") && tag("api-key-4x").value){ | |
window["api_key_4x"]=tag("api-key-4x").value | |
} | |
Excel.run(async function(excel){ | |
const sheet = excel.workbook.worksheets.getActiveWorksheet() | |
const cur_rng = sheet.getRange("F11:I13") | |
cur_rng.load("values") | |
await excel.sync() | |
if(cur_rng.values[1][3]){ | |
//The worksheet has already been configured for the exchange rate | |
if(cur_rng.values[0][0]==="USD"){ | |
sheet.getRange("F11").values=cur_rng.values[1][0] | |
sheet.getRange("H34").numberFormat=cur_rng.values[1][1].split(".").join("")+" #,###" | |
console.log(cur_rng.values[1][1].split(".").join("")+" #,###") | |
}else{ | |
sheet.getRange("F11").values="USD" | |
sheet.getRange("H34").numberFormat="$ #,###" | |
} | |
excel.sync() | |
return | |
} | |
// the sheet has not yet been configured for forex | |
if(cur_rng.values[1][0]==="USD"){ | |
// The customer is in the US. No need to configure | |
return | |
} | |
if(! window["api_key_4x"]){ | |
alert("No API Key Provided") | |
return | |
} | |
fetch_json({ | |
url:"https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=USD&to_currency="+cur_rng.values[1][0]+"&apikey=" + window["api_key_4x"], | |
on_success:exchange_rate, | |
sheet:sheet, | |
currency:cur_rng.values[1][0], | |
symbol:cur_rng.values[1][1] | |
}) | |
}) | |
} | |
function exchange_rate(params, data){ | |
console.log("exchange params",params) | |
console.log("exchange data",data) | |
params.sheet.getRange("I16").copyFrom("G16:G30"); | |
params.sheet.getRange("F13").copyFrom("F11:H11"); | |
params.sheet.getRange("I12").values = data["Realtime Currency Exchange Rate"]["5. Exchange Rate"] | |
params.sheet.getRange("I13").values = 1 | |
params.sheet.getRange("F11").values = params.currency | |
params.sheet.getRange("G11").formulas="=VLOOKUP(F11,F12:G13,2,FALSE)" | |
params.sheet.getRange("H11").formulas="=VLOOKUP(F11,F12:H13,3,FALSE)" | |
params.sheet.getRange("G16:G30").formulas="=VLOOKUP($F$11,$F$12:$I$13,4,FALSE)*I16" | |
params.sheet.getRange("F12:H13").format.font.color="white" | |
params.sheet.getRange('I:I').columnHidden = true | |
params.sheet.getRange("H34").numberFormat=params.symbol.split(".").join("") +" #,###" | |
console.log(params.symbol) | |
console.log(params.symbol +" #,###") | |
params.sheet.context.sync() | |
tag("div-4x").style.display="none" | |
tag("div-4x-toggle").style.display="inline-block" | |
} | |
function fetch_failed(params, error){ | |
console.log("failed to get url", params.url) | |
console.log("error", error) | |
} | |
function fill_in_customer(params, data){ | |
if(tag("div-4x") && data.currency === "USD"){ | |
tag("div-4x").style.display="none" | |
}else{ | |
tag("div-4x-toggle").style.display="none" | |
if(data.state){ | |
tag("div-4x").style.display="none" | |
}else{ | |
tag("div-4x").style.display="block" | |
if(window["api_key_4x"]){ | |
tag("api-key-4x").value=window["api_key_4x"] | |
} | |
} | |
} | |
// include state if present | |
const locale=[data.city] | |
if(data.state){locale.push(data.state)} | |
locale.push(data.country) | |
let d = new Date() | |
// place customer data | |
params.sheet.getRange("F5").values="0"+rand_between(100000,400000) + data.country_code | |
params.sheet.getRange("H5").values= d.getFullYear()+"/"+d.getMonth()+"/"+d.getDate() | |
params.sheet.getRange("F8").values=data.customer_number | |
params.sheet.getRange("H8").values=data.terms | |
params.sheet.getRange("A8").values="Attn: " + data.contact | |
params.sheet.getRange("A9").values=data.hospital | |
params.sheet.getRange("A10").values=locale.join(", ") | |
// place currency data | |
params.sheet.getRange("G11").values= "$" | |
params.sheet.getRange("F11").values= "USD" | |
params.sheet.getRange("H11").values= "Dollar" | |
// hide customer's preferred currency information | |
params.sheet.getRange("G12").values= data.symbol | |
params.sheet.getRange("F12").values= data.currency | |
params.sheet.getRange("H12").values= data.currency_name | |
params.sheet.getRange("F12:H13").format.font.color="white" | |
// update total to show correct symbol | |
//params.sheet.getRange("H34").numberFormat= data.symbol + " 0.00" | |
params.sheet.context.sync() | |
} | |
function fill_in_product(params, data){ | |
console.log(params) | |
params.sheet.getRangeByIndexes(params.row,0,1,1).values = "["+ data.test_code +"] " + data.test_name | |
params.sheet.getRangeByIndexes(params.row,6,1,1).values = data.price | |
const qty_index=[1,1,1,1,1,1,1,1,1,1,1,1,2,2,3,4] | |
params.sheet.getRangeByIndexes(params.row,5,1,1).values = qty_index[rand_between(0,qty_index.length-1)] | |
params.sheet.context.sync() | |
} | |
async function fill_in_invoice(sheet_name){ | |
// get a sheet | |
Excel.run(async function(excel){ | |
if(sheet_name){ | |
// a worksheet name was provide, use it to get the sheet | |
sheet = excel.workbook.worksheets.getItem(sheet_name) | |
}else{ | |
// a worksheet name was not provided, get the active sheet | |
sheet = excel.workbook.worksheets.getActiveWorksheet() | |
} | |
await excel.sync() | |
// now we have a sheet; check to see if it is a blank invoice. | |
const rng_inv = sheet.getRange("H1") | |
const rng_cust = sheet.getRange("A8") | |
rng_inv.load("values") | |
rng_cust.load("values") | |
await excel.sync() | |
console.log("rng_inv.values",rng_inv.values) | |
console.log("rng_cust.values",rng_cust.values) | |
console.log("array",rng_cust.values[0][0]) | |
if(rng_inv.values[0][0]==="INVOICE" && rng_cust.values[0][0]===""){ | |
// it appears to be a blank invoice. build | |
console.log("ready to complete the invoice") | |
fetch_json({ | |
url:"https://thegove.github.io/data/hospital/"+rand_between(1,200)+".json", | |
on_success:fill_in_customer, | |
sheet:sheet | |
}) | |
const prod_count=rand_between(1,9) | |
const products=[] | |
for(let i=1;i<=prod_count;i++){ | |
const prod=rand_between(1,127) | |
if(!products.includes(prod)){ | |
products.push(prod) | |
} | |
} | |
console.log(products) | |
for(let i=0;i<products.length;i++){ | |
fetch_json({ | |
url: "https://thegove.github.io/data/dna_test/"+products[i]+".json", | |
on_success: fill_in_product, | |
sheet: sheet, | |
row: i+15 | |
}) | |
} | |
}else{ | |
// this is not a blank invoice, build an invoice and try again | |
console.log("building") | |
sheet_name = await Excel.run(build_empty_invoice_example) | |
console.log((sheet_name)) | |
fill_in_invoice(sheet_name) | |
} | |
}) | |
} | |
async function build_empty_invoice_example(excel){ | |
/*Jade.listing:{"name":"Build Invoice","description":"Makes a blank invoice."}*/ | |
const worksheets=excel.workbook.worksheets | |
worksheets.load("items") | |
await excel.sync(); | |
for(const ws of worksheets.items){ws.load("name")} | |
await excel.sync(); | |
let file_number=1 | |
let proposed_name="Invoice" | |
do{ | |
let name_found = false | |
for(const ws of worksheets.items){ | |
if(ws.name.toLowerCase()===proposed_name.toLowerCase()){ | |
name_found = true | |
break | |
} | |
} | |
if(!name_found){break} | |
proposed_name = "Invoice " + file_number++ | |
}while(file_number < 200) | |
if(file_number >= 200){ | |
return // we have too many invoices, let's not go crazy | |
} | |
const sheet = excel.workbook.worksheets.add(proposed_name) | |
sheet.activate() | |
await excel.sync() | |
// add data | |
// company information | |
sheet.getRange("A1:A4").values=[ | |
["Covefront Laboratories"], | |
["698 Candlewood Lane"], | |
["Cabot Cove, Maine 04456"], | |
["Phone: (207) 555-1212"] | |
] | |
// invoice labels | |
sheet.getRange("H1").values="INVOICE" | |
sheet.getRange("A7").values="Bill To" | |
sheet.getRange("A15").values="DESCRIPTION" | |
sheet.getRange("F4").values="INVOICE #" | |
sheet.getRange("H4").values="DATE" | |
sheet.getRange("F7").values="CUSTOMER ID" | |
sheet.getRange("H7").values="TERMS" | |
sheet.getRange("F10").values="Symbol" | |
sheet.getRange("H10").values="Currency" | |
sheet.getRange("A15").values="DESCRIPTION" | |
sheet.getRange("A31").values="Thank you for your business!" | |
sheet.getRange("A37").values="If you have any questions about this invoice, please contact" | |
sheet.getRange("A38").values="Martin Eddington (207) 555-1212 meddington@covefrontanalytic.com" | |
sheet.getRange("F31:F34").values=[ | |
["SUBTOTAL"], | |
["TAX RATE"], | |
["TAX"], | |
["TOTAL"] | |
] | |
sheet.getRange("F15:H15").values=[ | |
["QTY","PRICE","AMOUNT"] | |
] | |
// set column widths | |
sheet.getRange("A1").format.columnWidth=40 | |
sheet.getRange("B1").format.columnWidth=75 | |
sheet.getRange("C1").format.columnWidth=115 | |
sheet.getRange("D1").format.columnWidth=40 | |
sheet.getRange("E1").format.columnWidth=80 | |
sheet.getRange("F1").format.columnWidth=35 | |
sheet.getRange("G1").format.columnWidth=45 | |
sheet.getRange("H1").format.columnWidth=90 | |
sheet.getRange("I1").format.columnWidth=70 | |
// set the height of row 1 | |
sheet.getRange("a1").getEntireRow().format.rowHeight=40 | |
// set font size | |
sheet.getRange("A1").format.font.size=20 | |
sheet.getRange("H1").format.font.size=35 | |
sheet.getRange("F34:H34").format.font.size=16 | |
// set cell fill color | |
sheet.getRange("A1:J50").format.fill.color="white" | |
sheet.getRange("F31:F34").format.fill.color="lightSteelBlue" | |
sheet.getRange("H31:H34").format.fill.color="#DDDDDD" | |
// set cell font color | |
sheet.getRange("A1").format.font.color="mediumBlue" | |
sheet.getRange("H1").format.font.color="maroon" | |
// set cell indentation | |
sheet.getRange("A2:A4").format.indentLevel=1 | |
sheet.getRange("A8:A13").format.indentLevel=1 | |
sheet.getRange("A7").format.indentLevel=2 | |
sheet.getRange("A15").format.indentLevel=2 | |
sheet.getRange("F31:F34").format.indentLevel=2 | |
// set several properties of a cell with a function | |
make_header(sheet, "A7") | |
make_header(sheet, "A15") | |
make_header(sheet, "F15:H15") | |
make_header(sheet, "F4:H4") | |
make_header(sheet, "F7:H7") | |
make_header(sheet, "F10:H10") | |
// merge cells | |
sheet.getRange("A7:C7").merge() | |
sheet.getRange("A15:E15").merge() | |
sheet.getRange("F31:G34").merge(true) | |
sheet.getRange("F4:G10").merge(true) | |
sheet.getRange("A31:E31").merge() | |
sheet.getRange("A37:H38").merge(true) | |
// center cells | |
sheet.getRange("F4:H15").format.horizontalAlignment = "Center" | |
sheet.getRange("F16:F30").format.horizontalAlignment = "Center" | |
sheet.getRange("A31:A38").format.horizontalAlignment = "Center" | |
// right align | |
sheet.getRange("H1").format.horizontalAlignment = "Right" | |
// set cells bold | |
sheet.getRange("A1:H1").format.font.bold = true | |
sheet.getRange("F34:H34").format.font.bold = true | |
// borders | |
let borders = sheet.getRange("A30:H30").format.borders | |
borders.getItem("EdgeBottom").style="Continuous" | |
borders = sheet.getRange("A16:H30").format.borders | |
borders.getItem("InsideHorizontal").color="lightGrey" | |
borders.getItem("InsideHorizontal").style="dot" | |
borders.getItem("InsideHorizontal").weight="hairline" | |
borders = sheet.getRange("E16:H30").format.borders | |
borders.getItem("InsideVertical").color="lightGrey" | |
borders.getItem("InsideVertical").style="dot" | |
borders.getItem("InsideVertical").weight="hairline" | |
// add formulas | |
sheet.getRange("H16:H30").formulas = '=if(G16="","",product(F16:G16))' | |
sheet.getRange("H31").formulas = "=sum(H16:H30)" | |
sheet.getRange("H32").values = 0.06 | |
sheet.getRange("H33").formulas = "=product(H31:H32)" | |
sheet.getRange("H34").formulas = "=H31+H33" | |
// set cell number formats | |
sheet.getRange("G16:H33").numberFormat="#,###" | |
sheet.getRange("H34").numberFormat="$ #,###" | |
sheet.getRange("H32").numberFormat="0.0#%" | |
await excel.sync(); | |
return proposed_name | |
} | |
function make_header(sheet, range){ | |
sheet.getRange(range).format.fill.color="royalBlue" | |
sheet.getRange(range).format.font.color="white" | |
sheet.getRange(range).format.font.bold=true | |
sheet.getRange(range).format.font.size=12 | |
} | |
function setup(){ | |
show_html('<button onclick="Excel.run(build_empty_invoice_example)">Build full empty invoice</button>') | |
} | |
function rand_between(min, max) { | |
return Math.floor(Math.random() * (max - min + 1)) + min; | |
} | |
function launch_fill_in_invoice(){ | |
/*Jade.listing:{"name":"Enter Invoice Data","description":"Fills the invoice with sample data."}*/ | |
fill_in_invoice() | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This code is designed to work with the JavaScript Automation Development Environment (JADE) add-in for Microsoft Excel Install the add-in from the add-in store then select "Import a Code Module" and enter the id of this Gist: 2e4da32b8ea2ce47301b7a75dd594409 Then click the "Run" button. More information at https://support.jsvba.com