Here’s a sample OfficeScript that compares two sheets and highlights the differences in range A1:D100:
function compareSheets() { // Get the active workbook var workbook = context.workbook; // Get the first and second sheets var sheet1 = workbook.getWorksheets().getAt(0); var sheet2 = workbook.getWorksheets().getAt(1); // Get the range A1:D100 in both sheets var range1 = sheet1.getRange("A1:D100"); var range2 = sheet2.getRange("A1:D100"); // Load the values of both ranges var range1Values = range1.load("values"); var range2Values = range2.load("values"); return context.sync() .then(function() { // Iterate over each cell in the ranges and compare values for (var i = 0; i < range1Values.length; i++) { for (var j = 0; j < range1Values[i].length; j++) { var cellValue1 = range1Values[i][j]; var cellValue2 = range2Values[i][j]; // Compare the cell values if (cellValue1 !== cellValue2) { // Highlight the cells that differ range1.getCell(i, j).format.fill.color = "yellow"; range2.getCell(i, j).format.fill.color = "yellow"; } } } }) .then(context.sync); }
In this example, the compareSheets function performs the following steps:
It gets the active workbook using the
context.workbookproperty.It retrieves the first and second sheets from the workbook using the
getWorksheets().getAt()method. Adjust the index as needed.It defines the range A1:D100 in both sheets using the
getRange()method.It loads the values of both ranges using the
load("values")method.It synchronizes the context to ensure the values are retrieved.
It iterates over each cell in the ranges, compares the values, and highlights the cells that differ.
Finally, it synchronizes the context again to apply the formatting changes.
You can call the compareSheets function to compare the two sheets and highlight any differences in the range A1:D100. The cells with different values will be highlighted in yellow.
Please note that this example assumes you have an active workbook with at least two sheets, and the code compares the values cell by cell. Adjust the range and other parameters as needed for your specific use case.
Additionally, remember to set up the necessary context and load the OfficeScript runtime to ensure the script executes successfully.