Last reviewed: 2026-09-15

Direct answer

A practical CometAPI Google Sheets Apps Script workflow uses a spreadsheet-bound script to add a custom menu, read the active range, send each nonempty row to CometAPI, and write the returned summary into the adjacent column. Keep the endpoint, model ID, and authenticated request headers in Apps Script Properties rather than worksheet cells or source-code literals.

The CometAPI OpenAI-compatible quickstart documents the Chat Completions request shape and the choices[0].message.content response path. Google documents external requests through UrlFetchApp , scoped string configuration through the Properties Service , and spreadsheet-bound menus in Custom Menus in Google Workspace .

Begin in a disposable copy of the spreadsheet. Open Extensions → Apps Script, then add these Script Properties through Project Settings:

  • COMETAPI_ENDPOINT: the complete Chat Completions endpoint specified by the current CometAPI quickstart.
  • COMETAPI_MODEL: a current text-capable CometAPI model ID.
  • COMETAPI_REQUEST_HEADERS: a one-line JSON object containing the authenticated request headers required by the current CometAPI contract.

Do not put those values in cells, code, screenshots, or logs. If configuration must appear in documentation or a support record, replace the entire stored value with the standalone marker [REDACTED]. The script below loads the header object without reproducing or assigning a credential-specific example.

const MAX_INPUT_CHARS = 6000;

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("CometAPI")
    .addItem("Summarize selected rows", "summarizeSelectedRows")
    .addToUi();
}

function summarizeSelectedRows() {
  const ui = SpreadsheetApp.getUi();
  let config;

  try {
    config = readConfig_();
  } catch (error) {
    const safe = sanitizeError_(error);
    logSafe_({
      event: "summary_batch",
      outcome: "error",
      row_number: null,
      input_chars: 0,
      output_chars: 0,
      http_status: null,
      error_code: safe.code,
      duration_ms: 0,
      model: null,
    });
    ui.alert("Configuration is incomplete or invalid. Check Script Properties.");
    return;
  }

  const range = SpreadsheetApp.getActiveRange();
  if (!range) {
    ui.alert("Select one or more input rows first.");
    return;
  }

  const sheet = range.getSheet();
  const values = range.getDisplayValues();
  const firstRow = range.getRow();
  const outputColumn = range.getLastColumn() + 1;
  const outputRange = sheet.getRange(firstRow, outputColumn, values.length, 1);
  const existingOutput = outputRange.getDisplayValues().flat();
  const existingFormulas = outputRange.getFormulas().flat();
  const destinationOccupied =
    existingOutput.some((value) => String(value).trim() !== "") ||
    existingFormulas.some((formula) => String(formula).trim() !== "");

  if (destinationOccupied) {
    ui.alert("The destination column contains a value or formula. Clear it or select a different input range.");
    return;
  }

  let completed = 0;
  let failed = 0;
  let skipped = 0;

  values.forEach((row, index) => {
    const rowNumber = firstRow + index;
    const outputCell = sheet.getRange(rowNumber, outputColumn);
    const input = row
      .map((value) => String(value).trim())
      .filter((value) => value !== "")
      .join(" | ");

    if (!input) {
      try {
        writeText_(outputCell, "SKIPPED: EMPTY_ROW");
        skipped += 1;
        logSafe_({
          event: "row_summary",
          outcome: "skipped",
          row_number: rowNumber,
          input_chars: 0,
          output_chars: 0,
          http_status: null,
          error_code: "EMPTY_ROW",
          duration_ms: 0,
          model: config.model,
        });
      } catch (error) {
        failed += 1;
        const safe = sanitizeError_(error);
        logSafe_({
          event: "row_summary",
          outcome: "error",
          row_number: rowNumber,
          input_chars: 0,
          output_chars: 0,
          http_status: null,
          error_code: safe.code,
          duration_ms: 0,
          model: config.model,
        });
      }
      return;
    }

    try {
      if (input.length > MAX_INPUT_CHARS) {
        throw taggedError_("INPUT_TOO_LARGE", null, 0);
      }

      const result = requestSummary_(config, input);
      writeText_(outputCell, "Summary: " + result.summary);
      completed += 1;
      logSafe_({
        event: "row_summary",
        outcome: "ok",
        row_number: rowNumber,
        input_chars: input.length,
        output_chars: result.summary.length,
        http_status: result.httpStatus,
        error_code: null,
        duration_ms: result.durationMs,
        model: config.model,
      });
    } catch (error) {
      failed += 1;
      const safe = sanitizeError_(error);
      try {
        outputCell.setValue("ERROR: " + safe.code);
      } catch (_) {}
      logSafe_({
        event: "row_summary",
        outcome: "error",
        row_number: rowNumber,
        input_chars: input.length,
        output_chars: 0,
        http_status: safe.httpStatus,
        error_code: safe.code,
        duration_ms: safe.durationMs,
        model: config.model,
      });
    }
  });

  ui.alert(`Finished: ${completed} summarized, ${failed} failed, ${skipped} skipped.`);
}

function readConfig_() {
  const properties = PropertiesService.getScriptProperties();
  const endpoint = properties.getProperty("COMETAPI_ENDPOINT");
  const model = properties.getProperty("COMETAPI_MODEL");
  const requestHeadersText = properties.getProperty("COMETAPI_REQUEST_HEADERS");

  if (!endpoint || !model || !requestHeadersText) {
    throw taggedError_("CONFIG_MISSING", null, 0);
  }

  let requestHeaders;
  try {
    requestHeaders = JSON.parse(requestHeadersText);
  } catch (_) {
    throw taggedError_("CONFIG_INVALID", null, 0);
  }

  if (
    !requestHeaders ||
    Array.isArray(requestHeaders) ||
    typeof requestHeaders !== "object" ||
    Object.keys(requestHeaders).length === 0
  ) {
    throw taggedError_("CONFIG_INVALID", null, 0);
  }

  return { endpoint, model, requestHeaders };
}

function requestSummary_(config, input) {
  const started = Date.now();
  let response;

  try {
    response = UrlFetchApp.fetch(config.endpoint, {
      method: "post",
      contentType: "application/json",
      headers: config.requestHeaders,
      muteHttpExceptions: true,
      payload: JSON.stringify({
        model: config.model,
        messages: [
          {
            role: "system",
            content: "Summarize the spreadsheet row in one concise sentence. Do not invent missing facts.",
          },
          {
            role: "user",
            content: input,
          },
        ],
        temperature: 0.2,
        max_completion_tokens: 180,
      }),
    });
  } catch (_) {
    throw taggedError_("FETCH_FAILED", null, Date.now() - started);
  }

  const httpStatus = response.getResponseCode();
  const body = response.getContentText();

  if (httpStatus < 200 || httpStatus >= 300) {
    throw taggedError_("UPSTREAM_HTTP", httpStatus, Date.now() - started);
  }

  let parsed;
  try {
    parsed = JSON.parse(body);
  } catch (_) {
    throw taggedError_("RESPONSE_NOT_JSON", httpStatus, Date.now() - started);
  }

  const summary = parsed?.choices?.[0]?.message?.content;
  if (typeof summary !== "string") {
    throw taggedError_("RESPONSE_SHAPE_UNEXPECTED", httpStatus, Date.now() - started);
  }

  if (!summary.trim()) {
    throw taggedError_("EMPTY_RESPONSE", httpStatus, Date.now() - started);
  }

  return {
    summary: summary.trim(),
    httpStatus,
    durationMs: Date.now() - started,
  };
}

function writeText_(cell, text) {
  try {
    cell.setValue(text);
  } catch (_) {
    throw taggedError_("CELL_WRITE_FAILED", null, 0);
  }
}

function taggedError_(code, httpStatus, durationMs) {
  const error = new Error(code);
  error.code = code;
  error.httpStatus = httpStatus;
  error.durationMs = durationMs;
  return error;
}

function sanitizeError_(error) {
  const allowedCodes = new Set([
    "CONFIG_MISSING",
    "CONFIG_INVALID",
    "FETCH_FAILED",
    "UPSTREAM_HTTP",
    "RESPONSE_NOT_JSON",
    "RESPONSE_SHAPE_UNEXPECTED",
    "EMPTY_RESPONSE",
    "INPUT_TOO_LARGE",
    "CELL_WRITE_FAILED",
  ]);
  const code = allowedCodes.has(error?.code) ? error.code : "UNEXPECTED_ERROR";

  return {
    code,
    httpStatus: Number.isInteger(error?.httpStatus) ? error.httpStatus : null,
    durationMs: Number.isInteger(error?.durationMs) ? error.durationMs : 0,
  };
}

function logSafe_(fields) {
  console.log(JSON.stringify(fields));
}

MAX_INPUT_CHARS is a local application guard, not a claim about a provider limit. Adjust it only after testing the selected model and the real row shape. Prefixing successful output with Summary: also gives every written value a predictable text beginning.

For a happy-path operator run:

  1. Select two or three non-sensitive data rows without selecting the header.
  2. Confirm that the column immediately to the right contains neither values nor formulas, including formulas that currently display an empty string.
  3. Choose CometAPI → Summarize selected rows.
  4. Approve the Apps Script permissions prompt if this is the first run.
  5. Confirm that every nonempty row receives one summary and that the final dialog reports the expected counts.
  6. Open the Apps Script execution log and verify that it contains only the sanitized fields emitted by logSafe_.

For an error-path operator run:

  1. Read the stable marker in the output cell, such as UPSTREAM_HTTP or RESPONSE_NOT_JSON.
  2. Match its row number and HTTP status in the execution log without copying the input, response body, endpoint, or stored request headers.
  3. Correct the configuration, model choice, destination protection, or transient request problem.
  4. Clear only the failed output cells.
  5. Select only those failed input rows and run the menu action again.
  6. Escalate with the sanitized event fields if the same failure repeats.

Who this is for

This pattern is for Google Sheets users who can edit a spreadsheet-bound Apps Script project and need repeatable row summaries without manually copying data into a chat interface. It also gives Apps Script developers a small, inspectable integration before they build a sidebar, add-on, or scheduled workflow.

It is not a good fit for highly sensitive rows, unattended bulk processing, or spreadsheets whose editors should not share one script-level configuration. Review which data may leave the spreadsheet before enabling the menu. In a shared document, decide whether controlled Script Properties or separate User Properties match the access model.

Key takeaways

  • A bound script can create the menu in onOpen, keeping the action attached to the intended spreadsheet.
  • Script Properties keep configuration out of cells and code literals, but their sharing scope still requires an access decision.
  • One synchronous request per row is easier to audit than incremental output for this short summarization workflow.
  • The destination preflight checks both displayed values and formulas, preventing formulas that currently render blank from being overwritten.
  • The script checks HTTP status, JSON parsing, response shape, and blank content before accepting a result.
  • Safe logs contain event, outcome, row_number, input_chars, output_chars, http_status, error_code, duration_ms, and model only.
  • Raw cell values, generated summaries, response bodies, endpoints, and stored request headers do not belong in operational logs.
  • Failed rows can be retried selectively instead of repeating an otherwise successful batch.

Sources checked

Contract details to verify

Before deploying the workflow, recheck the CometAPI quickstart instead of assuming an old example still matches. The request must target the documented Chat Completions route, use a current text-capable model ID, and send a messages array. This script uses one system message and one user message. It keeps the documented synchronous behavior because each row needs one complete result rather than partial rendering.

The documented success path exposes assistant text at choices[0].message.content. The script accepts that value only when it is a nonempty string. A successful HTTP status alone is not enough: an HTML error page, malformed JSON, or changed response shape must not be written as if it were a valid summary.

Check the Apps Script contracts separately. The custom-menu guide says only a script bound to the spreadsheet can create this menu, and onOpen is the normal place to install it when the file opens. If the menu does not appear after saving, reload the spreadsheet and confirm that the code belongs to its bound project rather than an unrelated standalone project.

The Properties Service stores strings in key-value stores with different scopes. Script Properties are shared by users of a script, User Properties belong to the current user, and Document Properties use the document-oriented scope described by Google. This tutorial uses Script Properties for a controlled shared setup. That choice does not remove the need to restrict project access, avoid bulk property logging, and decide who may run requests. Use User Properties instead if each operator must maintain separate configuration.

The selection contract is intentionally narrow. Each selected row becomes one prompt by trimming nonempty displayed values and joining them with a separator. The output goes into the first column immediately to the right of the selected range. Google’s Range API provides the sheet reads and writes, but the spreadsheet layout determines whether that destination is appropriate. The script aborts the whole batch if any destination cell contains a displayed value or a formula, even when the formula currently renders an empty string.

The logging contract is equally important. Retain identifiers, counts, timing, status, the chosen model, and stable error codes. Exclude source row text, model output, complete response bodies, endpoint values, property values, and request headers. Those exclusions make a diagnostic record useful without creating another copy of the spreadsheet’s contents.

Failure modes

Missing or malformed configuration: CONFIG_MISSING means at least one required Script Property is absent or blank. CONFIG_INVALID means the stored headers could not be parsed as a nonempty JSON object. Both failures stop the workflow before any row is sent.

Request rejection: A non-success response becomes UPSTREAM_HTTP, with only the numeric status retained. The CometAPI quickstart specifically calls out authentication failures, unavailable model IDs, and clients pointing to the wrong service. Verify the current contract and model selection before retrying. Do not add the complete response body to the log merely to obtain more detail.

Transport failure: FETCH_FAILED means UrlFetchApp did not return a response that the script could process. Confirm that the script has been authorized and retry one harmless row before replaying a larger selection.

Unexpected response: RESPONSE_NOT_JSON, RESPONSE_SHAPE_UNEXPECTED, and EMPTY_RESPONSE distinguish parsing, contract-shape, and blank-output problems. Preserve the sanitized status and duration, then compare the implementation with the current response contract. Do not silently write an undefined value into the sheet.

Oversized local input: INPUT_TOO_LARGE comes from this tutorial’s local character guard. It is not a published service limit. Reduce the selected columns, split the row into smaller tasks, or revise the guard after controlled testing.

Occupied or protected output: An adjacent destination containing a value or any formula—including a formula that currently displays an empty string—stops the batch before requests begin. A protected or otherwise unwritable destination produces CELL_WRITE_FAILED. Test on a copy, choose a dedicated output column, and confirm that the operator can edit it.

Blank rows and headers: Blank rows receive a visible skip marker. Headers are not detected automatically, so exclude them from the selection. The script reads displayed values; decide whether that presentation is the data you intend to send.

Sensitive source data: The script sends selected displayed values to an external service. Selection is the operator’s approval boundary. Do not run it on rows that have not been approved for that destination, and do not assume that moving configuration out of cells resolves data-governance requirements.

Partial completion: A batch can contain successful rows followed by failures. Keep the successful summaries, resolve the failure, clear the failed markers, and rerun only those rows. Blindly rerunning the full selection creates avoidable duplicate requests.

FAQ

Why use a menu instead of a custom cell function?

A menu makes the operator choose a range and run an explicit write action. It also supports a destination preflight and final batch report, making the external request and its side effects easier to understand.

Can each editor use separate configuration?

Yes, but change the scope deliberately. Google documents User Properties as specific to the current user, while Script Properties are shared by users of a script. Choose the scope that matches ownership and test it with a second account before rollout.

Why does the example not stream responses?

The CometAPI quickstart describes the route as synchronous by default and reserves streaming for incremental output. Short row summaries need one validated result per cell, so a complete response is simpler to handle.

Can I select several columns?

Yes. Each selected row is flattened into one prompt from its nonempty displayed values. If column meaning matters, construct explicit field labels in code. Do not include the header row as ordinary source data.

What if the output column looks empty but contains formulas?

The script checks getFormulas() as well as displayed values. A formula that renders an empty string still counts as occupied, so the batch stops rather than replacing it. Clear the formula intentionally or select an input range whose adjacent output column is truly unused.

Should the script retry every error automatically?

No. Missing configuration, rejected requests, an unavailable model, and an unexpected response shape need correction rather than immediate replay. Start with selective manual retries. Add bounded automation only after defining which failures are transient and how duplicate requests will be controlled.

Does Properties Service make sensitive configuration risk-free?

No. The cited guide documents scoped string storage, not a dedicated secrets vault. Properties keep values out of worksheet cells and source literals, but project access, property scope, logging, and organizational policy still matter.

Where can I learn more about the response object?

Read the response-object walkthrough before extending the parser or persisting additional fields.

Reader next step

Make a disposable copy of a sheet, add the three Script Properties, paste the code, and reload the file. Select two non-sensitive rows and run the menu once. Verify the adjacent summaries, the completed count, and the absence of cell contents or sensitive configuration in the execution log.

Test the overwrite guard by putting a formula that displays an empty string in one destination cell. The script should stop before sending any rows. Remove that test formula, then force one harmless configuration error, confirm that only a stable error marker and sanitized fields appear, restore the correct setting, clear the failed marker, and retry that row. This proves the happy path, destination protection, and recovery path before other editors depend on the menu.

Before sharing the script, review the repository credential-boundary guide . Decide who owns the configuration, which rows may be sent, how many rows constitute a safe batch, and which sanitized fields operators should provide when they need help.