Forum Discussion
SCRIPT EXCEL
1. First start with "ORANGE" -worksheet-, but I need switch to "BLACK" -worksheet-, to replace more information. I tried with a) let dataWorksheet = workbook.getWorksheet("BLACK"); dataWor...
Why can't you just loop?
function main(workbook: ExcelScript.Workbook) {
let sheets = workbook.getWorksheets().filter(q => q.getName() === 'ORANGE' || q.getName() === 'BLACK');
sheets.forEach(q => {
q.getUsedRange().replaceAll("A.REY", "A. REY", { completeMatch: false, matchCase: false });
});
}
Nice solution. I'd only not to hardcode sheet names within formula, something like
function main(workbook: ExcelScript.Workbook) {
const names = ["BLACK", "ORANGE"]
workbook.getWorksheets()
.filter( q => names.includes(q.getName()))
.map( w => w.getRange("H:H").getUsedRange()
.replaceAll("A.REY", "A. REY",
{ completeMatch: false, matchCase: false }
)
)
}Also better the range name to move outside if only only we won't apply to entire used range within the sheet.
- Thank you so much! that is much cleaner. When I code in c#, I relied heavily on the intellisense of JetBrain Rider to automatically convert my for loop to linq etc. so when I started to write this script, I felt so good because the intellisense is much better than I expected and colour theme is dark, but then I felt handicapped when I realised it won't automatically help me refactor my code. so I actually ended up spending half of my morning googling the translation from C# Linq to Javascript to write this.
Lot of technologies involved. C#, Rider, Linq, TypeScript and finally Excel. Impressive!
- Most IT professionals can grasp the basics of a new programming language within days...but it just take lots of experience to develop a fine taste in Excel. Probably because there is no shortcut such as intellisense or debugging tools for those excel formulas