In Excel: convert a worksheet to JSON by exporting the range with headers and mapping each data row to one object whose keys are the header cells — or use Power Query (Data ▸ Get Data ▸ From File ▸ From JSON) for the reverse direction.
On this page7
Put field names in row 1, one column per field, with no merged cells.
Upload the .xlsx (or paste CSV) into the converter above and keep "first row is headers" on.
Check the live JSON preview: each data row should be one object.
Download the .json file or copy the output for your API or script.
What this does
JSON stores records as objects with named fields, while a worksheet stores them as rows under header cells. Converting between the two means deciding which row holds the field names and how empty cells should appear (empty string vs. missing key). Excel itself has no one-click "save as JSON"; Power Query imports JSON, but exporting requires a tool or script. Before you build the flow, confirm the workbook is stored where the connector can reach it and that the data has the shape the action expects. A flow that cannot find its file or table fails rather than guessing. Treat “power automate get excel column values to json array” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected columns shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
A sheet has headers name and qty in row 1, with Bolt M8 / 42 in row 2 and Nut M8 / 120 in row 3. Converted with the first row as headers, the output is [{"name":"Bolt M8","qty":"42"},{"name":"Nut M8","qty":"120"}] — two records, keys taken from row 1. APIs, config files, and most web tooling speak JSON. Converting a worksheet to JSON lets spreadsheet data feed scripts and apps without manual re-typing. Point the flow at a test copy of the workbook first, run it, and open the run history to confirm every step succeeded before you switch it to the live file.
In Google Sheets
Power Automate's Excel Online (Business) actions work on Excel workbooks in OneDrive for Business or SharePoint; a Google Sheets file needs the separate Google Sheets connector, whose actions and requirements differ. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. The short version of “power automate get excel column values to json array”: test on a copy, read the run history and keep an eye on the connection it depends on. Automation saves time only while it keeps running, so plan for the run that fails.
Common mistakes
- Merged header cells, which leave some columns without a key.
- Expecting Excel's Save As dialog to offer JSON — it does not; you need a converter or script.
- Forgetting that all cell values arrive as text; cast numbers in the consuming code.
- Using a sheet where the real headers start in row 3 — move them to row 1 first.
Frequently asked questions
Can Excel save a file as JSON directly?
No. Save As has no JSON option. Use a converter like the one above, Power Automate, or a script (Office Scripts, Python).
How do I open a JSON file in Excel?
Data ▸ Get Data ▸ From File ▸ From JSON, then expand the records into columns in Power Query and Close & Load.
Does the converter upload my spreadsheet?
No. The conversion runs entirely in your browser; the file never leaves your machine.