I need to copy data from one excel worksheet and paste (values only) on another worksheet using power automate + Office script
I started to creat a flow using the answer in the link bellow.
Power Automate: Copy Excel OneDrive table to the bottom of another Excel OneDrive table
The problem is I didnt understood the second script so I was not able to modify it to what I need ( that one paste on the end of the workbook)
SCRIPT on the link
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);
}
The reason for RemoveString is to remove the Date & Time Stamp from the outputs
This requires a little different workflow.
Run Script
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)
}
Compose
@{outputs('Run_script')?['body']?['Logs'][0]}
Compose 2
@{outputs('Run_script')?['body']?['Logs'][1]}
RemoveString
@{split(outputs('Compose'),' ')[0]}
NewString
@{replace(outputs('Compose'),variables('RemoveString'),'')}
RemoveString2
@{split(outputs('Compose_2'),' ')[0]}
NewString2
@{int(replace(outputs('Compose_2'),variables('RemoveString2'),''))}
Num
@{int(variables('NewString2'))}
Run Script 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");
}