Google Apps Script（GAS）を利用して、スマレジとスプレッドシートをAPI連携してみた

isfuliti

2024年5月14日 22:52

今回はスマレジとスプレッドシートをAPI連携してみたいと思います。
具体的にはスマレジの取引情報に変化があったら、自動的にスプレッドシートにデータが反映されるように連携させてみたいと思います。

事前必要項目
①スマレジ　管理権限と同等の基本的な権限
②google　一般的なアカウントが必要

スマレジ・デベロッパーズ　アカウント作成

スマレジとAPI連携を実現するためには、スマレジ・デベロッパーズを利用してアプリを作成する必要があります。
まずはスマレジ・デベロッパーズのアカウントを作成します。

下記リンクをクリックして、アカウントを作成してください。

必要項目を入力して、アカウントを作成します。

アカウント作成後、スマレジ・デベロッパーズにログインできるようになります。

スマレジアプリ作成

ログインできたら、左のメニュー「アプリ」からプライベートアプリを選択します。

その後、右上に表示される「プライベートアプリを新規登録」をクリック

アプリ区分：WEBアプリ
アプリ名：スプシテスト（別に何でもいいです）
アイコン：適当に画像データを選択（何故かアイコンが必須なので、下記フリー素材を載せておきます）

アイコン用のフリー素材は下記からダウンロード（別に何でも良いです）

次に「登録」ボタンを押します。
これでひとまずアプリが作成されました。
この先で詳細を設定していきます。

まずは、「スコープ」を設定していきます。
今回は取引情報を取得したいので、
「スコープ」を選択して、[pos.transactions:read]　取引/予算の参照ができます。の右側をクリックし、青色に変わったことを確認します。

続いて、「環境設定」を行います。

まず「開発環境」の設定をおこないます。
アプリのURL：以下のURLをコピペ

https://id.smaregi.dev/

利用者契約通知先URL：以下のURLをコピペ

https://api.smaregi.dev/

リダイレクトURLは変更しなくて良いです。
続いて、本番環境の設定をおこないます。

アプリのURL：以下のURLをコピペ

https://id.smaregi.jp/

利用者契約通知先URL：以下のURLをコピペ

https://api.smaregi.jp/

「開発環境」と同様に「本番環境」でも、リダイレクトURLは変更しなくて良いです。

続いて、クライアントシークレットを発行します。「本番環境」の「再発行」ボタンを押してください。

アプリ名「スプシテスト」を入力して、再発行を押します。

クライアントシークレットが発行されるので、右のコピーボタンを押します。

一旦、メモ帳などにクライアントシークレットを貼り付けておきましょう。

また、同じく「本番環境」のクライアントIDもメモ帳などにコピペしておきましょう。

この状態で、一度、右下の「保存」を押します。

最後にアクティベートをします。
まずはスマレジの利用契約IDを調べます。
ここで注意していただきたいのは、今使用しているスマレジ・デベロッパーズの契約IDではなく、スマレジの契約IDを調べる必要があります。

スマレジにログインします。

ログインしたら、左上の利用者名の下の契約IDをクリックします。すると、契約IDをコピーすることができます。

再び、スマレジデベロッパーズアプリに戻り、「利用状況」の「契約ID」に先ほどコピーした契約IDを貼り付け、「アクティベート」ボタンを押します。

この時、スマレジアプリの基本情報に設定しているメールアドレスに承認メールが届くため、事前に管理者へ知らせておく必要があります。

設定しているメールアドレスに承認メールが届きます。
新たな契約を結ぶ形にはなりますが、基本的に自分で作成したアプリは無料なので、変更前と変更後の金額に問題がなければ、「同意します」にチェックを入れ、「上記の内容を申し込む」をクリックします。

再ログインが求められるので、再ログインすると今回のアプリが反映されます。
以上でスマレジアプリを作成することができました。
続いては、メモしたクライアントIDおよびクライアントシークレットを利用して、GASと連携していきます。

スプレッドシートとGoogle Apps Script（GAS）の準備

下のリンクからスプシを作成しましょう。googleアカウントない方は作ってログイン。

スプシの名前も何でもオッケーです。左上の「無題のスプレッドシート」をクリックすると簡単に名前を編集できます。
今回は「スマレジテスト」としておきました。

スプシが作成できたら、次はGoogle Apps Script（GAS）の準備です。
上のメニューから「拡張機能」→「Apps Script」を選択し、GASを起動させてください。こちらも名前は何でも良いのですが、せっかくなのでスプレッドシートの名前と同じにしておきましょう。

次に、ライブラリーを２つ設定します。
「OAuth2」と「Parser」です。

まずは、左のメニューからライブラリーの「＋」マークをクリックしてください。
スクリプトIDに以下のIDをコピペし、「検索」ボタンを押してください。

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

IDに「OAuth2」が表示されたことを確認して、追加ボタンを押します。
同様に「Parser」も追加します。
ライブラリーの「＋」マークをクリックし、
スクリプトIDに以下のIDをコピペし、「検索」ボタンを押します。

1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw

IDに「Parser」が表示されたことを確認して、追加ボタンを押します。
ライブラリーに２つ追加されました。

続いて、コードを記入していきます。
初期設定されている「ファイル」の「コード.gs」を選択して、右側に表示された全てのコードを選択します。

選択したコードに以下のコードをコピペします。

//初期設定
const contactId = 'ここ１'; //スマレジID
const clientId = 'ここ２'; //クライアントID
const clientSecret = 'ここ３'; //クライアントシークレット
const nissuu = 2; //今日から何日分のデータを取得するか設定
function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu("スマレジ")
        .addItem("売上データ", "fetchSmaregi")
        .addToUi();
}
//取引に変更あったら
function doPost(e) {
  fetchSmaregi();
}

//取引情報
function fetchSmaregi(){
  //日付の設定
  let today = new Date();
  let year = today.getFullYear(); //年
  let month = ("0"+(today.getMonth()+1)).slice(-2); 
  let day = ("0"+(today.getDate())).slice(-2);

  let weekMae = new Date();
  weekMae.setDate(today.getDate()-nissuu);
  let yearMae = weekMae.getFullYear();
  let monthMae = ("0"+(weekMae.getMonth()+1)).slice(-2);
  let dayMae = ("0"+(weekMae.getDate())).slice(-2);
  let stDate = yearMae+'-'+monthMae+'-'+dayMae+'T00:00:00Z';
  let edDate = year+'-'+month+'-'+day+'T23:59:59Z';

  //アプリのアクセストークン取得
  let requestUrl = 'https://id.smaregi.jp/app/'+contactId+'/token';
  let requestHeaders = {
    'Authorization' : 'Basic ' + Utilities.base64Encode(clientId + ":" + clientSecret) ,
    'Content-Type' : 'application/x-www-form-urlencoded',
    'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36'
  };
  let requestPayload = {
    'grant_type': 'client_credentials',
    'scope': 'pos.transactions:read'
  }
  let requestOptions = {
    'method': 'post',
    'headers': requestHeaders,
    'payload': requestPayload
  };
  let response = UrlFetchApp.fetch(requestUrl, requestOptions);
  let appAccess = response.getContentText('UTF-8');
  let appAccessToken = Parser.data(appAccess).from('access_token":"').to('"').iterate();
  
  //日付から取引の取得
  let getUrl = 'https://api.smaregi.jp/'+contactId+'/pos/transactions?fields=&transaction_date_time-from='+stDate+'&transaction_date_time-to='+edDate+'&with_deposit_others=all&with_money_control';
  let getHeaders = {
    'Authorization' : 'Bearer ' + appAccessToken,
    'Content-Type' : 'application/x-www-form-urlencoded',
    'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36'
  };
  let getOptions = {
    'method': 'get',
    'headers': getHeaders
  };
  let getResponse = UrlFetchApp.fetch(getUrl, getOptions);
  let responseData = JSON.parse(getResponse.getContentText());
  let nameTori = 'シート1'
  writeToSpreadsheet(responseData,nameTori);

  //日付から明細の取得
  let fieldsmeiSentaku = 'transactionHeadId';
  let getmeiUrl = 'https://api.smaregi.jp/'+contactId+'/pos/transactions?fields='+fieldsmeiSentaku+'&transaction_date_time-from='+stDate+'&transaction_date_time-to='+edDate+'&with_detail_product_attributes=all&with_details=all';
  let getmeiHeaders = {
    'Authorization' : 'Bearer ' + appAccessToken,
    'Content-Type' : 'application/x-www-form-urlencoded',
    'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36'
  };
  let getmeiOptions = {
    'method': 'get',
    'headers': getmeiHeaders
  };
  let getmeiResponse = UrlFetchApp.fetch(getmeiUrl, getmeiOptions);
  let responsemeiData = JSON.parse(getmeiResponse.getContentText());
  
  //ここから要素の数だけループを作る
  let obj = [];
  for(let i=0; i<responsemeiData.length; i++){
    if(responsemeiData[i]['details'] !== null){
      for(let j=0; j<responsemeiData[i]['details'].length; j++){
        obj.push(responsemeiData[i]['details'][j])
      }
    }
  }
  let nameMei = 'シート2'
  writeToSpreadsheet(obj,nameMei);
}

//シートへ反映
function writeToSpreadsheet(data,nameData) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameData); 
  sheet.clear();
  // 例: データの最初の行にヘッダーを書き込む
  let spreadHeaders = Object.keys(data[0]);
  sheet.appendRow(spreadHeaders);
  // データの各行をスプレッドシートに書き込む
  data.forEach(function (row) {
    let values = Object.values(row);
    sheet.appendRow(values);
  });
}

もちろん、このままでは不十分です。
初期設定部分のコードを書き換える必要があります。
'ここ１' → 'スマレジID'　スマレジの契約ID
'ここ２' → 'クライアントID'　先ほどコピペしたクライアントID
'ここ３' → 'クライアントシークレット'　同じくクライアントシークレット
以下は例です（こちらをコピーしてもエラーになります）
ちなみにnissuに設定されている数字「2」を変更すると、取得するデータの日数を変更できます。後ほど調整してみて下さい。

//初期設定
const contactId = 'ojci793e3'; //スマレジID
const clientId = 'fjaohoga9gaoiga84hogi40iijks0jjj'; //クライアントID
const clientSecret = 'f8sgj4lognvn4bsafho0sno34nogsonpmmlmw82mlmva9vmmmiushot5jp6jpk3p'; //クライアントシークレット
const nissuu = 2; //今日から何日分のデータを取得するか設定
function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu("スマレジ")
        .addItem("売上データ", "fetchSmaregi")
        .addToUi();
}

API連携テスト

初期設定部分の書き換えが終わったら、上メニューのデバックの右がonOpenになっていることを確認して、「実行」ボタンを押します。

すると、承認を求められます。
権限を確認をクリックしてください。

続いて、googleアカウントの確認です。
問題なければログインを選択。

このアプリはGoogleで確認されていません
と怖いメッセージが表示されますが、左下の「詳細」をクリックしてください。

信頼できる場合のみ、と表示されますが、自身のgoogleアカウントなので、「スマレジテスト（安全ではないページ）に移動」をクリックします。

続いて、アカウントの許可を求められたら、許可を選択してください。

スクリプトが実行され、ログが表示されま

スプレッドシートに戻ると、メニューにスマレジが追加され、さらに売上データが追加されていることが確認できます。

最後に、シートを追加します。
スプレッドシートの左下の＋ボタンを押して、「シート２」を追加します。

それでは、連携できたか確認しましょう。
メニューの「スマレジ」をクリックして、さらに「売上データ」をクリックしてみましょう。

「スクリプトを実行しています」と表示されるので、しばらく待ちます。

「スクリプトが終了しました」が表示されたら成功です。

終了

初期設定の「nissuu」を2に設定した場合、一昨日・昨日・今日のデータが取り込まれます。
スマレジに取引が発生していれば、以下のように表示されます。

シート１は列AからDKまで全部で115の項目が、
シート２は列AからBPまで全部で68の項目が読み込まれます。
シート１は取引に関するデータを、シート２は取引明細に関するデータを取得することができます。
様々なデータを取り出すことができますが、取り出したいデータはそれぞれだと思いますので、ここでは項目を絞り込んで、毎回同じように取り出す方法を紹介します。

スプレッドシートのQUERY関数

QUERY関数を使って、別シートに必要な列のみ転記させます。
まず、左下の＋をクリックして、新規シートを作成します。

「シート３」が作成される

シートが作成されたら、▼マークをクリックして、名前を「取引」に変更しましょう。

作成されたシートの１行目のセルに、
それぞれ以下の項目を追加してください。
A1：取引ID　B1：取引日時　C1：合計　D1：現金　
E1：クレカ　F1：数量　G1：端末取引ID　H1：カード会社

続けて、セルA2に以下の式をコピペして下さい

=QUERY('シート1'!A2:DK,"Select(A),(B),(P),(U),(V),(AC),(BC),(BZ)")

「Enter」を押すと、シート１から絞り込まれたデータのみ抽出されます。

同様に「シート2」から明細データも抽出します。
新規にシートを作成して、名前を「明細」に変更しましょう。

「明細」シートの１行目のセルに、
それぞれ以下の項目を追加してください。
A1：取引ID　B1：取引番号　C1：商品コード　D1：商品名　
E1：商品単価　F1：数量　

続けて、セルA2に以下の式をコピペし「Enter」を押してください

=QUERY('シート2'!A2:BP,"Select(A),(B),(F),(G),(M),(S)")

同様に明細データの抽出ができると思います。
QUERY関数のSelect後のアルファベットが、元のシートの列に該当します。
この部分を変えることで、欲しいデータを絞り込むことができます。

最後に、元データの「シート1」「シート2」はシートを非表示を選択して、隠しておきましょう。

売上データを取り込むごとに、自動で「取引」「明細」が取り込まれます。

ここまでは、手動でスプレッドシートのメニュー「スマレジ」→「売上データ」を押すことによって、データが取り込まれる方法を紹介しました。
このままでも十分なのですが、取引情報に変更があった時に、自動でデータを取り込む方法があります。
以下で紹介していきます。

Webhookの設定

まずは、GASの設定をおこないます。
左下のギアマークをクリックしてください。

プロジェクトの設定画面が表示されたら、右上の「デプロイ」→「新しいデプロイ」をクリックしてください。

新しいデプロイが表示されたら、ギアマークを押して「ウェブアプリ」をクリックしてください。

ウェブアプリの「次のユーザーとして実行」は自分のまま
「アクセスできるユーザー」は全員に変更して、「デプロイ」をクリックしてください。

デプロイが更新されるので、ウェブアプリのURL下のコピーボタンを押して、完了を押してください。

続いて、スマレジデベロッパーズアプリのWebhook設定をしていきます。
再び、スマレジデベロッパーズにログインしてください。

「アプリ」→「プライベートアプリ」→「スプシテスト」アプリを開いてください。
まずは「環境設定」をしていきます。

「開発環境」および「本番環境」それぞれのWebhook送信先エンドポイントに先ほどコピーした、URLを貼り付けて、右下の保存ボタンを押してください。

続いて、Webhookを開いて、「基本設定」の「Webhookの利用」を「利用する」に変更します。
さらに、「送信するイベント」→「スマレジ」→「取引」の右のボタンをONにします。
最後に、右下の保存ボタンを押してください。

以上で設定は完了です。
とは言え、正常に設定できたか、取引に変化がなければ確認できません。
そこで、スマレジを開いて既存の取引データのメモ等に適当に文字を入れて、更新してみましょう。

取引の更新はGASで確認することができます。
GASを開いて、左のバーから「実行数」をクリックしてみましょう。

先ほど、スマレジの取引を更新した時間に、種類「ウェブアプリ」、関数「doPost」が実行されていれば成功です。

実行状況が確認できる

スプレッドシートの「取引」および「明細」も最新のものに更新されているはずです。

ただし・・・
取引データが更新されるたびに、GASが実行されてしまうため、注意が必要です。まだ試験段階ならば、スマレジデベロッパーズアプリ「スプシテスト」のWebhook設定を「利用しない」にしておいてください。

この記事が気に入ったらサポートをしてみませんか？