Extract a Table from an Image in Google Sheets

const BASE_URL = "https://prod.visionapi.unsiloed.ai";

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 onOpen() {
  SpreadsheetApp.getUi().createMenu("Unsiloed")
    .addItem("Extract Table From Image", "extractTable")
    .addToUi();
}

function extractTable() {
  const image = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  if (!image || image.valueType !== SpreadsheetApp.ValueType.IMAGE) {
    throw new Error("Select a cell holding an image placed in the cell, not over the grid.");
  }

  const rows = tableRows(parseImage(imageBlob(image)));
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = spreadsheet.getSheetByName("Extracted")
    ? "Extracted " + Date.now()
    : "Extracted";
  const sheet = spreadsheet.insertSheet(sheetName);
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

// The cell holds a link to the image, not the bytes themselves.
function imageBlob(image) {
  const blob = UrlFetchApp.fetch(image.getContentUrl()).getBlob();
  const extensions = {
    "image/png": "png",
    "image/jpeg": "jpg",
    "image/tiff": "tiff"
  };
  const extension = extensions[blob.getContentType()];
  if (!extension) throw new Error("Use a PNG, JPEG, or TIFF image.");
  return blob.setName("table." + extension);
}

function parseImage(blob) {
  const headers = { "api-key": unsiloedApiKey() };
  const started = UrlFetchApp.fetch(BASE_URL + "/parse", {
    method: "post",
    headers,
    payload: { file: blob }
  });
  const jobId = JSON.parse(started.getContentText()).job_id;

  for (let attempt = 0; attempt < 100; attempt++) {
    Utilities.sleep(3000);
    const response = UrlFetchApp.fetch(BASE_URL + "/parse/" + jobId, {
      headers
    });
    const job = JSON.parse(response.getContentText());
    if (job.status === "Succeeded") return job;
    if (job.status === "Failed" || job.status === "Cancelled") {
      throw new Error(job.message || "Parsing " + job.status.toLowerCase() + ".");
    }
  }
  throw new Error("Parsing took too long.");
}

// No schema needed: /parse returns whatever table the image happens to hold.
function tableRows(job) {
  const table = job.chunks
    .flatMap(chunk => chunk.segments)
    .find(segment => segment.segment_type === "Table");
  if (!table) throw new Error("No table found in that image.");
  if (!table.html) throw new Error("The detected table contained no HTML.");

  const htmlRows = table.html.match(/<tr[\s\S]*?<\/tr>/gi);
  if (!htmlRows) throw new Error("The detected table contained no rows.");

  const rows = htmlRows.map(tr =>
    (tr.match(/<t[dh][\s\S]*?<\/t[dh]>/gi) || []).map(safeCellText));
  if (!rows.some(row => row.length)) {
    throw new Error("The detected table contained no cells.");
  }

  // setValues needs every row the same length, but spanning cells can make rows short.
  const width = Math.max(...rows.map(row => row.length));
  return rows.map(row => row.concat(Array(width - row.length).fill("")));
}

function safeCellText(cell) {
  const text = cell
    .replace(/<br\s*\/?\s*>/gi, "\n")
    .replace(/<[^>]+>/g, "")
    .replace(/&nbsp;/gi, " ")
    .replace(/&quot;/gi, '"')
    .replace(/&#39;|&apos;/gi, "'")
    .replace(/&lt;/gi, "<")
    .replace(/&gt;/gi, ">")
    .replace(/&#(\d+);/g, (_, code) => String.fromCodePoint(Number(code)))
    .replace(/&#x([0-9a-f]+);/gi, (_, code) => String.fromCodePoint(parseInt(code, 16)))
    .replace(/&amp;/gi, "&")
    .trim();

  // Prevent document text from becoming a spreadsheet formula.
  return /^[=+\-@]/.test(text) ? "'" + text : text;
}