writeExcel Logic Documentation

Overview

The writeExcel logic is designed to convert tabular data (an array of objects) into a formatted Excel workbook (.xlsx file). This functionality includes styling headers, adjusting column widths automatically, and ensuring that data types are properly represented. The generated workbook can be saved directly to OneDrive, making it suitable for seamless integration with cloud-based workflows.

Purpose

The primary purpose of the writeExcel function is to facilitate data export in a widely-used spreadsheet format. This allows users to download and manipulate data in Excel, making it easier to analyze, report, and share insights generated from the analytics and data platform, Vantage.

Settings

The configuration options for the writeExcel logic include the following settings:

1. fileName

• Input Type: String
• Description: The name of the generated Excel file. Adding a timestamp ensures that files do not overwrite one another. If the file name includes an extension (like .xlsx), the timestamp will be added before the extension.
• Default Value: export.xlsx

2. subfolder

• Input Type: String (optional)
• Description: The name of a subfolder within OneDrive where the file will be saved. If the specified subfolder doesn't exist, it will be created.
• Default Value: Not set (the file will be saved in the root directory of OneDrive)

3. mode

• Input Type: Dropdown ('auto' | 'create' | 'overwrite')
• Description: Determines how the file will be saved if a file with the same name already exists in the specified location.
  • 'auto': Automatically generates a new file name if a conflict exists.
  • 'create': Attempts to create a new file; will fail if a file with the same name exists.
  • 'overwrite': Replaces the existing file with the same name.
• Default Value: auto

4. sheetName

• Input Type: String (optional)
• Description: Sets the name of the worksheet within the Excel file. If not set, the default sheet name will be used.
• Default Value: Data

Inputs

The writeExcel function expects the following input data:

• input1: An array of objects, where each object represents a row in the Excel sheet. The keys of the objects will become the column headers.

How It Works

1. Data Preparation: The function begins by capturing the input data. It checks if the provided data is an array, and if it is nested, it attempts to unwrap it.
2. File Naming: The function generates a unique file name by appending a timestamp to the specified fileName.
3. OneDrive Integration: It ensures that OneDrive is connected and attempts to create or find a specified subfolder.
4. Excel Workbook Generation: Using the ExcelJS library, an Excel workbook is created:
   • Headers are styled and set up.
   • Data is added to the worksheet.
   • The workbook is formatted to improve readability (e.g., auto-fit columns, alternating row colors, frozen headers).
5. File Saving: The completed workbook is saved to OneDrive in the specified location, with the provided mode determining the file handling method.
6. Output: The function returns metadata about the saved file, including its ID, name, URL, and the number of rows written.

AI Integrations

The writeExcel function does not include direct AI integrations. However, it can be part of a larger workflow where AI-generated insights are exported for analysis, reporting, or presentation.

Billing Impacts

While writeExcel does not involve direct costs, usage of OneDrive may incur costs based on the OneDrive storage plan and API transaction rates set by Microsoft. Frequent file writes and large datasets may impact performance and resource utilization, which could indirectly affect billing based on the nature of the subscriptions in use within the Vantage platform.

Use Cases & Examples

Use Cases

1. Reporting Automation: A marketing team needs to export campaign performance data weekly into Excel for further analysis and reporting using pivot tables and charts.
2. Data Validation: A data science team wants to export cleaned and transformed datasets into Excel for validation and sharing with business stakeholders.
3. Inventory Management: A retail manager needs to generate an inventory report in Excel for monthly inventory reviews and forecasting.

Example Configuration

Use Case Scenario

Reporting Automation: A company’s marketing team wants to export campaign performance analytics.

Specific Configuration

{
  "inputs": {
    "input1": [
      {"Campaign": "Spring Sale", "Clicks": 1500, "Conversions": 300},
      {"Campaign": "Winter Sale", "Clicks": 1200, "Conversions": 220}
    ]
  },
  "config": {
    "fileName": "Campaign_Report.xlsx",
    "subfolder": "Marketing_Reports",
    "mode": "auto",
    "sheetName": "2023 Campaign Performance"
  }
}

In this configuration, the scheduled logic generates a file named Campaign_Report_20231017_123456.xlsx (where 123456 is the current timestamp) stored in the "Marketing_Reports" subfolder of OneDrive. If a file with the same base name exists, it would auto-generate a new name to prevent overwriting, ensuring continuity in report generation.