Import Sharelink Data With Google Apps Script

/* ==========================================================================
 * Airbridge Sharelink → Google Sheets import
 *
 * You only need to edit the [Settings] section. Everything below it works as is.
 * After editing, save (Cmd+S or Ctrl+S) and reload the sheet.
 *
 * Common edits
 *   Add a link                                   → Add one line to TARGETS
 *   Change the sheet or position for the data    → Edit sheet and cell on that line
 *   Change the daily refresh time                → Edit DAILY_HOUR, then turn daily refresh on again from the menu
 *   Change menu names or emojis                  → Edit MENU
 *   Frequent "Server is still calculating" fails → Increase WARMUP_WAIT_MS
 *   Want a new query on every run                → Add useCache=false to a /v3/ URL (see the url notes below)
 * ========================================================================== */

// ------------------------------------------------------------------ [Settings]

// List of links to import. One link per line.
//
//   sheet : Name of the sheet (tab) to put the data in. If a sheet with that name exists, the data goes there;
//           if not, a new sheet is created.
//   cell  : Cell where the data starts. Data fills right and down from this cell. Defaults to A1 if omitted.
//           If the sheet used an IMPORTDATA formula, enter the cell where that formula was.
//           (Delete the formula first. If the cell has content, it is not overwritten and is reported as a failure.)
//   url   : Sharelink URL copied from Airbridge. Paste it as is, whether /v2/ or /v3/.
//           Adding useCache=false starts a new query on every run (/v3/ URLs only).
//           If the URL contains ?, append &useCache=false; otherwise append ?useCache=false.
//           The run waits until the new query finishes, so it takes that much longer,
//           and if there are several such links, it waits for them one by one.
//
// Examples
//   { sheet: 'Raw_Actuals',   url: 'https://report.airbridge.io/v3/TOKEN.csv?language=en' },
//   { sheet: 'Raw_Retention', url: 'https://report.airbridge.io/v3/TOKEN.csv?language=en' },
//   { sheet: 'Dashboard', cell: 'H3', url: 'https://report.airbridge.io/v3/TOKEN.csv?language=en' },
//   { sheet: 'Raw_Fresh',     url: 'https://report.airbridge.io/v3/TOKEN.csv?language=en&useCache=false' },
//
// If you miss a comma (,) at the end of a line or a quote ('), nothing works, so follow the example format exactly.
// When putting two links in one sheet, space their cells far enough apart that they don't overlap.
const TARGETS = [
  { sheet: 'Actuals', cell: 'A1', url: 'PASTE_YOUR_SHARELINK_URL_HERE' },
];

// Hour of the daily refresh. Enter a number from 0 to 23 (24-hour clock). 9 = 9 AM, 15 = 3 PM.
// Google picks a moment within one hour from that time and runs it at the same time every day. Minutes cannot be set.
// The time zone follows the Time zone in Project Settings (gear icon on the left of the Apps Script editor).
const DAILY_HOUR = 9;

// Menu names shown at the top of the sheet. Feel free to change the text and emojis inside the quotes.
const MENU = {
  title: '📊 Sharelink',
  refresh: '🔄 Refresh now',
  dailyOn: '⏰ Turn on daily refresh at ' + DAILY_HOUR + ':00',
  dailyOff: '⏹️ Turn off daily refresh',
};

// Time to wait after starting the calculation on the server before fetching the result (1000 = 1 second).
// If heavy reports often fail with "Server is still calculating", increase this to around 40000.
const WARMUP_WAIT_MS = 25000;

// Interval and number of retries when the data still isn't ready. Usually no need to change.
const RETRY_WAIT_MS = 15000;
const MAX_ROUNDS = 4;

// ------------------------------------------------- No need to edit anything below

const STATE_HEADER = 'x-airbridge-sharelink-state';
const SIZE_KEY_PREFIX = 'written-size:';

/** Creates the menu at the top when the sheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu(MENU.title)
    .addItem(MENU.refresh, 'refreshNow')
    .addSeparator()
    .addItem(MENU.dailyOn, 'autoDaily')
    .addItem(MENU.dailyOff, 'autoOff')
    .addToUi();
}

/** Entry point shared by the menu and the daily trigger. */
function refreshNow() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.toast('Refreshing ' + TARGETS.length + ' link(s). This takes about a minute.', MENU.title, 120);

  // A plain v3 URL only starts the calculation on the first call if there is no cache, so call each one once first to warm it up.
  // A useCache=false request starts a new query and waits for the result by itself, so warming it up would run the query twice.
  const warmupTargets = TARGETS.filter(function (target) {
    return isV3_(target) && !skipsCache_(target);
  });
  warmupTargets.forEach(function (target) {
    fetchOnce_(target.url);
  });
  if (warmupTargets.length > 0) Utilities.sleep(WARMUP_WAIT_MS);

  var pending = TARGETS.slice();
  const failed = [];
  var done = 0;

  for (var round = 1; round <= MAX_ROUNDS && pending.length > 0; round++) {
    const stillWaiting = [];

    pending.forEach(function (target) {
      const skipCache = skipsCache_(target);
      // Sending useCache=false again would start yet another query, so once we have to wait, later checks use a plain request.
      const requestUrl = skipCache && round > 1 ? withoutUseCache_(target.url) : target.url;
      const result = fetchOnce_(requestUrl, skipCache && round === 1);
      if (result.waiting) {
        stillWaiting.push(target);
        return;
      }
      if (!result.rows) {
        failed.push(labelOf_(target) + ' - ' + result.message);
        return;
      }
      try {
        write_(target, result.rows);
        done++;
      } catch (e) {
        failed.push(labelOf_(target) + ' - ' + e.message);
      }
    });

    pending = stillWaiting;
    if (pending.length > 0 && round < MAX_ROUNDS) Utilities.sleep(RETRY_WAIT_MS);
  }

  pending.forEach(function (target) {
    failed.push(labelOf_(target) + ' - Server is still calculating');
  });

  const summary = done + ' of ' + TARGETS.length + ' done';
  spreadsheet.toast(failed.length === 0 ? summary : summary + ' / Failed: ' + failed.join(', '), MENU.title, failed.length === 0 ? 5 : 30);
}

function isV3_(target) {
  return /\/v3\//i.test(target.url);
}

/** Checks whether the query of a v3 URL has useCache=false. The value is matched ignoring case. */
function skipsCache_(target) {
  if (!isV3_(target)) return false;
  const queryStart = target.url.indexOf('?');
  if (queryStart < 0) return false;
  const query = target.url.slice(queryStart + 1).split('#')[0];
  return /(?:^|&)useCache=[Ff][Aa][Ll][Ss][Ee](?:&|$)/.test(query);
}

/** Removes only the useCache parameter, leaving other query parameters and the fragment as they are. */
function withoutUseCache_(url) {
  const hashStart = url.indexOf('#');
  const hash = hashStart < 0 ? '' : url.slice(hashStart);
  const beforeHash = hashStart < 0 ? url : url.slice(0, hashStart);
  const queryStart = beforeHash.indexOf('?');
  if (queryStart < 0) return url;

  const base = beforeHash.slice(0, queryStart);
  const params = beforeHash.slice(queryStart + 1).split('&').filter(function (param) {
    return param.split('=')[0].toLowerCase() !== 'usecache';
  });
  return base + (params.length > 0 ? '?' + params.join('&') : '') + hash;
}

function labelOf_(target) {
  return target.sheet + '!' + (target.cell || 'A1');
}

/**
 * v3 is judged by the state header. v2 and per-report URLs, which have no header, are judged by the shape of the body.
 * A normal report has multiple columns, while a notice message comes as a single cell.
 */
function fetchOnce_(url, waitOnError) {
  var response;
  // muteHttpExceptions: 4xx and 5xx come back as responses and are judged by status code below.
  // The catch block only runs when no response arrived at all (connection lost or timeout).
  try {
    response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  } catch (e) {
    // With useCache=false, the server keeps running the query even if the client gives up first, so recheck with a plain request.
    if (waitOnError) return { waiting: true };
    return { message: 'No response' };
  }

  // Decision order. Only in-progress is rechecked in the next round (every RETRY_WAIT_MS, up to MAX_ROUNDS rounds).
  // 4xx and 5xx fail immediately without a retry.
  const state = readState_(response);
  if (state === 'in-progress') return { waiting: true };
  if (response.getResponseCode() === 404) return { message: 'Link not found' };
  if (response.getResponseCode() >= 400) return { message: 'http ' + response.getResponseCode() };

  const text = response.getContentText();
  if (state && state !== 'ok') return { message: text.slice(0, 120) };

  const rows = Utilities.parseCsv(text);
  const isNotice = rows.length <= 1 && (rows.length === 0 || rows[0].length <= 1);
  if (isNotice) return { message: text.slice(0, 120) };

  return { rows: rows };
}

function readState_(response) {
  const headers = response.getAllHeaders();
  for (var key in headers) {
    if (key.toLowerCase() === STATE_HEADER) return String(headers[key]);
  }
  return '';
}

/**
 * Clears and rewrites only the area this script wrote last time, not the whole sheet.
 * If the new area has content this script did not write, it stops without overwriting.
 */
function write_(target, rows) {
  const width = rows.reduce(function (max, row) {
    return Math.max(max, row.length);
  }, 0);

  // setValues requires all rows to have the same length.
  const padded = rows.map(function (row) {
    const copy = row.slice();
    while (copy.length < width) copy.push('');
    return copy;
  });

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(target.sheet) || spreadsheet.insertSheet(target.sheet);
  const start = sheet.getRange(target.cell || 'A1');
  const top = start.getRow();
  const left = start.getColumn();

  const properties = PropertiesService.getDocumentProperties();
  const sizeKey = SIZE_KEY_PREFIX + labelOf_(target);
  const previous = (properties.getProperty(sizeKey) || '0,0').split(',').map(Number);

  const blocker = findBlocker_(sheet, top, left, padded.length, width, previous[0], previous[1]);
  if (blocker) throw new Error(blocker + ' has other content; not overwritten');

  if (previous[0] > 0) sheet.getRange(top, left, previous[0], previous[1]).clearContent();
  sheet.getRange(top, left, padded.length, width).setValues(padded);
  properties.setProperty(sizeKey, padded.length + ',' + width);

  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');
  start.setNote('Last fetched: ' + stamp);
}

/** Returns the address of the first non-empty cell in the new area outside the area written last time. Empty string if none. */
function findBlocker_(sheet, top, left, height, width, previousHeight, previousWidth) {
  const values = sheet.getRange(top, left, height, width).getValues();
  for (var r = 0; r < height; r++) {
    for (var c = 0; c < width; c++) {
      const wasOurs = r < previousHeight && c < previousWidth;
      if (!wasOurs && values[r][c] !== '') return columnLetter_(left + c) + (top + r);
    }
  }
  return '';
}

function columnLetter_(column) {
  var letters = '';
  for (var n = column; n > 0; n = Math.floor((n - 1) / 26)) {
    letters = String.fromCharCode(65 + ((n - 1) % 26)) + letters;
  }
  return letters;
}

function autoDaily() {
  clearTriggers_();
  ScriptApp.newTrigger('refreshNow').timeBased().everyDays(1).atHour(DAILY_HOUR).create();
  SpreadsheetApp.getActiveSpreadsheet().toast('Refreshes automatically every day around ' + DAILY_HOUR + ':00.', MENU.title, 5);
}

function autoOff() {
  clearTriggers_();
  SpreadsheetApp.getActiveSpreadsheet().toast('Daily refresh turned off.', MENU.title, 5);
}

/** Also cleans up triggers left by older versions. */
function clearTriggers_() {
  ScriptApp.getProjectTriggers().forEach(function (trigger) {
    ScriptApp.deleteTrigger(trigger);
  });
}