Excellent workaround. This is a cleaner and faster way versus going through api keys for third-party services and the drama that comes with them. Worked beautifully for me
@@DamoBird365 Sorry I wasn't clear. I meant to look at how fast Power Automate goes through each increment in the loop if you process a bunch of CSVs in a row.
@@OlivierTravers ooooh. It’s about 6 seconds for each or 18 secs for the loop. The slight problem with the run script action is concurrency, I don’t think it’s supported, certainly when I tried, it often failed.
I have a CSV file with approximately 31 columns and 2700 records. The file has the following characteristics: Windows format (CR LF) UTF-8-BOM encoded It is structured as follows: "Personal Name","Enterprise ID","Work Order","External ID Number","Size Code" I made an identical copy of your Power Automate flow and copied the script, but it doesn't do anything at all. It keeps running the script for 40 or 50 minutes until it fails. I need help, please, even if it's paid :(
You could try the forums, alternatively I do offer support, rates on my contact page www.damobird365.com/contact-me/ drop me a message if you want to proceed.
Hello. I get an error in run script step. "message": "The file format is not recognized. Please check that the selected workbook is valid. clientRequestId: 497850f7-2d83-488a-9d37-b80828d66655", "logs": null
Thank you for this solution - my flow is failing at the last step with the following error message: Office JS error: Line 38: Range setValues: The argument is invalid or missing or has an incorrect format. Line 38 of the script is this: range.setValues(data); Any ideas as to what needs to change in my dataset, my csv, etc. to get this running? It's almost there! :)
Hello Sir, This is really interesting, I have 5 cols and 1400 rows in the csv. It worked for me. only thing is, I wanted to append the rows in an existing excel file where I have previous data. So, added some codes: let tbldata = sheet.gettable(‘data’) let rowCount = tbldata.getrowcount() +1 One the range: let range = sheet.getRangeByIndexes(index + rowCount, 0, 1, data[0].length) this changes should work easily. but these small modifications give me a bad request of Gateway timeout. how come this appending to existing file is that difficult instead of creating a new file?
I’m not sure about your error but check out the script here as it will start from the next available row - Power Automate, Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html
I figured out the issue now. To append the rows after current data, I used a var tRows. let tRow = sheet.getUsedRange().getRowCount(); this makes the whole process extremely slow. if i use static number (2941) to start putting new data, it completes within 1 min. I am not very expert. would you be able to tell me any efficient way to deal with this? Please note that, my current sheet is not too big,
@@smarttaus1f I think this video writes the rows as a loop, but the latest one writes the data as a batch. It will therefore be faster. I’ve learned loads in the past year, hence my latest video above.
Awesome video. Very clear. Thank you. Now... I've done excel.new and Excel has fired up in the browser window. I don't have the Automate tab... Is there something I need to enable or have the organisation license. I do have Automate scripts and they do run etc - so there's an organisation license for automate (however this works).
@DamoBird365, is there a way to do this when the CSV files are stored in Sharepoint rather than Outlook? There doesn't seem to be a way of getting the filename without extension on sharepoint. Cheers
@@DamoBird365 The problem I hit was after I put the CSV in SharePoint, there then wasn't option in creating the blank file to name it without the extension, so it ended up being called *.csv.xlsx which didn't read properly. I've worked round it by putting the csv into OneDrive, creating the xlsx there, then copying it over to SharePoint. If you have a more elegant method, I'm all ears! Cheers for your videos.
Just discovered your channel. We are going to start using Power Automate soon. I followed your instructions but the script failed to populate the new excel file. I noticed that the script on the link is different than the one you used. I created a new script using the code that you used. With that code the Flow work and the excel file was populated. The only problem now is that all my cell data has " " around them which didn't happen on your xlsx file. Any tips on fixing the script? Thanks
Thanks for stopping by, the data in the file is determined by regular expression. Is this the script you’re using? learn.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv
@@DamoBird365 yes it was at first but failed. I basically then created a script by hand copying the script from your video. With that code it worked but adding the “”. I have another script that formats the xlsx file so I added a find and replace to remove the “”. I created a flow for that script next step is to combine the two flows. Have a webinar on Power Automate next week so was trying to get to familiar with it before then. Thanks again for taking the time to reply.
@@DamoBird365 tried again this morning with the script from the link and it worked. I must have had an error in script the first time. Still have "" but I can clear that up in another script. Thanks again!!!
Best to explore the limits in the docs learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits?tabs=business#data-limits you could consider breaking it down into chunks/batches.
Hi Damion, Thank you so much. I created the flow using your video and it all works fine but the thing is according to my requirement the csv file may or may not get gernerated for the current day depenind on data availability, in case if csv file is not generated, the "get file content" runs infinitely. I am new to power automate.Any help would be much appreciated
👍 yes, return the array back to Power Automate and then place in an apply to each loop to add list item. But if the csv is massive, it won’t be efficient.
You could prepend a line to the start of the CSV? I am assuming it’s missing one and is just data? The solution uses the first line of data as a header. Was that your experience?
@@DamoBird365 Yes the csv has no header to start with, I need a header on the file as I am trying to then use the file content to populate a table in SQL that I have created so need the header on the Excel file within the table.
@@zaraaxon3940 a csv is just text, so you can insert a compose, with a header, a return line and the csv body and then pass the compose to the script, rather than the csv body. Your file will then have a header in the table.
Hi, I get this error We were unable to run the script. Please try again. The script couldn't create a connection with Excel. Please try again. clientRequestId: 8aceb31c-f062-40eb-989c-400dcd63bbb2 the run script won't run however the conversion happens I can see the files in there location but i can't open the xlsx showing up error "different format to file extension"
Hi Damien, this worked - after some sweat - just as promised, thanks a lot! One problem I run into and can't quite solve: we use the flow to save csvs from email to a Sharepoint location. Those csvs are (if I am not mistaken) Linux and sometimes contain LF-linebreaks within double quotes. This messes up the csv and resulting xlsx because it creates new lines where they shouldn't be. Would you know of any way to remove those line breaks within double quotes as to prevent this? Thanks already!
@@DamoBird365 thanks! I tried a little bit, but it might be a bit too advanced for me (I am already struggling to get the file content from the earlier step into that formula from your step 4). Could there be a way to include it in the script ConvertCSV that is also mentioned in the video?
I was working with the MS docs already but the change for the unix and Mac csv format is really useful. The problem I have is that the csv files I'm being supplied with have a new line or carriage return in some description cells. Anyone got any ideas how to work around that?
@@DamoBird365 Quick response! I got round the LF in a cell issue by swapping the MS script to remove the LF and then split on the CR instead of the other way round. But I have some cells with a CRLF in the text (no idea how they even did that). I'll watch your video but I'm pretty stumped
Hello.. thank you for the lovely post. It was very helpful. I am facing an issue for some csv files in the Run script section. I can create excel with 50 columns but csv having higher no of columns (eg.70) throwing the following error. The Error: " We are unable to run the script. Office JS error: Line 27: Range set Values. The number of rows or columns in the input array doesn't match size or dimensions of the range."
I haven’t tried it to that level to be honest. You’ve maybe identified a limit. Or could there be a formatting issue in your csv? If you can share the csv, I can try ideas@damobird365.com
Thank you for the reply. There was a issue in the csv and it's fixed now. I have another question, HOW TO SHARE THE OFFICE SCRIPT TO END USER SO THAT THE FLOW SHOULD NOT THROUGH "SCRIPT NOT FOUND " ERROR.
I’m sorry to hear that. There must be a logical reason. Best thing would be to explore your flow history to see when the data first became empty. Can you see the csv data being passed to the excel script? If not, work back through the flow. I’ll try help where I can.
I keep get this "message": "We were unable to run the script. Please try again. Runtime error: Line 4: Cannot read property 'trim' of undefined rInclientR equestld:447f02b0-1010-4522-928 9-97c562118546",
Love your videos, The process works almost perfectly However i'm having an issue with my dates formatting incorrectly. For example the 08-09-22 (8th of September) is formatted as the 09-08-22(9th of august). However dates with the day after the 13th are formatted correctly. I've made sure my sharepoint, Excel and Power automate are set to my local time(Sydney AUS). I have also tried NOT setting the DateTime Format to ISO 8601(this causes the flow to fail and makes the times/dates appear with the T and Z at the ends.Aalso using an expression to format the date and time formatDateTime(parsedatetime(item()?['COLUMN NAME'],'en-au'),'dd-MM-yyyy HH:mm:ss' also fails with the ISO datetime turned off). Formatting with the same expression also causes the issue unfortunately. Any help or guidance would be appreciated!
@@DamoBird365 Hi Damo, I do not, i use the format date time during a select action for when turning the data that I need into a html table. The dates are still incorrect when viewing the Excel sheet after the office script has been run. This select action happens after the script runs.
@@DamoBird365 I have tried, However i am unsure if i am doing it incorrectly. the current flow is this I receive an email with a report in csv format the flow gets the attachments from the email and dumps them into a sharepoint then a 2nd flow runs that is > a file is created in a folder > a variable is initialized for the blank workbook file content for the new xlsx > create file for the new xlsx > get file content using path of the newly created xlsx > Run script for the CSV content to be copied/exported to the xlsx (This is the step where the number format shenanigans are happening) > Get tables > list rows present in a table > Select which selects the columns i need from the xlsx > create html table from the output from the Select action > sent email with html table The goal is to send an email with a html table from the data from the csv report i receive every day dynamically I have tried the convert time zone action on the csv and excel sheet but it errors out, the settings i have tried are convert from UTC +10 to UTC +10 with format string of Short date pattern, General date/time pattern (long time), and the same but short time. However all 3 error. I have also tried creating another script to format the cells to English (Australia) Locale but this also errors out and also modifying the script to attempt to force the data to be pasted in local date time but my coding experience is limited and this also failed. I am happy to send you screenshots of the flow with all of the sensitive data redacted(as this contains data from the company i work for). The csv file i receive and then convert manually in excel has the time/date formatted correctly as well(confirmed using long date showing month name instead of just the number)
Is the aim just to turn a csv into html? Ie no need for excel? I haven’t got a video on this and I realise it’s quite difficult to solve via RU-vid comments. Wonder if you could post to the forum? powerusers.microsoft.com/ I would approach it differently, split the csv into an array and pass to create html table, but it can also depend on the formatting of your csv.
Me ayudarías a entender qué hice mal,porque por más que intento siempre me aparece el error siguiente: No pudimos ejecutar el script. Vuelve a intentarlo. Error de tiempo de ejecución: Line 32: Cannot read property 'getRangeByIndexes' of undefined clientRequestId: 66324fcf-4c53-4e9d-a1d3-f7a171467a31
When I test the flow, I get the following error when it comes to script part : We were unable to run the script. Please try again. Runtime error: Line 37: Cannot read property 'getRangeByIndexes' of undefined clientRequestId: c7fb8695-5e00-4550-adbb-a9471777a04c. How can i solve this
@@DamoBird365 I got the same problem (Line 37: Cannot read property 'getRangeByIndexes' ...). If you are not using the English version of Excel, you need to replace "Sheet1" (in your Script :.....let sheet = workbook.getWorksheet("Sheet1").........) by the translated version (french version : "Feuil1"). It works for me !!
I tried similar steps for csv files in sharepoint. Value variables are not available in list folders in sharepoint, similarly filename without extension due to fact sharepoint stores info with extension. I tried get file properties and filtered query for csv to get csv files only and selected body/value in loop but operation fails could you put new video to do csv to excel conversion in sharepoint
Hi Damion, I'm trying to convert an .xls file to .xlsx using Power Automate. I followed this tutorial earlier with great success so I'm hoping that you can point me into the right direction for this conversion as well.
Hi Kayla, if you are doing low volume, you could use encodian, which comes with a 50 action limit per month on their free account. support.encodian.com/hc/en-gb/articles/360011804178-Convert-Excel
The script is only work for 4000 rows and below, i have around 20k rows for my csv, i've been looking solution for hours but no luck. Appreciate if you can help!
very helpful video and was able to export the csv files. however, is there any way to email the excel file as an attachment? it doesn't seem to work if I select the "current item" as an attachment.
Current item would apply to an apply to each but even then you need the file content and file name. If you are converting a file, you need to get file contents and pass the file content to the email action.
How can I insert data to specific worksheet , now it will add data to default one "Sheet1" ? I have tried to change the line in script " let sheet = workbook.getWorksheet("Sheet1");" but this change produce an error. New Worksheet is created in flow, so I was expecting that office script will know where to put data
@@DamoBird365 In meantime I have managed to write script to work as I need. I am converting two csv files to one xlsx with two worksheets. Your video and instructions are really great and thank you very much on them
Many thanks for this great video and knowledge sharing! I have tested this script and works great. However I would like to know if there is a way to apply this solution with csv files with +10k records as it seems this solution handles small files. I believe there is a limitation in Power Automate for cases like this but is there any known solution for situations like this? Thanks in advance.
Can you let me know where you have encountered the limit? An error? Here it states 5MB and 5 million rows docs.microsoft.com/en-us/office/dev/scripts/testing/platform-limits
@@DamoBird365 Hi and thank you very much for replying back! perhaps I am doing something wrong but spent hours trying to understand my problem... at the moment I am working with a 4.5k file rows and receive flow failed message "An action failed. No dependent actions succeeded." which I am afraid spent hours trying to resolve it without success. Do you have by any chance what this problem can be resolved? Best regards,
@@DamoBird365 Hi, I am encoutering similar probelms. My CSV files have 10000 rows, when running the Power Automate, error occurs with the Run scrip of ConverCSV: Office JS error: Line 35: Worksheet getRangeByIndexes: The request failed with status code of 413. Do you have any solution for this? Thanks in advance
Really great video. I'll be watching more of your videos. I do have a slight problem when I try to recreate this flow on a CSV. I see to get an error when running the script: { "message": "We were unable to run the script. Please try again. Runtime error: Line 23: Cannot read property 'getRangeByIndexes' of undefined clientRequestId: 8cb75c89-f85f-4d2c-975e-35aca2e77dce", "logs": [] } I know it must be a noobie error, but I have been working on this for hours now and still no luck. Any ideas? Many thanks J
If you've copied/pasted the script, I would guess that the most likely cause is problems with your CSV data. Look at the history of the failed run, check the input to the office script, does it look like a CSV? filed1,filed2,field3 etc? I think the error basically means it's unable to process the data, albeit I am not a programmer :-)
@@DamoBird365 : Thanks so much for answering. I did copy and paste the script just as you showed in your video. When I check the Raw Inputs in the history, I do not see a CSV, instead it is a Base64 string. I double checked that I inserted the correct ID's in all the actions. I decided to insert a Compose Action to convert the CSV output from Base64 to String, then inserted that as an input parameter into the Run Script action. I now see the CSV string in its correct format, but I still get the same error: { "message": "We were unable to run the script. Please try again. Runtime error: Line 23: Cannot read property 'getRangeByIndexes' of undefined clientRequestId: 80867442-7d3b-4598-9efa-95b8bd9c3c2c", "logs": [] } I don't know enough about JavaScript to debug the script in Excel. Could localisation be an issue? I am using Power Automate in a Chrome browser and my O365 Instance has Swedish as default language, so when I log into Excel in Chrome, it's in Swedish. There are some differences in number notation: "," denotes a decimal place for example and ";" is used in Excel to denote the delimiter between function parameters. (I may be clutching at straws here.)
@@Boobydoo999 woah! You’re testing my knowledge here 😂 might be one to ask on the forum? You can supply the csv direct to the script and test locally in excel. But it is a learning curve. I am not a JavaScript programmer but have a grasp of troubleshooting the code. Can you change the delimiter in the script to ; ?
@@DamoBird365 Is there a forum? That would seem the best way forward for me I guess. It feels like I am so close to completing this now. I did change the delimiter in the script but that did not help. Really appreciate your input and assistance!
Possibly, I’ve not tested it to the extremes. Take a look here docs.microsoft.com/en-us/office/dev/scripts/testing/platform-limits 5MB is one possible limit.
@@DamoBird365 Its a Bad Gateway error 504. Flow times out, while running the Office Script. My file is a little over 5MB. Thanks for the link, I've posted it there :)
@@AaronPaul_google The Power Automate "Run Script" Action has a 2-minute run time limit. Try using a loop in your Flow and if possible split your csv file into smaller ones first and the Office Script "Run Action" against each of those smaller files. See this video which describes that problem : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-b2hLdkioPGg.html
This was very helpful! The script ran as shown in your video, but it is adding quotation marks around the values in one of my columns where the data type is already string. The column has multiple text values in it like this: MA, SYE, KUP, DSWR strung together as a single string. The column gets converted fine on the CSV side, but when in the Excel file, the values for that column look like this: “MA, SYE, KUP, DSWR” I don’t know much about scripts to figure out how to prevent this. Can you help? I’m creating my CSV from the CSV table step in the flow. It’s output is from a Select statement where I map my columns. I tried hard coding the values in the mapping and also using concat to string them together, and it did the same thing.
Hi Kim, a quick and easy way to achieve this would be to do a replace all on the sheet after you have created it. Add the following to your existing function: let selectedSheet = workbook.getActiveWorksheet(); selectedSheet.replaceAll("\"", "", {completeMatch: false, matchCase: false});
@@DamoBird365 Thanks! I searched the forum and saw you had provided this answer. Thanks for the follow up. Instead of using selected.Sheet, I had to use just sheet, and it worked.
One more question on this….something else came up. I’m using this script for various files and the column letter is different in which the quotation marks needs to be fixed. How do I adjust the new code you shared for this issue or adjust the flow to account for this? I don’t know enough about scripting to figure out on my own.
@@kimsalas8197 do you mean a replace on a single column rather than the whole sheet? For column B you could try selectedSheet.getRange("B:B").replaceAll("\"", "", {completeMatch: false, matchCase: false});
@@DamoBird365 So I mean one of files where I want to run the script may have a column to be replaced in column L (where those quotes need to be replaced). But using the same script (with the added code to account for the quotes) on a different file that column may reside on column H. So how would I use the same script for that scenario? Or would it be better just to make a copy of the script, rename it and then change the column letter for the affected column that has the quotes?
We were unable to run the script. Please try again. Office JS error: Line 45: Worksheet getRangeByIndexes: The request failed with status code of 413. getting this error please hep