ワークフロー最適化 - GAS統合とn8n簡略化

Antigravity → 93件の骨格JSON → configシートA2 → GAS → ideasシート
    ↓
n8n「SNS投稿作成advanced」
    ↓
ideasシート(NEW) → Gemini API → 完成品生成 → postsシート(DRAFT)
    ↓
n8n「Canva用シート振り分けadvanced」
    ↓
postsシート(DRAFT) → カテゴリ判定 → canva_A〜Eシート

Antigravity → 完成品JSON → configシートA2
    ↓
GASメニュー「configA2からideas+postsに追加」
    ↓
ideasシート(ADOPTED) + postsシート(DRAFT)
    ↓
GASメニュー「postsからCanvaシートに振り分け」
    ↓
canva_A〜Eシート + postsシート(CANVA_READY)
    ↓
Canvaで一括生成 → 動画ファイル
    ↓
n8n「音声合成advanced」（変更なし）
    ↓
n8n「Instagram Reel from Drive v5」（フィルタ変更のみ）

/**
 * スプレッドシート起動時にカスタムメニューを追加
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('コンテンツ管理')
    .addItem('configA2からideas+postsに追加', 'importAndProcess')
    .addItem('postsからCanvaシートに振り分け', 'distributeToCanva')
    .addToUi();
}

/**
 * configシートのA2にあるJSONをideas+postsシートに追加
 * メニューから呼び出し用
 */
function importAndProcess() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const configSheet = ss.getSheetByName('config');
  const ideasSheet = ss.getSheetByName('ideas');
  const postsSheet = ss.getSheetByName('posts');

  const jsonStr = configSheet.getRange('A2').getValue();
  if (!jsonStr) {
    SpreadsheetApp.getUi().alert('configシートのA2にJSONがありません');
    return;
  }

  const items = JSON.parse(jsonStr);
  if (!Array.isArray(items)) {
    SpreadsheetApp.getUi().alert('JSONは配列形式である必要があります');
    return;
  }

  const now = new Date();

  // 既存のpost_idを取得
  const postsLastRow = postsSheet.getLastRow();
  const existingPostIds = postsLastRow > 1
    ? postsSheet.getRange(2, 1, postsLastRow - 1, 1).getValues().flat()
    : [];

  const ideasRows = [];
  const postsRows = [];

  items.forEach(item => {
    const ideaId = item.idea_id;  // Antigravityで一意のIDを振る
    const postId = ideaId.replace('IDEA-', 'POST-');

    // ideasに追加する行
    ideasRows.push([
      ideaId,
      item.month || '',
      item.title || '',
      item.main_tool || '',
      '',
      item.category || '',
      '',
      'ADOPTED',
      '',
      now,
      JSON.stringify(item)
    ]);

    // 重複チェック
    if (!existingPostIds.includes(postId)) {
      postsRows.push([
        postId,
        'REEL',
        'DRAFT',
        item.caption || '',
        item.hashtags ? item.hashtags.join(',') : '',
        '', '', '', '', '', '', '', '',
        now,
        '',
        '',
        JSON.stringify(item)
      ]);
    }
  });

  // ideas追加
  if (ideasRows.length > 0) {
    const ideasLastRow = ideasSheet.getLastRow();
    ideasSheet.getRange(ideasLastRow + 1, 1, ideasRows.length, 11).setValues(ideasRows);
  }

  // posts追加
  if (postsRows.length > 0) {
    const postsLastRow2 = postsSheet.getLastRow();
    postsSheet.getRange(postsLastRow2 + 1, 1, postsRows.length, 17).setValues(postsRows);
  }

  SpreadsheetApp.getUi().alert(
    'ideas: ' + ideasRows.length + '件追加\n' +
    'posts: ' + postsRows.length + '件追加（重複スキップ: ' + (ideasRows.length - postsRows.length) + '件）'
  );
}

/**
 * postsシートのDRAFTをcanva_A〜Eに振り分け
 * メニューから呼び出し用
 */
function distributeToCanva() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    'Canvaシート振り分け',
    'canva_A〜Eシートをクリアして、DRAFTのpostsを振り分けます。\n既存のCanvaデータは削除されます。続行しますか？',
    ui.ButtonSet.YES_NO
  );

  if (response !== ui.Button.YES) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const postsSheet = ss.getSheetByName('posts');

  // canvaシートをクリア
  const canvaSheets = ['canva_A', 'canva_B', 'canva_C', 'canva_D', 'canva_E'];
  canvaSheets.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (sheet && sheet.getLastRow() > 1) {
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
    }
  });

  // postsからDRAFTを取得
  const postsData = postsSheet.getDataRange().getValues();
  const headers = postsData[0];
  const statusCol = headers.indexOf('status');
  const contentJsonCol = headers.indexOf('content_json');
  const postIdCol = headers.indexOf('post_id');

  const canvaRows = { A: [], B: [], C: [], D: [], E: [] };
  const updatedRows = [];

  for (let i = 1; i < postsData.length; i++) {
    if (postsData[i][statusCol] !== 'DRAFT') continue;

    const postId = postsData[i][postIdCol];
    const contentJsonStr = postsData[i][contentJsonCol];
    if (!contentJsonStr) continue;

    try {
      const json = JSON.parse(contentJsonStr);
      const category = json.category || 'C';

      const row = [
        postId,
        json.narration_1 || '',
        json.narration_2 || '',
        json.thumb_main || '',
        json.thumb_sub || '',
        json.set_1 || '',
        json.set_2 || '',
        json.set_3 || '',
        json.set_4 || '',
        json.set_5 || '',
        json.set_6 || '',
        'NORMAL',
        json.main_tool || ''
      ];

      if (canvaRows[category]) {
        canvaRows[category].push(row);
        updatedRows.push(i + 1);
      }
    } catch (e) {
      // skip invalid JSON
    }
  }

  // canvaシートに追加
  let totalAdded = 0;
  Object.keys(canvaRows).forEach(cat => {
    if (canvaRows[cat].length > 0) {
      const sheet = ss.getSheetByName('canva_' + cat);
      const lastRow = sheet.getLastRow();
      sheet.getRange(lastRow + 1, 1, canvaRows[cat].length, 13).setValues(canvaRows[cat]);
      totalAdded += canvaRows[cat].length;
    }
  });

  // postsのstatusを更新
  updatedRows.forEach(rowNum => {
    postsSheet.getRange(rowNum, statusCol + 1).setValue('CANVA_READY');
  });

  ui.alert(
    '振り分け完了',
    'canva_A〜Eに ' + totalAdded + ' 件追加しました。\n' +
    'A: ' + canvaRows.A.length + '件\n' +
    'B: ' + canvaRows.B.length + '件\n' +
    'C: ' + canvaRows.C.length + '件\n' +
    'D: ' + canvaRows.D.length + '件\n' +
    'E: ' + canvaRows.E.length + '件',
    ui.ButtonSet.OK
  );
}

/**
 * n8nからのHTTPリクエストを処理
 */
function doPost(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const jsonStr = e.postData.contents;
  const data = JSON.parse(jsonStr);

  // === action分岐 ===
  if (data.action === 'archiveAndCleanCanvaSheets') {
    try {
      const result = archiveAndCleanCanvaSheets();
      return ContentService.createTextOutput(JSON.stringify(result))
        .setMimeType(ContentService.MimeType.JSON);
    } catch (error) {
      return ContentService.createTextOutput(JSON.stringify({
        success: false,
        error: error.message
      })).setMimeType(ContentService.MimeType.JSON);
    }
  }

  // === updateAudioStatus ===
  if (data.action === 'updateAudioStatus') {
    try {
      const result = updateAudioStatus(data.postId, data.category, data.status);
      return ContentService.createTextOutput(JSON.stringify(result))
        .setMimeType(ContentService.MimeType.JSON);
    } catch (error) {
      return ContentService.createTextOutput(JSON.stringify({
        success: false,
        error: error.message
      })).setMimeType(ContentService.MimeType.JSON);
    }
  }

  return ContentService.createTextOutput(
    JSON.stringify({ success: false, error: 'Unknown action' })
  ).setMimeType(ContentService.MimeType.JSON);
}

/**
 * 指定したカテゴリのcanvaシートでaudio_statusを更新
 */
function updateAudioStatus(postId, category, status) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = 'canva_' + category;
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    return { success: false, error: 'Sheet not found: ' + sheetName };
  }

  const sheetData = sheet.getDataRange().getValues();
  const headers = sheetData[0];
  const postIdCol = headers.indexOf('post_id');
  const audioStatusCol = headers.indexOf('audio_status');

  if (postIdCol === -1 || audioStatusCol === -1) {
    return { success: false, error: 'Required columns not found' };
  }

  for (let i = 1; i < sheetData.length; i++) {
    if (sheetData[i][postIdCol] === postId) {
      sheet.getRange(i + 1, audioStatusCol + 1).setValue(status);
      return { success: true, postId: postId, category: category, status: status };
    }
  }

  return { success: false, error: 'Post not found: ' + postId };
}

/**
 * canva_A〜Eのデータをarchiveへコピーし、canvaシートをクリア
 */
function archiveAndCleanCanvaSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const archiveSheet = ss.getSheetByName('archive');

  if (!archiveSheet) {
    throw new Error('archiveシートが見つかりません。');
  }

  const canvaSheets = ['canva_A', 'canva_B', 'canva_C', 'canva_D', 'canva_E'];
  const now = new Date().toISOString();
  let totalArchived = 0;

  canvaSheets.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return;

    const lastRow = sheet.getLastRow();
    if (lastRow <= 1) return;

    const lastCol = sheet.getLastColumn();
    const data = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues();

    data.forEach(row => {
      if (!row[0]) return;
      archiveSheet.appendRow([...row, now, sheetName]);
      totalArchived++;
    });

    sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
  });

  return {
    success: true,
    archivedCount: totalArchived,
    timestamp: now
  };
}

IDEA-YYYYMMDDHHMM-001