GASのWebAPIのIssueをCloud Functionsを使って回避する

こんにちは！株式会社スマレジ 、開発部のmasaです。

すっかり秋めいてきたと思ったら、今週の大阪は暑い日が多くて夜は寝苦しかったりと、機構に振り回され気味です。

今回は、このブログで紹介してきたGASスクリプトで回避してきたGASのWebAPI周りの不具合とその回避として、Cloud Functionを導入する例をお伝えします。

GASのWebAPIは権限がAnyoneで実行されると、jdbcが未定義になるIssue

参考URLはこちら

jdbcは、GASから各DBMSに接続するための標準ライブラリの一つです。 例えば下記のようなソースでsqlを実行したりします。

/**
* SQL実行汎用メソッド
*/
function executeSQL(sql, params = [], isUpdate = false) {
  let userName = "データベースのユーザ名";
  let password = "データベースユーザのパスワード";
  let dbName = "データベース名";
  let ipAddress = "データベースサーバのIPアドレス";
  let connection = Jdbc.getConnection("jdbc:mysql://" + ipAddress + ":3306/" + dbName, userName, password);
  let statement = "";
  if (params.length > 0) {
    statement = connection.prepareStatement(sql);
    for (let i = 0; i < params.length; i++) {
      statement.setString(i + 1, params[i]);
    }
    if (isUpdate) {
      statement.executeUpdate();
      return true;
    } else {
      return statement.executeQuery();
    }
  } else {
    statement = connection.createStatement();
    if (isUpdate) {
      statement.executeUpdate(sql);
      return true;
    } else {
      return statement.executeQuery(sql)
    }
  }
  statement.close();
  connection.close();
}

上記で使用している let connection = Jdbc.getConnection("jdbc:mysql://" + ipAddress + ":3306/" + dbName, userName, password);がjdbcの呼び出し場所です。 もしこの関数を使ったAPIが公開されていて、尚且つ権限がAnyone(Web上に公開している状態）にしていると、上述の箇所でJdbc is not definedエラーになってしまうんです。。。

もしかすると、Google側でセキュリティ上の観点から、権限がAnyoneになっている時は、jdbcを使わせないようにしているのかもしれないのですが、そうであればどこかに記載するか、エラーメッセージにその旨を記載してほしいです。。。

Cloud Functions

そこで、GASの代わりになるのがCloud Functionsです。 GCPのサービスの一つで、クラウド上に関数を用意して、ネットを介してWebAPIのように関数をコールして結果を得るものです。

Cloud Functionsのドキュメントは↓

cloud.google.com

Cloud FunctionからもCloud SQLは呼び出しできるので、GASの代わりにデータを取ってきてもらおうというわけです。

GCPのCloud Functions（{...}というマークがめじるしです)を有効にすると、下記のような一覧が出てきます。（masaはすでに登録しているので１件見えちゃってますが、はじめはここは空っぽです。）

ここから、「関数の作成」に移動し、画面のナビゲートに従って必要事項を入力して、「次へ」を押します。

エディターが出てくるので、ここに記載したい関数を記載します。（zipやAPIでのデプロイももちろん対応しています）

ここで、問題になるのが`Cloud FunctionはGASやJavascriptは対応していないところです。対応しているのはJaca,Python,Goの３つのみです。

そのため、masaは下記のようにGoでDB接続とSQL実行の処理を書き換えました。 （masaはGo初心者なので、おかしな書き方をしているかもしれませんが悪しからず。。。）

// Package p contains an HTTP Cloud Function.
package p

import (
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "io"
    "log"
    "net/http"
    "os"
    "time"
)

type Item struct {
    Title     string    `json:"title"`
    CreatedAt time.Time `json:"created_at"`
}

// HelloWorld prints the JSON encoded "message" field in the body
// of the request or "Hello, World!" if there isn't one.
func RefreshSpreadSheet(w http.ResponseWriter, r *http.Request) {
    var d struct {
        Sql string `json:"sql"`
        Message2 string `json:"message2"`
    }

    if err := json.NewDecoder(r.Body).Decode(&d); err != nil {
        switch err {
        case io.EOF:
            fmt.Fprint(w, "Hello World!_err")
            return
        default:
            log.Printf("json.NewDecoder: %v", err)
            http.Error(w, http.StatusText(http.StatusBadRequest), http.StatusBadRequest)
            return
        }
    }
    if d.Sql == "" {
        w.WriteHeader(400)
        fmt.Fprint(w, "sqlは必須です")
        return
    }

    var (
        dbUser                 = "データベースユーザ名"                  // e.g. 'my-db-user'
        dbPwd                  = "データベースユーザパスワード"                  // e.g. 'my-db-password'
        instanceConnectionName = "プロジェクトID:エリア:Cloud SQL名" // e.g. 'project:region:instance'
        dbName                 = "DB名"                  // e.g. 'my-database'
    )

    socketDir, isSet := os.LookupEnv("DB_SOCKET_DIR")
    if !isSet {
        socketDir = "/cloudsql"
    }

    var dbURI string
    dbURI = fmt.Sprintf("%s:%s@unix(/%s/%s)/%s?parseTime=true", dbUser, dbPwd, socketDir, instanceConnectionName, dbName)

    // dbPool is the pool of database connections.
    db, err := sql.Open("mysql", dbURI)
    if err != nil {
        panic(err.Error())
    }
    defer db.Close() // 関数がリターンする直前に呼び出される
    err = db.Ping()
    if err != nil {
        panic(err.Error())
    }

    rows, err := db.Query(d.Sql) //
    if err != nil {
        panic(err.Error())
    }


    columns, err := rows.Columns() // カラム名を取得
    if err != nil {
        panic(err.Error())
    }

    values := make([]sql.RawBytes, len(columns))

    //  rows.Scan は引数に `[]interface{}`が必要.

    scanArgs := make([]interface{}, len(values))
    for i := range values {
        scanArgs[i] = &values[i]
    }

    res := "["
    for rows.Next() {
        err = rows.Scan(scanArgs...)
        if err != nil {
            panic(err.Error())
        }

        res += "{"
        var value string
        for i, col := range values {
            // Here we can check if the value is nil (NULL value)
            if col == nil {
                value = "NULL"
            } else {
                value = string(col)
            }
            res += "\"" + columns[i] + "\": \"" + value + "\","
        }
        res = res[:len(res)-1]
        res += "},"
    }
    if len(res) > 1 {
        res = res[:len(res)-1]
    }

    res += "]"
    fmt.Fprint(w, res)
}

panicコード使いまくりな上、メソッド分割もしていないイケてないコードなのですが、 処理の流れの雰囲気と、各処理で使う命令をつかんでいただければ幸いです。

あとはGAS側で下記のようにSQLを渡す部分を作成すればOK

function executeSQLForAnyone(sql, params = [], isUpdate = false) {
      // urlfetchappのオプション情報
    const options = {
        'method' : 'post',
        'muteHttpExceptions': true,
        'payload': JSON.stringify({
          'sql' : sql,
          "message2": "456"
        })
    };
  
    try {
        //外部へアクセスさせる
        let resStr = UrlFetchApp.fetch("Cloud Functionsのエンドポイント", options).getContentText();
        if (resStr.length === 0) {
            throw new Error("受信データがありませんでした。");
        }
        Logger.log(resStr);
        return JSON.parse(resStr);
    } catch(e) {
        Logger.log("エラー：" + e);
        throw new Error(e);
    }  

これで、権限に悩まされずにDBからデータを取得できます。