구글 앱스 스크립트로 쉐어링크 데이터 가져오기

/* ==========================================================================
 * 에어브릿지 쉐어링크 → 구글 시트 가져오기
 *
 * [설정] 부분만 고치면 됩니다. 그 아래는 고치지 않아도 동작합니다.
 * 고친 뒤에는 저장(Cmd+S 또는 Ctrl+S)하고 시트를 새로고침하세요.
 *
 * 자주 하는 수정
 *   링크를 추가하고 싶다            → TARGETS 에 한 줄 추가
 *   데이터가 들어갈 시트·위치 변경   → 그 줄의 sheet, cell 수정
 *   자동 최신화 시각 변경            → DAILY_HOUR 수정 후 메뉴에서 자동 최신화를 다시 켜기
 *   메뉴 이름·이모지 변경            → MENU 수정
 *   "Server is still calculating" 실패가 잦다 → WARMUP_WAIT_MS 를 늘리기
 *   실행할 때마다 새로 조회하고 싶다   → /v3/ 주소에 useCache=false 추가 (아래 url 설명 참고)
 * ========================================================================== */

// ------------------------------------------------------------------ [설정]

// 가져올 링크 목록. 링크 하나가 한 줄이다.
//
//   sheet : 데이터를 넣을 시트(탭) 이름. 이미 있는 시트 이름을 적으면 그 시트에 넣고,
//           없는 이름을 적으면 새 시트를 만든다.
//   cell  : 데이터가 시작될 셀. 이 셀부터 오른쪽·아래로 채워진다. 생략하면 A1.
//           IMPORTDATA 수식을 쓰던 시트라면, 그 수식이 있던 셀을 적으면 된다.
//           (수식은 먼저 지워야 한다. 셀에 내용이 있으면 덮어쓰지 않고 실패로 알려준다.)
//   url   : 에어브릿지에서 복사한 쉐어링크 주소. /v2/ 든 /v3/ 든 그대로 붙여넣는다.
//           useCache=false를 붙이면 실행할 때마다 새로 조회한 데이터를 받는다(/v3/ 주소만).
//           주소에 ?가 있으면 &useCache=false, 없으면 ?useCache=false를 끝에 붙인다.
//           새 조회가 끝날 때까지 기다리므로 실행이 그만큼 오래 걸리고,
//           이런 링크가 여러 개면 하나씩 차례로 기다린다.
//
// 예시
//   { sheet: '원본_액츄얼스', url: 'https://report.airbridge.io/v3/토큰.csv?language=ko' },
//   { sheet: '원본_리텐션',   url: 'https://report.airbridge.io/v3/토큰.csv?language=ko' },
//   { sheet: '대시보드', cell: 'H3', url: 'https://report.airbridge.io/v3/토큰.csv?language=ko' },
//   { sheet: '원본_새조회',   url: 'https://report.airbridge.io/v3/토큰.csv?language=ko&useCache=false' },
//
// 줄 끝의 쉼표(,)와 따옴표(')를 빠뜨리면 전체가 동작하지 않으니 예시 모양을 그대로 따른다.
// 한 시트에 링크 두 개를 넣을 때는 서로 겹치지 않게 cell 을 충분히 띄운다.
const TARGETS = [
  { sheet: '액츄얼스', cell: 'A1', url: '여기에_쉐어링크_주소를_붙여넣으세요' },
];

// 매일 자동 최신화가 도는 시각. 0~23 사이 숫자(24시간제)로 적는다. 9 = 오전 9시, 15 = 오후 3시.
// 구글이 그 시각부터 1시간 안의 한 시점을 골라 매일 같은 때에 실행한다. 분 단위 지정은 안 된다.
// 기준 시간대는 Apps Script 편집기 왼쪽 톱니바퀴(프로젝트 설정)의 '시간대'를 따른다.
const DAILY_HOUR = 9;

// 시트 상단에 보이는 메뉴 이름. 따옴표 안의 글자와 이모지는 자유롭게 바꿔도 된다.
const MENU = {
  title: '📊 쉐어링크',
  refresh: '🔄 지금 바로 최신화',
  dailyOn: '⏰ 매일 ' + DAILY_HOUR + '시 자동 최신화 켜기',
  dailyOff: '⏹️ 자동 최신화 끄기',
};

// 서버에 계산을 시작시킨 뒤 결과를 받으러 가기까지 기다리는 시간(1000 = 1초).
// 리포트가 무거워 "Server is still calculating" 실패가 잦으면 40000 정도로 늘린다.
const WARMUP_WAIT_MS = 25000;

// 그래도 준비가 안 됐을 때 다시 물어보는 간격과 횟수. 보통은 고칠 일이 없다.
const RETRY_WAIT_MS = 15000;
const MAX_ROUNDS = 4;

// ------------------------------------------------- 여기부터는 고치지 않아도 된다

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

/** 시트를 열 때 상단에 메뉴를 만든다. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu(MENU.title)
    .addItem(MENU.refresh, 'refreshNow')
    .addSeparator()
    .addItem(MENU.dailyOn, 'autoDaily')
    .addItem(MENU.dailyOff, 'autoOff')
    .addToUi();
}

/** 메뉴와 매일 트리거가 함께 쓰는 진입점. */
function refreshNow() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.toast(TARGETS.length + '개를 최신화하는 중입니다. 1분 안팎 걸립니다.', MENU.title, 120);

  // 일반 v3는 캐시가 없으면 첫 호출에서 계산만 시작하므로, 먼저 한 번씩 불러 예열한다.
  // useCache=false 요청은 그 호출 자체가 새 조회를 시작하고 결과까지 기다리므로 예열하면 조회가 두 번 돈다.
  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);
      // useCache=false를 다시 보내면 새 조회를 또 시작하므로, 기다리게 되면 다음부터 일반 요청으로 확인한다.
      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 = TARGETS.length + '개 중 ' + done + '개 완료';
  spreadsheet.toast(failed.length === 0 ? summary : summary + ' / 실패: ' + failed.join(', '), MENU.title, failed.length === 0 ? 5 : 30);
}

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

/** v3 주소의 쿼리에 useCache=false가 있는지 확인한다. 값의 대소문자는 무시한다. */
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);
}

/** 다른 쿼리 파라미터와 fragment는 그대로 두고 useCache 파라미터만 뺀다. */
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는 상태 헤더로 판정한다. 헤더가 없는 v2·리포트별 주소는 본문 모양으로 판정한다.
 * 정상 리포트는 여러 열이고, 안내 문구는 한 셀짜리로 온다.
 */
function fetchOnce_(url, waitOnError) {
  var response;
  // muteHttpExceptions: 4xx·5xx도 예외가 아니라 응답으로 받아 아래에서 상태 코드로 판정한다.
  // 예외(catch)는 응답 자체가 안 온 경우(연결 끊김·타임아웃)뿐이다.
  try {
    response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  } catch (e) {
    // useCache=false는 클라이언트가 먼저 포기해도 서버 조회가 계속되므로 일반 요청으로 재확인한다.
    if (waitOnError) return { waiting: true };
    return { message: 'No response' };
  }

  // 판정 순서. in-progress만 다음 라운드(RETRY_WAIT_MS 간격, 최대 MAX_ROUNDS회)에 다시 확인하고,
  // 4xx·5xx는 재시도 없이 바로 실패로 알린다.
  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 '';
}

/**
 * 시트 전체가 아니라 지난번에 이 스크립트가 쓴 영역만 지우고 새로 쓴다.
 * 새로 쓸 자리에 스크립트가 쓰지 않은 내용이 있으면 덮어쓰지 않고 멈춘다.
 */
function write_(target, rows) {
  const width = rows.reduce(function (max, row) {
    return Math.max(max, row.length);
  }, 0);

  // setValues는 모든 행의 길이가 같아야 한다.
  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('마지막으로 가져온 시각: ' + stamp);
}

/** 새로 쓸 영역 중 지난번에 쓴 영역 바깥에서, 내용이 있는 첫 셀의 주소를 돌려준다. 없으면 빈 문자열. */
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('매일 ' + DAILY_HOUR + '시경 자동으로 최신화합니다.', MENU.title, 5);
}

function autoOff() {
  clearTriggers_();
  SpreadsheetApp.getActiveSpreadsheet().toast('자동 최신화를 껐습니다.', MENU.title, 5);
}

/** 옛 버전이 남긴 트리거까지 함께 정리한다. */
function clearTriggers_() {
  ScriptApp.getProjectTriggers().forEach(function (trigger) {
    ScriptApp.deleteTrigger(trigger);
  });
}