【定期実行】サスケWorks API と GoogleAppsScript でスプレッドシートに書き出す方法

小原 達也/ 2024年 1月 9日/ 技術 サスケWorks

こんにちは！小原です。

今回はサスケWorksからGoogleスプレッドシートに書き出す方法をご紹介します。

サスケWorksのプレミアムプランにご加入で、Googleアカウントをお持ちの方であればすぐにお試しいただけます。

スプレッドシート連携により、次のようなことが可能です。

• サスケアカウントのない人がデータを閲覧できる
• 決まった時間にバックアップを取る

コピペで簡単に実装できます。
ぜひお試しください！

サスケWorks API 利用上の注意点

Premiumプランご加入の場合、サスケWorks APIは追加料金なしで利用できます。

ただし1か月のリクエスト回数が10万件を超える場合、1,000円/1万件 でリクエスト枠を購入することになります。

大量のリクエストをする予定がある場合には、この点ご注意ください。

なお、上記は2024年1月9日現在の情報です。
最新の情報については、公式APIドキュメントをご確認いただければと思います。

>> API利用上の制限について

完成イメージ

完成イメージは次の通りです。

NorthTorchではサスケWorksを蔵書管理に使っています。（詳しくはこちら）
ここでは、蔵書のデータをスプレッドシートに書き出してみます。

今回の設定により、次の作業を実行します。

• シートを新規作成
• 取得時点の全レコードを転記する
• 毎日決まった時間に定期実行

シート名は「作成時点の日付（年月日）」にしてみました。

実装までの流れ

次の流れで実装します。

1. サスケWorksから必要情報を取得する
2. 転記先のスプレッドシートを新規作成する
3. プログラミングコードを貼り付ける
4. 定期実行の設定をする

どれもそこまで難しい手順はありません。
同じように操作いただければ、簡単に実現できると思います。

1.サスケWorksから必要情報を取得する

サスケWorksからは、以下の3つの情報を取得してきます。

• APIキー
• APIトークン
• APPキー

まずはサスケWorksの右上「歯車のマーク」をクリックします。
メニューの中から「APIの管理」をクリックしてみてください。

以下のようなAPIの設定画面に遷移します。
「新規作成」ボタンをクリックしてください。

分かりやすい名前をトークン名に設定してから、登録します。

APIのメインメニューに戻ります。
APIキーが表示されているので、ここで控えを取っておいてください。

その後、先ほど作成したトークン名のリンクをクリックします。

遷移先のページでAPIトークンが表示されます。
後ほど使いますので、こちらも控えを取っておきましょう。

アプリの画面に戻り、アドレスバーからAPPキーを確認します。（おそらくすべて大文字です）
こちらも後ほど使いますので控えを取ります。

2.転記先のスプレッドシートを新規作成する

続いてスプレッドシート側の設定に移ります。
まずはGoogleDriveのホーム画面に移動してください。
>> Google Drive にアクセスする

右クリックして「Googleスプレッドシート」を選択します。

すると新規でSpreadSheetが作成されますので、それをダブルクリックで開きます。

3.プログラミングコードを貼り付ける

タブ「拡張機能」から「Apps Script」を選択します。

以下のような画面が表示されます。

赤で囲った部分がコードを入力する場所です。
いったんこちらを全削除して、以下のコードを貼り付けてください。

/*!
 * SaaskeSDK
 * Copyright (c) North Torch Co.,ltd. 2023
 * Released under the MIT License.
 * ライセンスの全文は以下をご参照ください
 * LICENSE: https://github.com/northtorch/saaske-sdk/blob/main/LICENSE.md
 * README: https://github.com/northtorch/saaske-sdk/blob/main/readme.md
*/

function transposeArray(array) {
  // 転置（行と列の入れ替え）
  return array[0].map((_, colIndex) => array.map(row => row[colIndex]));
}

function reorderColumns(newOrder) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const data = range.getValues();

  // データを転置
  const transposedData = transposeArray(data);

  // 列数と同じだけの空の要素を持つ配列を作成
  let newColumns = new Array(transposedData.length).fill(null);

  // 未転記列のリスト
  let completeList = []
  // 新しい順序に基づいて列を埋める
  newOrder.forEach(function (order, index) {
    newColumns[order - 1] = transposedData[index];
    completeList.push(index)
  });

  // 未転記の列を最後尾に加える
  for (i = 0; i < newColumns.length; i++) {
    if (newColumns[i] == null) {
      for (let j = 0; j < transposedData.length; j++) {
        if (!completeList.includes(j)) {
          newColumns[i] = transposedData[j];
          completeList.push(j);
          break;
        };
      };
    };
  };

  // 再度転置して元の形に戻す
  const finalData = transposeArray(newColumns);

  // 並び替えたデータをシートにセットする
  range.setValues(finalData);
}

// 辞書から Key=name の value を抽出して返す
function extractNameFromDict(item) {
  if (typeof item === "object" && item != null && item.hasOwnProperty('name')) {
    return item.name;
  } else if (Array.isArray(item)) {
    // 配列だった場合には本関数を再帰的に適用
    // updateAccount, registAccountなどは配列に入っている
    return item.map(extractNameFromDict);
  } else {
    return item;
  };
}

// yyyymmdd を文字列で返す
function getCurrentDate() {
  const now = new Date();
  const year = now.getFullYear();
  let month = now.getMonth() + 1; // 月は0はじまり
  let day = now.getDate();

  // 月と日が一桁なら、先頭に0を追加
  month = month < 10 ? '0' + month : month;
  day = day < 10 ? '0' + day : day

  // yyyymmdd 形式の文字列で返す
  return `${year}${month}${day}`;
}

function createNewSheet(sheetName = getCurrentDate()) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const oldSheet = spreadsheet.getSheetByName(sheetName);

  if (oldSheet) {
    spreadsheet.deleteSheet(oldSheet)
  };

  const sheet = spreadsheet.insertSheet(sheetName);
  return sheet;
}

function getSaaskeApiData(apiKey, apiToken, appKey, next) {
  let baseUrl = `https://api.works.app/v1/${appKey}/records`;

  // 1000レコードを超える場合に分割される
  // その場合、ページ番号をクエリパラメータで指定
  if (next != null) {
    const queryParams = `customKey=${next}`
    url = baseUrl + '?' + queryParams
  } else {
    url = baseUrl;
  };
  const headers = {
    'x-api-key': apiKey,
    'x-token': apiToken
  };
  const options = {
    'method': 'get',
    'headers': headers
  };
  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());
  return data;
}

function writeDataToSheet(data, index) {
  const allRecord = data.records;
  const sheet = createNewSheet();
  let headers = Object.keys(allRecord[0]);
  let values = allRecord.map(function (item) {
    return headers.map(function (header) {
      return item[header];
    });
  });
  // 辞書があれば、key=nameの値を採用する
  values = values.map(row => row.map(extractNameFromDict));

  // 1ページ目の場合のみ実行
  if (index == 0) {
    // 一行目に見出しを配置
    sheet.getRange(1, 1, 1, headers.length).setValues([headers])
  };

  // 最終行を取得
  const lastRow = sheet.getLastRow();
  // 二行目以降に値を設定
  sheet.getRange(lastRow + 1, 1, values.length, headers.length).setValues(values);

  next = data.next;

  return next;
}

function translateFirstRow(translationMap) {
  // スプレッドシートを開く
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 最初の行を取得
  const firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  const firstRow = firstRowRange.getValues()[0];

  // 文字列を置き換える
  const translatedRow = firstRow.map(item => translationMap[item] || item);

  // シート上の値を更新する
  firstRowRange.setValues([translatedRow])

  // 変換された行のデータをログに出力
  Logger.log(translatedRow);

}

function main() {
  // SaaskeAPI 設定値
  const apiKey = '';
  const apiToken = '';
  const appKey = '';
  const newOrder = [];
  const translationMap = {};

  // データを取得する
  let next = null;
  for (let index = 0; true; index++) {
    const data = getSaaskeApiData(apiKey, apiToken, appKey, next);
    next = writeDataToSheet(data, index);

    // 1000件以上レコードがあるとnext!=undefinedになる
    if (typeof next === "undefined") {
      break;
    };
  };

  // 見出しを変更
  translateFirstRow(translationMap);

  // 列の並び順を変更
  reorderColumns(newOrder);
}

コードの中に以下の項目があります。
ここには先ほど控えを取ったAPIキーなどの情報を記入しておきましょう。

「デバッグ」の右の項目を「main」に変更します。
そのうえで「実行」ボタンをクリックしてみてください。

するとアプリの全レコードがSpreadSheetに転記されます。

これで正常に動作することが確認できました。

4.定期実行の設定をする

ここからは定期実行を行いたい方のみ、ご覧ください。

設定は簡単です。

まずは先ほどのコードをコピーした画面に移動して、時計のマークをクリックします。

右下のトリガーを追加をクリックします。

以下を参考に、スケジュールを設定してみてください。
例では毎日、午後3-4時に自動実行する設定にしました。

これで3時から4時までのどこかのタイミングで実行されます。
何分に実施されるかについては、指定できません。

また、「実行する関数を選択」は「main」としてください。

無事に自動実行されると、以下のように「前回の実行」の欄に実行された日時が表示されます。

その他の細かいカスタマイズ方法

ここまで実践いただくとお気づきかもしれませんが、次の問題が残っています。

1. 列の順番が崩れる
2. 列名がアルファベットで表示される

先ほど貼り付けていただいたコードを一部修正することで、これらに対応します。

1.列の順番が崩れる

列の順番を変更するため、以下のコードを少し修正します。

const newOrder = [];

このブラケット [] の中に、並び替え後の順番をカンマ区切りで入力します。

例えば、以下のような表があったとします。

列１ ｜　列２　｜　列３

これを以下のように変更したい場合を考えてみましょう。

列２ ｜　列１　｜　列３

コードを以下のように修正すると、列の順番を並び替えられます。

const newOrder = [2, 1, 3];

こちらは新しく出力されたものに対してのみ有効です。
既に書き込み済みのシートには適用されませんので、ご注意ください。

2.列名がアルファベットで表示される

列名を変更したい場合は、以下の部分のコードを変更します。

const translationMap = {};

'変更前の文字列': '変更後の文字列'というペアを作ります。

例えば、「title」という列名を「タイトル」に変更したい場合には、次のようにします。

const translationMap = {
    'title': 'タイトル'
};

複数の列名を変更したい場合には、以下のようにします。

const translationMap = {
    'title': 'タイトル',
    'author': '著者'
};

要素同士はカンマで区切ることに注意してください。

こちらも列順と同様、すでに出力済みのシートには適用されませんのでご注意ください。

まとめ

本記事で行った設定内容をまとめると、以下の二つです。

• サスケWorksからAPIキーなどを取得
• スプレッドシートにコードを貼り付け

これによりサスケWorksからレコードを一覧を取得して、スプレッドシートに書き込むことができました。

本ブログでは他にもサスケWorks関連の記事をアップしています。

NorthTorch - サスケWorks 関連記事

ぜひ、別記事の方もご覧ください！

ソースコード公開中

本記事で紹介したコードは、GitHubからもご覧いただけます。

https://github.com/northtorch/saaske-sdk/blob/main/fetchAllDataAndWriteToSheet.gs