Microsoft Excel

1. Overview

Microsoft Excel is a powerful spreadsheet application developed by Microsoft, widely used for organizing, analyzing, and visualizing data. It is a core component of the Microsoft 365 suite of productivity software.

With the GoInsight Microsoft Excel node, you can seamlessly integrate spreadsheet automation into your workflows. This allows you to manage the entire lifecycle of your Excel files stored in OneDrive, from creation to detailed data manipulation. Key capabilities include:

• Workbook Management: Create, list, delete, and populate Excel workbook files in your OneDrive.
• Worksheet Operations: Add, delete, list, and clear worksheets within a specific workbook.
• Data Handling: Read data from sheets and tables, append new data to sheets, and update existing cell ranges.
• Table Management: Create and delete tables within a worksheet, manage their columns and rows, and convert them back to standard ranges.

2. Prerequisites

Before using this node, you need to have a valid Microsoft account with a subscription that includes OneDrive access (e.g., Microsoft 365). You may also need appropriate permissions within your Microsoft organization to grant API access to GoInsight for managing files.

3. Credentials

For a detailed guide on how to obtain and configure your credentials, please refer to our official documentation: Credentials Configuration Guide.

4. Supported Operations

Summary

This node primarily operates on the following core resources within Microsoft Excel: Workbook, Worksheet, Table, Row, and Column.

Operation Details

Create a Workbook

Create a new Excel workbook in user's OneDrive using Microsoft Graph API. Requires file name parameter. Returns new workbook metadata including ID, name, and web URL.

Input Parameters:

• FileName: Name for the new Excel workbook. The .xlsx extension will be added automatically if not provided. Example: 'quarterly_report' will create 'quarterly_report.xlsx'. File name should not exceed 255 characters and cannot contain special characters: \ / : * ? " < > |

Output:

• Workbook (object): The newly created workbook metadata object. Contains fields like id, name, webUrl, createdDateTime, lastModifiedDateTime, size, and createdBy. Empty object {} if creation failed.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API. Default 0 means the request did not reach upstream. Common values: 200/201=Success, 401=Token expired, 409=File already exists, 429=Rate limited, 5xx=Server error.
• StatusCode (number): Operation status code: -1=Parameter validation error, 200=Operation completed, 500=System error.
• ErrorMessage (string): Detailed error message if any error occurred. Empty string if the operation succeeded.

Get Workbooks

Retrieve a list of Excel workbooks from the user's OneDrive using Microsoft Graph API. This operation fetches all .xlsx files with their IDs, names, and web URLs. Requires a valid access token with delegated permissions. Returns array of workbook objects containing id, name, and webUrl fields along with status code and error message.

Options:

• Limit: Maximum number of workbooks to return. Default: 100. Set to 0 to retrieve all workbooks without limit. Example: 10 (returns maximum 10 workbooks). IMPORTANT: This action only returns Excel files (.xlsx and .xls formats).

Output:

• Workbooks (object-array): Array of Excel workbook objects found in user's OneDrive. Each object contains id, name, webUrl, createdDateTime, lastModifiedDateTime, size, and createdBy.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code: 200=Success, -1=Parameter validation error, 500=System error.
• ErrorMessage (string): Detailed error message if any error occurred. Empty string if the operation succeeded.

Write Data to Excel Workbook

Locate an Excel workbook in the OneDrive root by file name, create it if missing, ensure the target worksheet exists, and then append or write data. Use when you know the workbook file name but not its ID.

Input Parameters:

• FileName: Workbook file name to locate in the OneDrive root folder. If no matching workbook exists, a new .xlsx workbook is created with this name. Example: quarterly_report.
• SheetName: Worksheet name to locate in the workbook. If the worksheet does not exist, a new one is created before data is appended. Example: Sheet1 or Orders.
• Data: Data to append into the worksheet. Supports JSON 2D array or CSV text. The workflow first tries to parse JSON; if it fails, it falls back to CSV.

Options:

• RangeAddress: Optional Excel range address. Leave empty to append to the next available row automatically. Provide a range like A1:C3 only when you intentionally want to overwrite that range.

Output:

• Workbook (object): Workbook metadata returned by Microsoft Graph.
• WorkbookId (string): The drive item ID of the workbook that received the appended data.
• WorkbookCreated (bool): Whether a new workbook had to be created because no matching workbook name was found.
• SheetCreated (bool): Whether a new worksheet had to be created before appending data.
• Success (bool): Whether the final append operation succeeded.
• OriginalStatusCode (number): The original HTTP status code returned by the last Microsoft Graph API call.
• StatusCode (number): Overall execution status code.
• ErrorMessage (string): Detailed error message. Empty string means the workbook and worksheet were ready and the append succeeded.

Delete Workbook

Delete a specified Excel workbook from user's OneDrive using Microsoft Graph API. Requires user ID and workbook ID parameters. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: DESTRUCTIVE OPERATION WARNING: This action permanently deletes the ENTIRE workbook file from OneDrive/SharePoint. The unique identifier (drive item ID) of the Excel workbook to delete.

Output:

• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API. Values: 204 (successfully deleted), 401 (authentication failed), 404 (workbook not found), 429 (rate limited), 400 (invalid request).
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation (workbook permanently deleted and moved to OneDrive Recycle Bin for 30 days).

Add a Sheet to a Workbook

Add a new worksheet to a specified Excel workbook using Microsoft Graph API. Requires workbook ID, and sheet name parameters. Returns new sheet object with id, name, and position along with status code and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint - think of it as the file's 'passport number' in Microsoft's cloud storage.
• SheetName: The name for the new worksheet to add to the workbook. Important: Must be unique within the workbook.

Output:

• NewSheet (object): The newly created worksheet metadata object. Empty object ({}) if operation failed.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation.

Get Sheets

Retrieve sheet list from an Excel workbook using Microsoft Graph API. Requires workbook ID parameter. Returns sheet data array with id, name and position, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.

Output:

• Sheets (object-array): Array of worksheet objects found in the workbook. Each object contains id, name, position, and visibility.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Copy Sheet to Workbook

Copy an entire worksheet (including formulas, formatting, and cell styles) from one Excel workbook to another using Microsoft Graph API. Supports renaming the copied sheet and optionally preserving cell formatting.

Input Parameters:

• SourceWorkbookId: The unique identifier (drive item ID) of the source Excel workbook in OneDrive/SharePoint.
• SourceSheetName: Name of the worksheet to copy from the source workbook (case-sensitive).
• TargetWorkbookId: The unique identifier (drive item ID) of the destination Excel workbook where the sheet will be copied to. Must be different from source workbook.

Options:

• NewSheetName: Name for the new sheet in the target workbook. If left empty, will use the source sheet name.
• IncludeFormulas: Whether to copy cell formulas and number formatting along with data. Default is true.
• CopyRange: Specify the range of cells to copy from the source sheet. If left empty, copies the entire used range.

Output:

• CopiedSheet (object): Object containing details of the successfully copied sheet. Empty object {} if operation failed.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation.

Update Sheet

Update properties of an Excel worksheet using Microsoft Graph API. Supports updating sheet name and visibility. Requires workbook ID, and sheet name parameters. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet to update. Important: Sheet names are case-sensitive and must match an existing sheet in the workbook.
• Data: The data to update in the specified range. Supports JSON Array String or CSV Text.
• RangeAddress: Excel cell range (cell position) where data will be written. This parameter is REQUIRED for Update Sheet operation.

Output:

• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation.

Append Data to Sheet

Append data to a specified worksheet in an Excel workbook using Microsoft Graph API. Requires workbook ID, sheet name, and data parameters. Supports JSON or CSV format data. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique drive item ID of the Excel workbook.
• SheetName: The name of the worksheet to append data to. Must match an existing sheet in the workbook (case-sensitive).
• Data: The data to append to the sheet. Supports JSON Array or CSV Text.

Options:

• RangeAddress: Optional Excel Cell Range Address. Leave empty to append to the next available row automatically.

Output:

• Success (bool): Boolean flag indicating whether data was successfully written to the worksheet.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Append or Update Sheet

Append or update row data in a worksheet using Microsoft Graph API. Requires workbook ID, sheet name, data, and range address parameters. Supports JSON or CSV format data. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier of the Excel file in OneDrive/SharePoint (also called drive item ID).
• SheetName: The name of the worksheet to append/update. Must match an existing sheet in the workbook (case-sensitive).
• Data: The data to append or update. Supports JSON Array or CSV Text.

Options:

• Range: Optional Excel Cell Range Address. Leave empty to enable upsert mode using column A as primary key.

Output:

• Success (bool): Boolean flag indicating whether data was successfully appended/updated.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Clear a Sheet

Clear content from a specified worksheet in an Excel workbook using Microsoft Graph API. Requires workbook ID and sheet name parameters. Optionally accepts range address (defaults to usedRange). Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet to clear. This is the sheet tab name visible at the bottom of the Excel file.

Options:

• Range: Excel range address to clear specific cells (optional). Leave empty or use empty string "" to clear all data in the worksheet.

Output:

• Success (bool): Boolean flag indicating whether the clear operation was successful.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Delete Sheet

Delete a specified worksheet from an Excel workbook using Microsoft Graph API. Requires workbook ID, and sheet name parameters. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet to delete. Critical Warning: This action permanently deletes the sheet and cannot be undone.

Output:

• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful deletion.

Create a Table

Create a new table in a specified worksheet using Microsoft Graph API. Requires workbook ID, sheet name, range address, and hasHeaders flag. Returns new table metadata, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet where the table will be created. Sheet names are case-sensitive.
• TableName: The name for the new table. Must be unique within the worksheet and cannot exceed 255 characters.

Options:

• RangeAddress: The Excel range address containing the table data (optional). Leave empty to auto-detect the used range.
• HasHeaders: Whether the first row of the table contains headers (column names). Default is true.

Output:

• NewTable (object): Object containing details of the newly created table.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Append Rows to Table

Append rows to a specified table in an Excel workbook using Microsoft Graph API. Requires workbook ID, sheet name, table name, and row data parameters. Supports JSON or CSV format data. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique drive item ID of the Excel workbook containing the table.
• SheetName: The name of the worksheet containing the table. Sheet names are case-sensitive.
• TableName: The name or ID of the table to append rows to.
• Data: The row data to append. Supports JSON Array or CSV Text.

Output:

• Success (bool): Boolean flag indicating whether rows were successfully appended to the table.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Convert to Range

Convert a table to a normal cell range in an Excel workbook using Microsoft Graph API. Requires workbook ID, sheet name, and table name parameters. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook containing the table in OneDrive/SharePoint.
• SheetName: The name of the worksheet containing the table. DESTRUCTIVE OPERATION WARNING: This action permanently converts the table to a regular cell range.
• TableName: The name or ID of the table to convert to a range.

Output:

• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation.

Delete a Table

Delete a specified table from an Excel workbook using Microsoft Graph API. Requires workbook ID, sheet name, and table name parameters. Returns success status, status code, and error message.

Input Parameters:

• WorkbookId: The unique drive item ID of the Excel workbook containing the table.
• SheetName: The name of the worksheet containing the table. WARNING: This action will permanently delete the entire table structure.
• TableName: The name or ID of the table to delete. CRITICAL: This will permanently delete the entire table.

Output:

• Success (bool): Boolean flag indicating whether the table was successfully deleted.
• OriginalStatusCode (number): The original HTTP status code returned by the upstream API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Get Rows

Retrieve row data from a specified table in an Excel workbook using Microsoft Graph API. Requires workbook ID, sheet name, and table name parameters. Returns row data array, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook containing the table.
• SheetName: The name of the worksheet containing the table.
• TableName: The name or ID of the table to retrieve rows from. Table names are unique within a worksheet.

Options:

• Limit: Maximum number of rows to return (optional). Used for pagination. Default is empty (returns all rows).
• Offset: Number of rows to skip from the beginning (optional, default 0). Used for pagination.

Output:

• Rows (object-array): Array of row objects retrieved from the table (excluding header row).
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Get Rows from Sheet

Retrieve row data from a specified worksheet in an Excel workbook using Microsoft Graph API. Requires workbook ID, and sheet name parameters. Returns row data array with row numbers and values, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet within the workbook.

Options:

• Limit: Maximum number of rows to return. Range: 1-10000. Default: 1000.
• RangeAddress: Excel range address to retrieve specific cell ranges (optional).

Output:

• Rows (object-array): Array of row objects from the worksheet. Each row object contains rowNumber and values.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Get Rows from Sheet with Range

Gets the range object specified by the address or name.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet within the workbook.
• RangeName: Excel range address to retrieve specific cells (required for this action).

Output:

• Rows (object-array): Array of row objects from the specified range.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code.
• ErrorMessage (string): Detailed error message if any error occurred.

Get Columns

Retrieve column data from a specified table in an Excel workbook using Microsoft Graph API. Requires workbook ID, sheet name, and table name parameters. Returns column data array, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet containing the table.
• TableName: The name or ID of the table to retrieve column information from.

Options:

• Limit: Maximum number of columns to return (optional). Used for pagination.
• Offset: Number of columns to skip from the beginning (optional, default 0). Used for pagination.

Output:

• Columns (object-array): Array of column objects retrieved from the table, with pagination support.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation.

Lookup a Column

Lookup detailed information about a specific column in an Excel table using Microsoft Graph API. Requires workbook ID, sheet name, table name, and column name parameters. Returns column data object with id, name, index and values, status code, and error message.

Input Parameters:

• WorkbookId: The unique identifier (drive item ID) of the Excel workbook in OneDrive/SharePoint.
• SheetName: The name of the worksheet containing the table.
• TableName: The name or ID of the table containing the column.
• ColumnName: The name or ID of the specific column to retrieve.

Options:

• IncludeValues: Whether to include column data values in the response (optional, default true). Set to false to retrieve ONLY column metadata.

Output:

• Column (object): Object containing detailed information about the looked-up column. Empty object {} if column not found.
• OriginalStatusCode (number): The original HTTP status code returned by Microsoft Graph API.
• StatusCode (number): Operation status code indicating the execution result.
• ErrorMessage (string): Detailed error message. Empty string indicates successful operation.

5. Example Usage

This section will guide you through creating a simple workflow to create a new Excel workbook in your OneDrive.

The workflow will consist of three nodes: Start -> Create a Workbook -> Answer.

1. Add the Tool Node

• In the workflow canvas, click the "+" button to add a new node.
• In the panel that appears, select the "Tools" tab.
• Find and select "Microsoft Excel" from the list of tools.
• In the list of supported operations for Microsoft Excel, click on "Create a Workbook" to add the node to your canvas.

2. Configure the Node

• Click on the newly added "Create a Workbook" node to open its configuration panel on the right.
• Credentials Configuration: At the top of the panel, find the credentials field. Click the dropdown menu and select your pre-configured Microsoft Excel credential.
• Parameter Configuration: Fill in the required input parameters for the action.
  • FileName: Enter a name for your new workbook, for example, Monthly_Report.xlsx.

3. Run and Validate

• Once all required parameters are correctly filled, any error indicators on the workflow canvas will disappear.
• Click the "Run" button in the top-right corner of the canvas to execute the workflow.
• After a successful execution, you can click the log icon in the top-right corner to view the detailed input and output of the node, confirming that the operation was successful.

After completing these steps, your workflow is fully configured. When you run it, a new Excel workbook named "Monthly_Report.xlsx" will be created in the root directory of your OneDrive.

6. FAQs

Q: Why am I getting a 401 Unauthorized or 403 Forbidden error?

A: This typically indicates an issue with your credentials or permissions. Please check the following:

• Correct Credentials: Ensure you have selected the correct Microsoft Excel credential in the node configuration.
• API Permissions: Verify that the permissions granted when you configured your credentials in GoInsight are sufficient. You may need permissions like Files.ReadWrite.All or Sites.ReadWrite.All depending on where the files are stored.
• Token Expiration: Your access token may have expired. Try re-authorizing your credential in the GoInsight credentials section.

Q: What is a WorkbookId and how do I find it?

A: The WorkbookId is the unique identifier for an Excel file stored in OneDrive or SharePoint, also known as the drive item ID. The easiest way to find it within a workflow is to use the Get Workbooks operation. This action will return a list of all your Excel files, each with its corresponding id (which is the WorkbookId), name, and webUrl. You can then pass this ID to other nodes.

Q: What format should the Data parameter be in for appending rows?

A: The Data parameter is flexible and accepts a string in one of two formats:

• JSON Array of Arrays: A JSON-formatted string where each inner array represents a row. Example: [[1, "Product A", 10.50], [2, "Product B", 22.00]]
• CSV Format: A string where values are separated by commas and rows are separated by newline characters (\n). Example: "1,Product A,10.50\n2,Product B,22.00"

7. Official Documentation

For more in-depth information about the underlying API used by this node, please refer to the official Microsoft Graph API documentation for Excel:

Microsoft Excel API Documentation