Unanswered

Double quotes disappear in Excel Script when run in Power Automate

(0) Share

Report

Posted on by ivan_vbn

Hi, I have the following piece of script to get rid of the zeroes that result from the transpose-function returning empty cells:

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range A3 on selectedSheet
selectedSheet.getRange("A3").setFormulaLocal("=TRANSPOSE(IF(ISBLANK(A1:GG2);\"\";A1:GG2))");
}

It works fine when run from Excel in the browser (in the Automate tab), with this result in A3:
=TRANSPOSE(IF(ISBLANK(A1:GG2);"";A1:GG2))

But when Power Automate runs the same script with Excel's 'Run script'-action, it outputs the following formula in the sheet that its run on:
=TRANSPOSE(IF(ISBLANK(A1:GG2);;A1:GG2))

Thus, the double quotes and the formula returns zeroes after all. ‌‌

Any advise on how to maintain those double quotes and output the correct formula is very welcome!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Yutao Huang Microsoft Employee on at

Like (0)

Report

Hi @ivan_vbn ,

I was trying to repro the issue. The formula seemed to be working for me (except that I needed to replace semicolons with commas in the formula, which I guess was probably a typo in your original question?).

Were there other actions in the flow that might impact the script execution?

Was this reply helpful? Yes No
ivan_vbn 4 on at

Like (0)

Report
Hi @Yutao,

The semicolons are used in Dutch, I translated the formulas but didn't change the semicolons, apologies.

The whole script is as follows:

/** * 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. let rows = csv.split("\r\n"); // Split each line into a row. if (csv.search(/\r\n/) > - 1) { rows = csv.split("\r\n"); } else if (csv.search(/\n/) > - 1) { rows = csv.split("\n"); } else if (csv.search(/\r/) > - 1) { rows = csv.split("\r"); } /* * 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. row.forEach((cell, index) => { row[index] = cell.indexOf(",") === 0 ? cell.substr(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. let selectedSheet = workbook.getActiveWorksheet(); // Set range A3 on selectedSheet selectedSheet.getRange("A3").setFormulaLocal("=TRANSPONEREN(1:2)"); // Set visibility of row(s) at range 1:2 on selectedSheet to true selectedSheet.getRange("1:2").setRowHidden(true); // Auto fit the columns of range range A:B on selectedSheet selectedSheet.getRange("A:B").getFormat().autofitColumns(); // Replace all "\"" with "" on range A:B on selectedSheet selectedSheet.getRange("A:FF").replaceAll("\"", "", { completeMatch: false, matchCase: false }); // Set number format for range B3 on selectedSheet selectedSheet.getRange("B3").setNumberFormatLocal("d-m-jjjj"); // Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range B:B on selectedSheet selectedSheet.getRange("B:B").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left); }

Was this reply helpful? Yes No
ivan_vbn 4 on at

Like (0)

Report
PS with the transpose-line of course being:

selectedSheet.getRange("A3").setFormulaLocal("=TRANSPOSE(IF(ISBLANK(A1:GG2);\"\";A1:GG2))");

Was this reply helpful? Yes No