DraftKings data in Google Sheets and Excel

Why IMPORTDATA does not work

Every endpoint requires an Authorization header. Google Sheets' IMPORTDATA and IMPORTHTML cannot send one, and the API rejects keys in URLs, so those formulas return an error. Apps Script in Sheets and Power Query in Excel can both send headers, and each stores the key outside the grid.

Google Sheets with Apps Script

Apps Script runs on Google's servers, so it can call the API with a key held in Script Properties. The Sheets script reads one page per refresh into a sheet tab.

1. In your sheet, open Extensions, then Apps Script, and paste the file's contents.
2. Open Project Settings, then Script Properties. Add DRAFTKINGS_API_KEY with your key.
3. Run refreshPick6Board once from the editor and grant the permissions it asks for.
4. Adjust the sport, stat and limit values at the top of the script.

function fetchPage_(path, params) {
  const key = PropertiesService.getScriptProperties().getProperty("DRAFTKINGS_API_KEY");
  const query = Object.keys(params)
    .map((name) => name + "=" + encodeURIComponent(params[name]))
    .join("&");
  const response = UrlFetchApp.fetch("https://draftkingsapi.com" + path + "?" + query, {
    headers: { Authorization: "Bearer " + key },
    muteHttpExceptions: true,
  });
  const body = JSON.parse(response.getContentText());
  if (response.getResponseCode() !== 200) throw new Error(body.error.message);
  return body;
}

Never paste the key into a cell. Anyone with view access could read it, and it would travel with every copy of the sheet. Sharing a sheet shares values, not Script Properties, but editors can still run the script and read script settings, so give edit access with care.

Refresh on a schedule

In Apps Script, add a time-driven trigger from the Triggers panel. Set it based on your allowance. Each page costs one request, so a trigger running a three-page refresh every hour uses about 2,160 requests in a 30-day month, more than the Free plan includes.

Keep triggers infrequent on Free: a daily refresh of a few pages leaves plenty for testing. Do not attach calls to cell recalculation or sheet-open events, because they can run far more than you expect. Worker targets are five minutes for Pick6 and sportsbook, so a sheet refreshed more often gets nothing newer.

Excel with Power Query

Power Query can read native Pick6 CSV with a Web.Contents call. Open Data, then Get Data, then Blank Query, and paste this into the Advanced Editor. For the first connection, choose Anonymous credentials, because the header in the query carries the key.

let
    Key = Excel.CurrentWorkbook(){[Name="ApiKey"]}[Content]{0}[Column1],
    Source = Web.Contents("https://draftkingsapi.com/api/v1/pick6/projections",
        [
            Query = [sport = "NHL", stat = "Shots on Goal", limit = "100", format = "csv"],
            Headers = [Authorization = "Bearer " & Key]
        ]),
    Parsed = Csv.Document(Source, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Parsed, [PromoteAllScalars = true])
in
    Promoted

This example reads the key from a one-cell table named ApiKey, which keeps it out of the query text but still leaves it in the workbook. Keep that workbook private, and delete the key before sharing it. Excel's data privacy prompts may ask you to set a level for each source.

Datasets without native CSV

Only Pick6 has format=csv. For sportsbook, DFS and statistics, Power Query can parse JSON using Json.Document(Source) and expand the data list. A simpler path is to run export-csv.py on a schedule and open the output: python3 export-csv.py --resource odds --sport NBA --max-pages 3 --out rows.csv. The CSV guide covers columns for each dataset.

Keep values meaningful

Spreadsheets make it easy to lose context, so keep these columns next to every value and add a banner for the capture time.

• observed_at, so a reader can tell how old a row is.
• stale and is_paused, as columns or as conditional formatting.
• The three Pick6 identity columns, as text, because large IDs can be damaged by number formatting.
• selections as a JSON cell, or one column per side. Leave missing sides blank rather than filling them.

Pick6 rows carry standings multipliers such as 1.5x. A standings multiplier is not decimal odds, a probability or an established entry payout. Avoid a formula that treats it as a price. Salaries and American prices also have their own meanings, as the comparison guide explains. Do not publish the sheet to the web without checking your rights, because technical access is not a redistribution license.

Check the recorded data

• Historical sample data
• Sources and status

Related resources