概要

HTTPトリガーにより Azure Functions から Google Spreadsheets に書き込みその1 のつづきです。

何らかのトリガー（とりあえず HTTPトリガーを想定）により起動した Azure Functions (v2)（=Auzre Function App）の内部から Google Spreadsheets のセルに書き込みをする方法についてまとめました。専用のライブラリは使わずにRESTでGoogle Sheets API v4を操作する方法で実装しました。

RESTで Google Spreadsheets に書き込み

Azure FunctionsはC#で記述することができるので、はじめは、その1 で利用した「Google.Apis.Sheets.v4」ライブラリが使えそう…と思っていたのですがダメでした。原因は、ライブラリを使った認証処理のなかでファイルの入出力、ブラウザの起動などが行なわれるためです（クラウド上にある Azure Functions では直接的なファイルIOができません）。

そこで、REST、つまり、HTTPのリクエストによって Google Spreadsheets を操作することにしました。このためには、前回に取得方法を説明した「書き込み対象のシートのIDSheetId」と「アクセストークンaccess_token」を使用します。

RESTでセルに値を書き込む

セルA1から行方向に「2019/01/30」「15」「kame」という値を書き込みたいという要求があるとします。これは、書き込み対象のシートのIDSheetIdとアクセストークンaccess_tokenを埋め込んで、次のような HTTP PUTリクエストメッセージを送ることで実現できます。

PUT https://sheets.googleapis.com/v4/spreadsheets/{sheetId}/values/A1?valueInputOption=USER_ENTERED HTTP/1.1
Content-type: application/json
Authorization: Bearer {access_token}
Host: sheets.googleapis.com
Content-Length: 61

{"majorDimension":"ROWS","values":[["2019/01/30",15,"kame"]]}

実行するとスプレッドシートは次のように更新されます。

ちなみに、HTTPのレスポンスメッセージは次のようになります。

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
Vary: X-Origin
Vary: Referer
Date: xxxxxxxxxxxx
Server: ESF
Cache-Control: private
X-XSS-Protection: 1; mode=block
X-Frame-Options: SAMEORIGIN
Alt-Svc: xxxxxxxxxxxxx
Accept-Ranges: none
Vary: Origin,Accept-Encoding
Transfer-Encoding: chunked

{
  "spreadsheetId": "{sheetId}",
  "updatedRange": "Sheet1!A6:C6",
  "updatedRows": 1,
  "updatedColumns": 3,
  "updatedCells": 3
}

このようなHTTPリクエストは、WebClientなどのクラスから行なうことができるので、それを AzureFunctons 上に実装すれば目的は達成でいます。

アクセストークンには寿命がある

ここで「アクセストークンには寿命がある（有効期限がある）」という問題がでてきます。前回でてきたGoogle.Apis.Auth.OAuth2.Responses.TokenResponse-userを確認すると分かるのですが3600秒（＝1時間）で利用できなくなります。
期限を過ぎた場合は、リフレッシュトークン refresh_token、クライアントID client_id、
クライアントシークレット client_secretという3つの情報を使って、再度、アクセストークンを取得してこなければなりません。これらの情報の取得方法は前回に説明済みです。

アクセストークンの再取得の際に、ユーザによる再度の許可操作は不要です。また期限前にリフレッシュトークン使ってアクセストークンを再発行することもできました。

リフレッシュトークンを使ったアクセストークンの取得

RESTで行なうことができます。具体的には、次のようなHTTP POSTメッセージにより取得することができます。

POST https://www.googleapis.com/oauth2/v4/token HTTP/1.1
Host: www.googleapis.com
content-type: application/x-www-form-urlencoded
Content-Length: xxx

client_secret={client_secret}&grant_type=refresh_token&refresh_token={refresh_token}&client_id={client_id}

ボディをJSON形式ではなく、x-www-form-urlencoded形式（アンドマークでつなぐ）で与える点になります。また、内容はURLエンコードする必要もあります。例えば、リフレッシュトークンに/が含まれていた場合は、それを%2Fに置き換える必要があります。

このようなリクエストメッセージを送ると、次のようにアクセストークンを含んだレスポンスメッセージが返ってきます。ボディ部はJSON形式になっているので、ここからアクセストークンを取り出して利用します。

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Vary: X-Origin
Vary: Referer
Date: xxxxxxxxxxxxxxx
Server: ESF
Cache-Control: private
X-XSS-Protection: 1; mode=block
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
Alt-Svc: xxxxxx
Accept-Ranges: none
Vary: Origin,Accept-Encoding
Transfer-Encoding: chunked

{
  "access_token": "{access_token}",
  "expires_in": 3600,
  "scope": "https://www.googleapis.com/auth/spreadsheets",
  "token_type": "Bearer"
}

Azure Functionsに実装

ここまでのことを踏まえて Azure Functions に実装していきます。次のような流れになります。

HTTPリクエストをトリガーとして起動
アクセストークンを取得（期限の有無にかかわず）
取得したアクセストークンでGoogleSheetsAPIを利用

準備

Azureのポータルにアクセスして、Azure Functions（現在はFunction Appともよばれます）に関数を追加します。Visual Studio を使って開発する方法もありますが、今回はポータル内でコードを打ち込んで関数を作成します。

テンプレートの一覧のなかから HTTP trigger を選択します。

適当な名前をつけて認証レベルを Function に設定して作成します。ブラウザ内のコードエディタに以下を貼り付けます。

#r "Newtonsoft.Json"

using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using System.Collections.Specialized;
using System.Text;

using System.Collections;

public static async Task<IActionResult> Run(HttpRequest req, TextWriter outputBlob, ILogger log) {

    var clientId = "xxxx.apps.googleusercontent.com";
    var clientSecret = "xxxxx";
    var refreshToken = "xxxxx";
    var sheetId = "xxxxx";
    var range = "A1";

    var b = new Batch();
    b.Values.Add(new ArrayList() { DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"), 18, "Saru" });
    b.Values.Add(new ArrayList() { DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"), 19, "Usagi" });
    var jsonString = JsonConvert.SerializeObject(b);

    var accessToken = await GetAccessToken(clientId, clientSecret, refreshToken);
    var x = await Send(accessToken, sheetId, range, jsonString);

    return new OkResult();

}

public static async Task<int> Send(string accessToken, string sheetId, string range, string payload) {
    using (var wc = new WebClient()) {
       var url = $"https://sheets.googleapis.com/v4/spreadsheets/{sheetId}/values/{range}?valueInputOption=USER_ENTERED";
        wc.Headers[HttpRequestHeader.ContentType] = "application/json;charset=UTF-8";
        wc.Headers[HttpRequestHeader.Accept] = "application/json";
        wc.Headers[HttpRequestHeader.Authorization] = $"Bearer {accessToken}";
        wc.Encoding = Encoding.UTF8;
        string res = await wc.UploadStringTaskAsync(url, "Put", payload);
    }
    return 0;
}

public static async Task<string> GetAccessToken(string clientId, string clientSecret, string refreshToken) {
    byte[] resData;
    using (var wc = new WebClient()) {
        var url = "https://www.googleapis.com/oauth2/v4/token";
        var ps = new NameValueCollection();
        ps.Add("client_id", clientId);
        ps.Add("client_secret", clientSecret);
        ps.Add("refresh_token", refreshToken);
        ps.Add("grant_type", "refresh_token");
        resData = await wc.UploadValuesTaskAsync(url, ps);
    }
    dynamic resObject = JsonConvert.DeserializeObject(Encoding.UTF8.GetString(resData));
    return resObject?.access_token;
}

public class Batch {
    [JsonProperty(PropertyName = "majorDimension")]
    public string MajorDimension => "ROWS";

    [JsonProperty(PropertyName = "values")]
    public ArrayList Values { get; set; }

    public Batch() {
        Values = new ArrayList();
    }
}

保存＆実行のボタンで実行します。実行結果は次のようになります（時刻はクラウドサイドの時刻（UTC時刻）になります）。

以降は、</> 関数の URL の取得で取得できるURLにブラウザ等でアクセスするだけで、シートにデータが上書きされていきます。

次回は・・・

今回はシートに書き込む値を決め打ちしていましたが、次回は、関数を呼び出したHTTPリクエストのデータ（Runの引数HttpRequest req）を解析して、それをシートに書き込むようにしていきたいと思います。