Google Apps Script(GAS)からBox APIを叩くためのすべて

四苦八苦してしまって本当に辛かったので、他の人が同じような苦労をしないように・・・

個人アカウントのOAuth認証で良い場合はこのサイトの手順が参考になります。
How to Use the Box API with Google Apps Script - Digital Inspiration

ただ今回は、AppSheetからの呼び出しで使いたかったので、これだと用途に合わないんですよね。

必要なこと

ざっくりですが、以下の作業が必要です。

• Boxの開発アプリをCustom AppのServer Authenticationで作成
• Box Adminでアプリを承認
• BoxアプリのService Account IDを操作したいBoxフォルダの共有に追加
• GASの実行権限設定
• Box APIからアクセストークンの取得

Box側

1. 開発アプリの作成

Boxの開発アプリをCustom AppのServer Authenticationで作成します。

※途中で設定を日本語にしたので、図ではサーバー認証(クライアント資格情報許可)です

構成での作業

• クライアントシークレットの発行
• アプリケーションスコープを設定
• CROSドメインにGASのドメインを追加
  • https://*.googleusercontent.com

2. アプリのEnterprise承認をする

アプリの設定が終わったら承認リクエストを飛ばします。
そうするとBox Adminの権限があるメンバーにアプリ承認のリクエストが飛ぶので、承認してください。

GAS側

GASのコードはclaspを使って作成しています。
ここは特に真似る必要はありませんが、TypeScriptでのdoPost関数の実装例として見て貰えればと。

結局nodeライブラリは使わなかったのですが、claspの使い方はこのサイトを参考にさせて貰いました。

appsscript.jsonの中身

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/drive.readonly",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/script.external_request"
  ],
  "runtimeVersion": "V8"
}

main.tsの全量

重要なのはアクセストークンを取得しているgetAccessTokenで、他は各自の処理に合わせて貰えればOKです。

const BOX_CLIENT_ID = 'クライアントID';
const BOX_CLIENT_SECRET = 'クライアントシークレット';
const BOX_ENTERPRISE_ID = '組織ID';
const BOX_TEMPLATE_FOLDER_ID = 'コピー元フォルダのID';
const BOX_PARENT_FOLDER_ID = 'コピー先（親）フォルダのID';

// https://developers.google.com/apps-script/guides/web#request_parameters
export function doPost(e: GoogleAppsScript.Events.DoPost): GoogleAppsScript.Content.TextOutput {
  const contents = JSON.parse(e.postData.contents);
  const orderDate = contents["orderDate"];
  const customerName = contents["customerName"];

  const createFolderId = copyFolder(orderDate, customerName);

  const resultParams = {folderId: createFolderId};
  const result = JSON.stringify(resultParams);
  return ContentService.createTextOutput(result);
}

const getAccessToken = (): string => {
  const endpoint = 'https://api.box.com/oauth2/token';
  const payload = {
    client_id: BOX_CLIENT_ID,
    client_secret: BOX_CLIENT_SECRET,
    grant_type: 'client_credentials',
    box_subject_type: 'enterprise',
    box_subject_id: BOX_ENTERPRISE_ID
  };
  const headers = { 'Content-Type': 'application/x-www-form-urlencoded' };
  const response = UrlFetchApp.fetch(endpoint, {
    headers: headers,
    method: 'post',
    payload: payload
  });
  const json = JSON.parse(response.getContentText());

  return json.access_token;
};


const copyFolder = (orderDate: string, customerName: string): string => {
  const endpoint = `https://api.box.com/2.0/folders/${BOX_TEMPLATE_FOLDER_ID}/copy`;

  const access_token = getAccessToken();
  const headers = {
    'Authorization': 'Bearer ' + access_token,
    "Content-type": "application/json",
  };
  const payload = {
    name: `${orderDate}_${customerName}`,
    parent: {
      id: BOX_PARENT_FOLDER_ID
    }
  };
  const response = UrlFetchApp.fetch(endpoint, {
    headers: headers,
    method: 'post',
    payload: JSON.stringify(payload)
  });
  const json = JSON.parse(response.getContentText());
  Logger.log(json.id);
  return json.id;
};

以上です。

---

落ち穂拾い

Client Credentials Grantの日本語ページがない

今回のやり方のベースになっている、以下のページに該当する日本語のページがありません。

Boxの開発ドキュメントは日本語のものが用意されており、 そちらが最初に表示されたので、別段気にしていなかったのですが、 クライアントクレデンシャルでのアクセストークン取得方法のページはなぜかガッツリ抜け落ちていました。

英語

日本語

Boxで最初に企業ドメインアカウントで開発者登録をすると、その人が管理者になる

ADMINは、他のアカウントへのスイッチもできるので、適当なタイミングで適切な人に権限を渡しましょう。

https://support.box.com/hc/ja/articles/360044194953/

AppSheetはPOSTのレスポンスを使えない

本記事の例ではdoPostでレスポンスを返していますが、これを呼び出すAppSheetは現状レスポンスの中身を利用できないので、 あの部分はなくても問題ありません。

GASでデプロイ後はGCP連携していないとログが表示されない

GASをデプロイし、外部からGASのスクリプトをcurlなどで叩いた場合、GCP連携していないとコード中に組み込んだログは表示されません。

prismaのschemaファイルの書き方（MySQL用）

フルNext.jsアプリケーション作成の一環で、DBマイグレーションツールにprismaを使ってみたので、書き方のメモです。

いきなりですが、prismaは細かくDDL文を制御できず、思ったテーブルを作れなかったので、細かくテーブル定義を設定したい場合はTypeORMの方がいいかもしれません。

準備

プロジェクトにprismaをインストールし、初期ファイル（prisma/schema.prisma）を作成します。

$ yarn add -D prisma

$ yarn run prisma init

schema.prismaの書き方

公式ドキュメントの書き方を参考に、スキーマをschema.prismaに記載していきます。

記載する際の注意点・考慮点は以下です。

• providerをmysqlに変更する
• @db.Timestampではなく@db.Timestamp(0)のように書かないとマイグレーション出力時にエラーになる
• @updatedAtをつけてもON UPDATE CURRENT_TIMESTAMPがつかない
  • つけたい場合はマイグレーションファイルを作成してから、手動で追記するしかないっぽい（追記して実行したら反映はされる
• model名をキャメルケースで書いても、DDLでスネークケースのテーブル名に変換されるわけではない
  • スネークケースのテーブル名にしたければmodel名をそうしておく
• @relationのnameはmodel間のマッチングキーに利用する
• サポートされていない型（例えばgeometry）を使う場合は、Unsupported("geometry")のように書く

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Area {
  id          Int      @id @default(autoincrement())
  name        String   @db.VarChar(32)
  description String?  @db.Text
  created_at  DateTime @default(now()) @db.Timestamp(0)
  updated_at  DateTime @default(now()) @updatedAt @db.Timestamp(0)
  shop        Shop[]   @relation("shop_fk_1")
}

model Shop {
  id           Int                      @id @default(autoincrement())
  name         String                   @db.VarChar(128)
  area_id      Int
  area         Area                     @relation(name: "shop_fk_1", fields: [area_id], references: [id])
  address      String?                  @db.VarChar(128)
  tel          String?                  @db.VarChar(128)
  access       String?                  @db.Text
  localton     Unsupported("geometry")?
  url          String                   @db.Text
  created_at   DateTime                 @default(now()) @db.Timestamp(0)
  updated_at   DateTime                 @default(now()) @updatedAt @db.Timestamp(0)
  shop_article Shop_Article[]           @relation("shop_article_fk_1")
}

model Shop_Article {
  id                 Int                  @id @default(autoincrement())
  status             ArticleStatus?
  published_at       DateTime             @default(now()) @db.Timestamp(0)
  title              String?              @db.Text
  image_url          String?              @db.Text
  shop_id            Int?
  shop               Shop?                @relation(name: "shop_article_fk_1", fields: [shop_id], references: [id])
  created_at         DateTime             @default(now()) @db.Timestamp(0)
  updated_at         DateTime             @default(now()) @updatedAt @db.Timestamp(0)
  shop_article_image Shop_Article_Image[] @relation("shop_article_image_fk_1")

  @@index([shop_id])
  @@index(fields: [published_at], name: "idx_shop_article_published_at")
}

enum ArticleStatus {
  draft
  published
}

model Shop_Article_Image {
  shop_article_id Int
  shop_article    Shop_Article @relation(name: "shop_article_image_fk_1", fields: [shop_article_id], references: [id])
  order           Int
  image_url       String       @db.Text
  created_at      DateTime     @default(now()) @db.Timestamp(0)
  updated_at      DateTime     @default(now()) @updatedAt @db.Timestamp(0)

  @@id([shop_article_id, order])
}

※例は既存アプリケーションの書き直しなのでtimestamp型ですが、今から作るなら範囲を考えてdatetime型を使ってください。

TIMESTAMP[(fsp)]

タイムスタンプです。範囲は '1970-01-01 00:00:01.000000' UTC から '2038-01-19 03:14:07.999999' UTC です。TIMESTAMP 値は、エポック ('1970-01-01 00:00:00' UTC) からの秒数として格納されます。TIMESTAMP は、'1970-01-01 00:00:00' という値を表すことはできません。これは、エポックからの秒数が 0 であることと同等で、0 という値は '0000-00-00 00:00:00'、つまり「ゼロ」の TIMESTAMP 値を表すために予約されているからです。
MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションの fsp 値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.1.2 日付と時間型の概要

migration.sql

上記のschema.prismaから出力されたマイグレーションファイルは以下のようになりました。

-- CreateTable
CREATE TABLE `Area` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `description` TEXT,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Shop` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `area_id` INTEGER NOT NULL,
    `address` VARCHAR(128),
    `tel` VARCHAR(128),
    `access` TEXT,
    `localton` geometry,
    `url` TEXT NOT NULL,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Shop_Article` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `status` ENUM('draft', 'published'),
    `published_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `title` TEXT,
    `image_url` TEXT,
    `shop_id` INTEGER,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `Shop_Article.shop_id_index`(`shop_id`),
    INDEX `idx_shop_article_published_at`(`published_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Shop_Article_Image` (
    `shop_article_id` INTEGER NOT NULL,
    `order` INTEGER NOT NULL,
    `image_url` TEXT NOT NULL,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    PRIMARY KEY (`shop_article_id`, `order`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `Shop` ADD FOREIGN KEY (`area_id`) REFERENCES `Area`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Shop_Article` ADD FOREIGN KEY (`shop_id`) REFERENCES `Shop`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Shop_Article_Image` ADD FOREIGN KEY (`shop_article_id`) REFERENCES `Shop_Article`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

外部キー制約でON DELETE CASCADEを付けたくない場合は、手動で消さないとダメそうです。

また、DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;あたりは接続しているMySQLの設定から自動取得しているようでした。

書くときのコツ

最初に全テーブルのカラム定義を書いてしまい、その後にインデックスや外部キー制約を書いていくのが良さそうでした。
特に外部キー制約関連は、途中まで書いてフォーマットをかけると、prismaのフォーマッタが自動で埋めてくれることがあるのですが、 relation nameを使わないキャメルケースの定義を追加するので、 同じ意図の定義が、自分が書いたものとフォーマッタで複数追加されたりしたので、注意が必要でした。

まとめ

マイグレーションにおいてはTypeORMの方が細かく指定できていいかもしれないですが、prismaはSQLで出力されるのでDDLを変更できる人であればなんとでもできそうでした。

Next.jsでLayzr.jsを使って画像読み込みをする方法

既存サイトを書き直していたら、layzr.jsの扱いにハマったので。

next/imageを使えというのは無しで。

結論：useEffectを使えばOK

• libs/layzr.ts

import Layzr from 'layzr.js';

export default function LayzrConfig() {
  const instance = Layzr({
    threshold: 200,
  });
  instance.on('src:before', (image) => {
    // ...
  });
  instance
    .update() // track initial elements
    .check() // check initial elements
    .handlers(true); // bind scroll and resize handlers
}

• LogoImage.tsx

import { useEffect } from 'react';
import LayzrConfig from 'libs/layzr';

略

export const LogoImage: React.FC = () => {

  useEffect(() => {
    LayzrConfig();
  }, []);

  return (<img data-normal="/static/images/logo.svg" alt="LOGO" />);
};

【AWS】ALBのログをAthenaで分析するときの工夫（供養）

ALBのログをAthenaで分析する方法は、公式ドキュメントに記載があるのですが、この手順でデータベースを作成すると、日付の条件を入れて検索したいとき、大量のデータをスキャンしてしまい、遅い上にお金がかかるという問題に遭遇します。

そこでおすすめしたいのが、データベース作成時にパーティションを作っておくことです。

・・・という記事を書こうと思っていたのですが、このドキュメントでもパーティションを利用するようになっていたので、記事の役割が死にました。
なので、この記事は削除しようかとも思ったのですが、パーティションの切り方が公式と違っていたので、一応投稿して供養しておきます。

Athenaテーブルの作成

Athenaで適当なデータベースを選択、または作成したら、以下のクエリを実行してテーブルを作成します。
実際に作成する場合は、SQLのこれらの箇所を修正してください。

• LOCATION： 's3://ALBのログが保存されているS3バケット名/AWSLogs/AWSアカウントID/elasticloadbalancing/リージョン/'
• TBLPROPERTIES
  • projection.date_time.range：ログを取り始めた日から（サンプルクエリでは1年前にしている）
  • storage.location.template：LOCATIONと同じバケットのパス＋${date_time}

テーブル作成クエリ

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
  type string,
  time string,
  elb string,
  client_ip string,
  client_port int,
  target_ip string,
  target_port int,
  request_processing_time double,
  target_processing_time double,
  response_processing_time double,
  elb_status_code string,
  target_status_code string,
  received_bytes bigint,
  sent_bytes bigint,
  request_verb string,
  request_url string,
  request_proto string,
  user_agent string,
  ssl_cipher string,
  ssl_protocol string,
  target_group_arn string,
  trace_id string,
  domain_name string,
  chosen_cert_arn string,
  matched_rule_priority string,
  request_creation_time string,
  actions_executed string,
  redirect_url string,
  lambda_error_reason string,
  target_port_list string,
  target_status_code_list string,
  classification string,
  classification_reason string)
PARTITIONED BY (
  `date_time` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) ([^ ]*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\"')
LOCATION
  's3://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/ap-northeast-1/'
TBLPROPERTIES (
  'classification'='csv', 
  'projection.date_time.format'='yyyy/MM/dd', 
  'projection.date_time.interval'='1', 
  'projection.date_time.interval.unit'='DAYS', 
  'projection.date_time.range'='2020/06/16,NOW', 
  'projection.date_time.type'='date', 
  'projection.enabled'='true', 
  'projection.pvid.type'='injected', 
  'storage.location.template'='s3://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/ap-northeast-1/${date_time}')
;

このやり方のメリット

このテーブルを利用する場合のメリットは大きく2つあります。

1. S3バケットのデータスキャン範囲の限定
2. テーブルの定期更新が不要

解説

このクエリで作成したテーブルにはdate_time (string) (パーティション化)というカラムが追加されています。

このカラムを検索クエリに含めた場合、AthenaがスキャンするS3バケットのフォルダは、カラム条件の範囲だけになります。

例えば、2021/06/15のログから調査したい場合は、以下のようなクエリ（6/15でアクセスの多いIPアドレスを確認）にすることができます。

SELECT COUNT(request_verb) AS count, request_verb, client_ip
FROM alb_logs
WHERE date_time = '2021/06/15'
GROUP BY request_verb, client_ip;

注意したいのは、ALBで扱う日付はUTCになっているので、JSTで確認したい場合は日を跨いで指定する必要があることです。 範囲の指定はIN句でもBETWEENでも大丈夫なので、お好きな方でOKです。

時間を厳密に指定したい場合は、timeカラムを利用してください。
例えば、先程のクエリをJSTの6/15とした場合は以下のようになります。

SELECT COUNT(request_verb) AS count, request_verb, client_ip
FROM alb_logs
WHERE date_time in ('2021/06/14', '2021/06/15')
AND (parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')
     BETWEEN parse_datetime('2021-06-14-15:00:00','yyyy-MM-dd-HH:mm:ss')
     AND parse_datetime('2021-06-15-14:59:59','yyyy-MM-dd-HH:mm:ss'))
GROUP BY request_verb, client_ip;

また以前までのテーブルであれば、作成後はデータのスキャン範囲が自動的に増えなかったのですが、 このテーブルはprojection.date_time.rangeで範囲の終端にNOWを指定しているため、テーブルを更新し直さなくても常に最新のログまで検索することができます。

公式ドキュメントのパーティションとの差分

公式ドキュメントではフォルダのパーティションを、年、月、日で数値管理にしているので、クエリを書く際は年月日をそれぞれ分けて指定することになります。 また、rangeも2020,2021としているため、2022年以降のログはテーブルを更新し直す必要があります。

そういった違いを考えると、この記事にも一定の意味があったのかなと思えなくもないですね。