Actions6
Overview
This node integrates with OneDrive to interact with files shared with the user. Specifically, the 'Query Worksheet' operation allows users to select an Excel file shared with them, pick a worksheet within that file, and extract data from specified columns. It supports caching of downloaded files to optimize performance. This node is useful for automating data extraction from shared Excel worksheets, such as pulling specific columns of data for reporting or further processing in workflows.
Use Case Examples
- Extract sales data columns from a shared Excel worksheet for monthly reporting.
- Query a shared Excel sheet to retrieve project status updates from selected columns.
- Automate data extraction from shared financial spreadsheets by specifying columns of interest.
Properties
| Name | Meaning |
|---|---|
| File Type | Filter shared files by their extension to select the appropriate file type (e.g., Excel, Word, PDF, PowerPoint). |
| File | Select the shared file to query, represented as a JSON string containing driveId and itemId. |
| Sheet Name | Select the worksheet within the chosen Excel file to query. |
| Cache Duration (minutes) | Duration in minutes to reuse the downloaded file before refreshing it from OneDrive. |
| Columns to Extract | Select one or more columns from the worksheet to extract values from. |
Output
JSON
name- Name of the shared file (for 'pick' operation).id- ID of the shared file (for 'pick' operation).
- Extracted values from the specified columns in the worksheet (for 'querySheet' operation).
Dependencies
- Requires OneDrive API access with device flow authentication.
- Needs credentials for OneDrive device flow API including client ID, tenant ID, scopes, and optional WhatsApp API URL for authentication notifications.
Troubleshooting
- Authentication errors may occur if device flow authentication is not completed; check console logs for user code and verification URL.
- File not found errors occur if the selected file ID does not match any shared files; ensure correct file selection.
- Empty or missing worksheet or column names will result in no data extracted; verify worksheet and column selections.
- WhatsApp message sending failures for authentication notifications may occur if WhatsApp API URL or number is misconfigured.
Links
- Microsoft Graph OneDrive API - Official documentation for OneDrive API used to access shared files and worksheets.
- ExcelJS Library - Library used to read and parse Excel files in the node.