Time in Status Data Export Through API - Google Sheets

https://<site-name>/gateway/api/svc/<product>/apps/<app-id>/<path>

// ============================================================
//  JIRA → Google Sheets  |  OAuth 3LO with token refresh
//  Paste this entire file into Apps Script (Extensions → Apps Script)
// ============================================================

// ── 1. CONFIGURATION ────────────────────────────────────────
// Fill in your values here. Leave tokens blank if you prefer
// to set them via the menu (recommended for security).

const CONFIG = {
  // Your Atlassian OAuth 2.0 app credentials
  // Found at: https://developer.atlassian.com/console/myapps/
  CLIENT_ID:     "",   // e.g. "m507qTf5IM8PNpE0WCYKyEj0RQYddbGq"
  CLIENT_SECRET: "",   // e.g. "ATOAxxxxxx..."

  // Name of the sheet tab where data will be written
  SHEET_NAME: "Jira Data",
};

// NOTE: DATA_FEED_URL is stored securely in Script Properties.
// Set it via: 🔄 Jira Sync → ⚙️ Save Data Feed URL

// Atlassian token endpoint
const TOKEN_URL = "https://auth.atlassian.com/oauth/token";

// ── 2. TOKEN HELPERS (stored securely in Script Properties) ──

function getProps() {
  return PropertiesService.getScriptProperties();
}

function saveTokens(accessToken, refreshToken) {
  const props = getProps();
  props.setProperty("ACCESS_TOKEN",  accessToken);
  props.setProperty("REFRESH_TOKEN", refreshToken);
  Logger.log("✅ Tokens saved to Script Properties.");
}

function getAccessToken()  { return getProps().getProperty("ACCESS_TOKEN");  }
function getRefreshToken() { return getProps().getProperty("REFRESH_TOKEN"); }
function getDataFeedUrl()  { return getProps().getProperty("DATA_FEED_URL");  }

// ── 3. TOKEN EXPIRY CHECK ─────────────────────────────────────

// How many days before expiry to start warning
const WARN_DAYS_BEFORE = 7;

/**
 * Decodes a JWT and returns its payload as an object.
 * Works without any external library — JWTs are just base64url-encoded JSON.
 */
function decodeJwtPayload(token) {
  try {
    const base64Url = token.split(".")[1];
    // base64url → base64
    const base64 = base64Url.replace(/-/g, "+").replace(/_/g, "/");
    const json    = Utilities.newBlob(Utilities.base64Decode(base64)).getDataAsString();
    return JSON.parse(json);
  } catch (e) {
    Logger.log("⚠️ Could not decode JWT: " + e.message);
    return null;
  }
}

/**
 * Returns the number of days until the refresh token expires.
 * Returns null if the token cannot be decoded.
 */
function getRefreshTokenDaysLeft() {
  const token = getRefreshToken();
  if (!token) return null;

  const payload = decodeJwtPayload(token);
  if (!payload || !payload.exp) return null;

  const expiresAt  = new Date(payload.exp * 1000);
  const now        = new Date();
  const msLeft     = expiresAt - now;
  return Math.floor(msLeft / (1000 * 60 * 60 * 24));
}

/**
 * Checks the refresh token expiry and:
 *  - Sends an email warning if within WARN_DAYS_BEFORE days
 *  - Shows a popup if called from the UI (showUiAlert = true)
 * Called automatically by syncJiraData() and by the daily warning trigger.
 */
function checkTokenExpiry(showUiAlert) {
  const daysLeft = getRefreshTokenDaysLeft();

  if (daysLeft === null) {
    Logger.log("⚠️ Could not determine refresh token expiry.");
    return;
  }

  const expiryDate = new Date(decodeJwtPayload(getRefreshToken()).exp * 1000)
    .toLocaleDateString();

  Logger.log(`ℹ️ Refresh token expires in ${daysLeft} day(s) (${expiryDate}).`);

  if (daysLeft < 0) {
    const msg = `🔴 Your refresh token EXPIRED on ${expiryDate}.\nYou must re-authorize via the 3LO flow and save new tokens.`;
    Logger.log("🔴 " + msg);
    if (showUiAlert) SpreadsheetApp.getUi().alert("Token Expired ❌", msg, SpreadsheetApp.getUi().ButtonSet.OK);
    sendExpiryEmail("EXPIRED", expiryDate, daysLeft);

  } else if (daysLeft <= WARN_DAYS_BEFORE) {
    const msg = `⚠️ Your refresh token expires in ${daysLeft} day(s) (${expiryDate}).\nPlease re-authorize soon and save new tokens via the menu.`;
    Logger.log("⚠️ " + msg);
    if (showUiAlert) SpreadsheetApp.getUi().alert("Token Expiry Warning ⚠️", msg, SpreadsheetApp.getUi().ButtonSet.OK);
    sendExpiryEmail("WARNING", expiryDate, daysLeft);
  }
}

/**
 * Sends an email to the script owner when the token is expiring soon or expired.
 * Only sends once per day to avoid spam (tracked via Script Properties).
 */
function sendExpiryEmail(type, expiryDate, daysLeft) {
  const props   = getProps();
  const today   = new Date().toDateString();
  const lastKey = "LAST_EXPIRY_EMAIL_" + type;

  // Don't send more than once per day
  if (props.getProperty(lastKey) === today) return;
  props.setProperty(lastKey, today);

  const email   = Session.getEffectiveUser().getEmail();
  const subject = type === "EXPIRED"
    ? "🔴 Jira Sync: Refresh token has EXPIRED"
    : `⚠️ Jira Sync: Refresh token expires in ${daysLeft} day(s)`;

  const body = type === "EXPIRED"
    ? `Your Jira sync refresh token expired on ${expiryDate}.\n\nThe daily sync has stopped working. Please re-authorize via the 3LO flow and save new tokens using:\n  🔄 Jira Sync → ⚙️ Save tokens`
    : `Your Jira sync refresh token will expire on ${expiryDate} (in ${daysLeft} day(s)).\n\nPlease re-authorize soon via the 3LO flow and save new tokens using:\n  🔄 Jira Sync → ⚙️ Save tokens`;

  GmailApp.sendEmail(email, subject, body);
  Logger.log(`📧 Expiry warning email sent to ${email}.`);
}

/**
 * Menu action: show token status in a popup.
 */
function showTokenStatus() {
  const daysLeft = getRefreshTokenDaysLeft();
  const ui = SpreadsheetApp.getUi();

  if (daysLeft === null) {
    ui.alert("Token Status", "No refresh token found. Please save your tokens via ⚙️ Save tokens.", ui.ButtonSet.OK);
    return;
  }

  const expiryDate = new Date(decodeJwtPayload(getRefreshToken()).exp * 1000).toLocaleDateString();

  let icon, status;
  if (daysLeft < 0) {
    icon = "🔴"; status = `EXPIRED on ${expiryDate}`;
  } else if (daysLeft <= WARN_DAYS_BEFORE) {
    icon = "⚠️"; status = `Expires in ${daysLeft} day(s) — ${expiryDate}`;
  } else {
    icon = "✅"; status = `Valid for ${daysLeft} more day(s) — expires ${expiryDate}`;
  }

  ui.alert("Token Status " + icon, `Refresh token: ${status}`, ui.ButtonSet.OK);
}

// ── 4. REFRESH THE ACCESS TOKEN ──────────────────────────────

function refreshAccessToken() {
  const refreshToken = getRefreshToken();
  if (!refreshToken) throw new Error("No refresh token stored. Run Setup → Save Tokens first.");

  const clientId     = CONFIG.CLIENT_ID     || getProps().getProperty("CLIENT_ID");
  const clientSecret = CONFIG.CLIENT_SECRET || getProps().getProperty("CLIENT_SECRET");
  if (!clientId || !clientSecret) {
    throw new Error("CLIENT_ID and CLIENT_SECRET are required. Set them in CONFIG or via the menu.");
  }

  const payload = {
    grant_type:    "refresh_token",
    client_id:     clientId,
    client_secret: clientSecret,
    refresh_token: refreshToken,
  };

  const response = UrlFetchApp.fetch(TOKEN_URL, {
    method:      "post",
    contentType: "application/x-www-form-urlencoded",
    payload:     payload,
    muteHttpExceptions: true,
  });

  const code = response.getResponseCode();
  const body = JSON.parse(response.getContentText());

  if (code !== 200) {
    throw new Error(`Token refresh failed (${code}): ${JSON.stringify(body)}`);
  }

  // Atlassian rotates the refresh token on each use
  saveTokens(body.access_token, body.refresh_token || refreshToken);
  Logger.log("🔄 Access token refreshed successfully.");
  return body.access_token;
}

// ── 4. FETCH DATA FROM THE API ───────────────────────────────

function fetchDataFeed(accessToken) {
  const url = CONFIG.DATA_FEED_URL || getDataFeedUrl();
  if (!url) throw new Error("No Data Feed URL stored. Use 🔄 Jira Sync → ⚙️ Save Data Feed URL.");

  const response = UrlFetchApp.fetch(url, {
    method:  "get",
    headers: { Authorization: "Bearer " + accessToken },
    muteHttpExceptions: true,
  });

  const code = response.getResponseCode();

  // 401 = token expired → refresh and retry once
  if (code === 401) {
    Logger.log("⚠️ Access token expired. Refreshing...");
    const newToken = refreshAccessToken();
    return fetchDataFeed(newToken);
  }

  if (code !== 200) {
    throw new Error(`API request failed (${code}): ${response.getContentText()}`);
  }

  return JSON.parse(response.getContentText());
}

// ── 5. WRITE DATA TO SHEET ────────────────────────────────────

function writeToSheet(data) {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet = ss.getSheetByName(CONFIG.SHEET_NAME);

  // Create the sheet if it doesn't exist
  if (!sheet) {
    sheet = ss.insertSheet(CONFIG.SHEET_NAME);
    Logger.log(`📄 Created sheet: "${CONFIG.SHEET_NAME}"`);
  }

  sheet.clearContents();

  // ── Normalize the API response ──
  // The datafeed may return an array at the top level, or inside a key.
  // Adjust the path below if your feed has a different structure.
  let rows = [];

  if (Array.isArray(data)) {
    rows = data;
  } else if (data.items && Array.isArray(data.items)) {
    rows = data.items;
  } else if (data.data && Array.isArray(data.data)) {
    rows = data.data;
  } else if (data.results && Array.isArray(data.results)) {
    rows = data.results;
  } else {
    // Fallback: write raw JSON so you can inspect the structure
    sheet.getRange(1, 1).setValue("Raw API Response (adjust writeToSheet to match your structure):");
    sheet.getRange(2, 1).setValue(JSON.stringify(data, null, 2));
    Logger.log("⚠️ Could not detect row array. Raw JSON written to sheet. Check the structure and update writeToSheet().");
    return;
  }

  if (rows.length === 0) {
    sheet.getRange(1, 1).setValue("No data returned by the API.");
    return;
  }

  // Build header row from the keys of the first object
  const headers = Object.keys(rows[0]);
  const output  = [headers];

  // Build data rows
  rows.forEach(row => {
    const rowValues = headers.map(h => {
      const val = row[h];
      return (val !== null && typeof val === "object") ? JSON.stringify(val) : val;
    });
    output.push(rowValues);
  });

  // Write everything in one call (fast)
  sheet.getRange(1, 1, output.length, output[0].length).setValues(output);

  // Style the header row
  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setFontWeight("bold");
  headerRange.setBackground("#4a86e8");
  headerRange.setFontColor("#ffffff");
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, headers.length);

  // Timestamp
  sheet.getRange(output.length + 2, 1).setValue("Last updated: " + new Date().toLocaleString());

  Logger.log(`✅ Written ${rows.length} rows × ${headers.length} columns to "${CONFIG.SHEET_NAME}".`);
  SpreadsheetApp.getUi().alert(`✅ Done! ${rows.length} rows imported to "${CONFIG.SHEET_NAME}".`);
}

// ── 6. MAIN ENTRY POINT ───────────────────────────────────────

function syncJiraData() {
  try {
    let token = getAccessToken();
    if (!token) throw new Error("No access token stored. Use Setup → Save Tokens first.");

    const url = CONFIG.DATA_FEED_URL || getDataFeedUrl();
    if (!url) throw new Error("No Data Feed URL stored. Use 🔄 Jira Sync → ⚙️ Save Data Feed URL.");

    // Warn if refresh token is close to expiry
    checkTokenExpiry(true);

    Logger.log("🚀 Fetching data from Jira datafeed...");
    const data = fetchDataFeed(token);
    writeToSheet(data);
  } catch (e) {
    Logger.log("❌ Error: " + e.message);
    SpreadsheetApp.getUi().alert("❌ Error:\n\n" + e.message);
  }
}

// ── 7. MENU + SETUP UI ────────────────────────────────────────

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("🔄 Jira Sync")
    .addItem("Sync now",                    "syncJiraData")
    .addSeparator()
    .addItem("⚙️ Save tokens",              "promptSaveTokens")
    .addItem("⚙️ Save OAuth creds",         "promptSaveOAuthCreds")
    .addItem("⚙️ Save Data Feed URL",       "promptSaveDataFeedUrl")
    .addSeparator()
    .addItem("🔑 Check token status",       "showTokenStatus")
    .addSeparator()
    .addItem("⏰ Schedule daily sync",      "createDailyTrigger")
    .addItem("⏰ Schedule expiry check",    "createExpiryCheckTrigger")
    .addItem("🗑️ Remove all triggers",     "removeAllTriggers")
    .addToUi();
}

function promptSaveTokens() {
  const ui = SpreadsheetApp.getUi();

  const accessResp = ui.prompt(
    "Save Access Token",
    "Paste your current access token:",
    ui.ButtonSet.OK_CANCEL
  );
  if (accessResp.getSelectedButton() !== ui.Button.OK) return;

  const refreshResp = ui.prompt(
    "Save Refresh Token",
    "Paste your refresh token:",
    ui.ButtonSet.OK_CANCEL
  );
  if (refreshResp.getSelectedButton() !== ui.Button.OK) return;

  saveTokens(accessResp.getResponseText().trim(), refreshResp.getResponseText().trim());
  ui.alert("✅ Tokens saved securely in Script Properties.");
}

function promptSaveOAuthCreds() {
  const ui = SpreadsheetApp.getUi();
  const props = getProps();

  const idResp = ui.prompt(
    "Save OAuth Client ID",
    "Paste your Atlassian OAuth Client ID:",
    ui.ButtonSet.OK_CANCEL
  );
  if (idResp.getSelectedButton() !== ui.Button.OK) return;

  const secResp = ui.prompt(
    "Save OAuth Client Secret",
    "Paste your Atlassian OAuth Client Secret:",
    ui.ButtonSet.OK_CANCEL
  );
  if (secResp.getSelectedButton() !== ui.Button.OK) return;

  props.setProperty("CLIENT_ID",     idResp.getResponseText().trim());
  props.setProperty("CLIENT_SECRET", secResp.getResponseText().trim());
  ui.alert("✅ OAuth credentials saved securely.");
}

function promptSaveDataFeedUrl() {
  const ui    = SpreadsheetApp.getUi();
  const props = getProps();

  // Pre-fill the current URL so the user can see and edit it
  const current = props.getProperty("DATA_FEED_URL") || "";
  const hint    = current
    ? "Current URL (paste a new one to replace it):\n" + current
    : "Paste the Data Feed URL from your Jira report:";

  const resp = ui.prompt("Save Data Feed URL", hint, ui.ButtonSet.OK_CANCEL);
  if (resp.getSelectedButton() !== ui.Button.OK) return;

  const newUrl = resp.getResponseText().trim();
  if (!newUrl) { ui.alert("No URL entered — nothing was saved."); return; }

  props.setProperty("DATA_FEED_URL", newUrl);
  ui.alert("✅ Data Feed URL saved.\n\nYou can now run Sync now.");
}

// ── 8. TIME-BASED TRIGGERS ───────────────────────────────────

function createDailyTrigger() {
  // Remove existing sync triggers first to avoid duplicates
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === "syncJiraData")
    .forEach(t => ScriptApp.deleteTrigger(t));

  ScriptApp.newTrigger("syncJiraData")
    .timeBased()
    .everyDays(1)
    .atHour(8)   // 08:00 in the script's timezone
    .create();

  SpreadsheetApp.getUi().alert("⏰ Daily sync scheduled for 8 AM.");
}

/**
 * Sets up a daily trigger that checks token expiry and sends
 * an email warning when the refresh token is close to expiring.
 */
function createExpiryCheckTrigger() {
  // Remove existing expiry check triggers first
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === "dailyExpiryCheck")
    .forEach(t => ScriptApp.deleteTrigger(t));

  ScriptApp.newTrigger("dailyExpiryCheck")
    .timeBased()
    .everyDays(1)
    .atHour(9)   // 09:00 — runs after the sync trigger
    .create();

  SpreadsheetApp.getUi().alert("⏰ Daily token expiry check scheduled for 9 AM.\nYou will receive an email if the token is expiring within " + WARN_DAYS_BEFORE + " days.");
}

/**
 * Called by the daily expiry check trigger.
 * Does NOT show a UI popup (runs headlessly); sends email only.
 */
function dailyExpiryCheck() {
  checkTokenExpiry(false);
}

function removeAllTriggers() {
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  SpreadsheetApp.getUi().alert("🗑️ All triggers removed.");
}

🔄 Jira Sync → ⚙️ Save OAuth creds

🔄 Jira Sync → ⚙️ Save tokens

🔄 Jira Sync → ⚙️ Save Data Feed URL

🔄 Jira Sync → Sync now

🔄 Jira Sync → ⏰ Schedule daily sync

🔄 Jira Sync → ⏰ Schedule expiry check