MSDN Community

Hi everyone,

I'm new to Office Scripts and want to start automating some repetitive tasks in Excel. Specifically, I need to format a range of cells based on certain criteria and then export the data to a CSV file. Can anyone provide a basic example or point me to some good resources for scripting Excel with Office Scripts?

Thanks in advance!

Welcome to Office Scripts! It's a great tool for automating Excel tasks.

For formatting cells based on criteria, you'll typically use the Range.format object and conditional formatting methods. For exporting to CSV, you can iterate through your data and construct a string with comma-separated values.

Here's a super basic example to get you started with formatting:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Assuming your data is in range A1:C10
    let range = selectedSheet.getRange("A1:C10");

    // Example: Apply a fill color to cells with values greater than 50
    range.getFormat().fill.color = "#FFFF00"; // Yellow

    // You can also apply conditional formatting programmatically
    // For more complex logic, refer to the documentation.
}

For exporting, you'd build a string. Let me know if you'd like an example for that part too!

That's a good starting point, ExcelWizard! To add to that, you can also use conditional formatting rules. Here’s how you might set up a rule to highlight cells in a range if their value is above a certain threshold:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let range = selectedSheet.getRange("A1:C10");

    // Add a rule to highlight cells with values > 75
    let conditionalFormat = range.addConditionalFormat(
        ExcelScript.ConditionalFormatType.cellIs
    );
    conditionalFormat.getCellIs().operator = ExcelScript.ConditionalCellValueOperator.greaterThan;
    conditionalFormat.getCellIs().formula = "75";
    conditionalFormat.getFormat().fill.color = "#FFC0CB"; // Pink
    conditionalFormat.getFormat().font.bold = true;
}

Regarding CSV export, Office Scripts doesn't have a direct "export to CSV" function. You'll need to read the data and construct the CSV string yourself. You can get the values using range.getValues(), then join them with commas and newlines.