Power Platform Community Forum Thread Details

Hello,

I'm trying to use the Excel Script solution provided by Microsoft to convert CSV to Excel via Power Automate.

My flow goes through all the steps, but the final file is empty. Below is the Script used and the Input and Output of the flow step that runs the Script. What I'm doing wrong?

/**
 * Convert incoming CSV data into a range and add it to the workbook.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
 let sheet = workbook.getWorksheet("Sheet1");

 // Remove any Windows \r characters.
 csv = csv.replace(/\r/g, "");

 // Split each line into a row.
 // NOTE: This will split values that contain new line characters.
 let rows = csv.split("\n");

 /*
 * For each row, match the comma-separated sections.
 * For more information on how to use regular expressions to parse CSV files,
 * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
 */
 const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
 rows.forEach((value, index) => {
 if (value.length > 0) {
 let row = value.match(csvMatchRegex);
 
 // Check for blanks at the start of the row.
 if (row[0].charAt(0) === ',') {
 row.unshift("");
 }
 
 // Remove the preceding comma and surrounding quotation marks.
 row.forEach((cell, index) => {
 cell = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
 row[index] = cell.indexOf("\"") === 0 && cell.lastIndexOf("\"") === cell.length - 1 ? cell.substring(1, cell.length - 1) : cell;
 });
 
 // Create a 2D array with one row.
 let data: string[][] = [];
 data.push(row);
 
 // Put the data in the worksheet.
 let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
 range.setValues(data);
 }
 });

 // Add any formatting or table creation that you want.
}

Input

{
 "host": {
 "connectionReferenceName": "shared_excelonlinebusiness",
 "operationId": "RunScriptProd"
 },
 "parameters": {
 "source": "sites/htmicron2.sharepoint.com,c5ae0f67-41b1-4c87-aa0c-644412719cdb,0df60f0f-682b-4342-9e9f-c87dc7713ef7",
 "drive": "b!Zw-uxbFBh0yqDGREEnGc2w8P9g0raEJDnp_IfcdxPvekku0SZry-RJHIBNeW5Hqt",
 "file": "015NAVLMPEGPNNSSMMBVEYRSZSCULBNPPT",
 "scriptId": "ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01ID2FC2LH3GOL6FYD2ZDKQHC7PXU4WYPW",
 "ScriptParameters/csv": "OppID,Company,Customer,CustomerCode,Client,ProductCode,Product,Volume,Stage,Sales Price,Comment,DeliveryDate\r\n6836 ***[CONFIDENTIAL DATA DELETED]*** \r\n"
 }
}

Output

{
 "statusCode": 200,
 "headers": {
 "Cache-Control": "no-store, no-cache",
 "Transfer-Encoding": "chunked",
 "Vary": "Accept-Encoding",
 "Access-Control-Expose-Headers": "Version",
 "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
 "mise-correlation-id": "8c706624-5ceb-4b3d-b624-7aaed54e72ab",
 "x-ms-workflow-name": "f3f17a73-f177-4186-94bb-ba78687a76fa",
 "Version": "3.8",
 "x-ms-client-request-id": "29c75fe7-f6f2-49ed-b522-48bdae4dc62b",
 "x-ms-request-id": "29c75fe7-f6f2-49ed-b522-48bdae4dc62b",
 "X-Content-Type-Options": "nosniff",
 "X-Frame-Options": "DENY",
 "Timing-Allow-Origin": "*",
 "x-ms-apihub-cached-response": "false",
 "x-ms-apihub-obo": "false",
 "Date": "Fri, 24 May 2024 13:31:42 GMT",
 "Content-Type": "application/json; charset=utf-8",
 "Content-Length": "25"
 },
 "body": {
 "logs": []
 }
}

Thanks

Categories:

General topics