ＩＴリテラシー研究会 in 廿日市: GASとPowerShellを使ったスプレッドシートの更新実証

サンフレッチェのサッカースタジアム　　　　

Google Spread Sheet（以下，スプレッドシートという）を外部から更新する方法には，Google Sheet APIを利用する方法以外にGoogle Apps Script（以下，GASという）を利用する方法があります。

この方法はGoogle Cloud Consoleを利用しなくても良いので手軽ですが，スプレッドシート側にREST API(HTTP/HTTPS)として機能するGASスクリプトを作る必要があります。

一方，この方法はスプレッドシートを思い通りに変更できますので，システム開発にはこちらの方が有利かもしれません。

また，セキュリティ面からGAS内にシークレットキー等を設定して，より安全に更新制御したりすることも出来ます。

今回は，この方法の概要を簡単に記述します。

【実証前提条件】

・クラウドアプリ　：　Google Spread Sheet

・ツール　　　　　：　PowerShellを利用

それでは始めます。

はじめに
「doget()」関数の作成方法
「dopost()」関数の作成方法
Webアプリの作成
PowerShellからの呼び出し方法
まとめ

１．はじめに

実証の概要を説明します。スプレッドシートを更新する外部スクリプトはPowerShell上に構築します。そのためのコマンドには「Invoke-RestMethod」を利用します。

「Invoke-RestMethod」は、PowerShellからREST API(HTTP/HTTPS)を呼び出し、データを取得・送信するコマンドレットです。最大の特徴はJSONやXML形式の応答を自動的に解析し、PowerShellのオブジェクトとして利用できるため、ConvertFrom-Jsonの手間を省いて即座にデータを操作できる点です。

また，GoogleDrive上のスプレッドシートに紐づく「コンテナバインドスクリプト」として読み取り用の「doget()」と書き込み用の「dopost()」というREST API(HTTP/HTTPS)用関数をつくります。

GASにはGoogleドライブに紐づく「スタンドアロンスクリプト」とスプレッドシートに紐づく「コンテナバインドスクリプト」があり，用途に合わせて使い分けます。

今回は，スプレッドシートを扱う関数ですので，「コンテナバインドスクリプト」になります。

２．「doget()」関数の作成方法

それでは，Powershellからスプレッドシートを呼び出すためのGASスクリプトを作成します。

具体的には，以下のスクリプトコードをご覧ください。

GASスクリプト

function doGet(e) {
  // 読み取り処理
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート名");
  const values = sheet.getDataRange().getValues();
  // 1行目はヘッダーとして、オブジェクト配列に変換して返すとPS側で扱いやすいです
  const headers = values.shift();
  const data = values.map(row => {
    let obj = {};
    headers.forEach((h, i) => obj[h] = row[i]);
    return obj;
  });
  Logger.log(JSON.stringify(data, null, 2));
  return ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

この関数では，読み込んだスプレッドシートの配列の１行目を「shift」メソッドを使って抜き出し，キー付きJSONファイルに変換してreturnしており，PowerShell側で扱いやすいようにしています。

「JSON.stringify()」メソッドは，オブジェクトをJSON形式の文字列に変換するメソッドです。

PowerShell側でこのJSON形式の文字列をオブジェクトに戻すことになります。

３．「dopost()」関数の作成方法

次に，powerShell側で更新した配列を受けとって，スプレッドシートを更新するための関数スクリプトを作成します。

具体的には，以下のスクリプトコードをご覧ください。

GASスクリプト

const SECRET_TOKEN = "your-token-key"; // 簡易認証用
function doPost(e) {
  const payload = JSON.parse(e.postData.contents);
  
  // セキュリティチェック
  if (payload.token !== SECRET_TOKEN) {
    return ContentService.createTextOutput("Unauthorized");
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("シート名");
  
  // 例: IDが一致する行を探して結果を書き込む
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] == payload.ID) { // 0列目をIDとする場合

      // ここに任意の処理を書く   

    }
  }
  return ContentService.createTextOutput("Success");
}

この関数では，今回のスプレッドシートが配列０カラムに「ID」というユニークなデータを持っていると仮定しています。

Pwershell側で更新するデータをどのように編集して「dopost()」してくるかは，設計者次第になります。スプレッドシート全体を一括で更新することも可能です。

「your-token-key」は任意のキーを設定します。Powershell側と同調する必要があり，合致しない場合は，「Unauthorized」文字を返しています。

４．Webアプリの作成

これらのスクリプトをPowerShellから「Invoke-RestMethod」で呼び出すためのウェブアプリをデプロイします。

GASのスクリプトエディターの上部にある「デプロイ▼」から新しいデプロイを選択します。

デプロイ画面が開きますので，左側の「種類の選択（歯車マーク）」で「ウェブアプリ」を選びます。

次のユーザーとして実行を「自分で実行」，アクセスできるユーザーを「全員」にします。

最後に右下の「デプロイ」をクリックします。

デプロイが終了したら，「ウェブアプリのURL」をコピーしておきます。

５．PowerShellからの呼び出し方法

次にPowerShellからの呼び出しスクリプトを作成します。

設計者によって，内容が異なると思いますが，例として以下のようなスクリプトを作成しました。

PowerShellスクリプト

$url = "ウェブアプリのURL"
$token = "your-token-key"
$lockFile = "$PSScriptRoot/script.lock"

# 排他制御
if (Test-Path $lockFile) { exit }
New-Item $lockFile -ItemType File | Out-Null

try {
    # 1. スプレッドシートのデータを取得 (GET)
    $rows = Invoke-RestMethod -Uri $url -Method Get

    foreach ($row in $rows) {

        # ここに1行づつの更新処理を書く

        # Tokenを先頭に追加
        $postBody = $row | Select-Object @{Name="token"; Expression={$token}}, * | ConvertTo-Json

        # 中身を確認
        $postBody | Format-Table
        # 1行ずつ更新した上でGASにdepostする
        $response = Invoke-RestMethod -Uri $url -Method Post -Body $postBody -ContentType "application/json; charset=utf-8"
        if ($response -eq "Success") {
            Write-Host "ID: $($row.ID) を更新しました。"
        } else {
            Write-Host "ID: $($row.ID) の更新が失敗しました。errmsg: $response"
        }
    }
}
catch {
    Write-Error "ファイル取得に失敗: $($_.Exception.Message)"
}
finally {
    # ロック解除
    Remove-Item $lockFile -ErrorAction SilentlyContinue
}

このスクリプトでは「script.lock」というファイルを作成して，二重に起動しないように排他制御を行っています。

先ほどGAS側で作成した「ウェブアプリのURL」を使ってInvoke-RestMethodよりアクセスします。

PowerShellの「Invoke-RestMethod: REST APIのレスポンス（JSONなど）GET時」では、自動的にPowerShellオブジェクト（PSCustomObject）に変換して返しますので「ConvertFrom-Json」する必要はありません。

POSTする本文「$postbody」の冒頭に，「@{Name="token"; Expression={$token}}」というシークレットキーのJSONファイルを追加しています。これにより，GAS側でシークレットキーチェックを行います。

POST時におけるContentTypeは，「-ContentType "application/json; charset=utf-8"」としています。charsetを与えないと文字化けしました。

また，このスクリプトでは，1行ごとGASにPOSTして更新処理するサンプル例を示しています。（GASの利用頻度条件にご注意ください）

try～catch構文では，「finally」で排他制御のロックを解除しています。

６．まとめ

今回は，PowerShellからGASを通じてスプレッドシートを更新する方法を記述しました。

Google AppSheetなどのスマホやスプレッドシートを扱うシステムと，外部システム（例えば基幹システム）とを手軽に連携することが出来ます。

PowerShellは，「.Net」などのコマンドも利用できるため，便利なコマンドプロンプトですが，スクリプト実行時に権限が必要になる場合があります。

PowerShellスクリプトの実行で「実行ポリシー」が必要になる場合は，PowerShellに「-ExecutionPolicy RemoteSigned」オプションを付けて起動し，スクリプト実行して下さい。