Extract Worksheet from Excel in Power Automate

PDF4me Extract Worksheet from Excel action enables extraction of entire worksheets from Excel documents as structured JSON data in Power Automate with automatic header detection and flexible selection options. This powerful whole-worksheet extraction feature converts one or more worksheets to JSON format using the first row as column headers, with support for worksheet selection by name or index (or all worksheets by default), perfect for complete data export, multi-sheet processing, API integration, and comprehensive Excel-to-JSON transformation workflows across Microsoft 365.

Authenticating Your API Request

To access the PDF4me Web API through Power Automate, every request must include proper authentication credentials. Authentication ensures secure communication and validates your identity as an authorized user, enabling seamless integration between your Power Automate flows and PDF4me's powerful Excel worksheet extraction services.

Get your API Key

Key Features

• Complete Worksheet Extraction: Export entire worksheets as structured JSON data
• Automatic Header Detection: First row automatically used as column headers
• Flexible Selection: Extract by worksheet names, indexes, or all worksheets
• Multi-Sheet Support: Extract multiple worksheets in a single operation
• Structured JSON Output: Dictionary format with worksheet names as keys
• Data Type Preservation: Maintains numbers, dates, booleans, and text types

Parameters

Complete list of parameters for the Extract Worksheet from Excel action. Configure these parameters to control worksheet extraction.

Important: Parameters marked with an asterisk (***) are required. Worksheet indexes use 0-based numbering (Index 0 = first worksheet).

Parameter	Type	Description	Example
File Content***	Base64	Source Excel File Content • Map Excel file from previous action output • Supports Excel files from SharePoint, OneDrive, email attachments • Can be dynamically retrieved from flow variables • Must be valid Excel document (.xlsx, .xls formats)	[File Content from Get File]
File Name***	String	Excel Document Name • Specify Excel file name with extension (.xlsx, .xls) • Used for processing and output file naming • Must include proper file extension • Supports dynamic naming from flow variables	data.xlsx
Worksheet Names	String	Comma-Separated Worksheet Names • List of worksheet names to extract • Format: "Sheet1,Summary,Report" • Selection Logic: If provided → Extract those named worksheets. If both WorksheetNames and WorksheetIndexes provided → Extract worksheets matching EITHER (union). If NEITHER provided → Extract ALL worksheets • Case-sensitive worksheet names	Employees,Sales
Worksheet Indexes	String	Comma-Separated Indexes (0-based) • List of worksheet indexes to extract • Format: "0,1,2" (Index 0 = first sheet, Index 1 = second sheet, etc.) • Selection Logic: If provided → Extract those indexed worksheets. If both parameters provided → Extract worksheets matching EITHER (union) • 0-based indexing: Index 0 = first worksheet	0,2,4

Output

The PDF4me Extract Worksheet from Excel action returns comprehensive JSON output data for seamless Power Automate flow integration:

Table

Table View

Response data in a structured table format:

Parameter	Type	Description
document	Base64	JSON data as byte array (saved as .txt file)
FileName	String	Output filename with .txt extension
Success	Boolean	true if operation successful, false if failed
Error Message	String	Error description (null if successful)
Errors	Array	List of detailed error information (empty array if successful)

JSON

JSON Response Format

The raw JSON response from the action:

{
  "document": "[Base64 JSON Data]",
  "FileName": "report.txt",
  "Success": true,
  "ErrorMessage": null,
  "Errors": []
}

JSON Content Example (in document field):

{
  "Employees": [
    {"Name": "John Doe", "Age": 30, "Department": "IT"},
    {"Name": "Jane Smith", "Age": 28, "Department": "HR"}
  ],
  "Sales": [
    {"Product": "Widget", "Units": 100, "Revenue": 2500.00},
    {"Product": "Gadget", "Units": 75, "Revenue": 1875.00}
  ]
}

Error Response Example:

{
  "document": null,
  "FileName": null,
  "Success": false,
  "ErrorMessage": "Invalid worksheet selection",
  "Errors": [
    {
      "Code": "WORKSHEET_ERROR",
      "Message": "Worksheet 'NonExistent' not found. Available worksheets in workbook (3 total)"
    }
  ]
}

Flow Integration

Power Automate Flow Usage

Use extracted JSON data in subsequent actions:

• API Integration: Send entire worksheet data to REST APIs as JSON payload
• Database Import: Bulk import all worksheet data into SQL databases
• Data Archival: Export complete Excel workbooks as JSON for archival
• Multi-Sheet Processing: Process multiple worksheets with different logic
• Backup & Migration: Create JSON backups of Excel data
• Cross-System Sync: Synchronize entire Excel workbooks to other systems

Common Error Messages

Understanding and troubleshooting errors helps ensure smooth Excel worksheet extraction workflows:

Error Message	Cause	Solution
"Request is empty"	Request object is null	Provide valid request object
"Document is empty"	Document object is null or DocData is null	Provide valid Excel document
"Workbook contains no worksheets"	Excel file has 0 worksheets	Provide valid Excel file with at least one worksheet
"Invalid worksheet selection"	Worksheet name doesn't exist or index out of range	Use valid worksheet names or indexes (0-based)
"Available worksheets in workbook (X total)"	Informational message when worksheet selection validation fails	Review worksheet names/indexes and use valid values from X available worksheets

Workflow Examples

The PDF4me Extract Worksheet from Excel action in Power Automate provides comprehensive workflow templates designed for real-world business scenarios:

Multi-Sheet API Feed

Automated Multi-Worksheet to API Integration Workflow

Transform your data integration with complete workbook extraction and API feeding:

Complete Workflow Steps:

1. Trigger: Excel workbook uploaded to SharePoint "Data Export" folder
2. Get Excel File: Retrieve multi-sheet Excel from SharePoint
3. Extract All Worksheets: Leave WorksheetNames and WorksheetIndexes empty
4. Parse JSON: Convert document content to JSON object
5. Loop Through Worksheets: Use Apply to each for worksheet keys
6. Process Each Sheet: Extract worksheet name and data array
7. Call API Per Sheet: HTTP POST each worksheet's data to different API endpoint
8. Log Results: Record successful/failed API calls per worksheet
9. Send Summary: Email admin with processing summary by worksheet

Business Benefits:

• Integrates complete multi-sheet workbooks with APIs automatically
• Processes different worksheets to different API endpoints
• Provides structured JSON format for each worksheet
• Eliminates manual multi-sheet data extraction and API posting

Create Flow

Data Archival

Automated Excel to JSON Archival Workflow

Streamline your data archival with complete workbook to JSON conversion:

Complete Workflow Steps:

1. Trigger: Scheduled monthly archival on last day at 11 PM
2. Get Active Files: Retrieve all completed project Excel files from SharePoint
3. Loop Each File: Apply to each for Excel files
4. Extract All Sheets: Convert entire workbook to JSON
5. Parse JSON: Process extracted worksheet data
6. Add Metadata: Include extraction date and source filename
7. Store JSON: Save JSON to Azure Blob Storage or SharePoint
8. Archive Original: Move original Excel to "Archived" folder
9. Update Index: Add entry to archival index database

Business Benefits:

• Archives 50+ Excel workbooks monthly as JSON automatically
• Preserves all worksheet data in structured JSON format
• Enables long-term storage in cloud-native JSON format
• Reduces storage costs with compressed JSON archives

Create Flow

Selective Export

Automated Selective Worksheet Export Workflow

Optimize your data export with selective worksheet extraction:

Complete Workflow Steps:

1. Trigger: Request for client data export submitted via Microsoft Forms
2. Get Export Request: Retrieve form details (worksheets needed)
3. Get Master File: Retrieve master data Excel from secure SharePoint
4. Extract Specific Sheets: Set WorksheetNames = "Summary,ClientData,Metrics"
5. Parse Extracted JSON: Process only requested worksheets
6. Filter Sensitive Data: Remove confidential columns if needed
7. Create JSON File: Save filtered JSON to temp storage
8. Email Requestor: Send JSON export to authorized user
9. Log Export: Record export details in audit log

Business Benefits:

• Exports only requested worksheets for security and efficiency
• Reduces data transfer size with selective extraction
• Maintains audit trail of worksheet exports
• Automates client data delivery workflows

Create Flow

Database Migration

Automated Excel to Database Migration Workflow

Enhance your data migration with complete workbook to database transfer:

Complete Workflow Steps:

1. Trigger: Excel data source placed in "Migration Queue" folder
2. Get Excel File: Retrieve Excel workbook from queue
3. Extract All Worksheets: Convert entire workbook to JSON
4. Parse JSON Structure: Process worksheet dictionary
5. Map Worksheets to Tables: Match worksheet names to SQL tables
6. Loop Through Worksheets: Process each worksheet separately
7. Bulk Insert to SQL: Insert each worksheet's rows to corresponding table
8. Verify Row Counts: Confirm all rows migrated successfully
9. Archive Source: Move processed Excel to "Migrated" folder
10. Send Report: Email migration summary with row counts

Business Benefits:

• Migrates multi-sheet Excel files to SQL automatically
• Processes 1000+ rows per worksheet efficiently
• Maps worksheets to database tables intelligently
• Reduces manual database migration time by 90%

Create Flow

Industry Use Cases & Applications

IT & System Integration

IT & System Integration Use Cases

• System Migration: Export entire Excel workbooks as JSON for system migrations
• Multi-API Integration: Send different worksheets to different API endpoints
• Data Backup: Create JSON backups of multi-sheet Excel files
• Cloud Migration: Convert Excel workbooks to JSON for cloud storage

Finance & Accounting

Finance & Accounting Use Cases

• Multi-Period Export: Extract all worksheet tabs (monthly periods) to JSON
• GL Migration: Export chart of accounts and transactions from multi-sheet Excel
• Financial Consolidation: Combine data from multiple worksheet tabs
• Audit Exports: Extract complete workbooks for audit review

Sales & Marketing

Sales & Marketing Use Cases

• Multi-Region Data: Extract regional sales data from different worksheet tabs
• Campaign Analytics: Export campaign results from multi-sheet reports
• Lead Database Export: Convert lead tracking worksheets to JSON
• Territory Data: Extract territory-specific data from worksheet tabs

Human Resources

Human Resources Use Cases

• Employee Data Export: Extract all HR worksheets (employees, benefits, payroll)
• Multi-Department Reports: Export department data from separate worksheets
• Payroll Migration: Convert payroll worksheets to JSON for new system
• Benefits Enrollment: Extract enrollment data from multiple worksheet tabs

Operations & Supply Chain

Operations & Supply Chain Use Cases

• Inventory Export: Extract warehouse inventory from multiple worksheet tabs
• Multi-Location Data: Export location-specific data from worksheets
• Supply Chain Migration: Convert supplier and product worksheets to JSON
• Production Reports: Extract production data from multi-shift worksheets

Education & Research

Education & Research Use Cases

• Multi-Course Data: Extract course data from different worksheet tabs
• Student Records Export: Convert student records from multiple worksheets
• Research Data: Export experimental data from multi-tab worksheets
• Grade Books: Extract grade data from multiple class worksheets

Get Help

PDF4me ConnectGet your API KeyPower Automate Documentation