Forum Discussion

BRANDON1385's avatar
BRANDON1385
Copper Contributor
Mar 27, 2024

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

}

 

 

Office Scripts

7 Replies

  • rachel's avatar
    rachel
    Iron Contributor
    Mar 28, 2024

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

     

     

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 28, 2024

      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.

      • rachel's avatar
        rachel
        Iron Contributor
        Mar 29, 2024
        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.

Resources