背景
未だに Web System から Excel をダウンロードして内部の管理用 Excel に手作業更新する必要があったりするのです。
勿論、Web System 改修して Web 上で完結させられるってのが一番なんですが、現状を甘受する場合もあって・・
で、Power Automate で Exccel ファイル結合をしようとすると、以下のような方法しか思いつかなく色々と面倒でした。
- 一行ずつ更新
- Graph API で複数行対処
ってことで、Office Scripts で Sample 作ってみたってお話。
これなら、今までよりは使いやすいかな？と
概要
似たような Excel の表を Office Scripts で更新させる。
500 行程度で、13[s] なので、一行ずつ更新するよりは速くていいかな、
動作例
- 更新データを元に以下実施
- 存在しない KeyName の行は追加
- 存在する Keyname の行は、更新
- ColumnName が更新側にない場合は無視
KeyName は複合キー未対応
ColumnName が更新側にない場合に列追加したい場合は編集必要
最終行をもとに書式コピーしてあるので、最終行が表外の場合は修正必要（コピー自体も問題になるが）
Office Scripts で更新側を取得
JSON/object で取得出来るようになってた ので利用してみた
取得
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 で取得結果を反映
更新
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
keyword
update excel file without table by office scripts