Google Apps Script

const API = "https://api.constaia.com/v1";
const SHEET = "Documents";

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Constaia")
    .addItem("Analyze pending rows", "analyzePending")
    .addItem("Refresh running analyses", "refreshPending")
    .addToUi();
}

function apiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty("CONSTAIA_API_KEY");
  if (!key) throw new Error("Missing script property CONSTAIA_API_KEY");
  return key;
}

function call_(path, params) {
  const response = UrlFetchApp.fetch(API + path, {
    ...params,
    headers: { Authorization: "Bearer " + apiKey_() },
    muteHttpExceptions: true,
  });
  const code = response.getResponseCode();
  const body = JSON.parse(response.getContentText());
  if (code >= 400) {
    const e = body.error || {};
    throw new Error(`${e.code}: ${e.message} (${e.request_id})`);
  }
  return body;
}

function analyzeDriveFile_(fileId, expect) {
  const blob = DriveApp.getFileById(fileId).getBlob();
  return call_("/analyze", {
    method: "post",
    // An object containing a Blob is sent as multipart/form-data.
    payload: {
      file: blob,
      options: JSON.stringify({ expect, language: "en", metadata: { drive_file_id: fileId } }),
    },
  });
}

function value_(field) {
  return field && field.value != null ? field.value : "";
}

function writeResult_(sheet, row, analysis) {
  const fields = analysis.fields || {};
  const verdict = analysis.verdict;
  sheet.getRange(row, 3, 1, 5).setValues([[
    verdict ? verdict.status : analysis.status,
    verdict ? verdict.reasons.filter((r) => r.severity !== "info").map((r) => r.message).join("\n") : "",
    value_(fields.document_number),
    value_(fields.expiry_date),
    analysis.id,
  ]]);
}

function analyzePending() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET);
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    const [fileId, expect, verdict] = rows[i];
    if (!fileId || verdict) continue;
    try {
      writeResult_(sheet, i + 1, analyzeDriveFile_(fileId, expect || undefined));
    } catch (err) {
      sheet.getRange(i + 1, 3, 1, 2).setValues([["error", err.message]]);
    }
    Utilities.sleep(200);
  }
}

function refreshPending() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET);
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    const status = rows[i][2];
    const id = rows[i][6];
    if (!id || (status !== "queued" && status !== "processing")) continue;
    try {
      writeResult_(sheet, i + 1, call_("/analyses/" + encodeURIComponent(id), { method: "get" }));
    } catch (err) {
      sheet.getRange(i + 1, 4).setValue(err.message);
    }
  }
}