1

I am currently stuck trying to figure out how to copy all data from a OneDrive excel table to another OneDrive excel table. Simply paste data to the bottom of the table. Both tables have the same amount of columns and data types.

  • Copy table from an Excel OneDrive file (File 1)
  • Paste data at the bottom of another table to a different OneDrive excel file (File 2)

Hoping someone could help me figure this out. Its seems very simple. Below is my current Power Automate Flow

enter image description here

Ethan
  • 808
  • 3
  • 21
Elixir
  • 303
  • 3
  • 9
  • 26

1 Answers1

2

You can use Office Scripts to achieve this.

enter image description here

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

enter image description here

If you want to learn a little more about Office Scripts and adding to worksheets, you can check out one of Microsoft's PMs Sudhi Ramamurthy's YouTube video here.

Ethan
  • 808
  • 3
  • 21
  • Thank you Ethan. I tried recreating the flow but I am stuck at the script stage. My knowledge of power automate is still low unfortunately. – Elixir Apr 06 '21 at 18:13
  • Hi @Elixir, what part are you stuck on? – Ethan Apr 06 '21 at 18:16
  • Thank you for your help. I did a bit of research regarding office scripts and it appears my organisation has disabled this feature for us. Once my admin has enabled this I can give this a try and will let you know how I get on. Thank you again Ethan !! – Elixir Apr 07 '21 at 08:50
  • Just in case my admin team do not enable this feature for security reasons. I will try and figure out how to do this without Run Script. If this is possible. – Elixir Apr 07 '21 at 09:00
  • Your company should be able to turn it on! It took me about 48 hours to get the “Automate” in my Excel Online Ribbon. – Ethan Apr 07 '21 at 10:14