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 ...
Forum Discussion
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();
}
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.
- The initial question never had the requirement to maintain the formula in the resultant table. (Maybe I should have been clearer on this.) I just needed the formula result. Since I do not push the formula to the SQL table, I only push it's value.
When your script shall work. You have values only, all single apostrophes in texts will be doubled, and you have nothing but texts and numbers.
- Yes, my script is currently working as desired just looking for a more efficient solution. The problem I was having is if I didn't copy and paste the formula results, the formulas themselves would be come the cell value with additional apostrophes. It would have really been nice if the replaceAll function had an option to ignore formulas.
So, you'd like to
- remove first N rows
- convert the rest into the structured table (keeping formulae in it as is)
- double ' for each cell within the table ignoring ones with formula
Correct?
- You got it!
When it could be
function main(workbook: ExcelScript.Workbook) { // will be used as Power Automate parameters const WorkSheet_Name = "Sheet1 (2)" const DeleteTopRows = 11 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) usedRange.getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) // 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(); }As for the performance I'm not sure. Perhaps your variant will be better. That's only to test.
