Copy Excel OneDrive table to an specific cell on another Excel OneDrive table (Power Automate)

Question

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

Answer 1

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");
}

Answer 2

I may not be following this correctly, but you can also return values and pass them to another connector in Power Automate. Here is some documentation on how to return values with Office Scripts. Also, below is an example script with parameters and a number type return value. With returning values rather than console.logging them, you won't need to remove any output in your Flow steps. Let me know if you have any questions!

function main(
  workbook: ExcelScript.Workbook,
  issueId: string,
  issueTitle: string): number {
  // Get the "GitHub" worksheet.
  let worksheet = workbook.getWorksheet("GitHub");

  // Get the first table in this worksheet, which contains the table of GitHub issues.
  let issueTable = worksheet.getTables()[0];

  // Add the issue ID and issue title as a row.
  issueTable.addRow(-1, [issueId, issueTitle]);

  // Return the number of rows in the table, which represents how many issues are assigned to this user.
  return issueTable.getRangeBetweenHeaderAndTotal().getRowCount();
}