Extract Chart Data with a Custom Function in Google Sheets

const UNSILOED_BASE_URL = "https://prod.visionapi.unsiloed.ai";
const UNSILOED_JOB_MAX_AGE_MS = 24 * 60 * 60 * 1000;
const UNSILOED_MAX_CACHED_JOBS = 50;
const UNSILOED_JOB_VERSION = "chart-v6";
const UNSILOED_JOB_PREFIX = "UNSILOED_JOB_";
const UNSILOED_CHART_PROMPT = [
  "Extract every visible chart data point.",
  "Preserve category labels, series names, and units exactly.",
  "End with exactly one Markdown table.",
  "Put category or x-axis values in the first column",
  "and numeric series encoded by chart marks in the remaining columns.",
  "Do not add columns that only repeat plotted values as text, rankings, annotations,",
  "or derived comparisons.",
  "Every data row must contain the plotted value when it is visible.",
  "Include visible units in series headers.",
  "Keep values on multiple axes in their original units.",
  "For visible error bars, add numeric x error and y error columns.",
  "Use an empty cell only for a genuinely missing value.",
  "Use a period as the decimal separator.",
  "Do not put units or grouping separators in numeric cells."
].join(" ");

/**
 * Extracts chart data from a public image URL.
 *
 * @param {string} imageUrl A public PNG, JPEG, or TIFF URL.
 * @param {*} refresh Change this value to check an unfinished job again.
 * @param {boolean} numericX Use TRUE to return a numeric first column.
 * @return {Array<Array<string|number>>} Chart-ready data.
 * @customfunction
 */
function UNSILOED_CHART(imageUrl, refresh, numericX) {
  void refresh;

  if (Array.isArray(imageUrl)) imageUrl = imageUrl[0][0];
  imageUrl = String(imageUrl || "").trim();

  if (!/^https?:\/\//i.test(imageUrl)) {
    throw new Error("Pass a public HTTP image URL.");
  }

  const apiKey = unsiloedApiKey();
  const jobId = getOrCreateChartJob(imageUrl, apiKey);
  let job;

  try {
    job = unsiloedRequest(
      "/parse/" + encodeURIComponent(jobId),
      apiKey
    );
  } catch (error) {
    if (error.httpStatus === 404) {
      const cleared = clearCachedChartJob(imageUrl, apiKey, jobId);
      const action = cleared
        ? "The saved job was cleared. Change the refresh value to submit it again."
        : "Change the refresh value to try again.";
      throw new Error("The saved parse job no longer exists. " + action);
    }
    throw error;
  }

  if (job.status === "Succeeded") {
    return firstChartRows(job, numericX === true);
  }
  if (job.status === "Failed" || job.status === "Cancelled") {
    const cleared = clearCachedChartJob(imageUrl, apiKey, jobId);
    const action = cleared
      ? "The saved job was cleared. Change the refresh value to submit it again."
      : "Change the refresh value to try again.";
    throw new Error(
      (job.message || "Parsing " + job.status.toLowerCase() + ".") + " " + action
    );
  }

  return [["Still processing. Change the refresh value to check again."]];
}

function unsiloedApiKey() {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty("UNSILOED_API_KEY");
  if (!apiKey) throw new Error("Add UNSILOED_API_KEY to the script properties.");
  return apiKey;
}

function getOrCreateChartJob(imageUrl, apiKey) {
  const properties = PropertiesService.getScriptProperties();
  const propertyName = jobPropertyName(imageUrl, apiKey);
  const cached = readCachedChartJob(properties, propertyName);
  if (cached) return cached.jobId;

  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) throw new Error("Try the formula again.");

  try {
    const checkedAgain = readCachedChartJob(properties, propertyName);
    if (checkedAgain) return checkedAgain.jobId;

    pruneChartJobs(properties);

    const image = fetchChartImage(imageUrl);
    const started = unsiloedRequest("/parse", apiKey, {
      method: "post",
      payload: chartParsePayload(image)
    });

    if (typeof started.job_id !== "string" || !started.job_id) {
      throw new Error("Unsiloed returned no job ID.");
    }

    properties.setProperty(propertyName, JSON.stringify({
      jobId: started.job_id,
      createdAt: Date.now()
    }));
    return started.job_id;
  } finally {
    lock.releaseLock();
  }
}

function readCachedChartJob(properties, propertyName) {
  const saved = properties.getProperty(propertyName);
  if (!saved) return null;

  try {
    const state = JSON.parse(saved);
    const age = Date.now() - state.createdAt;
    if (
      typeof state.jobId === "string" &&
      state.jobId &&
      Number.isFinite(state.createdAt) &&
      age >= 0 &&
      age < UNSILOED_JOB_MAX_AGE_MS
    ) {
      return state;
    }
  } catch (error) {
    // Invalid entries are removed during the next locked cleanup.
  }
  return null;
}

function clearCachedChartJob(imageUrl, apiKey, jobId) {
  const properties = PropertiesService.getScriptProperties();
  const propertyName = jobPropertyName(imageUrl, apiKey);
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return false;

  try {
    const saved = properties.getProperty(propertyName);
    if (!saved) return true;

    try {
      const state = JSON.parse(saved);
      if (state.jobId !== jobId) return false;
    } catch (error) {
      // The matching property is unusable and can be removed.
    }
    properties.deleteProperty(propertyName);
    return true;
  } finally {
    lock.releaseLock();
  }
}

function pruneChartJobs(properties) {
  const all = properties.getProperties();
  const now = Date.now();
  const valid = [];

  Object.keys(all).forEach(key => {
    if (key.indexOf(UNSILOED_JOB_PREFIX) !== 0) return;

    try {
      const state = JSON.parse(all[key]);
      const age = now - state.createdAt;
      if (
        typeof state.jobId === "string" &&
        state.jobId &&
        Number.isFinite(state.createdAt) &&
        age >= 0 &&
        age < UNSILOED_JOB_MAX_AGE_MS
      ) {
        valid.push({ key: key, createdAt: state.createdAt });
      }
    } catch (error) {
      // Invalid entries are excluded from the retained set.
    }
  });

  valid.sort((a, b) => b.createdAt - a.createdAt);
  const retained = new Set(
    valid.slice(0, Math.max(UNSILOED_MAX_CACHED_JOBS - 1, 0))
      .map(entry => entry.key)
  );

  Object.keys(all).forEach(key => {
    if (
      key.indexOf(UNSILOED_JOB_PREFIX) === 0 &&
      !retained.has(key)
    ) {
      properties.deleteProperty(key);
    }
  });
}

function chartParsePayload(image) {
  const segmentAnalysis = {
    Page: {
      markdown: "VLM",
      model_id: "nova",
      vlm: UNSILOED_CHART_PROMPT
    }
  };
  const outputFields = {
    html: false,
    markdown: true,
    ocr: false,
    image: false,
    content: false,
    bbox: false,
    confidence: false,
    embed: false,
    chart_data: false
  };

  return {
    file: image,
    use_high_resolution: "true",
    layout_analysis: "page_by_page",
    segment_analysis: JSON.stringify(segmentAnalysis),
    response_profile: "custom",
    output_fields: JSON.stringify(outputFields)
  };
}

function fetchChartImage(imageUrl) {
  const response = UrlFetchApp.fetch(imageUrl, {
    followRedirects: true,
    muteHttpExceptions: true
  });
  const status = response.getResponseCode();
  if (status < 200 || status >= 300) {
    throw new Error("The image URL returned HTTP " + status + ".");
  }

  const image = response.getBlob();
  if (!/^image\//i.test(image.getContentType())) {
    throw new Error("The URL did not return an image.");
  }

  const path = imageUrl.split(/[?#]/)[0];
  const fileName = path.substring(path.lastIndexOf("/") + 1) || "chart.png";
  return image.setName(fileName);
}

function jobPropertyName(imageUrl, apiKey) {
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    UNSILOED_JOB_VERSION + "\n" + imageUrl + "\n" + apiKey,
    Utilities.Charset.UTF_8
  );
  const hex = digest.map(byte =>
    (byte + 256).toString(16).slice(-2)
  ).join("");
  return UNSILOED_JOB_PREFIX + hex;
}

function unsiloedRequest(path, apiKey, options) {
  const requestOptions = options || {};
  const response = UrlFetchApp.fetch(UNSILOED_BASE_URL + path, {
    muteHttpExceptions: true,
    ...requestOptions,
    headers: {
      ...(requestOptions.headers || {}),
      "api-key": apiKey
    }
  });
  const text = response.getContentText();
  const status = response.getResponseCode();

  if (status >= 200 && status < 300) return JSON.parse(text);

  let message = text;
  try {
    const body = JSON.parse(text);
    message = body.error?.message || body.message || body.detail || text;
  } catch (error) {
    // Keep the plain-text response.
  }

  const requestError = new Error(
    message || "Unsiloed returned HTTP " + status + "."
  );
  requestError.httpStatus = status;
  throw requestError;
}

function firstChartRows(job, numericX) {
  const tables = (job.chunks || [])
    .flatMap(chunk => chunk.segments || [])
    .flatMap(segment => markdownTables(segment.markdown || ""));
  const candidates = [];
  let conversionError = null;

  tables.forEach(rows => {
    if (rows.length < 2 || rows[0].length < 2) return;

    try {
      const converted = rows.map((row, rowIndex) => row.map(
        (cell, columnIndex) =>
          rowIndex === 0 || (columnIndex === 0 && !numericX)
            ? cell
            : chartValue(cell)
      ));

      if (numericX) {
        converted.slice(1).forEach((row, rowIndex) => {
          if (typeof row[0] !== "number") {
            throw new Error(
              'X-axis value in row ' + (rowIndex + 2) +
              ' is not numeric: "' + row[0] + '".'
            );
          }
        });
      }
      candidates.push(chartReadyRows(converted));
    } catch (error) {
      conversionError = error;
    }
  });

  if (candidates.length > 1) {
    throw new Error("Unsiloed returned more than one chart-ready table.");
  }
  if (candidates.length === 1) return candidates[0];
  if (conversionError) throw conversionError;
  throw new Error("Unsiloed did not return a chart data table.");
}

function markdownTables(markdown) {
  const lines = markdown.split(/\r?\n/);
  const tables = [];

  for (let index = 0; index < lines.length - 1; index += 1) {
    const header = splitMarkdownRow(lines[index]);
    const separator = splitMarkdownRow(lines[index + 1]);
    if (
      header.length < 2 ||
      separator.length !== header.length ||
      !separator.every(cell => /^:?-+:?$/.test(cell))
    ) {
      continue;
    }

    const width = header.length;
    const rows = [header];
    index += 2;

    while (index < lines.length && isMarkdownRow(lines[index])) {
      const row = splitMarkdownRow(lines[index]);
      if (row.length > width) {
        throw new Error("A Markdown data row has more cells than its header.");
      }
      rows.push(row.concat(Array(width - row.length).fill("")));
      index += 1;
    }

    if (rows.length > 1) tables.push(rows);
    index -= 1;
  }

  return tables;
}

function isMarkdownRow(line) {
  return splitMarkdownRow(line).length > 1;
}

function splitMarkdownRow(line) {
  let body = String(line || "").trim();
  if (body.charAt(0) === "|") body = body.slice(1);
  if (endsWithUnescapedPipe(body)) body = body.slice(0, -1);

  const cells = [];
  let cell = "";

  for (let index = 0; index < body.length; index += 1) {
    const character = body.charAt(index);
    const next = body.charAt(index + 1);

    if (character === "\\" && next && isMarkdownEscapable(next)) {
      cell += next;
      index += 1;
    } else if (character === "|") {
      cells.push(cleanMarkdownCell(cell));
      cell = "";
    } else {
      cell += character;
    }
  }

  cells.push(cleanMarkdownCell(cell));
  return cells;
}

function endsWithUnescapedPipe(text) {
  if (text.charAt(text.length - 1) !== "|") return false;

  let backslashes = 0;
  for (
    let index = text.length - 2;
    index >= 0 && text.charAt(index) === "\\";
    index -= 1
  ) {
    backslashes += 1;
  }
  return backslashes % 2 === 0;
}

function isMarkdownEscapable(character) {
  const code = character.charCodeAt(0);
  return (
    (code >= 33 && code <= 47) ||
    (code >= 58 && code <= 64) ||
    (code >= 91 && code <= 96) ||
    (code >= 123 && code <= 126)
  );
}

function cleanMarkdownCell(cell) {
  return cell.trim()
    .replace(/^\*\*(.*)\*\*$/, "$1")
    .replace(/`([^`]*)`/g, "$1");
}

function chartReadyRows(rows) {
  const columns = [0];

  for (let column = 1; column < rows[0].length; column += 1) {
    const values = rows.slice(1).map(row => row[column]);
    const populated = values.filter(value => value !== "");
    const numeric = populated.filter(value => typeof value === "number");

    if (populated.length === 0) {
      throw new Error(
        'Column "' + (rows[0][column] || column + 1) + '" has no values.'
      );
    }
    if (numeric.length !== populated.length) {
      const invalid = populated.find(value => typeof value !== "number");
      throw new Error(
        'Column "' + (rows[0][column] || column + 1) +
        '" contains a nonnumeric value: "' + invalid + '".'
      );
    }
    columns.push(column);
  }

  if (columns.length === 1) {
    throw new Error("Unsiloed returned no numeric chart series.");
  }
  return rows.map(row => columns.map(column => row[column]));
}

function chartValue(cell) {
  const value = cell.trim();
  if (!value) return "";

  const normalized = value.replace(/\u2212/g, "-");
  const looksNumeric = /^[+-]?(?:\d[\d.,]*|\.\d+)(?:e[+-]?\d+)?$/i
    .test(normalized);

  if (looksNumeric && normalized.indexOf(",") !== -1) {
    throw new Error('Numeric cell "' + value + '" contains a comma.');
  }
  if (!/^[+-]?(?:\d+\.?\d*|\.\d+)(?:e[+-]?\d+)?$/i.test(normalized)) {
    return value;
  }

  const number = Number(normalized);
  if (!Number.isFinite(number)) {
    throw new Error('Numeric cell "' + value + '" is outside the supported range.');
  }
  if (Number.isInteger(number) && !Number.isSafeInteger(number)) {
    throw new Error('Integer cell "' + value + '" cannot be represented exactly.');
  }
  return number;
}