Lesson 28: Sharing and Reusing Code

28.1 Code That Outlives One Workbook

The harvest app from Lesson 27. Building an Application in Excel is a success, and now the sustainability office wants it in their own workbook. You could copy and paste the code. But you'll keep improving the app, and every improvement would mean pasting it again, into every copy, and hoping nobody's copy falls behind.

The usual answer in programming is to keep one copy of shared code somewhere everyone can reach, and have each place that uses it load it from there. This lesson covers that within a workbook, between workbooks, and from the wider internet, and the question that comes with it: whose code are you running?

28.2 Calling Code in Another Module

Within one workbook, JADE already shares code between modules. Every function in a code module is registered in an object called jade_modules, under the module's name in lowercase, with spaces turned into underscores. So a module named Garden Tools is jade_modules.garden_tools, and its functions can be called from any other module:

// in a code module named Garden Tools
function kilogramsByBed(harvestRows) {
  const totals = {}
  // start at 1 to skip the header row; B is Bed ID, D is Kilograms
  for (let i = 1; i < harvestRows.length; i++) {
    const bedId = harvestRows[i][1]
    if (totals[bedId] === undefined) {
      totals[bedId] = 0
    }
    totals[bedId] += harvestRows[i][3]
  }
  return totals
}

// in any other code module
async function printBedTotals(excel) {
  const sheet = excel.workbook.worksheets.getItem("Harvests")
  const usedRange = sheet.getUsedRange()
  usedRange.load("values")
  await excel.sync()
  const totals = jade_modules.garden_tools.kilogramsByBed(usedRange.values)
  Jade.print(JSON.stringify(totals), "Kilograms by bed")
  Jade.open_output()
}

kilogramsByBed doesn't touch the workbook at all: it takes rows and returns an object, like the calculating functions in Lesson 15. Generating Documents. Functions like that are the easiest to share, because they work anywhere.

28.3 Sharing Between Workbooks

Here's the question for an assistant, in a new chat with your learner profile from Lesson 27. Building an Application in Excel and your JADE platform profile:

This is the honesty rule at its best. The assistant didn't know how JADE shares code, and said so. It even reasoned from a clue in your platform profile: keys starting with gist: are reserved, so JADE probably does something with GitHub gists. Then it told you where to look, and gave a fallback, copy and paste, that it knew would work.

JADE's documentation has a section called "Loading code from elsewhere," and it confirms the guess. Here are the lines it adds to the platform profile:

- Functions in one code module can be called from another as jade_modules.moduleName.functionName(). The module name is the code module's name in lowercase, with spaces replaced by underscores: a module named Harvest App is jade_modules.harvest_app.
- await Jade.load_gist(gistId, moduleName) loads a GitHub gist: it runs the gist's .js files and registers their functions as jade_modules[moduleName], then runs the module's auto_exec if it has one. JADE caches the gist in the workbook and downloads it again only when it has changed on GitHub. GitHub allows 60 anonymous requests an hour.
- Jade.import_code_module(gistIdOrUrl) copies a gist's .js files into the workbook as editor modules (the Import a Code Module menu item does this). A module with the same name is overwritten.
- await Jade.load_js(url, null) downloads a JavaScript library from a web address, runs it at global scope and waits for it. Jade.add_library(url) adds it without waiting, so it can't be used on the next line.

A gist is a small collection of files on GitHub, often a single file of code, with its own web address. You create one at gist.github.com, with a free GitHub account: give the file a name ending in .js, paste your code, and click Create secret gist or Create public gist. The gist's ID is the long string of letters and numbers at the end of its address.

With the new lines in the platform profile, the same question gets a different answer:

Read. The comparison is exactly right: import_code_module copies the code into their workbook once, and load_gist downloads it from GitHub, using the cached copy unless it has changed, so your updates reach them. A small loader module in their workbook calls load_gist when the workbook opens, and then opens the app. The notes are good too, especially that a secret gist is only unlisted, not private: anyone with the link can read it.

Now look at the name of the loader function: autoExec. The explanation even says "an auto_exec() function," and then the code says autoExec. JADE only runs a function spelled exactly auto_exec, so this loader would never run, and nothing would tell you why.

Where did autoExec come from? From your platform profile, which says "Write in the style of my textbook: camelCase names." The assistant applied a style rule to a name the platform requires. It's the same kind of collision as in Lesson 19. How Web Pages Work, where your rule against semicolons leaked into CSS. When two instructions meet, an assistant has to decide which wins, and it can choose wrong. The fix is to say which one wins, in the profile:

- Write in the style of my textbook: camelCase names, except for names JADE requires, such as auto_exec.

There's one more thing to know. load_gist runs the loaded module's own auto_exec, if it has one. The Harvest App module you wrote in the last lesson does have one, which opens the form. So the loader's second step, opening the app, would open it a second time. The simplest loader just loads the gist and lets the app's own auto_exec do the rest:

// in a small module named Harvest App Loader, in the university's workbook
async function auto_exec() {
  // loads the app from the gist; the app's own auto_exec then opens the form
  await Jade.load_gist("yourGistIdHere", "harvest_app")
}

28.4 Whose Code Is Running?

Sharing code this way is powerful, and it deserves a moment of care, because of what load_gist does: every time the university's workbook opens, it runs whatever is in your gist today, with full access to their workbook. That's what makes updates automatic. It also means:

• The gist's owner controls the code. If you change the gist, their workbook runs the change the next time it opens. If someone took over your GitHub account, they could put anything in it.
• Anyone can read it, public or secret. Never put passwords, keys or private data in a gist.

So match the method to the trust:

Situation	Use	Why
Your own code, in your own workbooks	load_gist	Updates are automatic, and you trust the author.
Code from someone else	import_code_module, then read it	You get a copy that stays the same, and you can read it before running it. It won't change unless you import it again.
Code you haven't read	Neither, yet	Don't run code you can't explain.

The university office is in a middle position: they're trusting you. A reasonable arrangement is to tell them what the code does, and to change the gist only when you've tested the change.

28.5 Loading a Library

Some code is so widely useful that other people have already written it, packaged as a library. Drawing charts is a good example. Here's a request for a chart of the harvest by bed, in a new chat with your learner profile and the updated platform profile:

Read. The first half is the counting-by-name pattern, followed by building two parallel arrays: bed IDs for the labels and totals for the bars. Then:

• await Jade.load_js(url, null) downloads Chart.js and waits until it's ready, so the next lines can use it. null means "run it at global scope," which is how Chart.js expects to be loaded: it creates a global named Chart.
• <canvas> is an HTML element for drawing, and getContext("2d") gets the tool Chart.js draws with.
• new Chart(context, { ... }) creates the chart from an object that describes it: its type, its labels and a datasets array with the numbers. The shape of that object comes from Chart.js's documentation, and the assistant knows Chart.js well; it's one of the most popular libraries on the web.

The assistant's reasoning about escaping is correct: the labels are drawn on the canvas, not inserted as HTML, so toHtmlEntities isn't needed. Three things need fixing:

• The library's address has no version. https://cdn.jsdelivr.net/npm/chart.js means "the latest version, whatever that is today." When Chart.js releases a new major version, your chart could change or break, without your code changing at all. An address with the version in it, like chart.js@4.5.1, always gets the same file. That's called pinning the version, and your platform profile now asks for it.
• The bars are in a strange order. Object.keys gives the beds in the order they first appear in the Harvests sheet, B2, B4, B1 and so on, not B1 to B8. For a chart, order matters.
• The panel name has a space, "Harvest Chart", the same slip as before.

Here's the chart with the three fixes. It uses the bed IDs in order, B1 to B8, so every bed gets a bar, even one with no harvests yet:

const CHART_JS_URL = "https://cdn.jsdelivr.net/npm/chart.js@4.5.1/dist/chart.umd.min.js"
const BED_IDS = ["B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8"]

async function showHarvestChart(excel) {
  /*Jade.listing:{"name":"Harvest chart","description":"A bar chart of kilograms harvested from each bed"}*/
  const sheet = excel.workbook.worksheets.getItem("Harvests")
  const usedRange = sheet.getUsedRange()
  usedRange.load("values")
  await excel.sync()
  const data = usedRange.values

  const totals = {}
  for (let i = 1; i < data.length; i++) {
    const bedId = data[i][1]
    if (totals[bedId] === undefined) {
      totals[bedId] = 0
    }
    totals[bedId] += data[i][3]
  }

  // one bar per bed, in order, rounded to one decimal place
  const kilograms = []
  for (let i = 0; i < BED_IDS.length; i++) {
    let total = totals[BED_IDS[i]]
    if (total === undefined) {
      total = 0
    }
    kilograms.push(Math.round(total * 10) / 10)
  }

  await Jade.load_js(CHART_JS_URL, null)
  Jade.open_canvas("harvestChart", '<h2>Kilograms harvested by bed</h2><canvas id="chartCanvas"></canvas>')
  const canvas = tag("chartCanvas")
  const chart = new Chart(canvas.getContext("2d"), {
    type: "bar",
    data: {
      labels: BED_IDS,
      datasets: [{ label: "Kilograms", data: kilograms }]
    }
  })
}

A library is code from elsewhere, like a gist, and the same questions apply. Chart.js is maintained by a large, long-running open-source project and served by a major content delivery network, and the pinned version always delivers the same file. That's about as trustworthy as outside code gets. Be much more careful with an obscure library from an address you don't recognize, even if an assistant suggests it.

28.6 Your Learner Profile

Your JADE platform profile changed too: four new lines about loading code, a new line about pinning versions, and a change to the style line that says JADE's required names win over camelCase.

28.7 Summary

Shared code lives in one place and is loaded wherever it's needed. Within a workbook, jade_modules lets one module call another's functions. Between workbooks, a GitHub gist can hold the code: Jade.load_gist runs the latest version every time, which suits your own code, and Jade.import_code_module copies it once, which suits code from others that you want to read first. Libraries like Chart.js load with Jade.load_js, and pinning the version keeps them from changing underneath you. Any code you load runs with full access to the workbook, so decide how much you trust its author before it runs. And when an assistant follows one of your rules into a place it doesn't belong, as camelCase did with auto_exec, add a line saying which rule wins. That completes the core of Part IV. The next lesson, on Office Scripts, is optional and needs a work or school Microsoft 365 account.