JSON Parser by Quickwork

The JSON Parser by Quickwork is a built-in utility that enables you to convert JSON strings, raw text, or other complex structured inputs into readable key–value pairs within a workflow. It is especially useful when dealing with HTTP API responses, CSV conversions, webhook payloads, or nested JSON objects where data needs to be extracted, mapped, or reformatted.

What is JSON

JSON (JavaScript Object Notation) is a widely used data format that represents information as name–value pairs and arrays, valued for its simplicity and readability. While JSON is often structured in a human-friendly format, there are cases where you may encounter raw or unformatted JSON data. In such cases, the JSON Parser by Quickwork can be used to transform the raw input into a well-structured JSON format, enabling easier processing and integration.

Functionality overview

JSON Parser by Quickwork supports:

• A single object of raw JSON data
• An array of objects containing raw JSON data

Guidelines for specifying raw JSON data

• Use curly braces for objects and square brackets [] for arrays.
• Do not include a trailing comma , after the last object in an array.
• Enclose all string keys and values in double quotes "".
• Failure to follow these conventions may result in errors during parsing.

For example, passing a string containing JSON object(s),

Open the History tab to observe the outut.

You can now access individual elements using the Data Tree Output.

📘

• Keys with integer values must include a placeholder integer in the syntax . For example: "ID": 1
• Keys with string values must include an empty string placeholder. For example: "Name": ""
• You can pass an array of objects containing raw JSON data.

[
  '{"ID": "001", "Name": "Alice Johnson", "Email": "[email protected]"}',
  '{"ID": "002", "Name": "Brian Lee", "Email": "[email protected]"}',
  '{"ID": "003", "Name": "Carla Mendes", "Email": "[email protected]"}'
]

Common use cases

• Convert API responses (received as plain text) into structured JSON for further processing.
• Parse custom payloads received from external systems or third-party services.
• Extract and loop through arrays in webhook or file inputs.

Use case: Select vendor with lowest cost

To automate the selection of the lowest-cost qualified vendor for each product listed in a Purchase Requisition (PR_data) sheet by comparing it with vendor qualification data stored in another sheet, and updating the PR sheet accordingly.

Prerequisites

• Access to the Quickwork.
• An active Microsoft Excel account with the workbook stored in OneDrive.
• Workbook with two worksheets PR_data and Qualification.

Steps to build the journey

1. Log into your Quickwork account.

2. Set up the trigger that starts the journey when an HTTP request is received.

   

3. Set up the action Get range (PR_data) via Microsoft Excel to fetch purchase request details from Excel.

   

4. Set up the action Get range (Qualification) via Microsoft Excel to fetch vendor qualification data for cost comparison.

   

5. Use the Foreach loop to process each PR record one by one.

   

6. Parse string to JSON via JSON Parser by Quickwork to compute and format the lowest-cost vendor details for each product.

   1. Select JSON Parser by Quickwork and Parse string to JSON action.
   2. Enter the code in the String field and the Sample JSON.
      CodeSample Response

      // current PR row being processed by Foreach (Step 6)
      const prRow = data pill Item from Step 3;
      // all Qualification rows from Get range (Step 5)
      const qualificationRows = data pill Rows from Step 2;
      
      // filter for matching Product_Code
      const matches = qualificationRows.filter(
        q => q.Product_Code === prRow.Product_Code
      );
      // if no match found
      if (matches.length === 0) {
        ({
          "Product_Code": prRow.Product_Code,
          "Vendor_Name": "",
          "Lowest_Price": ""
        });
      }
      // find row with minimum price
      let lowest = matches[0];
      for (let i = 1; i < matches.length; i++) {
        if (matches[i].Price < lowest.Price) {
          lowest = matches[i];
        }
      }
      // output vendor with min price
      ({
        "Product_Code": prRow.Product_Code,
        "Vendor_Name": lowest.Vendor_Name,
        "Lowest_Price": lowest.Price
      });

      {  "Product_Code": "P1001",  "Vendor_Name": "Vendor_A",  "Lowest_Price": "100"}

   The code does the following:

   • Compares the current Product_Code with all entries in the Qualification sheet.
   • Filters only those vendors where Status = Q..
   • Finds the minimum Price among those.
   • Returns a structured JSON output.

8. Update an existing cell via Microsoft Excel to write the selected vendor name into the PR_data sheet.

   

Testing the journey

1. To test the journey, click Save & Start.
2. The History tab will open automatically. Then, navigate to the Journey tab, click the web URL in the HTTP trigger, and your journey will begin running.
3. To view the results, open the PR_data sheet in Excel. You will see the Vendor_Name column updated automatically. In the background, the journey reads each PR_data entry, compares it against the Qualification sheet to find the lowest-cost qualified vendor, formats this result as JSON, and writes the selected vendor name back to the corresponding PR_data row.

This automation is made possible through the JSON Parser by Quickwork, which seamlessly converts the JavaScript output into structured data, allowing dynamic mapping and smooth data flow between steps, eliminating the need for manual lookups or complex formulas.