Render an array of objects in a chart

[Boris]

My API returns data in a format like this. The data is fetched with Fetch JSON and stored in a single cell in the column.

How can I render it in a line chart where each item in the data array is a point on the chart?

date should be used as the x axis.

{
  "data": [
    {
      "date": "01/01/2023, 00:00",
      "usage": 0.1, 
      "cost": 0.05,
      "energyPrice": 0.04 
    },
    {
      "date": "01/01/2023, 01:00",
      "usage": 0.1,
      "cost": 0.05,
      "energyPrice": 0.04 
    },
    {
      "date": "01/01/2023, 02:00",
      "usage": 0.2,
      "cost": 0.1,
      "energyPrice": 0.05 
    },
    {
      "date": "01/01/2023, 03:00",
      "usage": 0.3,
      "cost": 0.15,
      "energyPrice": 0.04 
    },
    {
      "date": "01/01/2023, 04:00",
      "usage": 0.1,
      "cost": 0.05,
      "energyPrice": 0.03 
    }
  ]
}

[Boris]

I don’t see a Parse JSON option anywhere?

[Boris]

If the bot is talking about Glide • Transform JSON column then it’s not very useful since the array can be of dynamic length. Also I don’t want to extract each key of each object manually.

[Darren_Murphy]

Here is how I would do it:

The JavaScript I used:

let json = JSON.parse(p1);
return json.data[p2].date;

In my example, I just pasted your JSON into a template column. Obviously you’d need to get it into the table in some other way, and you’ll need enough rows in the table to cover the maximum number of points on the chart.

[Boris]

@Darren_Murphy Thanks for jumping in to help once again. Your solution seems solid however there are a few issues, because I accidentally omitted some important information you’d need.

As I understand it I’d have to create a new row in the Helper table for each array item. The arrays have a dynamic length and would have at least 1000 items. That’s a low of rows that I’d have to create manually. On top of that it’d basically make it mandatory to use your Enterprise plan since it’d eat up a lot of my row quota. That’s a big price to pay for a simple chart.

Also, the API response would be unique for each user, so I’d probably need to add a row owner, which means i’d need to make the 1000+ rows for each user (manually?). This severely limits how many users can use my app before I hit the row limit you have.

Maybe I’ve misunderstood something about your proposal, but it doesn’t seem suitable for my application.

[Darren_Murphy]

@Boris

Also, the API response would be unique for each user, so I’d probably need to add a row owner, which means i’d need to make the 1000+ rows for each user (manually?). This severely limits how many users can use my app before I hit the row limit you have.

That’s not a problem. Write the API response into a User Specific Column, and all users can share the same 1000 rows.

If the 1000 rows is an issue, then you’d probably need to look at something like quickcharts in combination with an external code column. Quite a bit more work, but doable.

[Boris]

@Darren_Murphy

I’m trying to implement your solution (I’ll try to change the API to return less responses to make this easier), but I hit a bit of a snag.

The API response is already in a User Specific Column

But I’m having a hard time getting it into my helper column. If I do a Lookup I get all the API responses for all users. How can I get just the current user’s data from the invoices table?

[Darren_Murphy]

That’s a row owner column, it’s not a user specific column. And it looks like you have multiple rows for the same user?

Maybe clarify that first - should each user have only one row in your Invoices table, or multiple?
If multiple, which of their rows would you want to process?

[Boris]

Ideally, each user would have multiple rows in the invoices table.
I’m rendering all invoices as a Table in the UI that renders all items in the collection.

When an item in the table is clicked the users would go to the Details view and see the API response as a chart for the specific invoice.

If this isn’t possible then I need a way to limit users to only upload one invoice per user. The API response for the uploaded invoice should be rendered as a chart somewhere in the app.

[Darren_Murphy]

@Boris

When an item in the table is clicked the users would go to the Details view and see the API response as a chart for the specific invoice.

Okay, gotcha. Here is what I would suggest:

• In your Invoices table, add a Single Value column. This should take First → Whole Row from your Helper Table
• Now in your Helper Table, add a User Specific Text column.
• Then add a Single Value column that targets the previous column. (this will apply whatever value is in that column to all rows in the table).
• Now, create a custom action for your Invoice list:
  – the first step should be a Set Column Values through the Single Value → Whole Row column, and you want to write your JSON into the User Specific column.
  – and the second step will be a Show Details Screen → This Item
• The result of the above is that every time a user clicks an Invoice in the list, the JSON for that Invoice will be written to the Helper Table, and the chart data will be created. Then you can just add a chart to your Invoice Details screen, using your Helper Table as the source.

[Boris]

You are an artist, my friend! I’ll try this right away!

A couple of clarifying questions:

Now in your Helper Table, add a User Specific Text column.

Should I put anything in the column?

• Then add a Single Value column that targets the previous column. (this will apply whatever value is in that column to all rows in the table).

It seems important for the user-specific column to have a value for this to work.

[Darren_Murphy]

@Boris

Should I put anything in the column?

Nope.

@Boris

It seems important for the user-specific column to have a value for this to work.

Correct, and that’s the purpose of the multi-step action…

@Darren_Murphy

– the first step should be a Set Column Values through the Single Value → Whole Row column, and you want to write your JSON into the User Specific column.

[Boris]

I’ve made some progress but I’m still stuck on a couple of things.

All the columns have been set up as instructed, my helper table is working well (it’s extracting the data from the json response in the user column).

At the moment I just pasted the response there so that I can test things. What I’m struggling with is this part:

– the first step should be a Set Column Values through the Single Value → Whole Row column, and you want to write your JSON into the User Specific column.

Where do I configure the Set Column Values action? When an item is added to the list?

How am I meant to write my JSON in the User Specific column. At the moment it’s fetched in a column in the Invoices table.

– and the second step will be a Show Details Screen → This Item

Like this?

I also configured the chart in the details screen to get its data from the helper table

[Darren_Murphy]

@Boris

Where do I configure the Set Column Values action? When an item is added to the list?

No.

I’m assuming that you are displaying your Invoices as a list or collection, right?
The default action on that list/collection will be Show Details Screen → This Item
What you need to do is replace that action with a custom action as I described earlier…

@Darren_Murphy

Now, create a custom action for your Invoice list:
– the first step should be a Set Column Values through the Single Value → Whole Row column, and you want to write your JSON into the User Specific column.
– and the second step will be a Show Details Screen → This Item

The idea is that every time a user views an invoice, the value in the Helper Table User Specific column is updated, and the chart for that invoice is dynamically built.

[Boris]

I managed to get it working. Thank you for your help and patience @Darren_Murphy