Skip to content
Nappai Documentation

Excel

The Excel component allows you to automate tasks involving Excel workbooks stored in the cloud. You can use this component to read information from sheets, write new data, manage structured tables, and handle charts within your Nappai workflows. It securely connects to your Microsoft 365 environment to access OneDrive and SharePoint files, making it easy to work with your data without leaving the automation system.

How it Works

This component communicates with Excel files using Microsoft’s secure cloud services. When you configure it, you specify which workbook you want to work with and what actions to perform. The component handles the connection to OneDrive or SharePoint, navigates to the correct files and sheets, and performs the requested operations. It then returns the results, which you can use in subsequent steps of your workflow.

Connection & Credentials

This component requires configuring a credential in the Nappai panel before interacting with the external service:

  1. Go to the Credentials section in your Nappai panel.
  2. Create a new credential of the type Microsoft Excel and fill in the required fields (Client ID, Client Secret, Tenant ID, and Microsoft Excel Scope).
  3. In your workflow, select the saved credential in the Credential input field of this node.

Inputs

Mapping Mode

This component has a special mode called “Mapping Mode”. When you enable this mode using the toggle switch, an additional input called “Mapping Data” is activated, and each input field offers you three different ways to provide data:

  • Fixed: You type the value directly into the field.
  • Mapped: You connect the output of another component to use its result as the value.
  • Javascript: You write Javascript code to dynamically calculate the value.

This flexibility allows you to create more dynamic and connected workflows.

Input Fields

  • Operation: Defines the operation mode for the component.
  • Mode: Defines whether data is entered manually or selected from proposed options.
  • Drive ID: ID of the OneDrive or SharePoint drive. If not provided, the default drive will be used.
  • Select Folder Path: Selects the path to the folder containing the Excel file.
  • Folder Path: Path to the folder containing Excel files. Leave empty to use the root folder.
  • Workbook ID: Unique ID of the Excel workbook.
  • Select Workbook: Selects a workbook from the default OneDrive.
  • Worksheet Name: Name of the worksheet to access.
  • Worksheet ID: Unique ID of the worksheet.
  • Worksheet: Selects a worksheet from an Excel document in OneDrive or SharePoint.
  • Range Address: The range of cells to read or write, such as A1:C10.
  • Range Values: The data values to update in the selected range.
  • Table Name: Name of the structured table in the Excel file.
  • Table ID: Unique ID of the Excel table.
  • Table: Selects one of the existing tables in the workbook.
  • Table Range: The range covering the table, such as A1:D10.
  • Table Row Values: Values for a new row in the table, separated by commas.
  • Row Index: The index of the row to delete, starting from 0.
  • Chart Type: The type of chart to create, such as ColumnClustered or Pie.
  • Chart Data Range: The data range used for the chart, such as A1:B10.
  • Shallow Search: Limits the search to the specified folder only, or includes all subfolders recursively.
  • Documents Count: The maximum number of documents to retrieve, with a default of 50.

Outputs

The component returns the result of the action in the Data output. Depending on what you did, this might contain the values read from the Excel file, confirmation that data was updated, or details about a table or chart. You can connect this output to other components to process the information further.

Output Data Example (JSON)json

{ “range_data”: [ [“Product”, “Sales”], [“Widget A”, 1500], [“Widget B”, 2300] ], “success_status”: true }

Connectivity

This component typically connects to downstream components that need data from Excel or trigger actions based on spreadsheet changes. Common connections include:

  • Data Transformers: To clean or modify the data read from Excel.
  • Database Writers: To save Excel data into a database.
  • Email Senders: To include Excel data in notification emails.
  • Logic Components: To make decisions based on values in the workbook.

Usage Example

Scenario: Reading Sales Data from Excel

  1. Add the Excel component to your workflow.
  2. Select your Microsoft Excel credential.
  3. Enter the Drive ID or select the default drive.
  4. Use Select Workbook to choose your sales file.
  5. Use Worksheet to pick the sheet with the data.
  6. Set Range Address to the cells containing the data.
  7. Connect the Data output to an email component to send the sales report.

Tips and Best Practices

  • Use meaningful names for your sheets and tables to make them easier to find.
  • When working with large files, use Shallow Search to improve performance.
  • Ensure your Credential has the necessary permissions to access the workbooks.
  • If you need to insert new rows into a table, use Table Row Values for structured updates.
  • Use the Mapping Mode to dynamically adjust ranges based on previous steps in your workflow.

Security Considerations

  • Keep your Microsoft Excel credentials secure and do not share them publicly.
  • Only grant the minimum required permissions in your credentials to protect your data.
  • Ensure your Microsoft 365 account has access to the OneDrive or SharePoint locations where your workbooks are stored.