@takaekokaz (大好きハワイ)posted at 2024-03-09

Power automateでExcelシートの値を他のブックのシートにコピペしてテーブルにしたいです。

Q&A

Closed

OneDrive Office365 PowerAutomate OfficeScripts

解決したいこと

Onedrive内のExcelブック「出荷.xlsx」の「出荷シート」のデータを、他のExcelブック「出荷記録.xlsx」のシート1にコピペし、それを表から「出荷」という名のテーブルにしたいです。
テーブルの見出しはコピー元の最初の行にします。

発生している問題・エラー

ブック内のVBAなら簡単にできるのですが、ファイルをonedrive内から動かせないので、power automateまたはofficeスクリプトで解決するしかありません。
トリガーはフォルダ内にファイルが作成されたときです。
その後、どのようなアクションを選べばよいかご教示いただけますと幸いです。

自分で試したこと

ネットで調べても、PADでの説明がほとんどで、PAクラウド版のやり方が見つかりません。

1Answer

@nak435 posted at 2024-03-10

ブック間のセル範囲のコピーは、①コピー元、②コピー先にそれぞれスクリプトを準備して、Power Automateで①→②の順でスクリプトを実行します。

①コピー元のスクリプト

function main(workbook: ExcelScript.Workbook): WorksheetData[] {
    let cellData: WorksheetData[] = [];
    let selectedSheet = workbook.getActiveWorksheet();
    let values = selectedSheet.getRange("A1:D5").getValues()
    cellData.push({
      data: values as string[][]
    });
    return cellData
}

interface WorksheetData {
  data: string[][];
}

②コピー先のスクリプト

function main(workbook: ExcelScript.Workbook, cellData: WorksheetData[]) {
    let selectedSheet = workbook.getActiveWorksheet();
    cellData.forEach((value) => {
      if (value.data) {
        selectedSheet.getRange("A1:D5").setValues(value.data)
        console.log(value)
      }
    })
}

interface WorksheetData {
  data: string[][];
}

③セル範囲をテーブルに変換するスクリプト

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	let newTable = workbook.addTable(selectedSheet.getRange("A1:D5"), true);
}

上は②のスクリプトに含めてもよいです

なお、日付セルの書式が標準か数値の場合は、日付のシリアル値が表示されるため、yyyy/mm/dd等の日付の書式に変更してください。

1Like

Comments

@takaekokaz
Questioner
@nak435 さま
今回もご指導ありがとうございます。
さて教えていただいたスクリプトをPAフローに落とし込んで試してみましたが結果は以下のようになりました。

コピー元のスクリプトのoutputではexcelの表のデータはちゃんと変数に入っています。
{
"result": [
{
"data": [
[
"日付",
"商品番号",
"商品名",
"個数"
],
[
45292,
"a001",
"リンゴ",
1
],
[
45293,
"a002",
"バナナ",
2
],
[
45294,
"a003",
"なし",
3
],
[
45295,
"a004",
"みかん",
4
]
]
}
],
"logs": []
}
しかしコピー先のスクリプトのパラメータには受け渡しているはずの変数が空になっているようです。

テーブル先のスクリプトを実行するアクションのパラメータです。何か間違っているでしょうか？
@takaekokaz
Questioner
パラメーターを動的コンテンツresultにするのを忘れていました、、、初歩的な間違いでした。修正したらうまくできました。ありがとうございました。

@nak435

テーブルの名前変更は下記コードで可能です。

function main(workbook: ExcelScript.Workbook) {
	workbook.getTable("old_name").setName("new_name");
}

@takaekokaz
Questioner
@nak435 様
引き続き質問させてください。
①コピー元の表の最終行が変化する場合の対応ですが、①のスクリプトを下記のように変更しました。星印★がそれです。
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
let cellData: WorksheetData[] = [];
let selectedSheet = workbook.getActiveWorksheet();
★最終行を取得
let lastRow = selectedSheet.getCell(0,0).getRangeEdge(ExcelScript.KeyboardDirection.down).getRowIndex()
★最終行インデックスで範囲指定。
let values = selectedSheet.getRangeByIndexes(0,0,lastRow,22).getValues()
cellData.push({
data: values as string[][]
});
console.log(values.length)
console.log(cellData)
return cellData
}

interface WorksheetData {
data: string[][];
}
結果はvalues.lengthが10978
celldataも入っています。

ここから②コピー先スクリプトをどのように変更すればよろしいでしょうか？
下記はコピー元の表です。10979行あります。ファイルによっては最終行が変化します。

@nak435

対象行数もデータとして渡せばよいのでは？


function main(workbook: ExcelScript.Workbook): WorksheetData[] {
  let cellData: WorksheetData[] = [];
  let selectedSheet = workbook.getActiveWorksheet();
  //最終行を取得
  let lastRow = selectedSheet.getCell(0, 0).getRangeEdge(ExcelScript.KeyboardDirection.down).getRowIndex()
  //最終行インデックスで範囲指定。
  let values = selectedSheet.getRangeByIndexes(0, 0, lastRow, 22).getValues()
  cellData.push({
    rows: lastRow as number,
    data: values as string[][]
  });
  //console.log(values.length)
  //console.log(cellData)
  return cellData
}

interface WorksheetData {
  rows: number
  data: string[][];
}

②コピー先のスクリプトも同様で、
対象行数をデータとして受け取り、インデックスで範囲指定すればよいです。

当初の質問が解決したら一旦クローズして、続きの質問も新たな質問を起票しましょう。

@takaekokaz
Questioner
@nak435 様
返事が遅れて申し訳ありません。
①から取得した最終行value.rowを②で以下のように引き渡して問題が解決しました。
今回も丁寧なご指導誠にありがとうございました。
function main(workbook: ExcelScript.Workbook, cellData: WorksheetData[]) {
let selectedSheet = workbook.getActiveWorksheet();
cellData.forEach((value) => {
if (value.data) {
selectedSheet.getRangeByIndexes(1,0,value.rows,22).setValues(value.data)
console.log(value)
}
})
}

interface WorksheetData {
rows: number
data: string[][];
}

Are you sure you want to delete the question?