Office.js code in Excel

Office.js is the programming interface Excel gives add-ins. When none of Oda's other steps can do a task, DataMind Oda in Excel can write a short piece of Office.js code and run it, for example to set a chart option the other steps lack or to make many small edits in one go. Every change the code asks for is checked and approved like Oda's other changes. This page covers when Oda uses code, how it runs, when it asks, and what it never does.

When Oda writes code

• Oda prefers its other steps, such as writing cells or changing a chart, because their approval cards are clearer and they check more. It writes code for what they cannot do: a chart, shape or page setting they lack, or many small edits that would take many steps.
• The step row reads Running Office.js code with Oda's short description of what the code does, and shows the code itself; code longer than four lines is folded, with Show the code. When it finishes, the row reads, for example, Read the workbook in 2 syncs, 3 syncs, 12 changes, or The code stopped with an error after 2 syncs.
• A sync is one batch of work the code sends to Excel: what it reads, what it changes, or both.

How the code runs

1. The code runs in a sandbox, a separate, locked-down space inside the pane. It has no network access and cannot reach your sign-in, your chats or the files the pane stores.
2. The code never touches Excel itself. Each time it syncs, it hands the pane a list of what it wants to read and change.
3. The pane checks every item against what this version of Excel offers, the list of what Oda never does and the same formula checks as Oda's other steps. If anything fails, nothing of that sync runs.
4. The pane works out what the sync changes, decides by your permission mode whether to ask, and only then makes the change in Excel.

The code can call Oda's read steps, such as reading cells or searching the workbook, up to 50 times in one run. Changes the code made appear in the Changes panel under Oda's reply, like any other change.

When Oda asks

So in Accept edits, a sync runs without a card only when every change in it lands on sheets the same run of the code added, or on charts, tables, rules and shapes it made there. Copying your cells onto such a sheet counts, as it only reads them. A chart there that takes its data from one of your sheets asks, and so do pivot tables, slicers, defined names and styles. A table whose address names no sheet asks as a change Oda cannot describe exactly, because Excel puts it on the sheet that is active. Cells Oda wrote earlier do not count as the code's own: changing them asks, too. Once the code moves your cells onto a sheet it added, no sheet counts as new for the rest of that run. When one sync makes several changes, one card lists them all and asks as the strictest of them would. See Permission modes and approvals in Excel.

The approval card for code

A card for a sync of code takes the place of the message box and shows:

• Top line: Office.js code, or Removes content when the sync removes something.
• Title: Allow Oda’s code to make a change the other tools cannot check? for changes only code makes, or the usual title for the kind of change, such as Allow Oda to overwrite cells?.
• Step: a line such as Step 2 of the code Oda wrote: Add data labels, with Oda's description.
• Show the code: opens the full code.
• Calls: a list you can open, such as 3 Office.js calls of this step that change the workbook, naming every call that changes something, with its sheet or cells where Oda knows them. A call that moves or fills cells names both ends, such as Excel.Range.moveTo on Sales!A1 -> Other!C5.
• Changes: one line per change, as on Oda's other cards, such as Write 4 cells to Sales!A1:B2 or Link Sales!A1 to https://example.com. A change Oda cannot describe exactly says so, such as Changes made by Office.js code (Excel.Shape.delete) on Logo that Oda cannot describe exactly. A link's address is spelled out in full.

Answer with Allow, Don’t allow, or one of the answers under More ways to allow, the arrow beside Allow:

• Allow runs this sync.
• Always allow in this chat runs it, and later syncs of Oda's code that make the same kinds of change then run without a card in this chat, in the current mode, until the pane closes. On a card that also changes sheets the code added, it covers only the kinds of change it makes to your sheets and the workbook. Whose cells a change replaces makes no difference: allowing the code to overwrite cells covers cells Oda wrote and yours alike. Cards that remove or delete anything on a sheet the code did not add, move cells there, put a link on cells, make a change there that only code makes, change how Excel calculates or make a workbook-wide change only code makes, such as to a cell style or the document properties, do not offer it.
• Allow the rest of this run runs it, and the later changes of the same code that remove nothing then go ahead without asking until the code ends: Later changes of this code that remove nothing go ahead without asking; removals, links, changes to how Excel calculates and changes the other tools cannot check still ask. It is offered only on cards that write or overwrite. Cards that remove something outside the sheets the code added, change how Excel calculates or make a change only code makes do not offer it, and it ends with the code.
• Don’t allow refuses this sync, and the code stops. The step row reads You did not allow this. Changes of earlier syncs stay in the workbook and are listed in the Changes panel.

A card for a large or structural change can offer a snapshot sheet first, as other cards do. If what the sync changes is edited while the card waits, nothing of that sync runs, even after Allow.

What the code never does

Oda's code is refused, in every permission mode, when it tries to:

• add event handlers, or change the workbook's add-in settings;
• save or close the workbook, or create a new one;
• refresh or break links to other workbooks or data connections, or change custom XML parts;
• set, check or use passwords;
• mention people in comments, which sends them email;
• make sheets very hidden;
• load cell values from the web, such as web images and linked data types, or call custom functions;
• insert sheets from a file, which Oda does through its own step after checking the file; see Insert sheets from an attached workbook;
• add pictures to headers and footers;
• write formulas in a local language, or any formula Oda never writes; see Formulas Oda does not write.

Such a step shows Oda’s code asked for something Oda never does, so nothing of that step ran. A step that uses something your version of Excel does not have shows Oda’s code used something this Excel does not have, so nothing of that step ran.

Limits

A run that does more shows Oda’s code did more at once than one run may., and one that runs past its time shows This took too long and was stopped. Stop ends the code at once, and a card that is still waiting is answered Don’t allow.

Undo what the code changed

Revert turn treats the code's changes as it treats the same changes by Oda's other steps: it puts back cell values the code wrote. A turn in which the code made a change only code makes, or a change Revert turn cannot undo for other steps either, is refused, and nothing is changed. Use Ctrl+Z where it works, a snapshot sheet, or ask Oda to change it back. See Review and undo Oda's changes.

Where Office.js code is available

Oda writes code only when all of these hold:

• your company has Office.js code on in the Oda settings; see DataMind Oda settings;
• you have Office.js code on in the pane's Settings, which it is until you turn it off; see Settings;
• this Excel can start the sandbox; see What depends on where Excel runs.

When the setting goes off while you work, Oda stops offering code, and a code step that was already on its way shows This tool was turned off for Oda in Excel, by your company or in your settings.

Questions, answered

Why did Oda write code instead of using its normal steps?

None of its other steps could do the task, for example a chart or shape setting they lack, or many small edits at once. Oda prefers its other steps whenever one fits.

Does the code change my workbook directly?

No. The code runs apart from Excel, in the pane's sandbox. The pane checks each batch of changes the code asks for, asks you where your permission mode says so, and only then makes the change.

Can the code reach the internet or my files?

No. The sandbox has no network access and cannot reach your sign-in, your chats or the pane's stored files. Links the code puts on cells always ask, in every permission mode.

What does Allow the rest of this run cover?

The later changes of the same code that remove nothing, until the code ends. Removals, links, changes to how Excel calculates and changes only code makes still ask, and the next piece of code starts asking again.

Why does a code card ask in Accept edits?

The code changes something outside the sheets it added: cells of your sheets, even cells Oda wrote, the workbook's names, styles or document properties, or how Excel calculates. Or it removes something, or makes a change none of Oda's other steps makes, which counts as a removal. Only changes to sheets the same code added run without a card. A link always asks.

Why did code that built a new sheet run without a card?

Every change it made landed on a sheet the same code added, so nothing of yours changed. In Ask before edits such code still asks.

Why does a code card not offer Always allow in this chat?

The card removes or deletes something on a sheet the code did not add, moves cells there, puts a link on cells, makes a change there that only code makes, changes how Excel calculates, or makes a workbook-wide change only code makes, such as to a cell style or the document properties. Each of those is asked separately. A card that removes, links, changes how Excel calculates or makes a change only code makes offers only Allow and Don’t allow, with no More ways to allow.

Can I turn Office.js code off?

Yes. Open Settings from More options in the pane header and turn off Office.js code under What Oda can use. Your administrator can also turn it off for the whole company.

What happens if I click Don’t allow halfway through?

Nothing of that sync runs and the code stops. What earlier syncs changed stays, and the Changes panel lists it.