Time in Status Data Export Through API - Google Sheets

// ============================================================
//  JIRA → Google Sheets  |  OAuth 3LO with token refresh
//  Paginated: fetches the report in chunks and writes all rows.
//  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",

  // Work items to request per page (chunk). Default 100.
  // Change this to any value, or set it from the menu:
  //   🔄 Jira Sync → ⚙️ Set page size
  PAGE_SIZE: 100,
};

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

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

// ── PAGINATION PARAMETERS ────────────────────────────────────
// Query-string parameters the script adds to the data source URL.
// PAGE_SIZE_PARAM sets the chunk size. PAGE_TOKEN_PARAM carries the
// continuation token returned in meta.nextPageToken.
//

const PAGE_SIZE_PARAM  = "pageSize";
const PAGE_TOKEN_PARAM = "nextPageToken";

// Safety guard: stop after this many pages even if the API keeps
// returning a continuation token (prevents an accidental infinite loop).
const MAX_PAGES = 1000;

// ── 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 getDataSourceUrl()  { return getProps().getProperty("DATA_SOURCE_URL");  }

// Page size: Script Property override → CONFIG default → 100.
function getPageSize() {
  const stored = getProps().getProperty("PAGE_SIZE");
  const n = stored ? parseInt(stored, 10) : CONFIG.PAGE_SIZE;
  return (n && n > 0) ? n : 100;
}

// ── 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;
}

// ── 5. FETCH DATA FROM THE API (paginated) ───────────────────

// Append query parameters to a URL, preserving any it already has.
function withParams(url, params) {
  const parts = Object.keys(params)
    .filter(k => params[k] !== null && params[k] !== undefined && params[k] !== "")
    .map(k => encodeURIComponent(k) + "=" + encodeURIComponent(params[k]));
  if (parts.length === 0) return url;
  const sep = url.indexOf("?") === -1 ? "?" : "&";
  return url + sep + parts.join("&");
}

// Find the array of work items in the response, whatever its key.
function extractRows(payload) {
  if (Array.isArray(payload)) return payload;
  if (payload && Array.isArray(payload.data))    return payload.data;
  if (payload && Array.isArray(payload.items))   return payload.items;
  if (payload && Array.isArray(payload.results)) return payload.results;
  if (payload && Array.isArray(payload.values))  return payload.values;
  return null; // unknown shape
}

function extractMeta(payload) {
  return (payload && payload.meta) ? payload.meta : null;
}

/**
 * Fetches a single page. Sends pageSize on the first request and the
 * continuation token on later requests. Refreshes the access token and
 * retries this page once on a 401.
 * Returns { token, payload } — token may have been refreshed mid-call.
 */
function fetchPage(accessToken, pageToken) {
  const baseUrl = CONFIG.DATA_SOURCE_URL || getDataSourceUrl();
  if (!baseUrl) throw new Error("No Data Source URL stored. Use 🔄 Jira Sync → ⚙️ Save Data Source URL.");

  // Send pageSize on the first request only. If the API requires
  // pageSize on every page, move it out of the "if" below.
  const params = {};
  if (pageToken) {
    params[PAGE_TOKEN_PARAM] = pageToken;
  } else {
    params[PAGE_SIZE_PARAM] = getPageSize();
  }
  const url = withParams(baseUrl, params);

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

  const code = response.getResponseCode();

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

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

  return { token: accessToken, payload: JSON.parse(response.getContentText()) };
}

/**
 * Fetches every page and returns all rows combined.
 * Stops when meta.isLast is true, when no continuation token is returned,
 * or when the response has no meta (single-response feed).
 * Returns { rows, unknownPayload }.
 */
function fetchAllData(accessToken) {
  let token     = accessToken;
  let pageToken = null;
  let allRows   = [];
  let pageCount = 0;

  do {
    const result  = fetchPage(token, pageToken);
    token         = result.token;      // may have been refreshed mid-loop
    const payload = result.payload;
    pageCount++;

    const rows = extractRows(payload);
    if (rows === null) {
      if (pageCount === 1) {
        // First response has an unfamiliar shape — hand it to the fallback.
        return { rows: [], unknownPayload: payload };
      }
      Logger.log("⚠️ Unexpected response shape on a later page. Stopping with the rows collected so far.");
      break;
    }
    allRows = allRows.concat(rows);

    const meta = extractMeta(payload);
    // Continue only when meta says more pages remain AND a token is present.
    if (meta && meta.isLast !== true && meta.nextPageToken) {
      pageToken = meta.nextPageToken;
    } else {
      pageToken = null;
    }

    Logger.log(`📄 Page ${pageCount}: +${rows.length} rows (total ${allRows.length}). More: ${pageToken ? "yes" : "no"}.`);

    if (pageCount >= MAX_PAGES && pageToken) {
      Logger.log(`⚠️ Reached MAX_PAGES (${MAX_PAGES}). Stopping; data may be incomplete.`);
      pageToken = null;
    }
  } while (pageToken);

  return { rows: allRows, unknownPayload: null };
}

// ── 6. WRITE DATA TO SHEET ────────────────────────────────────

function writeToSheet(rows) {
  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();

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

  // Build header row from the keys of the first work item
  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}".`);
}

// Fallback when the row array can't be found: write the raw JSON so
// you can inspect the structure and adjust extractRows() if needed.
function writeRawFallback(payload) {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) sheet = ss.insertSheet(CONFIG.SHEET_NAME);
  sheet.clearContents();
  sheet.getRange(1, 1).setValue("Raw API Response (could not detect the work-item array):");
  sheet.getRange(2, 1).setValue(JSON.stringify(payload, null, 2));
  Logger.log("⚠️ Could not detect the row array. Raw JSON written to the sheet.");
  SpreadsheetApp.getUi().alert("⚠️ Could not detect the data array in the API response. Raw JSON was written to the sheet so you can inspect the structure.");
}

// ── 7. 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_SOURCE_URL || getDataSourceUrl();
    if (!url) throw new Error("No Data Source URL stored. Use 🔄 Jira Sync → ⚙️ Save Data Source URL.");

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

    Logger.log(`🚀 Fetching data from Jira datasource (page size ${getPageSize()})...`);
    const result = fetchAllData(token);

    if (result.unknownPayload) {
      writeRawFallback(result.unknownPayload);
    } else {
      writeToSheet(result.rows);
    }
  } catch (e) {
    Logger.log("❌ Error: " + e.message);
    SpreadsheetApp.getUi().alert("❌ Error:\n\n" + e.message);
  }
}

// ── 8. 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 Source URL",       "promptSaveDataSourceUrl")
    .addItem("⚙️ Set page size",            "promptSavePageSize")
    .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 promptSaveDataSourceUrl() {
  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_SOURCE_URL") || "";
  const hint    = current
    ? "Current URL (paste a new one to replace it):\n" + current
    : "Paste the Data Source URL from your Jira report:";

  const resp = ui.prompt("Save Data Source 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_SOURCE_URL", newUrl);
  ui.alert("✅ Data Source URL saved.\n\nYou can now run Sync now.");
}

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

  const current = getPageSize();
  const resp = ui.prompt(
    "Set page size",
    `Work items per page (chunk). Current: ${current}. Default: 100.\nEnter a whole number of 1 or more:`,
    ui.ButtonSet.OK_CANCEL
  );
  if (resp.getSelectedButton() !== ui.Button.OK) return;

  const raw = resp.getResponseText().trim();
  const n   = parseInt(raw, 10);
  if (isNaN(n) || n < 1) {
    ui.alert("Page size not saved. Enter a whole number of 1 or more.");
    return;
  }

  props.setProperty("PAGE_SIZE", String(n));
  ui.alert(`✅ Page size set to ${n}. It applies the next time you run Sync now.`);
}

// ── 9. 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.");
}