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

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

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

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

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

ダッシュボード(Looker Studio)

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

スプレッドシート

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

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

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

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.トリガーのアイコンを押下します
【Looker Studio×スプレッドシート×GAS×QiitaAPI】Qiita活動を可視化するダッシュボードを作成する。 - Qiita (5)

2.「トリガーを追加」を押下します
【Looker Studio×スプレッドシート×GAS×QiitaAPI】Qiita活動を可視化するダッシュボードを作成する。 - Qiita (6)

3.各項目を入力し、保存を押下します
【Looker Studio×スプレッドシート×GAS×QiitaAPI】Qiita活動を可視化するダッシュボードを作成する。 - Qiita (7)

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日アクセス.

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

FAQs

Looker Studioでスプレッドシートを埋め込むことはできますか? ›

Looker Studio(旧Googleデータポータル)にスプレッドシートを埋め込むことはできる? スプレッドシートなど、外部のWebページを埋め込むことが可能です。 メニューの「挿入」-「URLの埋め込み」を選択し、レポートの任意の場所に配置してください

looker studioで何ができますか? ›

Looker Studioは、Google広告やSearch Consoleなどを始め、様々なツール・媒体で取得したデータからレポートを作成できるクラウド型の無料BIツールです。 データはリアルタイムに反映されるため、フォーマットを作成すれば都度更新する手間がかかりません。

Looker Studioの料金はいくらですか? ›

Looker Studioは基本無料のBIツール

まず、Looker Studioの利用料金は基本無料です。 GA4の標準コネクタや、Googleスプレッドシートの標準コネクタを利用したダッシュボードであれば、閲覧や操作など運用によって発生する費用はありません。

LookerとLooker Studioの違いは何ですか? ›

Lookerは、複雑なデータ分析や計算を行うことが可能で、多岐にわたる機能を持っています。 これに対してLooker Studioは、主にデータの可視化やシンプルなダッシュボードの作成を行うことが得意です。 しかし、どちらもデータを取得し、適切な形式で表示するための機能を充実させています。

Looker Studioでデータソースを作成するには? ›

再利用可能なデータソースを作成する

Looker Studio にログインします。 作成]、[データソース] の順にクリックします。 接続するか埋め込むデータの種類を選択します。 データを選択し、必要に応じて承認を行います

Googleスプレッドシートは誰でも編集可能? ›

Googleスプレッドシートとは、Google社が提供する無料表計算ソフトです。 Webブラウザ上でリアルタイムの共同編集が可能で、互換性のあるExcelファイルをそのまま編集することもできます。 アプリのインストールは不要で、Googleアカウントさえあれば誰でも無料で利用可能です。

Looker Studioの弱点は何ですか? ›

Looker Studioのデメリットとして、設定にコツがいる点が挙げられます。 レポートを作成する際には、「指標」や「ディメンション」を選ぶ必要があります。 しかし、はじめのうちは、どの指標やディメンションを選べばよいかわからず、混乱する可能性が高いです。 そのため、レポート作成に時間がかかってしまうでしょう。

Lookerのアカウント料金はいくらですか? ›

利用料金が基本無料のBIツール

ビジネス情報を一元管理できるため、情報の共有やレポート作成における工数削減を実現します。 利用料金は基本無料であり、Googleアカウントさえあれば誰でも利用が可能です。 なお有料版としてLooker Studio Proというツールもあり、こちらは1ユーザーあたり7ドル/月かかります。

スプレッドシートをlookerに接続するにはどうすればいいですか? ›

接続するには:
  1. Looker Studio にログインします。
  2. Looker Studio のホームページで、左上にある ...
  3. [Google スプレッドシート] コネクタを選択します。
  4. スプレッドシートとワークシートを選択します。 ...
  5. 必要に応じてデータソース オプションを設定します(下記を参照)。

Looker Studioでテキストを入力するにはどうすればいいですか? ›

テキストを追加する
  1. ツールバーで、[テキスト] アイコンをクリックします。
  2. キャンバスにボックスを描きます。
  3. ボックス内にテキスト入力します。

スプレッドシートをリンクさせるにはどうすればいいですか? ›

スプレッドシート内のセル範囲にリンクする
  1. Google スプレッドシートスプレッドシートを開きます。
  2. リンクを追加するセルを選択します。
  3. [挿入] ...
  4. [リンクを追加するセルの範囲を選択します] ...
  5. リンクを追加するセル範囲を選択します。
  6. [OK] をクリックします。
  7. [適用] をクリックします。

ルッカースタジオでスプレッドシートを統合するにはどうすればいいですか? ›

Looker Studioでデータソースを統合する
  1. Googleアナリティクス(1)(2) Googleアナリティクスをデータソースとして追加する
  2. URLが記載されたGoogleスプレッドシート ...
  3. リソース > 統合を管理 を選択 ...
  4. 統合を追加 をクリック ...
  5. データの統合画面で各データソースを関連付ける
Apr 1, 2024

Top Articles
Latest Posts
Article information

Author: Gregorio Kreiger

Last Updated:

Views: 6416

Rating: 4.7 / 5 (57 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Gregorio Kreiger

Birthday: 1994-12-18

Address: 89212 Tracey Ramp, Sunside, MT 08453-0951

Phone: +9014805370218

Job: Customer Designer

Hobby: Mountain biking, Orienteering, Hiking, Sewing, Backpacking, Mushroom hunting, Backpacking

Introduction: My name is Gregorio Kreiger, I am a tender, brainy, enthusiastic, combative, agreeable, gentle, gentle person who loves writing and wants to share my knowledge and understanding with you.