Salesforce 接続アプリ作成の依頼手順書

/**
 * SFレポート全件くん v1 (商品版)
 * Salesforceのレポートを2000件制限なしでスプレッドシートに自動取得する
 *
 * 特徴:
 * - 鍵はコードに書かない(初期設定ダイアログで入力→ユーザーのプロパティ領域に保存)
 * - 複数レポート対応(「SF設定」シートに1行=1レポート)
 * - 定時自動更新(毎朝メニューからON/OFF)
 * 仕組み: レポートをID列で昇順に並べ、「前回の最後のIDより後ろ」を2000件ずつ取り続ける
 */

const API_VER = '/services/data/v60.0';
const PAGE_SIZE = 2000;
const MAX_PAGES = 500; // 安全弁(最大100万行)
const CONFIG_SHEET = 'SF設定';

/** シートを開いたときにメニューを追加 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Salesforce')
    .addItem('全レポート更新', 'refreshAll')
    .addSeparator()
    .addItem('初期設定(接続情報の登録)', 'setupCredentials')
    .addItem('レポート一覧シートを作る', 'createConfigSheet')
    .addSeparator()
    .addItem('毎朝の自動更新をON', 'enableDailyTrigger')
    .addItem('自動更新をOFF', 'disableDailyTrigger')
    .addItem('接続テスト', 'testConnection')
    .addToUi();
}

/** ── 初期設定 ─────────────────────────────── */

/** 接続情報をダイアログで聞いてユーザープロパティに保存(コードに鍵を残さない) */
function setupCredentials() {
  const ui = SpreadsheetApp.getUi();
  const ask = function (title, hint, current) {
    const res = ui.prompt(title, hint + (current ? '\n(現在: 設定済み。空のまま OK で変更しない)' : ''), ui.ButtonSet.OK_CANCEL);
    if (res.getSelectedButton() !== ui.Button.OK) throw new Error('初期設定を中断しました');
    return res.getResponseText().trim();
  };
  const props = PropertiesService.getUserProperties();
  const cur = getCreds_(true);

  const domain = ask('1/3 SalesforceのURL',
    '例: https://xxxx.my.salesforce.com\n(xxxx.lightning.force.com の場合は my.salesforce.com に読み替え)', cur.domain);
  const id = ask('2/3 コンシューマ鍵', '接続アプリケーションの「コンシューマ鍵」を貼り付け', cur.id);
  const secret = ask('3/3 コンシューマの秘密', '接続アプリケーションの「コンシューマの秘密」を貼り付け', cur.secret);

  if (domain) props.setProperty('SF_DOMAIN', domain.replace(/\/+$/, ''));
  if (id) props.setProperty('SF_CLIENT_ID', id);
  if (secret) props.setProperty('SF_CLIENT_SECRET', secret);

  ui.alert('保存しました。「Salesforce → 接続テスト」で確認してください。\n※鍵はあなたのGoogleアカウントのプロパティ領域にだけ保存され、シートを共有しても他人には見えません。');
}

function getCreds_(allowEmpty) {
  const p = PropertiesService.getUserProperties();
  const c = {
    domain: p.getProperty('SF_DOMAIN') || '',
    id: p.getProperty('SF_CLIENT_ID') || '',
    secret: p.getProperty('SF_CLIENT_SECRET') || '',
  };
  if (!allowEmpty && (!c.domain || !c.id || !c.secret)) {
    throw new Error('接続情報が未設定です。メニュー「Salesforce → 初期設定」から登録してください。');
  }
  return c;
}

/** レポート一覧シート(SF設定)の雛形を作る */
function createConfigSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh = ss.getSheetByName(CONFIG_SHEET);
  if (!sh) sh = ss.insertSheet(CONFIG_SHEET);
  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, 5).setValues([[
      'レポートID(00Oで始まる)', '出力シート名', 'ID列(通常は空=自動検出)', '最終更新', '結果']]);
    sh.getRange(2, 1, 1, 2).setValues([['00Oxxxxxxxxxxxxxxx', 'SFレポート1']]);
    sh.setFrozenRows(1);
    sh.autoResizeColumns(1, 5);
  }
  ss.setActiveSheet(sh);
  SpreadsheetApp.getUi().alert(
    '「' + CONFIG_SHEET + '」シートに取得したいレポートを1行ずつ書いてください。\n' +
    'レポートIDは、Salesforceでレポートを開いたときのURLにある 00O で始まる英数字です。');
}

/** ── 取得本体 ─────────────────────────────── */

/** SF設定シートの全レポートを順に更新(メニュー/トリガー共用) */
function refreshAll() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(CONFIG_SHEET);
  if (!sh || sh.getLastRow() < 2) {
    throw new Error('「' + CONFIG_SHEET + '」シートがありません。メニュー「Salesforce → レポート一覧シートを作る」から作成してください。');
  }
  const creds = getCreds_(false);
  const token = getToken_(creds);
  const rows = sh.getRange(2, 1, sh.getLastRow() - 1, 3).getValues();
  let ok = 0, ng = 0;

  rows.forEach(function (row, i) {
    const reportId = String(row[0]).trim();
    const sheetName = String(row[1]).trim() || ('SFレポート' + (i + 1));
    const idColumn = String(row[2]).trim();
    if (!reportId || reportId.indexOf('00O') !== 0) return; // 雛形行や空行はスキップ

    const cell = sh.getRange(i + 2, 4, 1, 2);
    try {
      const n = fetchReport_(creds, token, reportId, sheetName, idColumn);
      cell.setValues([[new Date(), '✅ ' + n + '件']]);
      ok++;
    } catch (e) {
      cell.setValues([[new Date(), '❌ ' + String(e.message).slice(0, 200)]]);
      ng++;
    }
  });
  ss.toast('更新完了: 成功' + ok + '件 / 失敗' + ng + '件', 'Salesforce', 10);
}

/** 1レポートを全件取得してシートに書く。取得件数を返す */
function fetchReport_(creds, token, reportId, sheetName, idColumnPref) {
  // 1) レポートの定義を取得
  const desc = sfGet_(creds, token, API_VER + '/analytics/reports/' + reportId + '/describe');
  const meta = desc.reportMetadata;
  if (meta.reportFormat && meta.reportFormat !== 'TABULAR') {
    throw new Error('表形式ではありません(形式: ' + meta.reportFormat + ')。レポート編集画面で「表形式」に変更してください。');
  }
  const cols = meta.detailColumns;
  const colInfo = desc.reportExtendedMetadata.detailColumnInfo;

  // 2) ページング用のID列(指定 or 自動検出)
  let idCol = idColumnPref;
  if (!idCol) {
    idCol = cols.filter(function (c) {
      return /(^|_|\.)ID$/i.test(c) || /\.Id$/.test(c);
    })[0];
  }
  if (!idCol || cols.indexOf(idCol) === -1) {
    const candidates = cols.map(function (c) {
      return c + ' (' + (colInfo[c] ? colInfo[c].label : '?') + ')';
    }).join(' / ');
    throw new Error('ID列が見つかりません。レポートに「レコードID」列を足すか、SF設定のID列に次から1つ指定: ' + candidates);
  }
  const idIdx = cols.indexOf(idCol);

  // 3) ID昇順に固定して2000件ずつ取り続ける
  meta.sortBy = [{ sortColumn: idCol, sortOrder: 'Asc' }];
  const baseFilters = meta.reportFilters || [];
  const baseLogic = meta.reportBooleanFilter || null;
  const rows = [];
  let lastId = null;

  for (let page = 0; page < MAX_PAGES; page++) {
    const m = JSON.parse(JSON.stringify(meta));
    if (lastId) {
      m.reportFilters = baseFilters.concat([{ column: idCol, operator: 'greaterThan', value: lastId }]);
      if (baseLogic) {
        m.reportBooleanFilter = '(' + baseLogic + ') AND ' + m.reportFilters.length;
      }
    }
    const result = sfPost_(creds, token,
      API_VER + '/analytics/reports/' + reportId + '?includeDetails=true',
      { reportMetadata: m });

    const fact = result.factMap['T!T'];
    if (!fact) throw new Error('詳細行が取得できません。レポート形式が「表形式」か確認してください。');
    const pageRows = (fact.rows || []).map(function (r) {
      return r.dataCells.map(function (c) { return c.label; });
    });
    if (pageRows.length === 0) break;
    rows.push.apply(rows, pageRows);

    const lastCell = fact.rows[fact.rows.length - 1].dataCells[idIdx];
    const v = lastCell.value;
    lastId = (v && v.id) ? v.id : String(v);
    if (pageRows.length < PAGE_SIZE) break;
  }

  // 4) シートに書き出し
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const out = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
  out.clearContents();
  const header = cols.map(function (c) { return colInfo[c] ? colInfo[c].label : c; });
  out.getRange(1, 1, 1, header.length).setValues([header]);
  if (rows.length > 0) {
    out.getRange(2, 1, rows.length, header.length).setValues(rows);
  }
  return rows.length;
}

/** ── 自動更新トリガー ─────────────────────── */

function enableDailyTrigger() {
  disableDailyTrigger();
  ScriptApp.newTrigger('refreshAll').timeBased().everyDays(1).atHour(6).create();
  SpreadsheetApp.getUi().alert('毎朝6〜7時ごろに全レポートを自動更新します。');
}

function disableDailyTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'refreshAll') ScriptApp.deleteTrigger(t);
  });
}

/** ── 接続まわり ───────────────────────────── */

function testConnection() {
  const creds = getCreds_(false);
  const token = getToken_(creds);
  const me = sfGet_(creds, token, API_VER + '/');
  SpreadsheetApp.getUi().alert('接続OK! Salesforceに繋がりました。');
}

/** 認証: クライアントログイン情報フローでアクセストークンを取る */
function getToken_(creds) {
  const res = UrlFetchApp.fetch(creds.domain + '/services/oauth2/token', {
    method: 'post',
    payload: {
      grant_type: 'client_credentials',
      client_id: creds.id,
      client_secret: creds.secret,
    },
    muteHttpExceptions: true,
  });
  const body = JSON.parse(res.getContentText());
  if (!body.access_token) {
    throw new Error('Salesforce認証に失敗しました。初期設定のURL・鍵・接続アプリの設定を確認してください。詳細: ' + res.getContentText());
  }
  return body.access_token;
}

function sfGet_(creds, token, path) { return sfFetch_(creds, token, path, null); }
function sfPost_(creds, token, path, payload) { return sfFetch_(creds, token, path, payload); }

function sfFetch_(creds, token, path, payload) {
  const options = {
    method: payload ? 'post' : 'get',
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true,
  };
  if (payload) {
    options.contentType = 'application/json';
    options.payload = JSON.stringify(payload);
  }
  const res = UrlFetchApp.fetch(creds.domain + path, options);
  if (res.getResponseCode() >= 300) {
    throw new Error('Salesforce APIエラー(' + res.getResponseCode() + '): ' + res.getContentText());
  }
  return JSON.parse(res.getContentText());
}