onedrivepower-automateexcel-onlineoffice-scripts

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


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


Solution

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

    enter image description here

    enter image description here