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, and delete entire 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

This node provides a comprehensive set of operations to interact with your Excel files, primarily centered around three main resources: Workbooks, Sheets, and Tables.

Summary

Operation Details

Create a Workbook

Creates a new Excel workbook in the user's OneDrive using the Microsoft Graph API. It requires a file name and returns the new workbook's metadata, including its ID, name, and web URL.

Input Parameters:

• FileName: The name for the new workbook.

Output:

• Workbook (object): Basic data of the new workbook.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Delete Workbook

Deletes a specified Excel workbook from the user's OneDrive using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID) to delete.

Output:

• Success (bool): The result of the delete operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Get Workbooks

Retrieves a list of Excel workbooks from the user's OneDrive using the Microsoft Graph API. This operation fetches all .xlsx files with their IDs, names, and web URLs.

Output:

• Workbooks (object-array): An array of all workbooks used by the user.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Add a Sheet to a Workbook

Adds a new worksheet to a specified Excel workbook using the Microsoft Graph API. It returns the new sheet object with its ID, name, and position.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the new sheet to add.

Output:

• NewSheet (object): Basic data of the new sheet.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Append Data to Sheet

Appends data to a specified worksheet in an Excel workbook using the Microsoft Graph API. It supports data in JSON or CSV format.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to append data to.
• Data: The data to append, formatted as a JSON string or comma-separated text (rows separated by newlines, e.g., '1,hello\n2,world' or '[[1,"hello"],[2,"world"]]').

Options:

• RangAddress: The range address to append to (optional, e.g., 'A1').

Output:

• Success (bool): The result of the append operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Append or Update Sheet

Appends or updates row data in a worksheet using the Microsoft Graph API. It supports data in JSON or CSV format.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to append/update.
• Data: The data to append/update, formatted as a JSON string or comma-separated text (rows separated by newlines, e.g., '1,hello\n2,world' or '[[1,"hello"],[2,"world"]]').

Options:

• Range: The range address for the update (e.g., 'A1:B2').

Output:

• Success (bool): The result of the append operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Clear a Sheet

Clears content from a specified worksheet in an Excel workbook using the Microsoft Graph API. If no range is specified, it defaults to clearing the entire used range.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to clear.

Options:

• Range: The range to clear (optional, e.g., 'A1:D10'; defaults to the used range).

Output:

• Success (bool): The result of the clear operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Delete Sheet

Deletes a specified worksheet from an Excel workbook using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to delete.

Output:

• Success (bool): The result of the delete operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Get Rows from Sheet

Retrieves row data from a specified worksheet in an Excel workbook using the Microsoft Graph API. It returns an array of row data with row numbers and values.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to get rows from.

Output:

• Rows (object-array): All used rows in the sheet.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Get Sheets

Retrieves the list of sheets from an Excel workbook using the Microsoft Graph API. It returns an array of sheet data including ID, name, and position.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID) to get sheets from.

Output:

• Sheets (object-array): All sheets contained in the workbook.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Update Sheet

Updates the properties of an Excel worksheet using the Microsoft Graph API, allowing you to change its name and visibility.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to update.
• NewName: The new name for the sheet (optional).
• Visibility: The visibility of the sheet (optional, e.g., 'Visible' or 'Hidden').

Output:

• Success (bool): The result of the update operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Append Rows to Table

Appends rows to a specified table in an Excel workbook using the Microsoft Graph API. It supports data in JSON or CSV format.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet containing the table.
• TableName: The name or ID of the table to append rows to.
• Data: The rows to append, formatted as a JSON string or comma-separated text (rows separated by newlines, e.g., '1,hello\n2,world' or '[[1,"hello"],[2,"world"]]').

Output:

• Success (bool): The result of the append operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Convert to Range

Converts a table to a normal cell range in an Excel workbook using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet containing the table.
• TableName: The name or ID of the table to convert to a range.

Output:

• Success (bool): The result of the conversion.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Create a Table

Creates a new table in a specified worksheet using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet to create the table in.
• TableName: The name of the table.
• HasHeaders: A boolean indicating if the data range contains headers.

Options:

• RangeAddress: The range address to use for creating the table (e.g., A1:C3). If empty, the entire used range will be used.

Output:

• NewTable (object): Basic data of the new table.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Delete a Table

Deletes a specified table from an Excel workbook using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet containing the table.
• TableName: The name or ID of the table to delete.

Output:

• Success (bool): The result of the delete operation.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Get Columns

Retrieves column data from a specified table in an Excel workbook using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet containing the table.
• TableName: The name or ID of the table to get columns from.

Output:

• Columns (object-array): All columns contained in the table.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Get Rows

Retrieves row data from a specified table in an Excel workbook using the Microsoft Graph API.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet containing the table.
• TableName: The name or ID of the table to get rows from.

Output:

• Rows (object-array): All rows contained in the table.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

Lookup a Column

Looks up detailed information about a specific column in an Excel table using the Microsoft Graph API. It returns the column data object with its ID, name, index, and values.

Input Parameters:

• WorkbookId: The ID of the workbook (drive item ID).
• SheetName: The name of the sheet containing the table.
• TableName: The name or ID of the table.
• ColumnName: The name or ID of the column to look up.

Output:

• Column (object): The data for the looked-up column.
• StatusCode (number): An HTTP status code indicating the success or failure of the API request.
• ErrorMessage (string): A descriptive error message explaining why the request failed.

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