onedrive - 将 Excel OneDrive 表复制到另一个 Excel OneDrive 表上的特定单元格 (Power Automate)

Question

我需要使用 power automate + Office 脚本从一个 Excel 工作表复制数据并粘贴(仅值)到另一个工作表

我开始使用下面链接中的答案创建流程。

Power Automate: Copy Excel OneDrive table to the bottom of another Excel OneDrive table

问题是我不理解第二个脚本，所以我无法将其修改为我需要的(粘贴在工作簿末尾的那个)

链接上的脚本

For Run script I have

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheets()[0];
  let lastRow = sheet.getUsedRange(true).getLastCell().getRowIndex() + 1;
  let rng = "A3:P" + lastRow
  let tableTest = sheet.getRange(rng).getValues();
  console.log(tableTest);
}
Then under Compose

@{outputs('Run_script')?['body']?['Logs'][0]}
Then Initialize the "RemoveString" variable

@{split(outputs('Compose'),' ')[0]}
Then Initialize the "NewString" variable

@{replace(outputs('Compose'),variables('RemoveString'),'')}
Then Run Script 2 and add "NewString" as the parameter.

function main(workbook: ExcelScript.Workbook, rangeTest: string) {
  let table = workbook.getTable("BacklogTable");
  let str = rangeTest;
  let testerTest = JSON.parse(str);
  table.addRows(null, testerTest);
}

RemoveString 的原因是从输出中删除日期和时间戳

Answer 1

这需要一些不同的工作流程。

运行脚本

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheets()[0];
  let lastRow = sheet.getUsedRange(true).getLastCell().getRowIndex() + 1;
  let rng = "A2:C" + lastRow
  let tableTest = sheet.getRange(rng).getValues();
  console.log(tableTest);
  console.log(tableTest.length)
}

撰写

@{outputs('Run_script')?['body']?['Logs'][0]}

撰写 2

@{outputs('Run_script')?['body']?['Logs'][1]}

删除字符串

@{split(outputs('Compose'),' ')[0]}

新字符串

@{replace(outputs('Compose'),variables('RemoveString'),'')}

删除字符串2

@{split(outputs('Compose_2'),' ')[0]}

新字符串2

@{int(replace(outputs('Compose_2'),variables('RemoveString2'),''))}

数量

@{int(variables('NewString2'))}

运行脚本 2

function main(workbook: ExcelScript.Workbook, rangeTest: string, length: number) {
  let str = rangeTest;
  const arr = JSON.parse(str);
  let sheet = workbook.getWorksheet("Sheet2");
  let rng = "A7:C" + (6 + length); //Change C to whichever column you want to end on
  sheet.getRange(rng).setValues(arr);
  sheet.getRange(rng).setNumberFormatLocal("0.00");
}