GCS API/GAS/Google Sheets組み合わせた検索順位確認ツール

CODE.js

/**
 * @see [【GAS】Google Custom Search APIを使って検索順位のチェックツールを作る - Qiita](https://qiita.com/kingpanda/items/54043eddcf09699ceabc)
 */

function scrape(query) {
  function fetch() {
    const API_KEY = PropertiesService.getScriptProperties().getProperty(
      "API_KEY"
    );
    const CSE_ID = PropertiesService.getScriptProperties().getProperty(
      "CSE_ID"
    );
    const url = `https://www.googleapis.com/customsearch/v1?key=${API_KEY}&cx=${CSE_ID}&q=${query}`;
    const content = UrlFetchApp.fetch(url, {
      method: "get",
      muteHttpExceptions: false,
    }).getContentText();
    return JSON.parse(content);
  }

  const responseContent = PropertiesService.getScriptProperties().getProperty(
    "IS_PRODUCTION"
  )
    ? fetch()
    : dummyResponse(query);
  const formatted = responseContent.items.map((item) => {
    return {
      title: item.title,
      link: item.link,
      snippet: item.snippet,
    };
  });
  // Logger.log(formatted);
  Logger.log(query);
  // Browser.msgBox(dummy);
  return formatted;
}

function getKeywords(baseSheet) {
  const values = baseSheet
    .getRange(2, 2, 24)
    .getValues()
    .map((v) => v[0]);
  const keywords = {
    base: values[0],
    comparison: values.slice(3).filter((v) => v),
  };
  return keywords;
}

function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const baseSheet = ss.getSheets()[0];

  const keywords = getKeywords(baseSheet);

  const baseScrapeResult = scrape(keywords.base);
  const baseUrls = baseScrapeResult.slice().map((v) => v.link);

  const comparisonScrapeResults = keywords.comparison.map((keyword) =>
    scrape(keyword).map((result) =>
      Object.assign(result, { match: baseUrls.includes(result.link) })
    )
  );

  const setBackground = (range, color = "yellow") => range.setBackground(color);

  // 新しいシート作る
  const timestamp = Utilities.formatDate(
    new Date(),
    "JST",
    "yyyyMMdd'T'HHmmss"
  );
  const newSheet = ss.insertSheet(timestamp);
  const newSheetId = newSheet.getSheetId();

  // 範囲取得
  const resultTableRange = newSheet.getRange(
    2,
    2,
    11,
    comparisonScrapeResults.length + 1
  );
  // 配列整形
  const resultTable = [
    [keywords.base].concat(baseScrapeResult.slice().map((arr) => arr.link)),
  ].concat(
    comparisonScrapeResults.map((arr, i) =>
      [keywords.comparison.slice()[i]].concat(arr.map((item) => item.link))
    )
  );
  // 行列変換
  const transpose = (a) => a[0].map((_, c) => a.map((r) => r[c]));
  // [JavaScriptで二次元配列の行列を転置するワンライナー - Qiita](https://qiita.com/kznr_luk/items/790f1b154d1b6d4de398)
  const transposed = transpose(resultTable);
  // データ書き込み
  resultTableRange.setValues(transposed);

  // 一致セル範囲作成
  // 競合キーワードのリンクだけの配列作る

  const labels = [
    "A",
    "B",
    "C",
    "D",
    "E",
    "F",
    "G",
    "H",
    "I",
    "J",
    "K",
    "L",
    "M",
    "N",
    "O",
    "P",
    "Q",
    "R",
    "S",
    "T",
    "U",
  ];
  const comparisonMatchUrls = comparisonScrapeResults
    .slice()
    .flat()
    .filter((obj) => obj.match)
    .map((obj) => obj.link)
    .filter((x, i, self) => self.indexOf(x) === i);
  // 基本キーワードのURL配列から一致リストを作る
  const baseMatchList = baseUrls.map((url) =>
    comparisonMatchUrls.includes(url)
  );
  const comparisonMatchA1Notations = comparisonScrapeResults
    .map((results, i) =>
      results.map((result, j) => (result.match ? { i: i, j: j } : false))
    )
    .flat()
    .filter((result) => result)
    .map((range) => `${labels[range.i + 2]}${range.j + 3}`);

  const baseMatchA1Notations = baseMatchList
    .map((urlMatch, i) => (urlMatch ? "B" + (i + 3) : false))
    .filter((v) => v);
  const matchRange = newSheet.getRangeList(
    baseMatchA1Notations.concat(comparisonMatchA1Notations)
  );
  setBackground(matchRange, "yellow");

  const threshold = 0.6;
  // 一致率出す
  const matchRate = comparisonScrapeResults.map(
    (results) => results.filter((i) => i.match).length / results.length
  );
  const matchRateRange = baseSheet.getRange(
    5,
    3,
    comparisonScrapeResults.length
  );
  // 書き込む値を整形
  const matchRateValues = matchRate.map((rate, i) => [
    `=HYPERLINK("#gid=${newSheetId}&range=${labels[i + 2]}2", "${
      rate * 100
    }%")`,
  ]);
  // 一致率を表に書き込み
  matchRateRange.setValues(matchRateValues);
  // 閾値越えのセルに色付け
  const matchRateA1Notations = matchRate
    .map((rate, i) => (rate >= threshold ? "C" + (i + 5) : false))
    .filter((v) => v);
  if (matchRateA1Notations.length) {
    const thresholdOverRange = ss
      .getSheets()[0]
      .getRangeList(matchRateA1Notations);
    setBackground(thresholdOverRange, "yellow");
  }
  // 元のシートをアクティブにする
  ss.setActiveSheet(baseSheet);

  return false;
}

/**
 * スプレッドシートのUIにメニュー追加
 *
 * @return {void}
 */
const onOpen = () => {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu("💥ランク取得🥊");
  menu.addItem("実行", "main");
  menu.addToUi();
};