LoginSignup
5
2
この記事誰得？ 私しか得しないニッチな技術で記事投稿！
Qiita Engineer Festa20242024年7月17日まで開催中！
@aki_number16(aki)inＡＲアドバンストテクノロジ株式会社（ARI）

【Looker Studio×スプレッドシート×GAS×QiitaAPI】Qiita活動を可視化するダッシュボードを作成する。

Posted at

Qiitaへの投稿に関して、閲覧数やいいね数など反響があるのは、次の記事を書くモチベーションがあがります。

マイページにContributionsや投稿数がありますが、日付ごとに集計したグラフなどがあれば、数字が上昇していることがわかりやすくなります。そして、その数字の上昇が投稿するモチベーションにつながるのではないかと考えました。そこで、Qiitaへの投稿活動を可視化するダッシュボードを作成しました。このダッシュボードでは、閲覧数やいいね数を1か所にまとめるだけでなく、1日ごとに集計を行い時系列にそったグラフを表示します。

やりたいこと

・Qiitaの投稿活動(閲覧数やいいね数)を示す数字を取得し、集計して記録する。
・集計は毎日行い、前日と数字を比較できるようにする。
・集計した結果をダッシュボードに表示する。
・ダッシュボードには、①項目ごとに集計した数字を表示、②閲覧数を時系列に沿ってグラフ化、③前日との増分を表示する
・ダッシュボードは無料で利用する。
・データの取得・集計、ダッシュボードへの表示・更新はなるべく手動運用をしない

完成品

完成したGASとスプレッドシートとダッシュボードはこちらです。

ダッシュボード（Looker Studio）

image.png

スプレッドシート

image.png

image.png

image.png

GAS

// 定数の定義
const USER_ID = "＠はなしでいれてね";
const TOKEN = "ダミーデータ";
const API_BASE_URL = "https://qiita.com/api/v2";
const SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();
const SHEET_ARTICLES = SPREADSHEET.getSheetByName("Articles");
const SHEET_AGGREGATE = SPREADSHEET.getSheetByName("Aggregate");
const SHEET_INCREASES = SPREADSHEET.getSheetByName("increases");
const TITLES = ["No", "作成日", "タイトル", "views", "LGTM", "ストック", "コメント"];
const COLUMNS = ["created_at", "title", "page_views_count", "likes_count", "stock_count", "comments_count"];

//メイン関数
function main(){
  qiitaOutput();
  qiitaAggregate();
  calculateIncreases() ;
}

// 記事一覧の出力
function qiitaOutput() {
  let sheet = SHEET_ARTICLES;
  sheet.clear();

  // 見出し行を出力する
  for (let indexY = 0; indexY < TITLES.length; indexY++) {
    sheet.getRange(1, indexY + 1).setValue(TITLES[indexY]);
  }

  // 記事一覧取得処理を呼び出す
  let qiitaData;
  try {
    qiitaData = getArticles();
  } catch (error) {
    Logger.log("記事一覧の取得に失敗しました: " + error.message);
    return;
  }

  // 記事一覧を出力する
  let rowIndex = 2; // 行インデックスを2から開始（見出し行が1行目なので）
  for (let indexX = qiitaData.length - 1; indexX >= 0; indexX--) {
    sheet.getRange(rowIndex, 1).setValue(qiitaData.length - indexX); // No列
    for (let indexY = 0; indexY < TITLES.length; indexY++) {
      sheet.getRange(rowIndex, indexY + 2).setValue(qiitaData[indexX][indexY]);
    }
    rowIndex++; // 次の行へ移動
  }
}

// 記事一覧取得処理
function getArticles() {

  let result = [];
  let apiUrl = `${API_BASE_URL}/users/${USER_ID}/items`;
  let total;
  
  // APIから記事の総数を取得
  try {
    let response = UrlFetchApp.fetch(apiUrl); 
    total = response.getHeaders()['total-count']; 
  } catch (error) {
    throw new Error("記事一覧の取得に失敗しました: " + error.message); 
  }

  let page = 1;
  let indexResult = 0; 

  // 全記事を取得するまでループ
  while (indexResult < total) {
    try {
      // 現在のページのAPI呼び出し
      let response = UrlFetchApp.fetch(`${apiUrl}?page=${page}`);
      let jsonData = JSON.parse(response.getContentText()); 

      // 取得した記事ごとに処理
      for (let indexJson in jsonData) {
        let work = []; 
        let detail = getQiitaDetail(jsonData[indexJson]['id']); // 記事の詳細を取得
        let stockCount = getQiitaStock(jsonData[indexJson]['id']); // ストック数を取得

        // 各カラムごとにデータを取得し、配列に追加
        for (let column of COLUMNS) {
          switch (column) {
            case "page_views_count":
              work.push(detail['page_views_count']); // 詳細データから閲覧数を取得
              break;
            case "created_at":
              work.push(Utilities.formatDate(new Date(jsonData[indexJson][column]), "JST", "yyyy/MM/dd")); // 作成日をフォーマットして追加
              break;
            case "stock_count":
              work.push(stockCount); // ストック数を追加
              break;
            default:
              work.push(jsonData[indexJson][column]); // その他のデータを追加
              break;
          }
        }
        result[indexResult] = work;
        indexResult++; // インデックスを更新
      }
      page++; // 次のページへ
    } catch (error) {
      throw new Error(`ページ ${page} の記事一覧の取得に失敗しました: ${error.message}`);
    }
  }
  return result; // 結果を返す
}


// 記事詳細取得処理
function getQiitaDetail(id) {
  let apiUrl = `${API_BASE_URL}/items/${id}`;
  let headers = { 'Authorization': `Bearer ${TOKEN}` };
  try {
    let response = UrlFetchApp.fetch(apiUrl, { headers }); 
    return JSON.parse(response.getContentText()); 
  } catch (error) {
    // エラーが発生した場合、エラーメッセージを出力して例外を投げる
    console.error(`記事ID ${id} の詳細取得に失敗しました: ${error.message}`);
    throw new Error(`記事ID ${id} の詳細取得に失敗しました: ${error.message}`);
  }
}

// ストック数取得処理
function getQiitaStock(id) {
  let apiUrl = `${API_BASE_URL}/items/${id}/stockers`;
  let headers = { 'Authorization': `Bearer ${TOKEN}` };
  let result = 0;
  let page = 1;
  let flg = true;

  // 全ストック数を取得するまでループ
  while (flg) {
    try {
      // 現在のページのAPI呼び出し
      let response = UrlFetchApp.fetch(`${apiUrl}?page=${page}&per_page=100`, { headers });
      let jsonData = JSON.parse(response.getContentText()); 
      result += jsonData.length; // 現在のページのストック数を合計に追加
      if (jsonData.length < 100) { // 100未満のストック数であれば終了
        flg = false;
      }
      page++; // 次のページへ
    } catch (error) {
      console.error(`記事ID ${id} のストック数取得に失敗しました: ${error.message}`);
      throw new Error(`記事ID ${id} のストック数取得に失敗しました: ${error.message}`);
    }
  }
  return result; // 合計ストック数を返す
}


// データを取得
function qiitaAggregate() {
  let sourceSheet = SHEET_ARTICLES;
  let aggregateSheet = SHEET_AGGREGATE;
  try {
    // Qiitaデータを取得
    let qiitaData = getQiitaData(sourceSheet);

    // 集計を計算
    let aggregates = calculateAggregates(qiitaData);

    // フォロー数を取得
    let followCounts = getQiitaFollowCounts();

    // 現在の日付を取得
    let currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy/MM/dd");
    let outputRow = aggregateSheet.getLastRow() + 1;

    // 集計結果をシートに出力
    aggregateSheet.getRange(outputRow, 1, 1, 8).setValues([[currentDate, ...aggregates, followCounts.followees, followCounts.followers]]);
  } catch (error) {
    // エラーログを出力し、処理を中断
    console.error("データの取得および集計処理に失敗しました: " + error.message);
    throw new Error("データの取得および集計処理に失敗しました: " + error.message);
  }
}

// 集計
function calculateAggregates(data) {
  let totalArticles = data.length;
  let totalViews = getTotalValue(data, "views");
  let totalLikes = getTotalValue(data, "LGTM");
  let totalStocks = getTotalValue(data, "ストック");
  let totalComments = getTotalValue(data, "コメント");
  return [totalArticles, totalViews, totalLikes, totalStocks, totalComments];
}

// Qiitaデータをシートから取得する関数
function getQiitaData(sheet) {
  try {
    let lastRow = sheet.getLastRow();
    let dataRange = sheet.getRange(2, 1, lastRow - 1, 7); // ヘッダーを除いた範囲
    return dataRange.getValues();
  } catch (error) {
    // エラーログを出力し、処理を中断
    console.error("シートからデータの取得に失敗しました: " + error.message);
    throw new Error("シートからデータの取得に失敗しました: " + error.message);
  }
}

// 合計値を計算する関数
function getTotalValue(data, columnName) {
  try {
    let columnIndex = TITLES.indexOf(columnName) + 1; // ヘッダーから列番号を取得
    let total = 0;
    for (let row of data) {
      total += row[columnIndex - 1]; // 配列のインデックスは0から始まるため、列インデックスを1引く
    }
    return total;
  } catch (error) {
    // エラーログを出力し、処理を中断
    console.error(`${columnName} の合計値の計算に失敗しました: " + error.message`);
    throw new Error(`${columnName} の合計値の計算に失敗しました: " + error.message`);
  }
}

// フォロー数とフォロワー数を取得する関数
function getQiitaFollowCounts() {
  let apiUrl = `${API_BASE_URL}/users/${USER_ID}`;
  try {
    let response = UrlFetchApp.fetch(apiUrl);
    let userData = JSON.parse(response.getContentText());
    return {
      followees: userData.followees_count,
      followers: userData.followers_count
    };
  } catch (error) {
    // エラーログを出力し、処理を中断
    console.error("フォロー数の取得に失敗しました: " + error.message);
    throw new Error("フォロー数の取得に失敗しました: " + error.message);
  }
}


// 差分計算
function calculateIncreases() {
  let aggregateSheet = SHEET_AGGREGATE;   let increasesSheet = SHEET_INCREASES; 

  try {
    let currentDate = new Date(); 
    currentDate.setHours(0, 0, 0, 0); // 時間を0:00に設定
    let yesterday = new Date(currentDate); 
    yesterday.setDate(yesterday.getDate() - 1); // 現在の日付から1日引く

    let yesterdayRow = getRowByDate(aggregateSheet, yesterday); // 昨日の日付に対応する行を取得
    let currentRow = getRowByDate(aggregateSheet, currentDate); // 現在の日付に対応する行を取得

    if (yesterdayRow === -1 || currentRow === -1) { // データが見つからない場合のチェック
      Logger.log("前日または当日のデータが見つかりませんでした。");
      return;
    }

    let increases = [currentDate]; // 差分の結果を格納する配列を初期化
    for (let i = 2; i <= 8; i++) { // 2列目から8列目までのデータを処理
      let increase = aggregateSheet.getRange(currentRow, i).getValue() - aggregateSheet.getRange(yesterdayRow, i).getValue(); // 差分を計算
      increases.push(increase); // 差分を配列に追加
    }

    increasesSheet.appendRow(increases); // 差分の結果をシートに追加
  } catch (error) {
    Logger.log("差分計算処理に失敗しました: " + error.message); // エラーメッセージをログに出力
    throw new Error("差分計算処理に失敗しました: " + error.message); // エラーをスローして処理を中断
  }
}

// 指定された日付の行番号を取得
function getRowByDate(sheet, date) {
  try {
    let lastRow = sheet.getLastRow(); // シートの最後の行番号を取得
    let dateColumn = sheet.getRange(2, 1, lastRow - 1, 1).getValues(); // 日付が格納されている列の値を取得

    for (let i = 0; i < dateColumn.length; i++) { // 各行の日付をチェック
      if (dateColumn[i][0].getTime() === date.getTime()) { // 日付が一致するか確認
        return i + 2; // 行番号を返す（インデックスが0から始まるため、+2する）
      }
    }
    return -1; // 該当する行が見つからない場合は-1を返す
  } catch (error) {
    Logger.log("指定された日付の行番号を取得する際に失敗しました: " + error.message); // エラーメッセージをログに出力
    throw new Error("指定された日付の行番号を取得する際に失敗しました: " + error.message); // エラーをスローして処理を中断
  }
}

実現方法

実現方法を紹介します。

実現方法を紹介します。
GASの大まかな流れは以下のようになります。

  1. GASでQiitaAPIを叩いて、記事ごとに「閲覧数」「いいね数」「ストック数」「コメント数」を取得します。

    • getArticles 関数で、QiitaAPIを使用して記事一覧を取得し、各記事の詳細情報やストック数を個別に取得します。

  2. 記事ごとに取得したデータの合計値を算出します。

    • calculateAggregates 関数で、取得したデータの合計値（総記事数、総閲覧数、総いいね数、総ストック数、総コメント数）を算出します。

  3. ユーザーに紐づく「フォロワー数」、「フォロー数」を取得します。

    • getQiitaFollowCounts 関数で、QiitaAPIを使用してユーザーのフォロワー数とフォロー数を取得します。

  4. 取得したデータを記事ごとに一覧シートと集計シートに分けて、スプレッドシートに出力します。

    • qiitaOutput 関数で、記事ごとのデータを一覧シートに出力し、qiitaAggregate 関数で集計結果を集計シートに出力します。

  5. 集計シートをもとに、前日との増分を計算し、スプレッドシートの増分シートに出力します。

    • calculateIncreases 関数で、前日と当日のデータの差分を計算し、増分シートに出力します。

そして、関数をまとめたmain関数を毎日定期実行します。

Looker stadioから、データソースとしてスプレッドシートを指定し、ダッシュボードを作成します。
※データが更新されると、ダッシュボードは自動で値が更新されます。

＊

まず、Qiitaの投稿活動を取得する部分については、QiitaAPIとGASを利用しました。ただQiitaAPIドキュメントをみると、ユーザーに紐づく情報にいいね数やストック数の総数がなかったので、ちょっと工夫が必要です。ユーザー情報を取得するAPIには、フォロー数フォロワー数だったらあったのですが。。

閲覧数やいいね数は記事に紐づいていますので、まず、1つの記事に対して閲覧数やいいね数を取得し、この取得処理を投稿済の記事全件に行います。そうすると、閲覧数やいいね数の総数をもとめられる状態になりますね。また、ユーザーに紐づくフォロワー数を取得し、各値の総数も出力します。記事一覧は1日ごとに取得するデータとします（1日ごとにデータを洗い替えする形で更新します）。集計用のシートと差分シートは1日1行出力して、データを蓄積するシートとします。このようにシートごとに役割を割り切って取得・集計することで、グラフ化できるデータを作成しました。

＊

閲覧数を取得するには、QiitaAPIにアクセストークンを付け加える必要があります。以下の記事のように行うと、マイページから取得できます。閲覧数はダッシュボードで表示する際に、一番動きがある数字になるので、アクセストークンを取得してみることにしました。ちなみに、当たり前ですが自分の記事しか閲覧数は見れません。

Qiita API アクセストークン発行方法
https://qiita.com/maiamea/items/680cca06f7825595cba0

トークンはGASの↓の部分にセットします。

const TOKEN = "ダミーデータ";

＊
上述したように毎日データを取得・集計することが前提なので、GASの定期実行の機能で毎日自動で実行します。設定の仕方は以下の通りです。

1.トリガーのアイコンを押下します
image.png

2.「トリガーを追加」を押下します
image.png

3.各項目を入力し、保存を押下します
image.png

GASにおける各種制限のまとめ
https://qiita.com/kakakaori830/items/84a7dcba2d6119ed320a

＊

データを集計しましたので、表示しましょう。今回はLooker stadioを使用しました。

Looker stadioとは、お使いのデータを、読み取りや共有が容易で柔軟にカスタマイズできる便利なダッシュボードとレポートに落とし込むことができる無料のツール

出典：「Looker Studio へようこそ」

Looker Studioは、様々なデータと接続できることも強みで、もちろんスプレッドシートにも接続することができます。さらに無料で使えることもあって、Looker stadioを採用することにしました。

Qiitaの活動をダッシュボード化することの目的は、投稿へのモチベーションアップです。数字が伸びていることがわかると、もっとがんばろうと思うので、一番の伸びやすそうな「閲覧数」をグラフ化して大きめに表示することとします。

さらに、前日との差分もわかるとよいので、補助資料として差分データもいくつか表示します。フォロー数もデータとして取得していますが、フォロー数は自分自身でコントロールできる値なので、ダッシュボードには載せませんでした。

あとは、単純にいいね数やストック数などの数字も載せて配置を整えます。Looker stadioは見た目に関していろいろとカスタマイズができるので、一度ダッシュボードのイメージを紙に書き出して全体像をつかんでから、実際にLooker stadioの画面を作成していきました。Qiitaの緑色をメインカラーに添えると、「らしさ」が演出できました。一部、イメージの絵と違う部分もありますが、なんとなくイメージしたダッシュボードになりました。

総閲覧数のグラフに関して、縦軸の設定がうまくできず、数字の「伸び」がちょっとわかりにくいものになってしまったのは反省です。

作ってみた感想

いろいろな記事を参考にしたりしてGASのコードをカスタマイズしていきましたが、最初の頃とできあがりがかなり変わりました。ChatGPTにソースレビューをしてもらって、try-catchをいれたり定数を整理したりしました。GAS自体は少しずつ時間をかけて書いていたこともあり、書き方が統一感のないコードになってしまいましたが、動き自体は思ったようになりました。

記事を取得して、記事一覧を出力する処理のところは、記事数によっては時間がかかってしまうかもしれません。自分の記事で行うと、記事数41本で1分程度の実行時間でした。APIを何度もたたいたり、一度にたくさんの処理を行う部分もあるのでパフォーマンスが少し心配です。

GASを書き始めたときはGASでグラフ化する想定でしたが、途中でLooker stadioに切り替えたら、見た目がとてもきれいになりました。見た目ってかなり大事なんだなって、再認識しました。モチベーションアップを図るなら、見た目のきれいさも大事です。あと、グラフ化するときには、縦軸と横軸の単位を調整がグラフのわかりやすさに大きな影響を与えます！

＊

実際に何日か動かしてみると、閲覧数やいいね数が目に見える形でのびていくので、びっくりしました。Qiitaへの投稿は、自分の備忘録として書いている面が大きいのですが、反応があるのはうれしいです。もう少し投稿活動をがんばってみようかなとは思えたので、このトライは成功です！このトライが誰かの役に立ったらうれしいです。

最後まで読んでいただいた方、ありがとうございました。

参考文献

Databeat Marketing Magazine（2023）「【Looker Studio×スプレッドシート】連携・レポート作成完全ガイド！接続方法からデータソースの切り替え方法まで詳しく解説」,https://www.data-be.at/magazine/dataportal-spreadsheet/ 2024年06月29日アクセス.

GA分析のススメ(2024)「Looker Studio(旧:Googleデータポータル)レポート作成時の操作方法を詳しく解説」https://ga4.hideharublog.com/looker-studio-how-to-operate 2024年06月29日アクセス.

Qiita maiamea(2021)「Qiita API アクセストークン発行方法」
https://qiita.com/maiamea/items/680cca06f7825595cba0 2024年06月29日アクセス.

Qiita Pell「【GAS編】QiitaAPIで投稿記事ごとのviews数、LGTM数、ストック数、コメント数を取得し一覧で表示」https://qiita.com/Pell/items/ee30815ae1a9ecc5ba74 2024年06月29日アクセス.

DevelopersIO　若槻龍太（2021）「GoogleデータポータルでGoogle スプレッドシートのデータを可視化してみた」
https://dev.classmethod.jp/articles/i-tried-making-a-dashboard-with-google-data-studio/ 2024年06月29日アクセス.

Qiita papasim824(2023)「一瞬でQiita活動を把握！手軽に使えるダッシュボードを作ってみた」
https://qiita.com/papasim824/items/ffd6d36326ed88ede988 2024年06月29日アクセス.

5
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
2