Share via

Help using Office Scripts with Power Automate to convert a CSV to XLSX table.

Anonymous
2023-07-04T14:44:57+00:00

I'm trying to utilize Power Automate and Office Scripts to automate reporting in my role. I'm really close to getting what I need accomplished, but the Office Script tutorial that I found that actually created an XLSX table is formatting the data and headers in an unexpected way. The table generated is including incorrect characters. And is splitting the data incorrectly as well. I've tried to apply a few other tutorials and details to the Office Script, but I haven't had any luck. I don't think I need to alter my Power Automate flow, as it is generating the excel table. And the data input into Office Scripts appears correct.

My understanding is I need to update the Office Script code so that it splits correctly (After "\r") and that the columns do not include \ in the column names. But I have no idea on how to do this. Any assistance would be appreciated!

Tutorial I followed to create the Power Automate Flow: https://youtu.be/2TIolfmtWQM 

Inputs to Office Script Code:

[ [""Interval Start","Interval End","Interval Complete","Filters","Media Type","Queue Id","Queue Name","Offer","Answer","Answer %","Abandon","Abandon %","ASA","Service Level %","Service Level Target %","Avg Wait","Avg Handle","Avg Talk","Avg Hold","Avg ACW","Hold","Transfer"\r",""7/3/23 12:00 AM","7/4/23 12:00 AM","TRUE","","voice","c49cdbc9-a4ca-4b31-8458-a2bcba36617e","IT Corporate Support","94","63","0.6702127659574468","31","0.32978723404255317","272505.04761904763","0.24468085106382978","0.8","247550.085106383","740172.640625","310790.4375","","436197.31746031746","",""\r",""7/3/23 12:00 AM","7/4/23 12:00 AM","TRUE","","voice","0c97fd2a-9f0a-4e14-850c-743affa9617e","IT MIDS","9","5","0.5555555555555556","4","0.4444444444444444","313497.4","0.1111111111111111","0.8","301832.77777777775","547755.2","213992.2","","333763","",""\r",""7/3/23 12:00 AM","7/4/23 12:00 AM","TRUE","","voice","4d010029-2331-4a70-81a2-56be591f6402","IT Rx Support","66","62","0.9393939393939394","4","0.06060606060606061","190304.46774193548","0.22727272727272727","0.8","182788.4393939394","704710.9838709678","379348.82258064515","171534.5","319828.79032258067","2","1"\r",""7/3/23 12:00 AM","7/4/23 12:00 AM","TRUE","","voice","8668b841-df1c-4d76-9abc-0608476e24eb","IT Store Support","339","287","0.8466076696165191","52","0.15339233038348082","237085.23344947735","0.2182890855457227","0.8","230479.42477876105","750096.9475524476","384328.03846153844","199194.33333333334","363679.45804195805","3","5"\r",""]]

Office Script Code:

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) { let selectedSheet = workbook.getActiveWorksheet();const alllines = lineCSV;let counter = 1;for (let line of alllines) {if (line.includes(",")) {let items = line.split(",");selectedSheet.getRange("A" + counter + ":W" + counter).setValues([[items[0], items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[8], items[9], items[10], items[11], items[12], items[13], items[14], items[15], items[16], items[17], items[18], items[19], items[20], items[21], items[22]]]);counter++;}}workbook.addTable(selectedSheet.getRange("A1:W" + counter), true).setName("Table1");}

Outputs from Office Script Code:

{ "statusCode": 200,"headers": {"Cache-Control": "no-store, no-cache","Transfer-Encoding": "chunked","Vary": "Accept-Encoding","Access-Control-Expose-Headers": "Version","Set-Cookie": "ARRAffinity=40237ffdc57de1390eeff374e782e979bae0af189a51754a0bc4cff0e861cdf3;Path=/;HttpOnly;Secure;Domain=excelonline-cus.azconn-cus-001.p.azurewebsites.net,ARRAffinitySameSite=40237ffdc57de1390eeff374e782e979bae0af189a51754a0bc4cff0e861cdf3;Path=/;HttpOnly;SameSite=None;Secure;Domain=excelonline-cus.azconn-cus-001.p.azurewebsites.net","mise-correlation-id": "31347922-6f52-4b22-8cb2-f566678074a4","x-ms-workflow-name": "eee34cf9-cb68-41d3-8a6f-d71589edcabd","Version": "3.7","x-ms-client-request-id": "ec257c52-3244-411a-b944-b52b882968fe","x-ms-request-id": "ec257c52-3244-411a-b944-b52b882968fe","Strict-Transport-Security": "max-age=31536000; includeSubDomains","X-Content-Type-Options": "nosniff","X-Frame-Options": "DENY","Timing-Allow-Origin": "*","x-ms-apihub-cached-response": "true","x-ms-apihub-obo": "false","Date": "Tue, 04 Jul 2023 14:28:30 GMT","Content-Type": "application/json; charset=utf-8"},"body": {"logs": []}}

Generated Result:

[""Interval Start" "Interval End" "Interval Complete" "Filters" "Media Type" "Queue Id" "Queue Name" "Offer" "Answer" "Answer %" "Abandon" "Abandon %" "ASA" "Service Level %" "Service Level Target %" "Avg Wait" "Avg Handle" "Avg Talk" "Avg Hold" "Avg ACW" "Hold" "Transfer"\r" ""7/3/23 12:00 AM"
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

2 answers

Sort by: Most helpful
  1. Anonymous
    2023-07-04T16:52:54+00:00

    Outstanding, thanks for your reply! I'll get this to the right place. Have a lovely day!

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2023-07-04T14:58:19+00:00

    Dear respected D0uttful,

    Good day! Thank you for posting to Microsoft Community. We are happy to help you.

    As per your description, it seems that you are using Power Automate and Office Scripts.

    If my understanding is right, since our category focus on Office 365 built-in products, the issue you encountered is related to Office Script code, to avoid any incorrect information from our side, and to make sure you get professional help, we sincerely recommend you raise questions in Microsoft Q&A – Office Development. Questions about programming, development, and so on can all get answered there. Experts there forum would conduct in-depth research on the problem you encountered and provide you with more professional assistance.

    The reason I ask you to post a new thread is that in Answers forum, we have limited resources and very little knowledge about Office Script code and in  Microsoft Q&A – Office Development, you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction.

    Appreciate your patience and understanding and thank you for your time and cooperation.

    Sincerely,

    Waqas Muhammad

    Was this answer helpful?

    0 comments No comments