Forum Discussion
Excel Script ReplaceAll ignore formulas
Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object
OK, I didn't read enough. Thanks for fixing my bad + sharing the appropriate code
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.
... 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)?
We may use console.log here, but no sense if the script runs by Power Automate.
You variant returns
${sheet} has no UsedRangeif 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("'", "''", {})) } } ) }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(); }
