Power Automate: Excel データを Office Scripts で高速に更新

# 背景
未だに Web System から Excel をダウンロードして内部の管理用 Excel に手作業更新する必要があったりするのです。
勿論、Web System 改修して Web 上で完結させられるってのが一番なんですが、現状を甘受する場合もあって・・


で、Power Automate で Exccel ファイル結合をしようとすると、以下のような方法しか思いつかなく色々と面倒でした。

- 一行ずつ更新
- Graph API で複数行対処


ってことで、Office Scripts で Sample 作ってみたってお話。

これなら、今までよりは使いやすいかな？と

# 概要
似たような Excel の表を Office Scripts で更新させる。

1. [Office Scripts で更新側を取得](#office-scripts-%E3%81%A7%E6%9B%B4%E6%96%B0%E5%81%B4%E3%82%92%E5%8F%96%E5%BE%97)
1. [Office Scripts で取得結果を反映](#office-scripts-%E3%81%A7%E5%8F%96%E5%BE%97%E7%B5%90%E6%9E%9C%E3%82%92%E5%8F%8D%E6%98%A0)
<br>![HowToUse.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/88cfcd9b-a720-fe0e-48f4-5b340ba8fa71.png)

500 行程度で、13[s] なので、一行ずつ更新するよりは速くていいかな、
<br>
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/066037e2-15d4-9581-e579-3bd377b2745c.png)


## 動作例
- 更新データを元に以下実施
    - 存在しない KeyName の行は追加
    - 存在する Keyname の行は、更新
    - ColumnName が更新側にない場合は無視
![2022-01-10_21h28_59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/2d443d37-11e4-4c99-88c1-2efe008d8de8.png)

:::note warn
KeyName は複合キー未対応
:::
:::note warn
ColumnName が更新側にない場合に列追加したい場合は編集必要
:::
:::note warn
最終行をもとに書式コピーしてあるので、最終行が表外の場合は修正必要（コピー自体も問題になるが）
:::

# Office Scripts で更新側を取得
[JSON/object で取得出来るようになってた](https://qiita.com/baku2san/items/05a3c10893069da453a8) ので利用してみた

```typescript:取得
function main(
  workbook: ExcelScript.Workbook,
): object {
  let topLeftAddress: string = "B6";

  let retreiveData: JSON;
  try {
    let targetSheet = workbook.getActiveWorksheet();
    let usedRange = targetSheet.getUsedRange();
    let lastCell = usedRange.getLastCell();
    let addressWithSheet = lastCell.getAddressLocal();;
    let regExp = new RegExp("!(?<columnName>[a-zA-Z]+)(?<rowValue>\\d+)");
    let match = addressWithSheet.match(regExp);
    let lastColumnName = match.groups["columnName"];            // ex.) 'H'
    let lastRowValue = match.groups["rowValue"];                // ex.) '17'
    let lastCellName = lastColumnName + lastRowValue;           // ex.) 'H17'

    regExp = new RegExp("(?<columnName>[a-zA-Z]+)(?<rowValue>\\d+)")
    match = topLeftAddress.match(regExp);
    let firstColumn = match.groups["columnName"];
    let firstRow = match.groups["rowValue"];
    let dataBeginRow = + firstRow + 1

    // get TableLikeData to 2 demension array 
    let headers = targetSheet.getRange(firstColumn + firstRow + ":" + lastColumnName + firstRow).getValues()
      .reduce((a, b) => { a.push(...b); return a }, [])		// 二次元 to 一次元;
    let dataRnageValues = targetSheet.getRange(firstColumn + dataBeginRow + ":" + lastCellName).getValues();
    retreiveData = <JSON><unknown>{
      "headers": headers,
      "data": dataRnageValues,
    };
    console.log("successfully retreived.");
  } catch (error) {
    if (error instanceof Error) {
      console.log(error.message)
    } else if (typeof error === 'string') {
      console.log(error)
    } else {
      console.log("something went wrong.")
    }
  }

  return retreiveData;
}
```

# Office Scripts で取得結果を反映

```typescript:更新
function main(
  workbook: ExcelScript.Workbook,
  topLeftAddress: string = "A1",
  keyName: string,
  updateDataAsBody: object,
) {
  try {
    let targetSheet = workbook.getActiveWorksheet();
    let usedRange = targetSheet.getUsedRange();
    let lastCell = usedRange.getLastCell();
    let addressWithSheet = lastCell.getAddressLocal();;
    let regExp = new RegExp("!(?<columnName>[a-zA-Z]+)(?<rowValue>\\d+)");
    let match = addressWithSheet.match(regExp);
    let lastColumn = match.groups["columnName"];            // ex.) 'H'
    let lastRowValue = match.groups["rowValue"];            // ex.) '17'
    let lastRow = + lastRowValue;                           // ex.) 17
    let lastCellName = lastColumn + lastRowValue;           // ex.) 'H17'

    regExp = new RegExp("(?<columnName>[a-zA-Z]+)(?<rowValue>\\d+)")
    match = topLeftAddress.match(regExp);
    let topLeftColumn = match.groups["columnName"];
    let headerRow = match.groups["rowValue"];
    let dataBeginRow = + headerRow + 1

    // get TableLikeData to 2 demension array 
    let headers = targetSheet.getRange(topLeftColumn + headerRow + ":" + lastColumn + headerRow).getValues()
      .reduce((a, b) => { a.push(...b); return a }, [])		// 二次元 to 一次元;
    let dataRnageValues = targetSheet.getRange(topLeftColumn + dataBeginRow + ":" + lastCellName).getValues();
    let keyIndex = headers.findIndex(f => f == keyName);
    let initialRowLength = dataRnageValues.length;  // 初期の行数

    let updateHeaders: string[] = updateDataAsBody["result"]["headers"];
    let updateData: (string | number | boolean)[][] = updateDataAsBody["result"]["data"];
    let updateKeyIndex = updateHeaders.findIndex(f => f == keyName);

    // Header の更新用 Index 調査: Copy 先に対する、Copy元のHeader の Indexes
    let update2dataIndexes: number[] = [];
    for (let index in updateHeaders) {
      update2dataIndexes.push(headers.findIndex(f => f == updateHeaders[index]));
    }

    let data2updateIndexes: number[] = [];
    for (let index in headers) {
      data2updateIndexes.push(updateHeaders.findIndex(f => f == headers[index]));
    }

    // update
    for (let index = 0; index < updateData.length; index++) {
      let targetRow = dataRnageValues.find(f => f[keyIndex] == updateData[index][updateKeyIndex])
      if (targetRow === undefined) {
        // Key が見つからない場合
        let newRow: (string | number | boolean)[] = [];

        // 受け側の Headers を基準としている。更新側を基準として、無いものを追加する場合などは、ここを修正
        for (let dataIndex in headers) {
          let convertIndex = data2updateIndexes[dataIndex];
          if (convertIndex == -1) {
            // 列名が見つからない場合
            newRow.push("");
          } else {
            // 列名が見つかれば、その値を取得
            newRow.push(updateData[index][convertIndex]);
          }
        }
        // console.log(newRow);
        dataRnageValues.push(newRow);
      } else {
        for (let dataIndex in headers) {
          let convertIndex = data2updateIndexes[dataIndex];
          if (convertIndex == -1) {
            // 列名が見つからない場合
            // 何も処理しない
          } else {
            // 列名が見つかれば、その値を取得
            targetRow[dataIndex] = updateData[index][convertIndex];
          }
        }
      }
    }
    // Copy 領域拡張。書式コピーの為の。
    let insertRowNumber = dataRnageValues.length - initialRowLength;  // 増加行数
    if (insertRowNumber > 0 ){
      // 追加行数.copyFrom(最終行)
      targetSheet.getRange(topLeftColumn + String(lastRow + 1) + ":" + topLeftColumn + String(lastRow + insertRowNumber)).copyFrom(targetSheet.getRange(topLeftColumn + lastRowValue + ":" + lastCellName), ExcelScript.RangeCopyType.all, false, false);
    }

    // Excel へ反映  
    targetSheet.getRange(topLeftColumn + dataBeginRow + ":" + lastColumn + String(dataRnageValues.length + dataBeginRow - 1)).setValues(dataRnageValues);
    console.log("successfully updated.");
  } catch (error) {
    if (error instanceof Error) {
      console.log(error.message)
    } else if (typeof error === 'string') {
      console.log(error)
    } else {
      console.log("something went wrong.")
    }
  }
}
```

# GitHub
https://github.com/baku2san/OfficeScriptsTools/blob/main/MergeExcelData/MergeExcelData.md

# keyword
update excel file without table by office scripts