Office Script not working properly when file is not opened

Vignesh Babu Sundararajan 46 Reputation points
2022-04-26T03:28:15.68+00:00

I have a flow which triggers a excel office script .

A collection from power app initiates the flow .
Power app data : [{"Operations":"CNC_Programming","Score":"83%"},{"Operations":"CNC_Punching","Score":"89%"},{"Operations":"Bending","Score":"88%"},{"Operations":"Tapping_Operation","Score":"81%"},{"Operations":"Welding_Operation","Score":"79%"},{"Operations":"Grinding_Buffing_Operation","Score":"66%"},{"Operations":"Common_Points","Score":"81%"}]

I pass this data to excel , delete the existing item , paste it in that table .
Once script is triggered a chart is sent back to flow .

function main(workbook: ExcelScript.Workbook

) {

let selectedSheet = workbook.getWorksheet("Sheet1");

//get the current used range and lastRow

let myUsedRange = selectedSheet.getUsedRange();

let lastRow = myUsedRange.getAddress();

let radarchart = selectedSheet.addChart(ExcelScript.ChartType.radarFilled, selectedSheet.getRange(lastRow));

radarchart.getSeriesNameLevel[0]

let radarchartimage = radarchart.getImage();

radarchart.getTitle().setText('Score by Section');

radarchart.getAxes().getValueAxis().getFormat().getFont().setSize(1);

radarchart.getDataLabels().setShowValue(true);

radarchart.delete();

return [radarchartimage]

}

It works perfectly when excel file is kept opened but fails to paste the items on the table ( Only one row is getting pasted ) .

Kindly help in resolving this issue

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,401 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,627 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yutao Huang - MSFT 701 Reputation points Microsoft Employee
    2022-05-02T18:01:45.383+00:00

    Hello @Vignesh Babu Sundararajan ,

    Just to make sure I understand your scenario correctly. In the script code you shared, I'm not seeing the part that does "pass this data to excel, delete the existing item, paste it in that table". I assume those operations are done directly in your Power Apps app, and then your app would invoke your Power Automate flow to run the Office Script to extract the chart image, right?

    I'm guessing there might be a timing issue between the Excel Online operations in Power Apps (to prep the data in the table) and the operations in Power Automate (to extract the chart image). When the Run script action is trying to extract the chart image from the workbook, the changes made by the Excel Online the Power Apps haven't been fully propagated yet. These operations are from different "sessions" of Excel Online so there might be a synchronization among sessions in play here.

    Do you think it's possible to move all the Excel related operations into the script? You can pass the original data all the way from Power Apps to the script and let the script to prep the table then extract the chart image. You will need to update your script code to accept parameter for the main function.

    You can also try to add some delay (like 30 seconds) in Power Automate flow, before the Run script action, and see whether that would help.

    Please let me know how it goes!


3 additional answers

Sort by: Most helpful
  1. Vignesh Babu Sundararajan 46 Reputation points
    2022-05-03T11:32:32.827+00:00

    Spot on !!!
    I have added delay in the power automate and the issue got resolved.

    Can you guide on how to send power automate collection (two columns) to Excel using script and create a chart?

    Thank You


  2. Yutao Huang - MSFT 701 Reputation points Microsoft Employee
    2022-05-05T19:14:38.287+00:00

    Firstly, you will need to add a parameter to the main function to accept the incoming data in JSON format:

       function main(workbook: ExcelScript.Workbook, data: string): string {  
         let selectedSheet = workbook.getWorksheet("Sheet1");  
         let items: Item[] = JSON.parse(data);  
         let values = items.map(item => [item.Operations, item.Score]);  
         values.unshift(["Operations", "Score"]);  
         let newRange = selectedSheet.getRange("A1").getResizedRange(values.length - 1, 1);  
         newRange.setValues(values);  
         //get the current used range and lastRow  
         let myUsedRange = selectedSheet.getUsedRange();  
         let lastRow = myUsedRange.getAddress();  
         let radarchart = selectedSheet.addChart(ExcelScript.ChartType.radarFilled, selectedSheet.getRange(lastRow));  
         radarchart.getSeriesNameLevel[0]  
         radarchart.getTitle().setText('Score by Section');  
         radarchart.getAxes().getValueAxis().getFormat().getFont().setSize(1);  
         radarchart.getDataLabels().setShowValue(true);  
         let radarchartimage = radarchart.getImage();  
         radarchart.delete();  
         return radarchartimage;  
       }  
         
       interface Item {  
         Operations: string;  
         Score: string;  
       }  
    

    The code is mostly based on the code snippet you shared but with a few changes:

    • I added a few lines to parse the input string into a two-dimensional string array then insert into the worksheet.
    • I slightly adjusted a few lines in the chart generation code to make sure it returns the chart image with the updated chart formatting.
    • I saw you returned [radarchartimage] in your original code. This is returning an array with a single item. Should it just return radarchartimage, which contains the base64-encoded image?

    Then on the Power Automate side, you'll need to pass along the input JSON data from the PowerApps trigger into the Run script action. You may need to delete the existing Run script action and insert again to make sure it shows up the data input box.

    199383-image.png

    0 comments No comments

  3. Vignesh Babu Sundararajan 46 Reputation points
    2022-05-06T08:22:27.28+00:00

    Thank You
    Is there any reference material for excel script?

    Thanks