In this article, we’re building on our previous guide on creating Process Behavior Charts (PBC) in Google Sheets, with data submission automated through iOS Shortcuts and Google Forms. Now, we’ll close the loop by bringing this data back to your iPhone, visualizing it using the Charty app. With this setup, you’ll have your real-time PBC data right at your fingertips!

Preparing Data for Export

First, let’s set up a new sheet in Google Sheets specifically for export. In your existing Google Sheets file, create a new sheet called "PBC Export". This sheet will serve as the source for data that will be published and accessible via Charty.

Over time, your PBC data may accumulate a substantial number of points. To maintain a manageable display on your phone, you’ll want to control which data points are shown. Use the PBC Export sheet to filter data from your main PBC sheet according to your needs. For instance, if you’d like to include only points after a particular date, add this formula to cell A1:

=FILTER(PBC!A1:G1000, PBC!A1:A1000 > DATE(2024,10,23))

This filter will retrieve only data from the PBC sheet for dates later than October 23, 2024. You can adjust the date as necessary, or even reference another cell to make this date dynamic.

Publishing the Data

Now that you’ve prepared your data, let’s make it publicly accessible so that an iOS Shortcut can fetch it. Follow these steps:

1. In Google Spreadsheet, navigate to File > Share > Publish to the web.
2. In the options, select the PBC Export sheet instead of the entire document, and choose the CSV format.
3. Google Sheets will provide a link to this CSV file – copy this link as we’ll need it in the next step.

Creating a Shortcut for Charty

With your data link ready, we’ll now create a shortcut in the iOS Shortcuts app to feed this data into Charty.

1. Open the Shortcuts app and create a new shortcut.

2. Add the following actions:

   • Create a chart: Name it something relevant, like “PBC Chart.”, or "Weight" in my case.
   • Get contents of URL: Paste the URL from the previous step here.
   • Add series: Use the data from the “Contents of URL” action as a line series in your chart (this should be the “Chart Id” you created earlier). Assign:
     • Date for X values.
     • Measurement, Upper Limit, Lower Limit, and Average for Y values.

   

3. Add an action to Update all widgets.

To complete the setup, add a Charty widget to your home screen and link it to the chart you just created. You should now see your PBC data right on your iPhone!

Customizing Your Chart

But it’s probably not super nice to read.

To enhance the visual clarity:

• For each Y value, add an action to Update Series Style. Here’s a suggested styling:
  • Upper and Lower Limits: Red (#FF0000).
  • Average: Blue (#0000FF).
  • Measurement: Black (#000000), with markers enabled and marker size set to 2.

An example of measurement settings:

Here’s my final result with the PBC displayed on my iPhone. Looks great, right?

Bonus point: Setup an automation to refresh the chart daily

On the shortcut app, create an automation that runs every day and executes the shortcut you’ve just created.
That way you won’t have to manually trigger a refresh of the chart.

Wrapping Up

This is the final piece of that series about collecting data from an iPhone right into a Google sheet to build a PBC, and then displaying it on your phone.
This flow gives you instant access to up-to-date charts, empowering better, data-informed decisions right from your home screen.

This was fun to build.

If you would like to see how using PBCs can help you improve your software team, I’d be more than happy to discuss that with you. Feel free to grab a slot on my calendar.