CSV to Excel Power Automate and Office Scripts Any File Encoding - Free | Fast | Easy

Let me show you how you can use a Microsoft Office Script to convert your CSV into Excel and further extend it to handle Mac/Unix file encodings and implement a table for later use in your Cloud Flow. No need for a premium or 3rd party connector. Much easier than using select/compose and apply to each. No need to worry about quote encapsulated values as the Microsoft Team have handled this with a regular expression.
Microsoft Script Example
docs.microsoft.com/en-us/offi...
How to Create a New Excel File using Compose
• Create a new Excel Fil...
00:00 Intro
00:44 Quick exploration of CSV's with different file encodings
01:26 Build our script using sample Microsoft Office Script
02:50 Building our Cloud Flow based on 3 sample files
06:09 Don't forget your file get file content!
07:01 Save, explanation of Logic and Test
08:30 Our Excel files but with Encoding problems demonstrated
09:06 Extend the script to handle file encodings and add a table
12:16 Looking at the newly converted CSV to Excel with encodings handled
13:00 Querying an excel file with the Table inserted during the Office Script Action
14:25 Summary and close
Additional Script for File Encodings (**NOTE** replace string GREATER with angled bracket as YT will not allow it in desc)
let rows = csv.split("
");
// Split each line into a row.
if (csv.search(/
/) GREATER -1) {
rows = csv.split("
");
} else if (csv.search(/
/) GREATER -1) {
rows = csv.split("
");
} else if (csv.search(/
/) GREATER -1) {
rows = csv.split("
");
}
Additional Script for adding a table to your Excel File
// Get the first worksheet
const selectedSheet = workbook.getFirstWorksheet();
// Create a table using the data range.
let newTable = workbook.addTable(selectedSheet.getUsedRange(), true);
newTable.setName("NewTableInExcel");
My previous attempt at converting CSV to JSON using Select/Compose and Apply to Each and an explanation of file encodings:
www.damobird365.com/how-to-pa... Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

[@MohammedKhan-rz1gz]

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]

Thank you Mohammed.

[@robsongdev]

Awesome! You just unlock a new world of possibilities in my mind

[@DamoBird365]

Office scripts are definitely a nice feature.

[@zachariecortes]

Thanks a lot for this very useful video ! This helped me so much !!!

[@gonzalofrontera305]

You’re the best! Thank you

[@tariqismail4571]

Excellent work as always!!! Very helpful..

[@DamoBird365]

Cheers Tariq. Hope it all made sense.

[@tariqismail4571]

@@DamoBird365 it sure did...thanks

[@indhumathi4727]

Too good, i cannot believe it would be this simple, i tried parsing CSV and write to excel and that took hours and this one less than a minute

[@DamoBird365]

Thank you. The script and regex is a definite benefit of this process and is super efficient 👍

[@indhumathi4727]

@@DamoBird365 Is there anyway to handle a column that has data with comma encolsed with ""XXX, XXX - 000""

[@franknielsen3219]

You are my God of flows!!!!! 👏👏👏👏👏👏

[@DamoBird365]

Cheers Frank.

[@jdduf]

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! :)

[@DavidWilsonNZ]

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]

Your org may have disabled docs.microsoft.com/en-us/microsoft-365/admin/manage/manage-office-scripts-settings?view=o365-worldwide

[@IvanLijnDiensten]

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]

Hi Ivan, could you explore this post www.damobird365.com/how-to-parse-a-csv-to-json-array-flow/ point 4 and use decodeuri ?

[@IvanLijnDiensten]

@@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?

[@zaraaxon3940]

Thanks for the video really helpful. Is there a way of adding a header line to the Excel worksheet before adding a table?

[@DamoBird365]

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?

[@zaraaxon3940]

@@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.

[@DamoBird365]

@@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.

[@user-sb9hn8sr1i]

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

[@DamoBird365]

I think this would be a good conversation for folk on the forum powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@dancelmarieminoza206]

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.

[@DamoBird365]

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.

[@OlivierTravers]

Nicely done! It would be interesting to look at the timestamps of the generated files to see how fast this ingestion works.

[@DamoBird365]

That was real-time. End to end, no editing. Hence the slight hiccup as I forget the get file content action. So, very quick.

[@OlivierTravers]

@@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.

[@DamoBird365]

@@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.

[@kaylapeixoto8510]

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.

[@DamoBird365]

Hey Kayla. I’ve not yet found a way to do that conversion. If you do, let me know 👍

[@DamoBird365]

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

[@facun_OK]

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.

[@DamoBird365]

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

[@facun_OK]

@@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,

[@maggiedu3895]

@@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

[@bconnaughton2514]

@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]

You can definitely get file content from a csv in SharePoint and subsequently pass to this script. What problem have you hit?

[@bconnaughton2514]

@@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.

[@andytuke1522]

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]

Check out www.damobird365.com/how-to-parse-a-csv-to-json-array-flow/ specifically point 4. This should handle all/most encodings.

[@andytuke1522]

@@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

[@zaraaxon3940]

My dates are not converting correctly into Excel so 10/04/2022 is ending up as 04/10/22 is there a way of dealing with this in the script?

[@Brazoul]

Hi Zara, Did you end up finding a fix for this ?

[@RollingEmpire]

Can you do the same conversation from share point is it possible

[@DamoBird365]

100% - just update the actions appropriately to get files from SharePoint

[@frankreymenants8386]

Great detail and explanation. I'm running into a problem. Gateway timeout 504. My csv is 4mb and about 17k rows. Would that be a problem?

[@DamoBird365]

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.

[@AaronPaul_google]

@@DamoBird365 I have the same issue and it is not working.

[@DamoBird365]

@@AaronPaul_google how complex is your conversion? Can you log details here powerusers.microsoft.com/t5/General-Power-Automate/bd-p/MPAForum?

[@AaronPaul_google]

@@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 :)

[@johnfromireland7551]

@@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 : kzbin.info/www/bejne/mGPLfZehntSDfck

[@kimsalas8197]

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.

[@DamoBird365]

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

[@kimsalas8197]

@@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.

[@kimsalas8197]

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.

[@DamoBird365]

@@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});

[@kimsalas8197]

@@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?

[@ahanachatterjee4664]

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."

[@DamoBird365]

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 [e-mail]

[@ahanachatterjee4664]

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.

[@timurkeles94]

I get the same error for line 27. How did you fix the error?

[@silasflewelling1736]

I'm getting the same error as well. How did you resolve?

[@johnfromireland7551]

@@ahanachatterjee4664 Click the three dots, in the code pane, with your script displaying, then "Share".

[@CyNiT]

I have a problem My CSV is a semicolon (;) in place of comma (,). How to do that then?

[@DamoBird365]

You would need to update the regex csvMatchRegex.

[@Argentinian]

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 :(

[@DamoBird365]

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.

[@VitoAD]

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

[@DamoBird365]

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

[@VitoAD]

@@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.

[@VitoAD]

@@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!!!

[@DamoBird365]

Nice one 👍 thanks for letting me know. Have fun 😉

[@ZoranRuzic1]

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]

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getWorksheet('AnotherSheet') selectedSheet.getRange("B2").setValue("123"); selectedSheet.getRange("C3").setValue("456"); }

[@DamoBird365]

The sample script above works fine with a sheet called AnotherSheet.

[@ZoranRuzic1]

@@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

[@chouyueh89]

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!

[@DamoBird365]

Do you encounter an error?

[@Brazoul]

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 Heath. Is that csv to office scripts? Do you use formatdatetime etc in your select before passing the data to excel? I’m not sure about this one.

[@Brazoul]

@@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]

@@Brazoul can you try use formatdatetime prior to runscript?

[@Brazoul]

@@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)

[@DamoBird365]

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 KZbin 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.

[@smarttaus1f]

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?

[@DamoBird365]

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 kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@smarttaus1f]

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,

[@DamoBird365]

@@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.

[@jett27]

Can I use this to convert my CSV file to a Sharepoint list?

[@DamoBird365]

👍 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.

[@jett27]

@@DamoBird365 Thanks for the swift response. Appreciate it!

[@hamidsiddiqi3573]

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]

Are you ruining this on a complex csv? Maybe try simplify it first. I’m not sure about that error.

[@jean-francoisfisette]

@@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 !!

[@DamoBird365]

@@jean-francoisfisette thank you very much for sharing. I’ve learned something new and will make sure I share that with others 👍

[@RollingEmpire]

Hello the video is helpful but when I run my flow it returning an empty workbook

[@DamoBird365]

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.

[@RollingEmpire]

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",

[@RollingEmpire]

I am from UK finding your videos really helpful

[@srinivasmanohar7146]

Hi Damion, how can we convert csv file to pipe delimited file in power automate. could you help me on this?

[@DamoBird365]

You could just use replace(dynamicfilecontent, ’,’ , ’|’) if there are no , in field data.

[@srinivasmanohar7146]

@@DamoBird365 Thanks for the quick reply. is there any office script for converting csv to pipe delimited as used for csv to excel.

[@DamoBird365]

Why would you use an office script? Or is it because of the regex and , in data columns? The replace would be more efficient.

[@srinivasmanohar7146]

@@DamoBird365 Thanks will work on it.

[@DamoBird365]

@@srinivasmanohar7146 no probs, let me know how you get on 👍

[@user-uh5iv6df4y]

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"

[@DamoBird365]

I would recommend you ask on the forumpowerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums as you will be able to provide more detail.

[@user-uh5iv6df4y]

@@DamoBird365 Thanks for your reply. I did that's how I ended up here :)

[@user-uy1pd3tw6p]

Hi sir, this is a very brilliant solution on PA! But it doesn't work when the CSV is at 1mb or more. Do you have the same problem? :)

[@DamoBird365]

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.

[@user-uy1pd3tw6p]

@@DamoBird365 Thanks for the link sir!! :)

[@yuricaceres1543]

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

[@yuricaceres1543]

De antemano, muchas gracias.

[@nikkikounder6000]

I tried this, it ran well once. After that I'm getting "Gateway timeout" error at "run script" action. No remedy on community works. Any help?

[@DamoBird365]

Was it the same file or maybe a large file?

[@nikkikounder6000]

@@DamoBird365 no, it is the same file. The csv hardly has just 10 to 20 rows of data

[@DamoBird365]

Have you deleted the original xlsx file that’s created or given the new file a dynamic name?

[@nikkikounder6000]

@@DamoBird365 the name itself is dynamic. And yes when I retried i deleted my first output (excel)

[@nikkikounder6000]

Only thing I'm yet to do is redo whole process from beginning

[@jallabalaram4331]

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

[@DamoBird365]

Possibly the file size? Have you tried with a smaller file?

[@jallabalaram4331]

@@DamoBird365 more 30 mb file with approx 10000 rows

[@jallabalaram4331]

can you please Suggest script for larger files

[@DamoBird365]

@@jallabalaram4331 probably dataflows.

[@jallabalaram4331]

@@DamoBird365 Can you suggest any thing done with power automate using office script

[@Boobydoo999]

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

[@DamoBird365]

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 :-)

[@Boobydoo999]

@@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.)

[@DamoBird365]

@@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 ; ?

[@Boobydoo999]

@@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!

[@DamoBird365]

@@Boobydoo999 powerusers.microsoft.com/ I’m on there too but another set of eyes with locale knowledge would help. Keep me posted.

[@villog3420]

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

[@joefisch4339]

This is what I am running into as well. I have followed the steps to a t. Have you or @DamoBird365 figured out this issue?