Skip to main content
PDF4me APIIntegrationsKnowledge BaseExtensionsAPI TesterBlog

Add Rows to Excel in Power Automate

PDF4me Add Rows to Excel action enables dynamic insertion of JSON data as rows into Excel documents in Power Automate with intelligent formatting and type conversion. This powerful data insertion feature supports two distinct modes: table-based insertion (with automatic header matching and table structure maintenance) and coordinate-based insertion (with precise positioning control), plus automatic conversion of strings to numbers/dates, culture-specific parsing, customizable date/numeric formatting, and null value handling, perfect for automated data population, report generation, and database-to-Excel 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 data insertion services.

Get your API Key
Add Rows to Excel Power Automate

Key Features​

  • Dual Insertion Modes: Table-based (with header matching) or coordinate-based (exact positioning)
  • JSON Data Support: Insert single objects or arrays of objects as Excel rows
  • Automatic Type Conversion: Convert JSON strings to Excel numbers and dates automatically
  • Format Customization: Apply custom date and numeric formatting patterns
  • Culture Support: Parse dates and numbers according to specified culture codes
  • Null Value Handling: Option to skip null values or insert as empty cells

Parameters​

Complete list of parameters for the Add Rows to Excel action. Configure these parameters to control data insertion.

Important: Parameters marked with an asterisk (***) are required. Some parameters are conditional based on the insertion mode.

Insertion Modes​

The action operates in two modes based on the TableName parameter:

  • Table-Based Insertion: When TableName is provided (non-empty) - Matches JSON properties to table headers, uses ExcelRowNumber for position, maintains table structure
  • Coordinate-Based Insertion: When TableName is empty or null - Inserts at exact row/column coordinates using InsertFromRow and InsertFromColumn, no header matching
ParameterTypeDescriptionExample
File Content***Base64Source 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***StringExcel 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
Json Input***StringJSON Data to Insert
• JSON data as string (array of objects or single object)
• Array Format: [{"Name":"John","Age":30},{"Name":"Jane","Age":28}] - Inserts multiple rows
• Single Object Format: {"Name":"John","Age":30} - Inserts single row
• Property names match table headers (table mode) or determine order (coordinate mode)		[{"Name":"John","Age":30}]
Worksheet NameStringTarget Worksheet Name
• Name of worksheet to insert data into
• Default: "Sheet1"
• Empty or null defaults to first worksheet
• Must match worksheet name exactly		Sheet1
Table NameStringExcel Table Name
• Name of Excel table for table-based insertion
• Empty = coordinate-based mode
• Non-empty = table-based mode
• Switches insertion mode behavior		SalesTable
Excel Row NumberNumber (int)Table Position (Table Mode)
• 1-based position within table (table mode only)
• 1 = first data row after headers
• Must be ≥ 1 and within table bounds
• Ignored in coordinate mode		5
Insert From RowNumber (int)Start Row (Coordinate Mode)
• 1-based row number where insertion starts
• Coordinate mode only - required and must be ≥ 1
• Error if used with TableName
• First row in worksheet = 1		10
Insert From ColumnNumber (int)Start Column (Coordinate Mode)
• 1-based column number where insertion starts
• Coordinate mode only - required and must be ≥ 1
• Error if used with TableName
• First column (A) = 1, B = 2, etc.		3
Convert Numeric And DateBooleanAutomatic Type Conversion
• True - Converts JSON numbers to Excel numeric values, date-like strings to DateTime values (applies DateFormat and NumericFormat)
• False - Insert all values as-is without type conversion or formatting
• Default: true
• Data Type Handling: Numbers → Numeric with NumericFormat; Date-like strings → DateTime with DateFormat; Other strings → Text; Boolean → Boolean; Null → Empty (if not ignored); Objects/Arrays → String (JSON)		true
Date FormatStringExcel Date Format
• Excel date format pattern to apply
• Examples: "yyyy-MM-dd", "MM/dd/yyyy", "dd-MMM-yyyy"
• Default: "yyyy-MM-dd"
• Used when ConvertNumericAndDate = true		MM/dd/yyyy
Numeric FormatStringExcel Numeric Format
• Excel numeric format pattern to apply
• Examples: "N2", "#,##0.00", "0.00%"
• Default: "N2"
• Used when ConvertNumericAndDate = true		#,##0.00
Ignore Null ValuesBooleanNull Value Handling
• True - Skip null values (don't insert into cells)
• False - Insert null as empty cells
• Default: false
• Affects how JSON nulls are processed		false
Ignore Attribute TitlesBooleanCase-Insensitive Header Matching
• True - Case-insensitive matching of JSON properties to headers
• False - Case-sensitive matching
• Default: false
• Useful for flexible JSON property naming		true
Culture NameStringCulture for Date/Number Parsing
• Culture code for parsing (e.g., "en-US", "de-DE", "fr-FR")
• Default: "en-US"
• Affects date and number interpretation
• Important for international data		en-US

Output​

The PDF4me Add Rows to Excel action returns comprehensive output data for seamless Power Automate flow integration:

Table View

Response data in a structured table format:

ParameterTypeDescription
documentBase64Excel document with new rows inserted
SuccessBooleantrue if operation successful, false if failed
Error MessageStringError description (null if successful)
ErrorsArrayList of detailed error information (empty array if successful)

Common Error Messages​

Understanding and troubleshooting errors helps ensure smooth Excel data insertion workflows:

Error MessageCauseSolution
"Request is empty"Request object is nullProvide valid request object
"Document is empty"Document object is nullProvide valid Excel document
"JSON input is required"JsonInput is null or emptyProvide valid JSON data string
"Invalid JSON structure"Malformed JSON syntaxFix JSON formatting and syntax
"Worksheet '{name}' not found"Invalid worksheet name specifiedUse existing worksheet name
"Table '{name}' not found"Invalid table name in table modeUse existing table name
"ExcelRowNumber must be greater than 0 for table-based insertion"ExcelRowNumber < 1 in table modeSet ExcelRowNumber ≥ 1
"InsertFromRow must be greater than 0 for coordinate-based insertion"InsertFromRow < 1 in coordinate modeSet InsertFromRow ≥ 1
"InsertFromColumn must be greater than 0 for coordinate-based insertion"InsertFromColumn < 1 in coordinate modeSet InsertFromColumn ≥ 1
"InsertFromRow and InsertFromColumn are ignored when TableName is specified"Using coordinate parameters in table modeSet both to 0 or omit when using TableName
"ExcelRowNumber X is beyond table bounds..."Position exceeds table sizeUse valid position within table range
"No valid data found in JSON input"Empty array or no parseable dataProvide valid JSON data with at least one object

Workflow Examples​

The PDF4me Add Rows to Excel action in Power Automate provides comprehensive workflow templates designed for real-world business scenarios:

Automated Daily Sales Data Import Workflow

Transform your sales reporting with automated data insertion into Excel tables:

Complete Workflow Steps:

  1. Trigger: Scheduled daily trigger at 11 PM to collect sales data
  2. Get Sales Data: Execute SQL query to fetch today's sales from database
  3. Convert to JSON: Transform SQL results to JSON array format
  4. Get Excel Template: Retrieve sales report template from SharePoint
  5. Add Rows (Table Mode): Insert JSON data into "DailySales" table
  6. Set Parameters: TableName = "DailySales", ExcelRowNumber = 1
  7. Enable Conversion: ConvertNumericAndDate = true for proper formatting
  8. Apply Formats: DateFormat = "MM/dd/yyyy", NumericFormat = "#,##0.00"
  9. Email Report: Send updated Excel to sales managers
  10. Archive: Store in daily reports SharePoint library

Business Benefits:

  • Automates insertion of 200+ daily sales records into Excel
  • Eliminates manual data entry saving 2 hours daily
  • Ensures consistent date and currency formatting
  • Maintains Excel table structure and formulas automatically

Industry Use Cases & Applications​

Sales & Marketing Use Cases

  • Sales Pipeline Reports: Insert CRM opportunity data into Excel sales pipelines
  • Lead Tracking: Add new leads from forms to Excel tracking sheets
  • Campaign Analytics: Populate marketing campaign results from analytics APIs
  • Customer Lists: Export customer data from databases to Excel customer lists

Get Help​

PDF4me ConnectGet your API KeyPower Automate Documentation