Google Sheets domain metrics with a controlled batch menu

A cell function such as =DOMDETAILER_TF(A2) can be convenient, but recalculation and repeated evaluation can issue more requests than the user intended. Each successful API evaluation costs one credit. This guide uses a manual menu and writes static values so ordinary sheet recalculation does not start paid checks.

The menu accepts up to 20 selected input cells, confirms the number of unique new domains and checks them sequentially. A script lock prevents overlapping menu batches. Existing status rows are skipped, and an attempt marker is written before each request so an interrupted run leaves an explicit review record.

This example uses domain metrics only. Backlinks and extension coverage have separate endpoints and separate credit costs.

1. Use a private spreadsheet. Put these headers in A1:G1: Domain, Status, DA, TF, CF, Moz Spam raw 0–17, Checked UTC.
2. Place bare ASCII or punycode hostnames in column A, starting at row 2. Leave B:G for this script's output.
3. Open Extensions → Apps Script and paste the code below into the bound project.
4. In Apps Script Project Settings → Script properties, add DOMDETAILER_API_KEY with your account key as its value.
5. Save the script and reload the sheet to create the DomDetailer menu. Authorize the script when running it from the menu.

Script Properties keep the key out of sheet cells and source text; they are shared within the script project. Restrict spreadsheet and script editor access to trusted collaborators. Project editors can access or change the code and its stored configuration, so a bound script is not a way to conceal a key from those editors.

Verify credentials with the free balance endpoint before a paid sample. Keep sufficient credits for the selected batch.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('DomDetailer')
    .addItem('Check selected new domains', 'checkSelectedDomains')
    .addToUi();
}

function plannedDomains(sheet, startRow, rowCount) {
  const all = sheet.getRange(2, 1, Math.max(1, sheet.getLastRow() - 1), 2)
    .getDisplayValues();
  const seen = new Set(all.filter(row => row[1].trim())
    .map(row => row[0].trim().toLowerCase()));
  const selected = sheet.getRange(startRow, 1, rowCount, 2).getDisplayValues();
  const hostPattern = /^(?=.{1,253}$)(?:[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?$/;
  const plan = [];
  selected.forEach((values, index) => {
    const host = values[0].trim().toLowerCase();
    if (!host || values[1].trim() || seen.has(host)) return;
    if (!hostPattern.test(host)) throw new Error('Use bare ASCII/punycode hosts.');
    seen.add(host);
    plan.push({ row: startRow + index, host });
  });
  return plan;
}

function checkSelectedDomains() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  if (!range || range.getColumn() !== 1 || range.getNumColumns() !== 1
      || range.getRow() < 2 || range.getNumRows() > 20) {
    ui.alert('Select 1-20 domain cells in column A below the header.');
    return;
  }
  const startRow = range.getRow();
  const rowCount = range.getNumRows();
  const approved = plannedDomains(sheet, startRow, rowCount);
  if (!approved.length) {
    ui.alert('No new unique domains. Rows with a status are skipped.');
    return;
  }
  const key = PropertiesService.getScriptProperties()
    .getProperty('DOMDETAILER_API_KEY');
  if (!key || !key.trim()) throw new Error('Add DOMDETAILER_API_KEY in project settings.');
  if (ui.alert('Run paid domain checks?',
    `Up to ${approved.length} one-credit requests will run. Existing status rows are skipped.`,
    ui.ButtonSet.YES_NO) !== ui.Button.YES) return;

  // Acquire after the dialog: a suspended dialog must not hold the lock.
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) {
    ui.alert('Another batch is running. Try again after it finishes.');
    return;
  }
  let completed = 0;
  try {
    const plan = plannedDomains(sheet, startRow, rowCount);
    if (JSON.stringify(plan) !== JSON.stringify(approved)) {
      throw new Error('Rows changed after confirmation. Review and select again.');
    }
    for (const item of plan) {
      const output = sheet.getRange(item.row, 2, 1, 6);
      const started = new Date().toISOString();
      output.setValues([['ATTEMPTED - verify before retry', '', '', '', '', started]]);
      SpreadsheetApp.flush();
      try {
        const url = 'https://domdetailer.com/api2/checkDomain.php?app=SheetsResearch&domain='
          + encodeURIComponent(item.host);
        const response = UrlFetchApp.fetch(url, {
          headers: { apikey: key.trim() },
          muteHttpExceptions: true,
          followRedirects: false,
        });
        const status = response.getResponseCode();
        if (status < 200 || status >= 300) {
          output.setValues([[`HTTP ${status} - review before retry`, '', '', '', '', started]]);
          break;
        }
        const data = JSON.parse(response.getContentText());
        const fields = ['mozDA', 'majesticTF', 'majesticCF', 'mozSpam'];
        if (!data || fields.some(field => !Number.isFinite(data[field]))) {
          throw new Error('Unexpected metric response');
        }
        output.setValues([['OK', ...fields.map(field => data[field]), started]]);
        SpreadsheetApp.flush();
        completed++;
      } catch {
        output.setValues([['UNCERTAIN - review account', '', '', '', '', started]]);
        SpreadsheetApp.flush();
        break;
      }
    }
  } finally {
    lock.releaseLock();
  }
  ui.alert(`${completed} rows completed. Review status cells before any further checks.`);
}

Select one domain cell in column A and choose DomDetailer → Check selected new domains. Confirm the request count. On success, B:G contains OK, DA, TF, CF, raw Spam Score and the attempt timestamp.

Duplicate input domains already associated with a status are skipped. The script stops at the first HTTP or response error; untouched rows stay available for a later deliberate batch. It does not automatically retry or run on edit, open or a schedule. Opening the sheet only creates the menu.

If a row says ATTEMPTED or UNCERTAIN, inspect the account and the response before retrying. A timeout does not prove that no credit was consumed. To retry deliberately, resolve the cause and clear that row's Status cell only after checking the earlier outcome.

The script writes B:G for selected eligible rows, so reserve those columns for its output. Protect the status history and avoid changing input rows while a batch is running. The lock coordinates script runs, not manual edits by collaborators.

Each successful domain check costs one credit. The confirmation bounds the attempts for that run; 20 successful checks consume 20 credits. Ordinary recalculation of the stored values costs nothing because no cell function calls the API.

1,000 unique successful requests consume 1,000 credits. That is about $0.79–$1.36 in consumed credit value at the current pack rates. Credits are bought in packs: the entry pack is $34 for 25,000 credits. This is not a $0.79 checkout option. Compare the current DomDetailer credit packs.

Apps Script execution and URL Fetch quotas still apply. Start with small batches; an execution limit can leave a row marked ATTEMPTED. Keep that marker until you have investigated it. For larger lists, use the checkpoint-conscious Python workflow or Windows desktop app.

Use the response and CSV within the service terms. API access does not grant redistribution rights. Keep keys out of shared code, terminal logs and public files.