Embrace the future of spreadsheet management with Excel Office Script Automation, the revolutionary feature in Excel for the web. This powerful automation tool is designed to streamline your workflow, allowing you to automate routine tasks with ease and precision. Discover the simplicity and power of Office Script in this detailed guide.
Office Script’s integration with Power Automate significantly expands Excel’s capabilities. Power Automate, part of Microsoft 365, allows users to connect Excel tasks with various applications, enabling automated, multi-step workflows. For instance, businesses can automate data entry from customer emails into Excel, or set up periodic financial report generation, improving accuracy and time efficiency.
Office Script offers greater flexibility than traditional Excel macros and promotes collaboration with in-team script sharing. Office Scripts’ online dependency limits offline functionality unlike embedded sharing common with macros. This modern scripting tool is user-friendly. Users can select between a simple recorder for basic task automation and a code editor for more advanced script customization. The recorder is ideal for automating data entry tasks, while the code editor suits complex operations like dynamic report generation based on variable data sets.
Three real-world business functions that can be automated with Office Script are:
- Automated Data Compilation: Combining data from multiple Excel tables into a single comprehensive table. This is useful for creating unified reports from diverse data sources.
- Dynamic Report Generation: Creating a table of contents with links to each worksheet in a workbook, which can streamline navigation in complex documents.
- Data Validation and Cleanup: Automatically detecting and counting blank rows in sheets, which is crucial for maintaining data accuracy, especially in large datasets.
Microsoft offers a variety of samples and scenarios on its website. These include basics, quick scenarios, cross-application scenarios with Power Automate, and real-world solutions. These resources provide detailed solutions for common Excel tasks and are categorized based on Excel features, offering a practical insight into how Office Scripts can be utilized in various business contexts. You can explore these samples and scenarios on Microsoft’s Office Scripts samples and scenarios page.
An Example of Office Script Automation
This script reformats the output from a QuickBooks file:
function main(workbook: ExcelScript.Workbook) {
// Get the 'From QB' worksheet
let fromQBWorksheet = workbook.getWorksheet('WTB');
let worksheetName = 'QB Formatted'; // Replace with your worksheet name
let worksheet = workbook.getWorksheet(worksheetName);
if (worksheet) {
worksheet.delete();
console.log(`Worksheet '${worksheetName}' has been deleted.`);
}
// Copy the data into a new worksheet called 'QB Formatted'
let qbFormattedWorksheet = workbook.addWorksheet('QB Formatted');
let usedRange = fromQBWorksheet.getUsedRange();
let rangeValues = usedRange.getValues();
qbFormattedWorksheet.getRange("A1").getResizedRange(rangeValues.length - 1, rangeValues[0].length - 1).setValues(rangeValues);
// Delete Rows one and two
qbFormattedWorksheet.getRange("1:2").delete(ExcelScript.DeleteShiftDirection.up);
// Loop through all values in column E, making each value negative if it's a number and copies it to the same row in Column C
let columnE = qbFormattedWorksheet.getUsedRange().getColumn(4);
let columnC = qbFormattedWorksheet.getUsedRange().getColumn(2);
let valuesE = columnE.getValues();
let valuesC = columnC.getValues();
for (let i = 0; i < valuesE.length; i++) {
if (typeof valuesE[i][0] === 'number') {
valuesE[i][0] = -Math.abs(valuesE[i][0]);
valuesC[i][0] = valuesE[i][0];
}
}
columnE.setValues(valuesE);
columnC.setValues(valuesC);
// Delete Columns A, D, E
qbFormattedWorksheet.getRange("A:A").delete(ExcelScript.DeleteShiftDirection.left); // Delete Column A
qbFormattedWorksheet.getRange("D:D").delete(ExcelScript.DeleteShiftDirection.left); // Delete Column D
qbFormattedWorksheet.getRange("E:E").delete(ExcelScript.DeleteShiftDirection.left); // Delete Column E
// Insert a new row at the top
qbFormattedWorksheet.getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);
qbFormattedWorksheet.getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);
// Label Column B 'Unassigned'
qbFormattedWorksheet.getRange("B2").setValue('Unassigned');
qbFormattedWorksheet.getRange("A1:B1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
qbFormattedWorksheet.getRange("A1:B1").getFormat().setIndentLevel(0);
// Set vertical alignment to ExcelScript.VerticalAlignment.bottom for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
qbFormattedWorksheet.getRange("A1:B1").getFormat().setIndentLevel(0);
// Set wrap text to false for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setWrapText(false);
// Set text orientation to 0 for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setTextOrientation(0);
// Indent set to 0 for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setIndentLevel(0);
// Merge range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").merge(false);
// Set format for range B:B on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B:B").setNumberFormatLocal("0.00");
// Set the width of column A
qbFormattedWorksheet.getRange("A:A").getFormat().autofitColumns();
// Set the width of column A
qbFormattedWorksheet.getRange("B:B").getFormat().autofitColumns();
// Set horizontal alignment to ExcelScript.HorizontalAlignment.center for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
qbFormattedWorksheet.getRange("B2").getFormat().setIndentLevel(0);
// Set vertical alignment to ExcelScript.VerticalAlignment.bottom for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
qbFormattedWorksheet.getRange("B2").getFormat().setIndentLevel(0);
// Set wrap text to false for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().setWrapText(false);
// Set text orientation to 0 for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().setTextOrientation(0);
// Indent set to 0 for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().setIndentLevel(0);
// Set font bold to true for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getFont().setBold(true);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalDown).setStyle(ExcelScript.BorderLineStyle.none);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalUp).setStyle(ExcelScript.BorderLineStyle.none);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.none);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.none);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.none);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.none);
// Set border for range B2 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("B2").getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.none);
//qbFormattedWorksheet.getRange("A1").copyFrom(fromQBWorksheet.getRange("C1"), ExcelScript.RangeCopyType.all, false, false);
qbFormattedWorksheet.getRange("A1:B1").copyFrom(fromQBWorksheet.getRange("C1"), ExcelScript.RangeCopyType.all, false, false);
qbFormattedWorksheet.getRange("A1:B1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
qbFormattedWorksheet.getRange("A1:B1").getFormat().setIndentLevel(0);
// Set vertical alignment to ExcelScript.VerticalAlignment.bottom for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
qbFormattedWorksheet.getRange("A1:B1").getFormat().setIndentLevel(0);
// Set wrap text to false for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setWrapText(false);
// Set text orientation to 0 for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setTextOrientation(0);
// Indent set to 0 for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().setIndentLevel(0);
// Merge range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").merge(false);
// Set font bold to true for range A1:B1 on qbFormattedWorksheet
qbFormattedWorksheet.getRange("A1:B1").getFormat().getFont().setBold(true);
}Office Script and Power Automate ushered in a new era for Excel, elevating it from a mere data processing tool to an advanced automation platform. Suitable for both beginners and seasoned users, these tools facilitate seamless, efficient, and accurate data management and task automation, essential in today’s fast-paced business environment.
Explore more about Office Script and Power Automate to revolutionize your data management strategy today!