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

Hey i answered that question, what did you want to do differently? Did you receive an error or something? Or did you want to add it to a cell that isn’t a table? — Ethan, Apr 21 '21 at 21:27
I know very little about office script but is not a problem if it is a table but I need the information to be paste on an specific cell and replace the information — Thaís Ferreira Araújo, Apr 21 '21 at 23:04
Starting on A7 with range of previous copied table (paste as number) — Thaís Ferreira Araújo, Apr 22 '21 at 00:02

Ethan · Accepted Answer · 2021-04-22T15:32:35.990

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

Thanks !!! It sure answer my question ! I will try the flow this weekend and tell u if it worked ! — Thaís Ferreira Araújo, Apr 23 '21 at 11:38

score 1 · Answer 2 · answered Apr 23 '21 at 20:12

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

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

2 Answers2