ToggleAPIから作業時間をスプレッドシートに自動書き込み！

はじめに

社会人1年目がおわって、だいぶお仕事にも慣れてきた今日は、普段使っている時間計測サービス「Toggl」を使って少しハック的なことができたので紹介します。

実際に作ったものはこちら↓
【今回作ったもの】

内容としては、スプレッドシートからGoogle App Scriptで任意のスクリプトを呼び出し、TogglのAPIを使って情報をゲットしようというものです。

Google App Scriptとは、web上でScriptを書けるサービスと言ったところでしょうか。。。
何せ、スプレッドシートもGoogleのサービスですので、この辺りの互換性はばっちしなはず！
では早速作っていきましょー！！

初期設定

新しいスプレッドシートを作ったらAppScriptをクリックして新しいGoogleScript(gs)の作業環境を作ります。

この右のエディターにAPIを叩く関数を書いていきます。

spread sheetからGoogle App Scriptのプロジェクトを作ると自動的に結合してくれるので、これだけで初期設定は完了です。

続いてTogglのAPIについて解説します！

TogglAPIについて

概要

※詳しくは公式ドキュメント（ https://developers.track.toggl.com/docs ）を参照してください。

TogglAPIをとりあえず叩いて様子を見たいっていう人は、以下の内容をコピペで、URLとApiKeyを入れてあげれば動きます。

function togglApi(apiUrl, apiKey) {
 var response = UrlFetchApp.fetch(apiUrl, {
   method: 'get',
   headers: {
     'Content-Type': 'application/json',
     'Authorization': 'Basic ' + Utilities.base64Encode(apiKey + ':api_token'),
   },
 });
 var responseData = JSON.parse(response.getContentText());
 return responseData;
}

公式の方ではAPIキーではなくemailとpasswordを使って認証を行っていますが、GoogleOAuthとか考えるのは面倒なので今回は、ApiKeyで認証していきます。（念の為公式のもemail, passward認証も）

function togglApi(apiUrl, email, password) {
 const basicCode = base64.encode(`${email}:${password}`);
 const response = UrlFetchApp.fetch(apiUrl, {
   method: "get",
   headers: {
     "Content-Type": "application/json",
     "Authorization": `Basic ${basicCode}`,
   },
 });
 var responseData = JSON.parse(response.getContentText());
 return responseData;
}

URLはtogglのwikiに書いてありますが、欲しい情報によって使い分ける必要があります。

例えば以下のようなtogglの情報を取得したいとなった場合、

togglの公式ドキュメントにてreports→DetailedにそのAPIURLと詳しい情報が書かれています。

この場合、APIURLは以下のようになります。

https://api.track.toggl.com/reports/api/v3/workspace/{workspace_id}/search/time_entries

ここで出てきたAPIのworkspace_idというのはtogglのwebサービスで言うURLの以下の数字です。

これらを当てはめることでTogglのAPI通信が完了します。

ここで注意なのですが、togglは1回のAPI通信が50件までという制約があります。
しかし、大量のデータを書き出すこともよくあると思うので、今回対処した方法も後ほど説明します！

Google App ScriptでAPIのPOST通信を行う

今回使うtogglのAPIはreportsのdetailsを取得するAPIなので以下のようになります。

/**
* detail情報取得用のPOST関数
*/
function fetchTogglData(apiKey, workspaceId, userArray, projectId, startDate, endDate, page) {
 const apiUrl = `https://api.track.toggl.com/reports/api/v3/workspace/${workspaceId}/search/time_entries`;


 // レポートのパラメータ
 const reportParams = {
     'start_date': startDate,
     'end_date': endDate,
     'project_ids': projectId,
     'user_ids': userArray,
     'first_row_number': page
   };
 // POSTオプション
 const options = {
   "method": 'post',
   "payload": JSON.stringify(reportParams),
   "headers": {
     "Content-Type": 'application/json',
     "Authorization": 'Basic ' + Utilities.base64Encode(apiKey + ':api_token')
   },
   "muteHttpExceptions": true
 }


 // レスポンスを取得
 const response = UrlFetchApp.fetch(apiUrl, options);
 const responseData = response.getContentText();
 const jsonData = {
   'body': JSON.parse(responseData),
   'header': response.getHeaders(),
 }
 return jsonData;
}

Detailsの値を取得するメソッドはPOSTなので、bodyにいくつかの値を設定する必要があります。
必要な値は、取得期間（開始日と終了日）、プロジェクトID、ユーザーID、データの開始行番号です。
返り値はjsonの形で返ってきます。

それぞれの型は以下の表を参考にしてください。

値名	型	（例）
開始日	文字列	’2024-03-09’
終了日	文字列	’2024-03-19’
プロジェクトID	文字列	‘1212121’
ユーザーID	配列	[山田, 佐藤, 加藤]
データの開始番号	数字	0

ここで注意なのですが、Google app scriptでPOST通信を行う場合、いわゆるbodyの要素はpayloadと表記されているみたいです。キーをpayloadに設定しないと正しく通信されないので注意してください。(ここがわかるのに2日くらいかかりました💦）
詳しくは公式ドキュメントを参照してください。
https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app?hl=ja

具体的な実装

それでは以上のことを踏まえて実際に実装した内容をトレースしてみましょう！

設計概要

今回実装した大まかな内容は以下の通りです。

Togglの情報をweb formのような形で入力したいので、Google Spread SheetでHTMLのモーダルを出力し、 そこからGoogle App Script経由でTogglのAPIを叩きます。

直接javascriptでAPIを叩けるのかなと思ったのですが、それはGoogleが許さないっぽいです。
なので今回のjavascriptの役割は、「フロントの制御」、「入力内容をGoogle App Scriptに渡すこと」の2つになります。
API取得自体はGoogle App Scriptで書いていきます。
Google App Scriptと言っても基本的にjavascriptと同じなのでやることはそんなに変わりはしないですね。

モーダル呼び出し

Spread SheetとGoogle App Scriptが同じGoogle出身（？）とだけあってこの辺りのメソッドはもうすでに提供されているみたいです。
Google App Scriptの作業スペースに以下のコードを追加します。

function openModal() {
 const htmlOutput = HtmlService.createHtmlOutputFromFile('index')
   .setWidth(500)
   .setHeight(700);


 SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Toggl情報入力');
}

SpreadsheetApp.getUi()の部分がモーダルの呼び出し部分になります。
上のHtmlServiceはFormをHtmlで構成するように設定する項目です。

続いて、呼び出すモーダルの元（HTML)を追加していきます。
Google App Scriptの作業スペースの.gsファイルの右上に＋ボタンを押します

HTMLを選択してhtmlファイルを追加していきます。
この中にフォームの内容を入れればOKです。

それではスプレッドシートに戻って実際に実行してみましょう！
上のメニューの拡張機能から　マクロ→マクロをインポート　を選択します。

するとGoogle App Script で作成した関数が選べるようになるので、関数を追加で関数を追加します。

するとマクロのなかに自分が作った関数が出てくると思うので選択して実行します。

初回起動は認証が求められますが、許可してください。

OKを押すとすごい怖い画面が出てきますが、無視して「詳細を表示」から無理やりアクセスしてください。
Google サービスにどっかのコードアクセスするけどええんか？みたいな感じですね。

Googleにログインしていない方はここでログインしなおして許可を押します。

もう一度、拡張機能のマクロから自分が作った関数を選択すると今度は動くかと思います。

JavascriptからGoogle App Script関数の発火

続いて、HTMLのフロントサイドのJavascriptからGoogleScriptの関数を発火させていきます。
まず、GoogleScript側の関数を作ります。
今回はTogglAPIの基本的な情報を取得していきましょう。

/**
* TogglAPIを叩く汎用的な関数
*/
function togglApiGet(apiUrl, apiKey) {
 let response = UrlFetchApp.fetch(apiUrl, {
   method: 'get',
   headers: {
     'Content-Type': 'application/json',
     'Authorization': 'Basic ' + Utilities.base64Encode(apiKey + ':api_token'),
   },
 });
 let responseData = JSON.parse(response.getContentText());
 return responseData;
}

こちらの関数を.gsファイルに追記します。
引数に取得したい情報のURLとapiトークンを入れることでjsonデータが帰ってくる関数です。

続いて、html側の記述です。

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
   <h1>モーダルオープン</h1>
   <form>
     <input type='text' id="api-token">
     <button type='button' onclick="connectToggl()">Toggl接続</button>
   </form>
 </body>
 <script>
   const connectToggl = () => {
     const apiKey = document.getElementById('api-token').value;
     google.script.run.withSuccessHandler(successFunction).withFailureHandler(handleError).togglApiGet('https://api.track.toggl.com/api/v9/me',apiKey);
   }
   const successFunction = (json) => {
     console.log(json);
   }
   const handleError = () => {
     alert('接続失敗');
   }
 </script>
</html>

HTMLで最低限のフォームを書いて、javascriptからGoogleScriptの関数を発火させています。
発火させている箇所は、

google.script.run.withSuccessHandler(successFunction).withFailureHandler(handleError).togglApiGet('https://api.track.toggl.com/api/v9/me',apiKey);

この箇所です。
toggleApiGetの部分がgsで書いた関数です。
正常に処理が行われた時のjs関数と、エラーが起きた時のjs関数を別で発火させるようにしています。

それでは一通り実行してみましょう。
モーダルを開くと以下のようになると思うのでこのフィールドにAPIキーを入れます。
（APIキーは次のURLの下部にあります。：https://track.toggl.com/profile )

Toggl接続ボタンを押して、コンソールにjsonDataが表示されていれば成功です。

こんな感じで、javascriptとGoogle App Scriptを行ったり来たりして、フロントから情報を入力していきます。

リクエスト上限の限界突破

必要な情報が集まったらここからが本番です。実際にdetailsの値を取得していきましょう。
POSTリクエストの送り方は冒頭に解説したので、それを基準に書いていきます。
togglAPIは50件以上のデータに関しては、headerにx-next-row-number
という値がくっついてきます。
この値は、名前の通り、50件以上のデータが帰ってくる場合、次のデータの行ナンバーを格納してくれています。

POSTリクエストを送るときにはfirst_row_numberの値を入れることが必須ですので、ここにheaderで取得したx-next-row-numberの値を入れて次のPOSTリクエストを繰り返すことで、50件以上のデータ取得にも対応してくれます。次の値がない場合はx-next-row-numberはnullを返すので、それを条件に繰り返し処理を終了すれば完了です。

/**
* Reports情報の取得
*/
function fetchAndWriteTogglData(apiKey, workspaceId, userArray, projectId, startDate, endDate) {
 // レポートの初期取得
 let page = 1;
 var resultResponse = [];
 var firstResponse = fetchTogglData(apiKey, workspaceId, userArray, projectId, startDate, endDate, page);
 Object.assign(resultResponse, firstResponse);


 while (resultResponse.header['x-next-row-number'] != null) {
   page += 50;
   var nextPageResponse = fetchTogglData(apiKey, workspaceId, userArray, projectId, startDate, endDate, page);
   resultResponse.header = nextPageResponse.header;
   for (var i = 0; i < nextPageResponse.body.length; i++) {
     resultResponse.body.push(nextPageResponse.body[i]);
   }
 }
 // タグデータの一覧取得
 const tagData = togglApiGet(`https://api.track.toggl.com/api/v9/workspaces/${workspaceId}/tags`, apiKey);
 writeDataToSpreadsheet(resultResponse.body, tagData);
}


/**
* detail情報取得用のPOST関数
*/
function fetchTogglData(apiKey, workspaceId, userArray, projectId, startDate, endDate, page) {
 const apiUrl = `https://api.track.toggl.com/reports/api/v3/workspace/${workspaceId}/search/time_entries`;


 // レポートのパラメータ
 const reportParams = {
     'start_date': startDate,
     'end_date': endDate,
     'project_ids': projectId,
     'user_ids': userArray,
     'first_row_number': page
   };
 // POSTオプション
 const options = {
   "method": 'post',
   "payload": JSON.stringify(reportParams),
   "headers": {
     "Content-Type": 'application/json',
     "Authorization": 'Basic ' + Utilities.base64Encode(apiKey + ':api_token')
   },
 }


 // レスポンスを取得
 const response = UrlFetchApp.fetch(apiUrl, options);
 const responseData = response.getContentText();
 const jsonData = {
   'body': JSON.parse(responseData),
   'header': response.getHeaders(),
 }
 return jsonData;
}

ここで注意なのですが、Google App ScriptでAPIを叩く時、帰ってくるデータのヘッダーの値を取得するときはfetchTogglData()関数で行っているように、getHeaders()メソッドを使用しないと取得することができません。
今回は、APIで取得した値に関してbodyとheaderのキーを作った配列を作り、それぞれにheaderとbodyの要素を入れています。

// レスポンスを取得
 const response = UrlFetchApp.fetch(apiUrl, options);
 const responseData = response.getContentText();
 const jsonData = {
   'body': JSON.parse(responseData),
   'header': response.getHeaders(),
 }
 return jsonData;

ここまでできたらあとは整形してスプレッドシートに書き出すだけです。

データの整形

このままのデータでは1日のうちの同じタスク名がタイマーの数だけでてしまいます。
同じタスク名を集計してくれるように関数を書いていきます。

/**
* detail情報取得用のPOST関数
*/
function organizeJsonData(jsonData, tagData) {
 const entries = jsonData;
 const dataToWrite = []; // 整形後のデータ保存用配列
 for (let i = 0; i < entries.length; i++) {
   const entry = entries[i];
   const dateTime = new Date(entry.time_entries[0].start);
   const formattedDate = Utilities.formatDate(dateTime, 'GMT', 'yyyy/MM/dd'); // 日時を日付に変換
   const userName = entry.username;
   const description = entry.description;
   const duration = entry.time_entries[0].seconds / 3600; // ミリ秒から時間に変換\


   // タグIDから名前を取得
   const tagId = entry.tag_ids;
   const getTagIndex = id => {
     const tagIndex = tagData.findIndex(data => data.id === id);
     return tagData[tagIndex];
   };
   const tagNames = [];
   for(let i = 0; i < tagId.length; i++) {
     tagNames.push(getTagIndex(tagId[i]).name);
   }
   const tags = tagNames[0]; // 今回はタグを一つしか使わない運用なので[0]のみ


   // 同じ日の同じdescriptionに関するデータを合計
   var existingDataIndex = findExistingDataIndex(dataToWrite, formattedDate, description);
   if (existingDataIndex !== -1) {
     // すでに同じ日の同じdescriptionがある場合は作業時間を合計
     dataToWrite[existingDataIndex].duration += duration;
   } else {
     // 同じ日の同じdescriptionがない場合は新しいデータを追加
     dataToWrite.push({
       formattedDate: formattedDate,
       userName: userName,
       duration: duration,
       description: description,
       tags: tags
     });
   }
 }
 return dataToWrite;
}


/**
* 同じ日の同じdescriptionに関するデータを探す関数
*/
function findExistingDataIndex(dataToWrite, formattedDate, description) {
 for (let i = 0; i < dataToWrite.length; i++) {
   var data = dataToWrite[i];
   if (data.formattedDate === formattedDate && data.description === description) {
     return i;
   }
 }
 return -1;
}

findExistingDataIndex関数にデータを渡して、同じタスク名（description)があるかをチェックし、あればそのindexを返していくと言った構造になっています。

SpreadSheetに書き出し

あとはこの関数をspreadsheetに書き込む前に呼び出してあげるだけです。

/**
* データをスプレッドシートに書き込む関数
*/
function writeDataToSpreadsheet(jsonData, tagData) {


 const organizeData = organizeJsonData(jsonData, tagData);
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = spreadsheet.getSheetByName('TogglData'); // スプレッドシートの名前を適切に設定


 // シートが存在しない場合は作成
 if (!sheet) {
   sheet = spreadsheet.insertSheet('TogglData');
 }


 // ヘッダーを書き込む
 sheet.getRange(1, 1).setValue('日時');
 sheet.getRange(1, 2).setValue('作業者');
 sheet.getRange(1, 3).setValue('対応時間(h)');
 sheet.getRange(1, 4).setValue('タスク');
 sheet.getRange(1, 5).setValue('分類');


 // データをシートに書き込む
 for (let j = 0; j < organizeData.length; j++) {
   var data = organizeData[j];
   sheet.appendRow([data.formattedDate, data.userName, data.duration, data.description, data.tags]);
 }
}

実行するとspreadsheetにTogglDataシートが追加され、そこに書き出されます。
これで作業自体は完了です。

フォームのHTMLなどを自分好みにカスタマイズすることでよりパーソナルな書き出しをすることができるので色々試してみたください。

実際に使ってみたい人は以下のURLからスプレッドシートを複製して自分の環境で試してみてください。

作ったもの

作ったもの

スプレッドシート（コピーして使ってみてください）※実行は全て自己責任でお願いします。

複製用SpreadSheet

コード(Gs,Js)

使い方

複製用SpreadSheetを開いて、フィアル→コピーを作成から自分のGoogle Driveにコピーを作成します。

警告文が出てますがそのまま無視で大丈夫です。
コピーを作成を押します。

コピーができたら拡張機能→マクロ→onOpenをクリックします。

初回起動は認証が必要なのでOKを押します。

スプレッドシートを開いてるGoogleアカウントを選択します。

ちょっと怖い警告文が出てきますが、詳細をタップしてそのまま開きます（特に壊れるコードは書いてないはず。。。心配な方はコード本体を見てください。）

ここも許可を押します。

Togglメニューからexportをクリックします。

以下のようなモーダルが出れば成功です（初回はちょっと時間かかるかも）
あとはTogglのAPIキーを入れて、UI通りに選択していけば書き出されます。

終わりに

今回は既存のサービスのAPIを用いて、自分のニーズにあったものを作っていきました。
タスクの書き出しの時間が削れてだいぶ楽をできるようになったのですが、togglにもcsvの書き出す機能はあるので、運用でどうしてもしないといけないという人だけやるべきかなとは思います（笑）
それでもAPI通信や、データの整形の方法などは、今回の実装を通してとても勉強になったのではないかなと思います。