Invoice Examples: These examples show the steps of building an invoice in Excel

01_Add_a_worksheet.js

/*Make a worksheet with a name of "Invoice"*/

async function add_a_worksheet(excel) {
  //  This example will fail if there is already a 
  //  worksheet named "Invoice"
  const sheet = excel.workbook.worksheets.add("Invoice")
  sheet.activate()
  await excel.sync();
}

function setup(){
    show_html('<button id="button-1">Add Worksheet</button>')
    tag("button-1").onclick = jade.automate(add_a_worksheet)
}

02_Add_a_unique_sheet.js

/*Build a worksheet named "Invoice", if necessary, append an integer for uniqueness*/

async function build_worksheet_without_name_conflict_example(excel) {
  
  // build a worksheet named "Invoice", if necessary, append
  // an integer for uniqueness
  
  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++
    console.log(file_number, proposed_name)
  }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()
  
}


function setup(){
  show_html('<button id="button-2">Build Invoice Sheet</button>')
  tag("button-2").onclick = jade.automate(build_worksheet_without_name_conflict_example)
}

03_Add_Data_to_Cells.js

/*Add data to various cells*/

async function add_invoice_data(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  
  // 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"]
    ]
  await excel.sync();
}


function setup(){
  show_html('<button id="button-3">Add Data</button>')
  tag("button-3").onclick = jade.automate(add_invoice_data)
}

04_Set_Column_Widths.js

/*Set several columns to different widths*/

async function set_column_widths(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // set Column Widths    
  sheet.getRange("A1").format.columnWidth=40
  sheet.getRange("B1").format.columnWidth=75
  sheet.getRange("C1").format.columnWidth=105
  sheet.getRange("D1").format.columnWidth=40
  sheet.getRange("E1").format.columnWidth=40
  sheet.getRange("F1").format.columnWidth=40
  sheet.getRange("G1").format.columnWidth=75
  sheet.getRange("H1").format.columnWidth=105
  sheet.getRange("I1").format.columnWidth=70
  
  await excel.sync();
}

function setup(){
  show_html('<button id="button-4">Set Column Widths</button>')
  tag("button-4").onclick = jade.automate(set_column_widths)
}

05_Set_Row_Height.js

/*Set the height of row 1*/
async function set_row_height(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // set the height of row 1
  sheet.getRange("a1").getEntireRow().format.rowHeight=40

  await excel.sync();
}

function setup(){
  show_html('<button id="button-5">Set Row Height</button>')
  tag("button-5").onclick = jade.automate(set_row_height)
}

06_Set_Cell_Font_Size.js

/*Set the font size of two cells*/
async function set_cell_font_size_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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
  
  
  await excel.sync();
}

function setup(){
  show_html('<button id="button-6">Set font size</button>')
  tag("button-6").onclick = jade.automate(set_cell_font_size_example)
}

07_Set_Cell_Font_Color.js

/*Set the font color of two cells*/
async function set_cell_fill_color_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // set cell font color
  sheet.getRange("A1").format.font.color="mediumBlue"
  sheet.getRange("H1").format.font.color="maroon"
  
  await excel.sync();
}

function setup(){
  show_html('<button id="button-7">Set cell font color</button>')
  tag("button-7").onclick = jade.automate(set_cell_fill_color_example)
}

08_Set_Cell_Indentation.js

/*Set the indentation level on a set of cells*/
async function set_cell_indentation_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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

  
  await excel.sync();
}

function setup(){
  show_html('<button id="button-8">Set cell indentation</button>')
  tag("button-8").onclick = jade.automate(set_cell_indentation_example)
}

09_Merge_Cells.js

/*Merge cells and merge cells by rows*/
async function merge_cells_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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)

  await excel.sync();
}

function setup(){
  show_html('<button id="button-9">Merge Cells</button>')
  tag("button-9").onclick = jade.automate(merge_cells_example)
}

10_Set_Cell_Fill_Color.js

/*Set the background color of two ranges using named colors and hex notation*/
async function set_cell_fill_color(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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"
  
  await excel.sync();
}

function setup(){
  show_html('<button id="button-10">Set Cell Background Color</button>')
  tag("button-10").onclick = jade.automate(set_cell_fill_color)
}

11_Set_Properties_With_Function.js

/*Set several properties of a cell with a function*/
async function set_cell_properties_with_function_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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")

  await excel.sync();
}

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 id="button-11">Set cell properties</button>')
  tag("button-11").onclick = jade.automate(set_cell_properties_with_function_example)
}

12_Cell_Horizontal_Alignment.js

/*Set the horizontal alignment of ranges to center and to right*/
async function cell_alignment_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
 // 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"

  await excel.sync();
}

function setup(){
  show_html('<button id="button-12">Set cell horizontal alighment</button>')
  tag("button-12").onclick = jade.automate(cell_alignment_example)
}

13_Cell_Font_Bold.js

/*Set a cell's font to bold*/
async function set_cell_font_bold_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet()
  sheet.getRange("F34:H34").format.font.bold = true
  
  // set cells bold
  sheet.getRange("A1:H1").format.font.bold = true

  await excel.sync();
}

function setup(){
  show_html('<button id="button-13">Set cell font to bold</button>')
  tag("button-13").onclick = jade.automate(set_cell_font_bold_example)
}

14_Set_Cell_Borders.js

/*Set inside vertical, inside horizontal, and bottom borders*/
async function set_cell_borders_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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"
  

  await excel.sync();
}

function setup(){
    show_html('<button onclick="Excel.run(set_cell_borders_example)">Set cell borders</button>')
}
function setup(){
  show_html('<button id="button-14">Set cell borders</button>')
  tag("button-14").onclick = jade.automate(set_cell_borders_example)
}

15_Set_Cell_Formulas.js

/*Enter formulas in individual cells and ranges*/
async function set_cell_formula_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // 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"
  

  await excel.sync();
}

function setup(){
  show_html('<button id="button-15">Set cell formulas</button>')
  tag("button-15").onclick = jade.automate(set_cell_formula_example)
}

16_Set_Cell_Number_Format.js

/*Set the number format of a cell and a range of cells*/
async function set_cell_number_format_example(excel) {
  // get a reference to the active worksheet
  const sheet = excel.workbook.worksheets.getActiveWorksheet();
  
  // set cell number formats
  sheet.getRange("H16:H33").numberFormat="0.00"
  sheet.getRange("H34").numberFormat="$ 0.00"
  sheet.getRange("H32").numberFormat="0.0#%"

  await excel.sync();
}

function setup(){
  show_html('<button id="button-16">Set cell number format</button>')
  tag("button-16").onclick = jade.automate(set_cell_number_format_example)
}

17_Build_Full_Empty_Invoice.js

/*Build the full invoice without and customer or product data*/
async function build_empty_invoice_example(excel){
  /*ace.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++
    console.log(file_number, proposed_name)
  }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("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=105
  sheet.getRange("D1").format.columnWidth=40
  sheet.getRange("E1").format.columnWidth=40
  sheet.getRange("F1").format.columnWidth=40
  sheet.getRange("G1").format.columnWidth=75
  sheet.getRange("H1").format.columnWidth=105
  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


  // 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")
  
  // merge cells
  sheet.getRange("A7:C7").merge()
  sheet.getRange("A15:E15").merge()
  sheet.getRange("F31:G34").merge(true)
  sheet.getRange("F4:G8").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("H16:H34").numberFormat="0.00"
  sheet.getRange("H32").numberFormat="0.0#%"


  await excel.sync();
}

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 id="button-17">Build full empty invoice</button>')
  tag("button-17").onclick = jade.automate(build_empty_invoice_example)
}

18_Full_Application.js

/*loads code from a Gist and shows a form*/
Jade.load_gist("8867ffecbacc68dc88246a23166745e8")
function setup(){
    show_html('<button onclick="jade_modules.invoice_complete_example.show_form()">Show Invoice Form</button>')
}