Process a list of DC appKeys with the Atlassian Marketplace API to find Cloud equivalents, pricing, etc.

compareCloudKeysWithDC.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Atlassian Tools')
    .addItem('Stage 1: Create Cloud/DC sheets from selection', 'createCloudDcSheetsFromSelection')
    .addItem('Stage 2: Build consolidated comparison', 'buildComparisonFromCloudDcSheets')
    .addToUi();
}

/**
 * Stage 1: For each selected addon key, create two sheets:
 * {key}_Cloud and {key}_DC, each with ImportJSON formulas.
 */
function createCloudDcSheetsFromSelection() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sel = ss.getActiveRange();
  const addonKeys = sel.getValues().flat().filter(k => typeof k === 'string' && k.trim() !== '');
  if (addonKeys.length === 0) {
    SpreadsheetApp.getUi().alert('No addon keys in selection.');
    return;
  }

  const base = 'https://marketplace.atlassian.com/rest/2/addons/';

  addonKeys.forEach((rawKey) => {
    const key = String(rawKey).trim();
    if (!key) return;

    const cloudSheetName = sanitizeSheetName(key + "_Cloud");
    const dcSheetName = sanitizeSheetName(key + "_DC");

    let cloudSh = ss.getSheetByName(cloudSheetName);
    if (!cloudSh) cloudSh = ss.insertSheet(cloudSheetName); else cloudSh.clear();

    let dcSh = ss.getSheetByName(dcSheetName);
    if (!dcSh) dcSh = ss.insertSheet(dcSheetName); else dcSh.clear();

    const cloudUrl = `${base}${encodeURIComponent(key)}/pricing/cloud/live`;
    const dcUrl = `${base}${encodeURIComponent(key)}/pricing/server/live`;

    cloudSh.getRange('A1').setFormula(`=ImportJSON("${cloudUrl}")`);
    dcSh.getRange('A1').setFormula(`=ImportJSON("${dcUrl}")`);

    cloudSh.setFrozenRows(1);
    dcSh.setFrozenRows(1);
  });
}

/**
 * Stage 2: Merge Cloud/DC sheets into AddonComparison sheet.
 */
function buildComparisonFromCloudDcSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const outName = "AddonComparison";
  let out = ss.getSheetByName(outName);
  if (!out) out = ss.insertSheet(outName); else out.clear();

  out.appendRow([
    "Addon Key",
    "Edition Description",
    "User Count",
    "License Type",
    "Cloud Amount (USD)",
    "Cloud Renewal (USD)",
    "Cloud Local Currency",
    "DC Amount (USD)",
    "DC Renewal (USD)",
    "DC Local Currency",
    "Can Change Prices"
  ]);

  const sheets = ss.getSheets();

  sheets.forEach(sh => {
    const name = sh.getName();
    if (!name.endsWith("_Cloud")) return;

    const key = name.replace(/_Cloud$/, "");
    const dcSheet = ss.getSheetByName(key + "_DC");
    if (!dcSheet) return;

    const cloudData = sheetToRecords(sh);
    const dcData = sheetToRecords(dcSheet);

    const allEditions = new Set([...Object.keys(cloudData), ...Object.keys(dcData)]);
    allEditions.forEach(edition => {
      const c = cloudData[edition] || {};
      const d = dcData[edition] || {};
      out.appendRow([
        key,
        edition,
        c.userCount || d.userCount || "",
        c.licenseType || d.licenseType || "",
        c.amount || "",
        c.renewal || "",
        c.localCurrency || "",
        d.amount || "",
        d.renewal || "",
        d.localCurrency || "",
        c.canChange || d.canChange || ""
      ]);
    });
  });

  autoResizeAll(out);
}

/* --- Helpers --- */

function sheetToRecords(sheet) {
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return {};

  const headers = values[0].map(h => String(h).toLowerCase());

  const editionIdx    = headers.findIndex(h => h.includes("editiondescription"));
  const userIdx       = headers.findIndex(h => h.includes("unitcount"));
  const licenseIdx    = headers.findIndex(h => h.includes("licensetype"));
  const amtIdx        = headers.findIndex(h => h.endsWith("amount") && !h.includes("renewal"));
  const renIdx        = headers.findIndex(h => h.includes("renewalamount") && !h.includes("local"));
  const localCurIdx   = headers.findIndex(h => h.includes("localcurrency") && !h.includes("renewal"));
  const localAmtIdx   = headers.findIndex(h => h.includes("localcurrencyamount") && !h.includes("renewal"));
  const localRenIdx   = headers.findIndex(h => h.includes("localcurrencyrenewalamount"));
  const canChangeIdx  = headers.findIndex(h => h.includes("canchangeprices"));

  const records = {};
  for (let r = 1; r < values.length; r++) {
    const row = values[r];
    const edition = editionIdx >= 0 ? row[editionIdx] : "";
    if (!edition) continue;

    records[edition] = {
      userCount: userIdx >= 0 ? row[userIdx] : "",
      licenseType: licenseIdx >= 0 ? row[licenseIdx] : "",
      amount: amtIdx >= 0 ? row[amtIdx] : "",
      renewal: renIdx >= 0 ? row[renIdx] : "",
      localCurrency: localCurIdx >= 0 ? row[localCurIdx] : "",
      localAmt: localAmtIdx >= 0 ? row[localAmtIdx] : "",
      localRenewal: localRenIdx >= 0 ? row[localRenIdx] : "",
      canChange: canChangeIdx >= 0 ? row[canChangeIdx] : ""
    };
  }
  return records;
}

function sanitizeSheetName(name) {
  return name.replace(/[:\\/?*\[\]]/g, '_').substring(0, 99) || 'addon';
}

function autoResizeAll(sheet) {
  const lastCol = sheet.getLastColumn();
  for (let c = 1; c <= lastCol; c++) {
    sheet.autoResizeColumn(c);
  }
}