OfficeスクリプトとPowerAutomateのタイムゾーンではまったはなし

alice

2022年6月4日 13:28

こんにちは。aliceです。

今日はOfficeスクリプトとPower Automateのタイムゾーンではまったはなしです。

ExcelのOfficeスクリプトではちゃんと実行できるのに、Power Automateで実行するとうまくいかない💦

理由はタイムゾーンでした。

こんなExcelの表があります。

この中から、今日の日付に入っている人（いも？）の部署名と名前が入った配列を取得します。

例えば、今日は２０２２年６月４日なので
[いも課タロイモ]
という配列を取得します。

タロイモってどんないもなんでしょうね？

タロイモは、サトイモ科の植物のうち、根茎を食用とするために栽培されている栽培種の総称。

Wikipedia

ほうほう、タロイモはさといもの仲間なんですね。
１つ勉強になりました。

まぁ、タロイモは置いておくとして、この取得した配列をPower Automateに渡したいのです。

つまり、やりたいことは次のとおりです。
①　Excelの表から今日出勤するいもの部署名と名前が入った配列を取得する
②　その配列をPower Automateに渡す

では、実際にやっていきましょう。

スクリプトを作成する（１）

まずは配列を取得するスクリプトを書きます。

function main(workbook: ExcelScript.Workbook): string[] {
  const today = new Date();
  const year: number = today.getFullYear();
  const month: number = today.getMonth();
  const day: number = today.getDate();
  const dt: Date = new Date(year, month, day, 9, 0, 0);

  const sheet = workbook.getWorksheet("Sheet1");
  const maxRow: number = sheet.getUsedRange().getRowCount();
  let list: string[] = [];
  let num: number = 0;
  for (let i = 1; i < maxRow; i++) {
    let dateRange = sheet.getCell(i, 0);
    let excelDateValue = dateRange.getValue() as number;
    let javaScriptDate = new Date(Math.round((excelDateValue - 25569) * 86400 * 1000));
    if (dt.getTime() === javaScriptDate.getTime()) {
      let busyo = sheet.getCell(i, 1).getValue();
      let name = sheet.getCell(i, 2).getValue();
      list[num] = busyo + "  " + name;
      num += 1;
    }
  }
  console.log(list);
  return list;
}

全体を表示しようとしたらだいぶ小さくなりました…。

実行して、ログを確認します。

["いも課　タロイモ"]と取得できました。

あとはこれをPower Automateで実行するだけです。

Power Automateでフローを作成する（１）

Power Automateのフロー作成画面です。
毎日実行したいのでスケジュール実行するフローにしました。

「スケジュール実行」トリガーと「スクリプトの実行」アクションです。

保存して実行します。

あれれ？？
配列にタロイモが入っていない？？

スクリプトを実行したときはデータを取得できたのに、Power Automateから実行すると空の配列が返ってきました。

涙がでます😢

タイムゾーンの違い

配列がうまく取得できない理由はこちら↓
スクリプトとPower Automateでは使っているタイムゾーンが違うのです😢

英語の方がわかりやすい。

Excelファイルには固有の場所やタイムゾーンはありません。ユーザーがブックを開くたびに、セッションはそのユーザーのローカルタイムゾーンを使用して日付を計算します。PowerAutomateは常にUTCを使用します。

docs.microsoft.com

Excelで実行：ローカルタイムゾーンを使用
Power Automateで実行：UTCを使用
ですね。

つづき↓

スクリプトで日付または時刻を使用している場合、スクリプトをローカルでテストする場合とPower Automateを使用して実行する場合では、動作に違いが生じる可能性があります。Power Automateを使用すると、時間を変換、フォーマット、および調整できます。

docs.microsoft.com

Power Automateで実行すると、Excelでの実行時とタイムゾーンが違うため動作が変わることが原因でした。

ということで作戦変更。
Power Automateでローカルタイムゾーン（日本時間）を取得して、その時間をスクリプトに渡します。

スクリプトを作成する（２）

まずスクリプトを書き換えます。

function main(workbook: ExcelScript.Workbook, today: string): string[] {
  // const today = new Date();
  // const year: number = today.getFullYear();
  // const month: number = today.getMonth();
  // const day: number = today.getDate();
  const dt: Date = new Date(today);

  const sheet = workbook.getWorksheet("Sheet1");
  const maxRow: number = sheet.getUsedRange().getRowCount();
  let list: string[] = [];
  let num: number = 0;
  for (let i = 1; i < maxRow; i++) {
    let dateRange = sheet.getCell(i, 0);
    let excelDateValue = dateRange.getValue() as number;
    let javaScriptDate = new Date(Math.round((excelDateValue - 25569) * 86400 * 1000));
    if (dt.getTime() === javaScriptDate.getTime()) {
      let busyo = sheet.getCell(i, 1).getValue();
      let name = sheet.getCell(i, 2).getValue();
      list[num] = busyo + "  " + name;
      num += 1;
    }
  }
  console.log(list);
  return list;
}

変更点は次の２つです。

①　Power Automateから引数として今日の日付を受け取る

②　受け取った日付を定数「dt」に入れる

①はmain関数の引数にtodayを追加します。

function main(workbook: ExcelScript.Workbook, today: string): string[] {

②は定数dtの宣言部分を書き換えます。

 const dt: Date = new Date(today);

使わないところはコメントアウトしました。

  // const today = new Date();
  // const year: number = today.getFullYear();
  // const month: number = today.getMonth();
  // const day: number = today.getDate();

スクリプトを書き換えたので、Power Automateのフローも変更しましょう。