A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Outstanding, thanks for your reply! I'll get this to the right place. Have a lovely day!
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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" |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
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.
Outstanding, thanks for your reply! I'll get this to the right place. Have a lovely day!
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