Here is what I got from ChatGPT:
I need help understanding how to generate a CSV file from a JSON API response. The JSON structure is not a proper tree or list format, and I’m struggling to process it. The JSON contains a legend under key “0”, which represents the column names, and the subsequent keys (e.g., “1”, “2”) contain row data. My goal is to create a CSV file where the column headers are derived from the legend (“0”) and the rows correspond to the entries under keys like “1” and “2”. JSON response example { “Tables”: [ { “TableJson”: { “0”: { “0”: “Column A”, “1”: “Column B”, “2”: “Column C”, “3”: “Column D”, “4”: “Column E” }, “1”: { “0”: “TEXT 1 A”, “1”: “TEXT 1 B”, “2”: “TEXT 1 C”, “3”: “TEXT 1 D”, “4”: “TEXT 1 E” }, “2”: { “0”: “TEXT 2 A”, “1”: “TEXT 2 B”, “2”: “TEXT 2 C”, “3”: “TEXT 2 D”, “4”: “TEXT 2 E” } } } ] } I want the resulting CSV to look like this: Column A,Column B,Column C,Column D,Column E TEXT 1 A,TEXT 1 B,TEXT 1 C,TEXT 1 D,TEXT 1 E TEXT 2 A,TEXT 2 B,TEXT 2 C,TEXT 2 D,TEXT 2 E How to archive this?
ChatGPT said:
ChatGPT
To generate a CSV file from the JSON response, you need to process the JSON structure to extract the column names from the “0” key and the row data from the other numbered keys. Here’s a Python script to achieve this:
Python Code to Generate CSV
python
Copy code
import csv
import json
# JSON response
json_data = {
"Tables": [
{
"TableJson": {
"0": {
"0": "Column A",
"1": "Column B",
"2": "Column C",
"3": "Column D",
"4": "Column E"
},
"1": {
"0": "TEXT 1 A",
"1": "TEXT 1 B",
"2": "TEXT 1 C",
"3": "TEXT 1 D",
"4": "TEXT 1 E"
},
"2": {
"0": "TEXT 2 A",
"1": "TEXT 2 B",
"2": "TEXT 2 C",
"3": "TEXT 2 D",
"4": "TEXT 2 E"
}
}
}
]
}
# Extract the TableJson object
table_json = json_data["Tables"][0]["TableJson"]
# Extract column headers from key "0"
headers = [table_json["0"][str(i)] for i in range(len(table_json["0"]))]
# Extract rows from other keys
rows = [
[table_json[key][str(i)] for i in range(len(table_json[key]))]
for key in table_json if key != "0"
]
# Write to a CSV file
output_csv_file = "output.csv"
with open(output_csv_file, mode="w", newline="") as file:
writer = csv.writer(file)
writer.writerow(headers) # Write column headers
writer.writerows(rows) # Write row data
print(f"CSV file '{output_csv_file}' created successfully.")
How It Works:
- Extract Column Headers:
- The key
"0" contains the column headers. These are accessed and stored in a list using a comprehension loop.
- Extract Rows:
- Keys
"1", "2", etc., contain row data. These are processed to extract corresponding values and stored as lists of rows.
- Write to CSV:
- The
csv.writer is used to write the headers first and then append the rows to the CSV file.
Running the Code:
- Save the script as
generate_csv.py.
- Run it with Python:
python generate_csv.py.
- The output will be saved as
output.csv in the current directory.
Resulting CSV:
mathematica
Copy code
Column A,Column B,Column C,Column D,Column E
TEXT 1 A,TEXT 1 B,TEXT 1 C,TEXT 1 D,TEXT 1 E
TEXT 2 A,TEXT 2 B,TEXT 2 C,TEXT 2 D,TEXT 2 E
This script will work for any similarly structured JSON input. Let me know if you need help customizing it further!
You said:
can you provide the code in a format so I can use the server script action in toolbox plugin?
ChatGPT said:
ChatGPT
To use the script in the Toolbox Plugin’s “Run JavaScript Server Script” action in Bubble, you need to convert the Python logic into JavaScript. Here’s how you can achieve the same functionality:
JavaScript Code for the Toolbox Plugin
javascript
Copy code
function(properties, context) {
// The JSON data (you can replace this with `properties.data` if passing dynamically)
const jsonData = {
"Tables": [
{
"TableJson": {
"0": {
"0": "Column A",
"1": "Column B",
"2": "Column C",
"3": "Column D",
"4": "Column E"
},
"1": {
"0": "TEXT 1 A",
"1": "TEXT 1 B",
"2": "TEXT 1 C",
"3": "TEXT 1 D",
"4": "TEXT 1 E"
},
"2": {
"0": "TEXT 2 A",
"1": "TEXT 2 B",
"2": "TEXT 2 C",
"3": "TEXT 2 D",
"4": "TEXT 2 E"
}
}
}
]
};
// Extract TableJson object
const tableJson = jsonData.Tables[0].TableJson;
// Extract headers from key "0"
const headers = Object.values(tableJson["0"]);
// Extract rows from other keys
const rows = Object.keys(tableJson)
.filter(key => key !== "0")
.map(key => Object.values(tableJson[key]));
// Combine headers and rows to form CSV content
const csvContent = [
headers.join(","), // Add headers as the first row
...rows.map(row => row.join(",")) // Add each row of data
].join("\n");
// Return the CSV content
return { csv: csvContent };
}
Explanation:
- JSON Parsing:
- The
jsonData is hardcoded here but can be dynamically passed via properties.data if provided through Bubble.
- Extract Headers:
- The headers are retrieved from the key
"0" and stored as an array.
- Extract Rows:
- Keys other than
"0" are processed to extract row data.
- CSV Formatting:
- The headers and rows are combined with commas and joined by newline characters to create the CSV format.
- Return CSV Content:
- The resulting CSV content is returned as an object so it can be accessed in Bubble’s workflow.
How to Use in Bubble:
- Add the Toolbox Plugin to your Bubble app.
- Create a new action using Run JavaScript (server-side).
- Paste the above script into the action’s code section.
- Pass the required JSON as an input parameter if necessary.
- Use the returned
csv value in subsequent workflow steps.
Output Example:
The result of the script will be:
csv
Copy code
Column A,Column B,Column C,Column D,Column E
TEXT 1 A,TEXT 1 B,TEXT 1 C,TEXT 1 D,TEXT 1 E
TEXT 2 A,TEXT 2 B,TEXT 2 C,TEXT 2 D,TEXT 2 E
