Forum Discussion
djmitz6
Feb 21, 2024Copper Contributor
Excel Script ReplaceAll ignore formulas
Greetings, I'm currently running an excel script where I am replacing all single quote characters with two single quote characters consecutively so that I can push the data in a table over to an ...
SergeiBaklan
Feb 22, 2024MVP
I'm surprized you generated such solution without Automate on the board.
Just in case. Since that will be used with Power Automate which has no idea about active worksheet, we need to define the worksheet explicitly; or iterate all worksheets if replacements shall be done in each of them. Like
function main(workbook: ExcelScript.Workbook) {
workbook
.getWorksheets()
.forEach(sheet => (
sheet.getUsedRange() !== undefined ) ?
sheet
.getUsedRange()
.getSpecialCells(ExcelScript.SpecialCellType.constants)
.getAreas()
.map(a => a.getUsedRange().replaceAll("'", "''", {}))
: ''
)
}
In addition we check if the sheet is empty and skip it, otherwise script stops on it.
Lorenzo
Feb 22, 2024Silver Contributor
... without Automate on the board (very frustrating). Thanks again for sharing, very educative even if I can't play with it. Let's see if I understood... could line 12 be something like?
: console.log('${sheet} has no UsedRange')
and, is : <something> mandatory (otherwise error msg)?
- SergeiBaklanFeb 22, 2024MVP
We may use console.log here, but no sense if the script runs by Power Automate.
You variant returns
${sheet} has no UsedRange
if change on
console.log( sheet.getName() + ' is empty')
it returns something like
Sheet3 is empty
===
Yes, without FALSE condition it returns
Line #13: ':' expected.
However, if instead of shortened "if" to use regular "if" we may skip it
function main(workbook: ExcelScript.Workbook) { workbook .getWorksheets() .forEach(sheet => { if( sheet.getUsedRange() !== undefined ) { sheet .getUsedRange() .getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) } } ) }
- djmitz6Feb 22, 2024Copper Contributor
I guess I could have been a little clearer and included my entire code.
My Goal: To create a Universal function that allows me to take excel workbooks from emails that do not have excel formatted tables (just ranges) and convert them into tables in excel. That way I can use Power Automate to push the tables to my SQL Table. Here was the temporary solution I came up with to solve my first post problem; however, it doesn't feel very efficient to me. I am also super concious about data efficiency as I often hit the Power Automate run script limits with the Excel API.
function main(workbook: ExcelScript.Workbook, WorkSheet_Name: string, DeleteTopRows: number) { let selectedSheet: ExcelScript.Worksheet; if (WorkSheet_Name == "") { selectedSheet = workbook.getWorksheets()[4]; } else { selectedSheet = workbook.getWorksheet(WorkSheet_Name); } if (DeleteTopRows > 0) { //Delete Top N Rows selectedSheet.getRange("1:" + DeleteTopRows).delete(ExcelScript.DeleteShiftDirection.up); } else { } // Clear Any Filters on Selected Sheet selectedSheet.getAutoFilter().remove(); // Get used Range let usedRange = selectedSheet.getUsedRange(); usedRange.copyFrom(usedRange,ExcelScript.RangeCopyType.values,false,false) // Add a new table let newTable = workbook.addTable(usedRange, true); // Replace SQL Reserved Characters selectedSheet.replaceAll("'", "''", { completeMatch: false, matchCase: false }) // Create ID column for Power Automate ODATA Filter newTable.addColumn(-1, Number[0], "ID"); let ID = newTable.getColumnByName("ID").getRangeBetweenHeaderAndTotal(); ID.setNumberFormatLocal("0"); ID.setFormula("=ROUNDUP((ROW()-1)/5000,0)") // Return Table ID to Power Automate return newTable.getId(); }
- SergeiBaklanFeb 22, 2024MVP
Thanks for the sharing.
May I clarify. When you use
usedRange.copyFrom(usedRange, ExcelScript.RangeCopyType.values, false, false)
you copy values from the range, even if they are generated by formulae. Thus initial question is not applicable here since you have no formulae in the resulting table. The question is would you like to keep initial formulae in the resulting table; or convert them into the structured formulae (not sure how to do that); or just values is enough.
- LorenzoFeb 22, 2024Silver Contributor
We may use console.log here, but no sense if the script runs by Power Automate
Sure, that was just to check I understood we were making an IF