スプレッドシートを繰り返しで1行ずつ処理する
この記事では、スプレッドシート/Excelに存在する情報を、AUTOROの「各要素を繰り返す」アクションで1行ずつ処理をする方法を紹介します。
この方法は、様々なワークフローで汎用的に利用可能です。
※この記事では、AUTOROやスプレッドシート/Excelの各行を繰り返す処理に少し慣れてきた方向けの方法を紹介しています。
スプレッドシート/Excelの繰り返し処理に慣れていない、または初めてだという場合は、こちらの記事もご参照ください。
【できるようになること】
・以下画像のような処理
・繰り返しでスプレッドシートの行情報を処理しながら、処理状況をシートに記入する
・処理途中の行から処理を再開する
・条件を満たす行のみを抽出/取得
・大量の行情報を処理している場合に、各行の処理状況が一目でわかるようになります。
・ロボットが停止/ロボット判定によりエラーしても、エラーした行から処理を再開できるようになります。
要件の確認
以下のようなロボットを作成することにより、スプレッドシートを1行ずつ処理する方法を紹介します。
「スプレッドシートに1行ごとに記入された複数のURL」に順にアクセスして、そのURL上のタイトルを取得し、スプレッドシートに転記する
情報取得前（処理されていない情報が切り分けられている）
情報取得完了後（処理されていない情報が切り分けられている）
- 処理したい情報が記入されているスプレッドシートを準備する
- 簡単なスプレッドシート関数を入力し、ロボットが処理していない行を抽出
- ワークフローを作成する
未処理の行をAUTOROで読み込み/処理させ、処理状況を記入する
今回は、このシートを使用します。
https://docs.google.com/spreadsheets/d/1efZRzXgMfOBSrpgR_5mupzdcBnKph7yoBIBsHHcVzc8/
このシートは、こちらからコピーしてご使用いただくことが可能です。
1. スプレッドシートの準備
今回の例では、上記のサンプルスプレッドシートを使用します。
この段落では、サンプル内の「全ての処理情報」シートの内容について詳しく見ていきます。
ロボットが、処理状況を書き込む行の番号を特定するため使用します。※この列は必ずしもA列である必要はありません。
もし、新たにこの列を追加される場合は、以下の関数を任意のセルに入力することを推奨します。これは、手動オートフィル等による連番付与時のミスを防ぐものとなります。
=arrayformula(if(B2:B="","",ROW(A2:A) -1))
この関数がA2セルに入力されている場合は、以下のような振る舞いをします。
「B2セルに情報が入力されたら、A2セルには1が表示」「B5セルに情報が入力されたら、A5セルには4が表示」
もし5行目までがヘッダーなら、B2:BをB6:Bに, ROW(A2:A)-1をROW(A6:A)-5と変更してください。
加えて、B列が空白となっている場合は、B2:Bの部分を、「b.処理したい情報が書き込まれている列」に変更してください。
ロボットにアクセスさせたいURLが記入された列を指します。
ロボットがURL上から取得した情報を書き込む列と、その処理状況/ステータスを書き込む列となります。列の位置は任意です。
2. ロボットが処理していない行を抽出
ロボットは、スプレッドシートの情報を1行ずつ処理するにあたり、各要素を繰り返す（ForEach）を使用します。
ここで、ForEachの「繰り返すリスト」パラメータには、ロボットが処理していない行情報のみを指定すると、以下のような運用上のメリットが得られます。
- 繰り返し回数が短くなる
- 既に処理済みの情報を再度処理する必要がなくなる
- URLに繰り返しアクセスする場合、制限を回避し、アクセス先サーバの処理を軽減できる
- ロボットの稼働時間やメモリの使用率が小さくなる
- セッションログが短くなり、閲覧が非常に容易になる（繰り返し内部で条件分岐を設定すると、ログが長くなってしまいます。）
これらの運用上のメリットを享受するために、サンプルシートからロボットが処理していない行情報のみを抽出する方法を3つ紹介します。
なお、この記事ではスプレッドシートのFilter関数を使用して作業を進めます。
サンプルスプレッドシート内の「未処理」シートでは、A2セルに入力された以下のFILTER関数により、ロボットが処理していない情報を抽出しています。
// 処理したい情報が書かれたシートの範囲A2:Dのうち、D列2行目以下が空欄=処理済みでない情報を抽出
=filter('全ての処理情報'!A2:D,'全ての処理情報'!D2:D = "")
ロボットは、上のイメージの右側のシートをGetCells（セルの値を取得）アクションで読み込みます。
※FILTER関数で指定できる条件
FILTER関数には、他の条件を指定することも可能です。
例えば、ある列の数値が1000以上であるとか、URLに特定の文字列が含まれている、といった条件で元となるシートから行情報を抽出できます。
// B列に"wikipedia"を含む行のみ抽出
=filter('全ての処理情報'!A2:D,REGEXMATCH('全ての処理情報'!B2:B,"wikipedia"))
// ORで条件を指定する例 C, D列のいずれかが空欄の行を抽出
=filter('全ての処理情報'!A2:D,('全ての処理情報'!C2:C = "")+('全ての処理情報'!D2:D = ""))※FIlter関数が使用できないExcelファイルでオススメの方法です。
こちらは、AUTOROのConvertTable（テーブルデータを変換）アクションでFIlter関数や表計算ソフトのフィルター機能と同じことを実現する方法になります。
詳細はこちらをご覧ください。
※スプレッドシートの場合は、シートの構造によっては使用できない場合があります。
※2022年10月現在、ConvertTableではOR条件を直接的に設定することができません。ORを設定する場合、NAND(否定論理積)で代替する必要があります。
例えば、セルの値がA,B,C,Dの4つのみを取る場合に、「A または Bのどちらかに一致する」というOR条件を設定したい場合は、「Cと一致しない かつ Dと一致しない」といった形で条件を指定する必要があります。
シート上のFilter関数やConvertTableアクションでは設定できないような複雑な条件式を組み込みたい場合は、JavaScriptのFIlter関数を利用します。
この記事ではこの方法の詳細には立ち入りませんが、複雑な条件を指定したいもののJavaScriptがわからない、といった事案が発生した場合は、サポートチームにお問い合わせください。
3. ワークフローを作成
事前準備は以上で終了になります。
実際にワークフローを作成してみましょう。
最初にスプレッドシートを取得します。
スプレッドシートのコネクションを作成していない場合は、こちらの記事を参考に作成をお願いします。
- GetSpreadsheet（既存のスプレッドシート を取得）を設置します。
- 「プロバイダID」パラメータに、登録したコネクションを入力します。
- 「スプレッドシートID」パラメータに、サンプルシートをコピーして作成されたシートを指定します。
次に、ロボットが処理していない情報のみが記載されている（未処理）シートから、値を取得してみましょう。
- GetCells（セルの値を取得）を、GetSpreadsheetの下に設置します。
- 「シート範囲」パラメータに、以下を入力します。
未処理!A2:D - ここで一旦ワークフローを実行し、無事にシートや値が取得できたか確認します。
シートや値が取得できたことを確認したら、繰り返し処理を設定していきます。
- ForEach（各要素を繰り返す）を設置します。
- 「繰り返すリスト」パラメータにGetCellsのアウトプットを指定します。
- 「要素を格納する変数名」パラメータに、
rowと入力します。
ブラウザを開き、シートのURLにアクセスします。
- OpenBrowser（ブラウザを開く）を設置します。
- URLに、${row[1]}と入力します。
※処理中の1行である変数rowは、配列となっています。今回はA列から情報を取得しており、かつB列にURLが記載されているため、変数rowの2列目を意味する${row[1]}を指定します。
URLからページタイトルを取得します。
- （任意）実際のアクセス対象ページのタイトルにカーソルを合わせて右クリックし、CSSセレクタを取得します。
- GetText(テキストを取得)アクションを設置します。
- 「CSSセレクタ」に、
h1と入力するか、先ほどコピーしたCSSセレクタをペーストします。
今回書き込む情報は、2列分の情報となります。（5.で取得したテキストと、処理状況のテキスト）
1列ずつ情報を書き込むことは手間であるため、2列分の情報を作成してしまいます。
- CreateList（リストを作成）を設置します。
- リストビルダーを開きます。
- 「以前のアクションのアウトプットを再利用する」をクリックし、GetTextのアウトプットを選択します。
- ＋ボタンから要素を追加し、済 と手入力します。
- 「作成」をクリックします。
6で作成した情報（リスト）をシートの所定位置に書き込みます。
- セルの値を更新（UpdateCells）を設置します。
- 「スプレッドシート」パラメータに、1.で設定したGetSpreadsheetのアウトプットを指定します。
- 「範囲」パラメータに、以下を入力します。
全ての処理情報!C${parseInt(row[0]) + 1}
変数rowの1列目(row[0], A列の行番号)に1を加算することによって、正確な書き込み開始位置を作成しています。 - 「値」パラメータに、CreateListのアウトプットを指定します。
繰り返し1回につき、ブラウザを閉じるよう設定します。
これにより、ロボットのCPU/メモリ負荷が大幅に軽減されます。
（ブラウザを開きすぎるとエラーや処理時間増加につながるのでご注意ください）
- CloseBrowser（ブラウザを閉じる）を設置します。
- 「ブラウザ」パラメータに、OpenBrowserのアウトプットを指定します。
# 既存のスプレッドシートを取得
+get_spreadsheet_1:
action>: GetSpreadsheet
provider: ''
spreadsheet_id: ''
meta:
display:
# セルの値を取得 全ての情報を抽出
+get_cells_1:
action>: GetCells
spreadsheet: +get_spreadsheet_1
range: '未処理!A2:D'
# 各要素について繰り返す スプレッドシートの行情報
+for_each_1:
for_each>:
row: +get_cells_1
_do:
# ブラウザを開く 処理したい情報のURL
+open_browser_1:
action>: OpenBrowser
url: ${row[1]}
lang: 'ja-JP'
headless: true
# テキストを取得 C列に書き込むテキスト
+get_text_1:
action>: GetText
browser: +open_browser_1
selector: h1
ignoreError: true
# リストを作成
+create_list_1:
action>: CreateList
items: ["+get_text_1","済"]
# セルの値を更新 A列の行数を使用して、C列から書き込む
+update_cells_1:
action>: UpdateCells
spreadsheet: +get_spreadsheet_1
range: '全ての処理情報!C${parseInt(row[0]) + 1}'
values: +create_list_1
# ブラウザを閉じる 繰り返し1回ごとに閉じる
+close_browser_1:
action>: CloseBrowser
browser: +open_browser_1
# 既存のスプレッドシートを取得
+get_spreadsheet_1:
action>: GetSpreadsheet
provider: ''
spreadsheet_id: ''
meta:
display:
# セルの値を取得 全ての情報を抽出
+get_cells_1:
action>: GetCells
spreadsheet: +get_spreadsheet_1
range: '全ての処理情報!A2:D'
# テーブルデータ（二次元配列）を変換 未処理の情報を抽出
+convert_table_1:
action>: ConvertTable
header: false
table: +get_cells_1
transform: [{"type":"filter","column":"3","operator":"!=","operand":"済","include_blank":false}]
# 各要素について繰り返す スプレッドシートの行情報
+for_each_1:
for_each>:
row: +convert_table_1
_do:
# ブラウザを開く 処理したい情報のURL
+open_browser_1:
action>: OpenBrowser
url: ${row[1]}
lang: 'ja-JP'
headless: true
# テキストを取得 C列に書き込むテキスト
+get_text_1:
action>: GetText
browser: +open_browser_1
selector: h1
ignoreError: true
# リストを作成
+create_list_1:
action>: CreateList
items: ["+get_text_1","済"]
# セルの値を更新 A列の行数を使用して、C列から書き込む
+update_cells_1:
action>: UpdateCells
spreadsheet: +get_spreadsheet_1
range: '全ての処理情報!C${parseInt(row[0]) + 1}'
values: +create_list_1
# ブラウザを閉じる 繰り返し1回ごとに閉じる
+close_browser_1:
action>: CloseBrowser
browser: +open_browser_1
# 既存のスプレッドシートを取得
+get_spreadsheet_1:
action>: GetSpreadsheet
provider: ''
spreadsheet_id: ''
meta:
display:
# セルの値を取得 全ての情報を抽出
+get_cells_1:
action>: GetCells
spreadsheet: +get_spreadsheet_1
range: '全ての処理情報!A2:D'
# 変数に保存
+store_value_1:
action>: StoreValue
key: values
value: +get_cells_1
# スクリプトを実行 未処理の情報を抽出
+run_script_1:
action>: RunScript
code: "return values.filter(row => row[3] != \"済\");\n"
# 各要素について繰り返す スプレッドシートの行情報
+for_each_1:
for_each>:
row: +run_script_1
_do:
# ブラウザを開く 処理したい情報のURL
+open_browser_1:
action>: OpenBrowser
url: ${row[1]}
lang: 'ja-JP'
headless: true
# テキストを取得 C列に書き込むテキスト
+get_text_1:
action>: GetText
browser: +open_browser_1
selector: h1
ignoreError: true
# リストを作成
+create_list_1:
action>: CreateList
items: ["+get_text_1","済"]
# セルの値を更新 A列の行数を使用して、C列から書き込む
+update_cells_1:
action>: UpdateCells
spreadsheet: +get_spreadsheet_1
range: '全ての処理情報!C${parseInt(row[0]) + 1}'
values: +create_list_1
# ブラウザを閉じる 繰り返し1回ごとに閉じる
+close_browser_1:
action>: CloseBrowser
browser: +open_browser_1