execute an action on a set of rows(第2回)一括処理をする

folder_openAppSheet

前回は、AppSheetに用意されているAction「Data: execute an action on a set of rows」について、テーブルをまたいだ処理の設定を解説しました。
今回は、2件以上のレコード(行)について一括処理する方法について学びます。
この使い方をマスターすれば、単純な一括処理なら、GAS(Google Apps Script)や外部サービスに頼らずに実行できるようになります。

前回に引き続き、在庫管理アプリのユースケースを使います。
使用する在庫管理アプリは、このブログで解説している在庫管理アプリ(7回シリーズ)か、メルマガ購読者特典のサンプルアプリをダウンロードしてください。

1.AppSheetで一括処理する方法

在庫管理アプリのユースケースで一括処理をやってみましょう。
今回のユースケースは、入出庫履歴を月別に集計して、月末の期末在庫数を計算するというものです。

  1. Actionで「品目」テーブルの「トリガ」列の値を一括で更新する
  2. 「期首在庫」「月別入庫」などの列にFormulaを設定して、列の値を一括で更新する

「 execute an action on a set of rows」の一括処理のユースケース

データソースに列を追加後に、AppSheetでRegenerateする

データソースに在庫量を列を追加します。

AppSheetエディタ左側メニューにある「Data」をクリック、タブメニュー「Tables」をクリックします。
「品目」テーブルをクリックして、設定パネルを開きます。

AppSheetエディタでテーブルを設定する

「View Source」をクリックしてデータソースを開きます。

データソースを表示する

データソースのGoogleスプレッドシートを開いたら、列を追加します。
今回のユースケースでは、入出庫履歴を月別に集計して、期末在庫を計算するので以下のような列を追加しました。

  • 期首在庫
  • 月別入庫
  • 月別出庫
  • 月別在庫修正
  • 期末在庫
  • トリガ

「トリガ」列は、各レコード(行)の値を一括更新するために使用します。
「トリガ」列の値を更新した時に、Formulaが計算を実行して各列の値を更新するようにします。

データソースに列を追加するデータソースを変更したので、AppSheet側の列設定を更新しましょう。

AppSheetエディタ左側メニューにある「Data」をクリック、タブメニュー「Columns」をクリックします。
「品目」テーブルをクリックして、列設定パネルを開きます。

AppSheetエディタで列を設定する

右上の「Regenerate Structure」をクリック、「Regenerate」をクリックして列設定を更新します。

続けて、さきほど追加した列のFormulaを設定します。
TYPEはすべて「Number」にしましょう。
「トリガ」列は、INITIAL VALUEを0にしておきます。

続けて、各列の「FORMULA」をクリックしてExpression Assistantを表示します。

列設定をする

「期首在庫」列の式は以下の通りです。
「品目」テーブルの「在庫量」列(Virtual Column)に設定している式を少し修正しています。

  • MAXROW => MINROWに変更した
  • 月別集計のため、「入出庫履歴」の「作成日時」が現在月であることを条件に追加した
  • 現在月の入出庫履歴がない場合、
    • 「数量」列と「在庫量」列の値が異なる時(過去に入出庫履歴がある) => 「在庫量」を「期首在庫」にする
    • それ以外の時 => 「数量」を「期首在庫」にする

では、以下の式をコピペしましょう。

IF(
 COUNT(
  SELECT(
   入出庫履歴[変更前の数量],
   [ID] = MINROW(
    "入出庫履歴", 
    "_RowNumber", 
    AND(
     [_THISROW].[ID] = [品目],
     MONTH(TODAY()) = MONTH([作成日時])
    )
   )
  )
 ) > 0,
 ANY(
  SELECT(
   入出庫履歴[変更前の数量],
   [ID] = MINROW(
    "入出庫履歴", 
    "_RowNumber", 
    AND(
     [_THISROW].[ID] = [品目],
     MONTH(TODAY()) = MONTH([作成日時])
    )
   )
  )
 ),
 IF(
  [数量] <> [在庫量], [在庫量],
  [数量]
 )
)

期首在庫の式を設定する

続いて、「月別入庫」列の式です。「入出庫履歴」テーブルで

  • 同じ品目
  • 「操作内容」列の値が「入庫」
  • 作成日時が現在月

のレコード(列)の「数量」を合計します。

SUM(
 SELECT(
  入出庫履歴[数量],
  AND(
   [_THISROW].[ID] = [品目],
   "入庫" = [操作内容],
   MONTH(TODAY()) = MONTH([作成日時])
  )
 )
)

月別入庫の式を設定する

続いて、「月別出庫」列の式です。「入出庫履歴」テーブルで

  • 同じ品目
  • 「操作内容」列の値が「出庫」
  • 作成日時が現在月

のレコード(列)の「数量」を合計します。

SUM(
 SELECT(
  入出庫履歴[数量],
  AND(
   [_THISROW].[ID] = [品目],
   "出庫" = [操作内容],
   MONTH(TODAY()) = MONTH([作成日時])
  )
 )
)

月別出庫の式を設定する

続いて、「月別在庫修正」列の式です。「入出庫履歴」テーブルで

  • 同じ品目
  • 「操作内容」列の値が「棚卸」
  • 作成日時が現在月

のレコード(列)の「数量の増減」を合計します。

SUM(
 SELECT(
  入出庫履歴[数量の増減],
  AND(
   [_THISROW].[ID] = [品目],
   "棚卸" = [操作内容],
   MONTH(TODAY()) = MONTH([作成日時])
  )
 )
)

月別棚卸の式を設定する

最後に「期末在庫」列の式です。
「期首在庫」以下、各列の値を加減して計算します。

期末在庫の式を設定する

2.Actionを設定する

はじめに「set the values of some columns in this row」を作成する

Actionを設定しましょう。
「execute an action on a set of rows」は別のアクションと組み合わせる必要があります。
前回と同様に「set the values of some columns in this row」のアクションを先に作っておきましょう。

AppSheetエディタの左側メニュー「Behavior」をクリック、タブメニュー「Actions」をクリックします。
「New Action」でActionを新規作成しましょう。

「 set the values of some columns in this row」Actionを設定する

まずは、「トリガ」列の値を更新するActionを作成します。 各項目は以下のように設定しました。

  • Action name:「月別集計トリガ」
  • For a record of this table:「品目」
  • Do this:「Data: set the values of some columns in this row」
  • Set these columns:「トリガ」、[トリガ]+1

Set these columnsには式を設定します。三角フラスコアイコンをクリックして、Expression Assistantを表示します。

Set these columnsの式を設定する

Expression Assistantには、[トリガ]+1を入力します。
このアクションを実行すれば、「トリガ」列の現在値に1を加算して、更新します。

右上の「Save」をクリックして保存するとエラーのアラートが表示されると思いますが、気にせず続けましょう。

Expression Assistantに式を設定する

このアクションは表示しないので、Prominenceで「Do not display」を選択しましょう。

Prominenceで「Do not display」にする

「execute an action on a set of rows」を設定する

一括処理のAction「execute an action on a set of rows」を作成しましょう。
各項目は以下のように設定しました。

  • Action name:「月別集計」
  • For a record of this table:「品目」
  • Do this:「Data: execute an action on a set of rows」
  • Reference Table:「品目」
  • Referenced Rows:LIST( [ID] )
  • Referenced Action:「月別集計トリガ」

前回解説したとおり、

  • For a record of this table:アクションのトリガになるテーブル
  • Referenced Table:アクションを実行するテーブル

を設定します。

今回のユースケースでは、「品目」テーブルのView上に、アクションを実行するボタンを置き、ボタンクリックで実行します。
なので、アクションのトリガも実行も同じ「品目」テーブルになります。

Referenced Rowsに式を設定するので、三角フラスコアイコンをクリックしてExpression Assistantを開きましょう。

「execute an action on a set of rows」Actionを設定する

Expression Assistantに、LIST( [ ID ] )を入力します。

「品目」テーブルのKEYは「ID」列です。
LIST( [ ID ] )は、「品目」テーブルにあるレコード(行)の「ID」(KEY値)をListで返すという意味になります。

以上により、「品目」テーブルにあるレコード(行)について、一括で「月別集計トリガ」アクションを実行することができます。
つまり、「品目」テーブルの「トリガ」列の値を一括で更新します。

Expression Assistantで式を設定する

このアクションについては、Prominenceで「Display Inline」を選択しましょう。

Prominenceで「Display Inline」を選択する

スライスを確認する

スライスの設定を確認します。

AppSheetエディタ左側メニュー「Data」をクリック、タブメニュー「Slice」をクリックします。
「在庫」というスライスがあるので、クリックしてスライス設定パネルを開きます。

AppSheetエディタでスライスを設定する

「品目」テーブルに追加した列があるか確認します。
ない場合は、「Add」で追加しておきます。

スライスに列を追加する

3.AppSheetアプリで一括処理を確認する

実際に操作して確認してみましょう。

AppSheetエディタ画面のプレビューで、タブメニュー「在庫」をクリックして選択します。
右上にチェックボックスのアイコンがあるので、クリックします。

Bulk Selectを実行する

Bulk Select(一括選択)という機能です。
チェックボックスをチェックして、品目を複数選択できるようになります。

2件以上の品目を選択してみましょう。

Bulk Selectでレコード(行)を選択する

右上にある3ドットアイコンをクリックすると、「月別集計」と表示されます。
さきほど設定した「月別集計」Actionを「Display Inline」で表示するとこのようになります。

では、「月別集計」をクリックしてみましょう。

Actionを実行する

先ほど選択した品目について、「期首在庫」以下、追加した列のFormulaが実行されて、値が更新されます。

データソースを確認すると、「トリガ」列の値が+1されていることが分かると思います。

一括処理できているか確認する

「在庫量」のVirtual Column、前回追加した「在庫量_actual」、そして今回追加した「期末在庫」の値が同じになっていたら成功です。

在庫がそろっているか確認する

4.AppSheet Automationで一括処理する際には件数に注意する

以上、「execute an action on a set of rows」を使った一括処理をやってみました。

Referenced Rowsで設定した条件が、2件以上のレコード(行)に該当する場合に、一括処理ができます。
今回のユースケースでは、「在庫」Viewで、「品目」テーブル内にあるレコード(行)をチェックボックスで選択(Bulk Select)して、選択したレコード(行)を対象とした一括処理を行いました。

ひとつ注意点があります。

AppSheetでは、サーバーに負荷がかかる処理については件数を制限したり、時間制限があります。
Automationを使って一括処理を実行することもできますが、その場合、時間制限(約2分)に引っかかることがあるようです。
概ね200件くらい一括処理するとエラーになり、実行できないことがありました。
Automationで処理する場合は、件数に気をつけましょう。

在庫管理アプリの機能としては、「在庫量」のVirtual Columnだけで事足りるので、実際には実用性はありませんが、

  1. AppSheetだけでテーブルをまたいだ処理をやりたい時
  2. AppSheetだけで2件以上のレコードの一括処理をやりたい時

に思い出して、使えるようにしておきましょう。

Share Me!

前の投稿
execute an action on a set of rows(第1回)テーブルをまたいだ処理
次の投稿
AppSheet Automationに新機能 – リンク設定の使い方

関連記事

メニュー