SCRIPT EXCEL

[BRANDON1385]

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");

    dataWorksheet.activate("BLACK");

b)

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getWorksheet("BLACK");

// ORANGE -worksheet-
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getWorksheet("ORANGE");
// Replace all "A.REY" with "A. REY" on range H:H on selectedSheet
selectedSheet.getRange("H:H").replaceAll("A.REY", "A. REY", { completeMatch: false, matchCase: false });

// switch to "BLACK" -worksheet- I tried a) and b)

let dataWorksheet = workbook.getWorksheet("BLACK");

    dataWorksheet.activate("BLACK");

   // Replace all "A.REY" with "A. REY" on range H:H on selectedSheet

selectedSheet.getRange("H:H").replaceAll("A.REY", "A. REY", { completeMatch: false, matchCase: false });

}

[#2]

@BRANDON1385 

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 });
	});
}

[#3]

@rachel 

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.

[#4]

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.

[#5]

@rachel 

Lot of technologies involved. C#, Rider, Linq, TypeScript and finally Excel. Impressive!

[#6]

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

[#7]

@rachel 

AFE could help a bit, now it's even with kind of debugger. Not an IDE, but compare to formula bar that's a great progress.

[#8]

@Sergei Baklan 

Thanks for letting me know!

It looks nice on my mac: