Forum Discussion
Find and Replace within a known range using typescript in the Script Editor
Hi, All!
I have a script within excel that returns csv data as json for use in a Power Automate flow. After a couple of tests, I discovered that one of the columns includes line breaks for multiple assigned tags from the source platform. Because this script is part of a larger automation, I want to be able to remove the line breaks and use a comma delimiter in the cell instead without manually finding and replacing the line breaks. So I have created a second script that I plan to splice into the main script once I have the working code. I have tried a couple of things so far and the following snippet is the closet I've gotten.
function main(workbook: ExcelScript.Workbook) {
// Your code here
let sheet = workbook.getActiveWorksheet();
let breakCol = sheet.getRange("O2:O25");
let cellToOverwrite = breakCol.find("\n", { completeMatch: false });
while (cellToOverwrite) {
cellToOverwrite.setValue(",");
cellToOverwrite = breakCol.find("no change", { completeMatch: false });
}
}
I realize that I will need to add an iterator to account for the number of records, but where I am stuck at is figuring out which methods are most appropriate for traditional find and replace. Or if the ones here are what I'd need, how should I format them to arrive at my goal?
Any insight would be greatly appreciated!
Entries before script:
Entries after script(ran 5 times to simulate result once iterator is added):
Hi Jrolle2022
I'm not a JavaScript dev. and don't have access to TypeScript with my 365 subscription so can't test the following that's only my understanding after reading the doc.
The https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts has method https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#excelscript-excelscript-range-replaceall-member(1) and after looking at the examples given for the https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.replacecriteria the following should work:
function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); let breakCol = sheet.getRange("O2:O25"); breakCol.replaceAll("\n", ",", { completeMatch: false }); }or, given that completeMatch defaults to false:
function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); let breakCol = sheet.getRange("O2:O25"); breakCol.replaceAll("\n", ","); }Hope this helps a bit & your feedback will be appreciated, whatever it is
(It's my understanding that TypeScript issues should be raised at https://stackoverflow.com/questions/tagged/typescript)
3 Replies
Hi Jrolle2022
I'm not a JavaScript dev. and don't have access to TypeScript with my 365 subscription so can't test the following that's only my understanding after reading the doc.
The https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts has method https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#excelscript-excelscript-range-replaceall-member(1) and after looking at the examples given for the https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.replacecriteria the following should work:
function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); let breakCol = sheet.getRange("O2:O25"); breakCol.replaceAll("\n", ",", { completeMatch: false }); }or, given that completeMatch defaults to false:
function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); let breakCol = sheet.getRange("O2:O25"); breakCol.replaceAll("\n", ","); }Hope this helps a bit & your feedback will be appreciated, whatever it is
(It's my understanding that TypeScript issues should be raised at https://stackoverflow.com/questions/tagged/typescript)
Lorenzo You are amazing! That was just what I needed. I figured the "find" method wasn't going to cut it, but really wasn't aware of a programmatic alternative. Thanks a ton for your help!
Glad this worked as per the doc. Thanks for providing feedback & marking the solution (can help those who search this site)
Nice day...
