【PowerAutomate】Formsの回答をExcelに自動転記する方法「OfficeScripts編」

ほしコラム管理人の「ほし」です。

今回は、以前記事で紹介していた「Formsの回答をExcelに自動転記する方法」の応用編になります。

前回の記事では、テーブル1つにFormsの結果が転記されています。

回答した日付で転記するテーブルを分割したい場合などに今回紹介するフローが使用できます。

まだ記事を読んでいない方は、こちらから！！

はじめに

前回は、PowerAutomateのExcel機能で使用してテーブルを更新していましたが、

Excelに関する操作を「OfficeScripts」を使用して更新していきます。

下記の「3. 表に行を追加」が「スクリプトの実行」に変わります。

OfficeScriptsって何？という方はこちらを読んでみてください！

処理の概要

Formsの内容をExcelに自動転記する内容を実装していきます。

PowerAutomateでFormsの情報を引数として、OfficeScriptsを実行します。

上記を実装する中で、Formsの回答した月によってSheetとTableを分けるようにしていきます。

そのため、Excelのシートとテーブルは以下のように準備しておきます。

・テーブル名

・シート名

PowerAutomate 実行結果

今回、PowerAutomateの実行結果は２パターンあります。

• 10月のシートが存在する場合は、「体温表2022年10」のテーブルを更新

事前に、10月分のテーブルを作成した状態でFormsを回答します。

・回答したFormsの情報

・更新されたTable情報（体温表2022年10）

Formsの回答内容をテーブルに追加されています。

• 10月のシートが存在しない場合は、シート・テーブルを作成・更新します。

Excel上に「2022_9」シートに「体温表2022年9」テーブルを作成しておきます。

・回答したFormsの情報

・「2022_10」シートが追加されている。

・「体温表2022年10」テーブルが作成され、Formsの回答が追加されている。

Formsの内容

Formsの内容は、前回と同様で「体調報告」のFormsを使用します。

PowerAutomateで、使用する項目は以下の3つです。

前回との相違点

フローの相違点としては、処理：3 、処理：4 になります。（赤枠点線内）

変更した箇所は、以下の３点になります。

1. 回答したシステム日付を取得するために「現在の時刻」
2. 取得した日付を「yyyy_MM」形式にするための「タイムゾーンの変換」
3. テーブルの更新・作成をするための「スクリプトの実行」

前回使用したフロー

今回作成したフロー

次に変更した処理の設定値について、説明していきます。

「現在の時刻」は設定する内容はないので割愛させていただきます。

タイムゾーンの変換

タイムゾーンの変換では、簡単に説明すると「書式を変更する」です。

今回は、Excelのシート名にしている「yyyy_MM」に変更していきます。

書式設定文字列は、プルダウンから使用したい書式に合わせて選択してみてください。

スクリプトの実行

スクリプトの実行は「OfficeScriptsを実行する」機能です。

引数は、OfficeScriptsのコードで設定した数だけ設定することができます。

次の章で説明しているソースコードと一致していることを確認しましょう！

今回は、「yyyy_MM」の日付やFormsの情報をもとにテーブルに追加する必要があるため、

実行時の引数として設定しています。

OfficeScripts

OfficeScriptsの実際のソースコードは以下のようになっています。

処理の概要は大きく分けて2つになります。

1. 回答日のシート・テーブルが既にある場合に、テーブルを更新する。
2. 回答日のシート・テーブルがない場合に、シート・テーブルを作成・更新する。

上記の処理を実行するソースコードは以下のようになっています。

function main ( workbook: ExcelScript.Workbook) 以降は、自身で設定した引数になります。

OfficeScriptsに受け渡したい値の数だけ設定しておきましょう！

1
2function main(workbook: ExcelScript.Workbook, day: string ,
3                         answer_day : string ,　temp : string , another : string ){
4
5  // dayはPowerAutomateで生成された日付、yyyy_mm のフォーマットになっている。
6  // answer_day : 回答日付　 temp : 体温　　　another : その他
7
8  let mm_dd = day 
9　　　　// 最後のワークシートの名前を取得する。　　　　
10  let sheet_name = workbook.getLastWorksheet().getName();
11  // テーブル名を作成
12  let table_name = "体温表" + mm_dd.replace("_","年");
13  // YY＿MM名のシートが存在するか確認する。
14    if (mm_dd == sheet_name){
15      // テーブルの更新
16      let table = workbook.getTable(table_name)
17      let rows = table.getRowCount()
18      table.addRow(rows,[answer_day,temp,another])
19  // シートが存在しない場合
20    }else{
21      //シートを作成
22      workbook.addWorksheet(mm_dd)
23      let sheet = workbook.getWorksheet(mm_dd);
24      console.log (sheet.getName())
25      // テーブルのヘッダーを作成
26      let range = sheet.getRange('B2:D2');
27      let value = [['回答日付','体温','その他']]
28      range.setValue(value)
29      //テーブルの作成
30      let table = workbook.addTable(range,true);
31      table.setName(table_name);
32      let rows = table.getRowCount()
33      table.addRow(rows, [answer_day, temp, another])
34    }
35  }

コメントを記述していますので、ソースコードの理解に役立てていただければと思います。

OfficeScriptsについて、詳しく知りたい方は以下の記事を参考にしていただければと思います。

参考資料

【OfficeScripts】セルの基本操作 Range：セルの値を取得・代入

最後に

最後まで読んでいただきありがとうございました。

PowerAutomateと様々なOffice製品を連携させれば、簡単に業務効率化をすることができます。

本ブログでは、PowerAutomateで、Forms・ExcelだけでなくTeamsやSharePointなどを効率化する方法を紹介しています。

あわせて読みたい

【PowerAutomate】SharePoint内のファイル存在チェック結果を連携

ほし

エンジニア

XInstagram

ほしコラム管理人のほしです。 20代のITエンジニアです。 PowerAutomateで業務効率化の方法を中心に紹介していきます！ たまに日常生活についても記事にしていきます！