Scripts in Excel & Power BI

Updated Nov 9, 2026
DataMagik Automate

What this does

A script can be published to Excel. It then has a web address that returns the script's rows as a table, which Excel, Power BI and Google Sheets can load and refresh. There is no Power Query code to write and nothing to set up inside each workbook: copy the link, paste it into Excel, load.

Each refresh runs the script and returns what it gives back that moment. The link is read-only: it can run only that one script, and a script marked as writing data can never be published.

There are two sides to it:

  • Publishing a script and making links, in the script editor. This needs the DataMagik - Builder permission.
  • Using published data, from Data for Excel. Anyone with DataMagik - Builder or DataMagik - Viewer can open it.

Getting data into Excel

  1. Get a link: from whoever published the data, or make one yourself (see Publishing a script below).
  2. In Excel, choose Data › From Web.
  3. Paste the link and click OK.
  4. When Excel asks how to connect, choose Anonymous.
  5. Click Load.

Anonymous is the step people get wrong. A token link already carries its credential, so Excel needs nothing more; any other choice fails.

If your link has no token in it (it looks like …/x/1234.csv), Excel has to send your API key instead. In the From Web dialog, choose Advanced, paste the link, and add a header named X-API-Key with your key. Make an API key from your profile: Security Settings › Bearer Tokens (API Keys). See Script webhooks.

Google Sheets

A token link works in Google Sheets too:

=IMPORTDATA("https://your-datamagik-address/x/1234/dxl_….csv")

Data for Excel

Script Engine › Data for Excel lists everything your company has published, with each script's description and the shape of its last result. People with only the Viewer permission find Data for Excel in the main menu. You do not need the script editor to use it.

  • Search with Search published data….
  • Each card shows a link that uses your API key, in the format you choose for that card. In Excel, add your key under Advanced as described above. Get an API key opens the page where you make one.
  • Would rather not use an API key? A Builder gets Create a link with a token instead, which opens the script with its Excel panel ready. Anyone else should ask whoever published the data for a token link.

Publishing a script

  1. Open the script in Script Engine › Scripts and click Excel in the toolbar. The Connect to Excel panel opens.
  2. Under Publish, tick Publish this script to Excel and Power BI and click Save. Until you do, every link to the script is refused and it is not listed in Data for Excel.
  3. If the script takes inputs, fill them in under Script inputs. The link updates as you type.
  4. Choose a format: CSV, XLSX, JSON or HTML.
  5. Create a link (below) and copy it.

What Excel will see previews the table, built from the script's last run, so you can check the columns before anyone loads them.

CSV or XLSX?

CSV is the default, because Excel shows it as a finished table straight away. But CSV carries no types, so Excel guesses as it loads, and it guesses wrong for anything that only looks like a number:

Value in DataMagikWhat CSV becomes in Excel
00124735124735: the leading zeros are gone.
3-44 March: read as a date.
A 19-digit IDScientific notation.

Choose XLSX when a column holds an identifier, such as a part number, rather than a quantity. XLSX keeps text as text, so a value the script returns as a string arrives exactly as it was. Power BI's OData feed has no such problem, because it describes each column's type.

Making a link

A link is the address you paste into Excel. Under Authentication, choose how it proves who it is:

  • Token in link (the default): the credential is part of the address, so Excel needs nothing else.
  • API key header: the address carries no credential, and Excel sends your API key as an X-API-Key header. Stricter, but in Excel it means using Advanced in the From Web dialog.

If token links are turned off for your company, the panel uses an API key header.

To make a token link:

  1. Type a label that says where it will be used, such as Finance monthly workbook. A label is required.
  2. Choose who it runs as. Runs as me is the default. Choosing another person needs the DataMagik - Security or Company Manager permission.
  3. Choose how long it lasts: 30, 90 (the default), 180 or 365 days, or Never expires.
  4. Click Create link.
  5. Copy the link straight away. The token is shown once, under Copy this now — it is not shown again. DataMagik keeps only a fingerprint of it, so it cannot show it to you again.

A token link is worth little on its own: it runs one script, read-only, and nothing else; it expires; it can be revoked at any time; and its refreshes are limited per hour. Every refresh appears in the script's run history.

A link sees exactly what its person's SQL Access Roles allow, for as long as the link lives.

Rotating and revoking links

The panel lists the script's links with their label, who they run as, when they were last used and how the last refresh went (ok, 0 rows, denied, throttled, too slow or error). Expired and revoked links stay in the list, so you can see why a workbook stopped working.

  • Rotate makes a new token and keeps the old link working for a few more days, 7 by default, so workbooks can be updated before it stops. The panel tells you the date the old link stops.
  • Revoke stops the link at once. Any workbook using it stops refreshing.

The person a link runs as is emailed 14 days before it expires.

Power BI (OData)

A token link also serves an OData feed. Open OData feed (Power BI) in the panel and copy the address. Use it with Power BI's Get Data › OData feed, or Excel's Data › From OData Feed.

  • The script must have finished a run before Power BI can connect: the feed describes its columns from a real result. Run the script once from the editor.
  • Each row has an extra _row column, because OData needs a key for every row and a script's rows have none.
  • $select, $top, $skip and $orderby (one column) work.
  • $filter is refused with an error rather than ignored, so you never get a full table you believe is filtered. Filter by adding the script's own inputs to the address instead.
  • Paging keeps one result for five minutes, so page two matches page one.

Shaping a script for Excel

Return your rows as a list of flat objects inside data.rows, with a columns list beside them. That needs no other setup:

function main(context) {
  const result = odbc.executeQuery("Parts by Status", { status: context.part_status });
  return {
    success: true,
    data: {
      columns: ["Part_No", "Name", "Qty"],
      rows: result.rows
    }
  };
}

If there is no data.rows, DataMagik looks for rows, then data, then the first list of objects anywhere in what the script returned.

Columns and their order

The columns list fixes which columns appear and in what order. Without it, the columns are every key found in the rows, in alphabetical order. That order is used so it never changes between refreshes: a column that moved would break every workbook using the link.

Nested values

An object one level down becomes dotted columns, such as address.city and address.zip. Anything deeper, and any list, is written into a single cell as JSON. The table is always rectangular. If your columns list names address itself, you get the whole object in one cell; list the dotted names when you want them separate.

Very large numbers

JavaScript numbers are exact only up to 9,007,199,254,740,992 (16 digits). Past that the last digits are lost inside the script, before the row ever reaches Excel, and no format can bring them back. Plex keys are well inside that range. DataMagik's own record IDs are 19 digits, so return those as text:

// Loses its last digits
rows.push({ Link_Id: r.link_id });

// Exact, and Excel keeps it as text
rows.push({ Link_Id: String(r.link_id) });

Inputs

Anything in the link after ? becomes the script's context. For example, ?part_status=Active arrives as context.part_status.

  • A name repeated in the link becomes a list.
  • true, false and numbers are converted, except values that start with a zero, which stay text so part numbers keep their zeros.

To give the panel proper input boxes and make some inputs required, describe the inputs in the script's Script settings › Assistant Tool Parameters schema. The panel then will not give you a link until every required input has a value, and a link missing one is refused with a message naming it.

Before you publish

  • Excel refreshes on open. A published script may run whenever someone opens a workbook, every time someone presses Refresh All, and on a timetable if the file is in SharePoint. Do not publish a script that changes anything. A script with Writes / mutates data ticked (under Script settings › Assistant Tool) cannot be published, but that box is ticked by hand, so check yours.
  • A refresh has 230 seconds. Excel and the networks in between will not wait longer, so a link gives up at 230 seconds even if the script's own timeout is longer. The panel warns you when it is.
  • Turn on caching for slow scripts. Cache (seconds) is off (0) by default, so every refresh runs the script. The panel suggests a value from how long the script usually takes. With caching on, a refresh that runs out of time gets the last good result from the past 24 hours instead of an error. People refreshing the same link at the same moment share one run either way.
  • Max rows (default 250,000): a result with more rows is refused rather than cut short, because a short table looks complete.
  • Refreshes per hour (default 60): the most times one link may be used in an hour. Your company can also run only a few Excel refreshes at the same time.

Click Save under Publish after changing these.

If the script stops returning columns it used to return, the panel warns: This script recently stopped returning columns it used to return. Workbooks bound to it may have broken. New columns are safe and do not warn.

When a link stops working

A failed refresh returns a plain message, never a table, so it cannot be mistaken for data. Excel shows it as an error.

Message beginsWhat to do
Missing required input(s)Add the named input to the link, for example ?part_status=Active.
This link is no longer validIt was revoked or has expired. Get a new link.
Invalid or expired API keyCheck the X-API-Key header, or make a new key.
… is not published to ExcelPublishing was turned off. A Builder can turn it back on in the script's Excel panel.
… is marked as a write scriptWrite scripts cannot be published.
The user this link runs as is no longer active, or no longer belongs to this companyAsk a Builder to make a new link.
Token links are turned off for this companyUse an API key header instead.
… returned … rows, over the …-row limitAdd an input that narrows the result, or ask for a higher Max rows.
This link has been used … times in the last hourWait, or ask for a higher Refreshes per hour.
Too many Excel refreshes running at onceWait a moment and refresh again.
… did not finish within … secondsNarrow the result with an input, or turn on caching.
… ran, but no table could be foundReturn the rows as { data: { rows: [...] } }.
… failed:The script itself failed. Its run on the Executions page shows why.

For administrators: Excel Links

Admin › Excel Links shows every Excel and Power BI link in the company on one screen: its label, the script it reads, who it runs as, who created it, when it was last used, how its last refresh went and when it expires. Filter by status (Active, Expired, Revoked), and revoke links one at a time or with Revoke selected. The menu item is shown to people with DataMagik - Security.

Related

Was this page helpful?