DEV Community: Amit Jotwani

Dynamically Updating Your Tidbyt Display with REST API Data

Amit Jotwani — Thu, 21 Sep 2023 16:40:19 +0000

Last time, I walked through building a simple "Hello World" app for my Tidbyt, the retro LED display screen that now sits ~~on my desk~~ in our Living Room.

This time, I was curious if I could use a REST API to dynamically update the Tidbyt's display.

For example, it would be pretty cool if it could display a "Good Morning" greeting in different languages each day (using GreetingsAPI.com) or a joke (using https://api.chucknorris.io).

The Quest

The mission is simple: Fetch a "Good Morning" greeting and its language from GreetingsAPI.com and display it on my Tidbyt using the http.star module for HTTP requests.

Pre-requisite

Before we dive in, make sure you've installed Pixlet on your machine. This tool is essential for serving , rendering , and pushing the app to your Tidbyt device.

If you're new to this or need a refresher, check out my previous post on Building a Hello World App for Tidbyt Retro Style Display.

Lets Get Started

First, fire up your favorite IDE and create a new file called greetings.star. Then, paste the code below into it.

`greetings.star`

# Import the required libraries
load("render.star", "render")
load("http.star", "http")

# Define API URLs for Chuck Norris Jokes and Greetings
GREETINGS_API_URL = "https://www.greetingsapi.com/random"
CHUCK_NORRIS_JOKE_URL = "https://api.chucknorris.io/jokes/random"

# Fetch a random greeting from Greetings API
def get_greeting():
    rep = http.get(GREETINGS_API_URL) # Make the HTTP GET request
    if rep.status_code != 200: # Check if the request was successful
        fail("Greetings API request failed with status %d", rep.status_code)
    print(rep.json()) # Debug print the response
    return rep.json() # Return the JSON response

# Fetch a random joke from Chuck Norris API
def get_joke():
    rep = http.get(CHUCK_NORRIS_JOKE_URL) # Make the HTTP GET request
    if rep.status_code != 200: # Check if the request was successful
        fail("Chuck Norris API request failed with status %d", rep.status_code)
    return rep.json() # Return the JSON response

# Main function to render the Tidbyt app
def main(config):
    greeting = get_greeting() # Get random greeting
    joke = get_joke() # Get random joke

    # Get font from config or default to "tb-8"
    font = config.get("font", "tb-8")
    print("Using font: '{}'".format(font)) # Debug print the font being used

    # Build the render tree
    return render.Root(
        child = render.Column(
            cross_align="center",
            children = [
                # Create a horizontal colored box
                render.Box(
                    width = 64,
                    height = 1,
                    color = "#78DECC",
                    padding = 10
                ),
                # Display the greeting text
                render.Text("%s" % greeting["greeting"], font = font, color = "#FF0000"),
                # Another horizontal colored box
                render.Box(
                    width = 64,
                    height = 1,
                    color = "#78DECC",
                ),
                # Scrolling marquee text to display the greeting in its native language
                render.Marquee(
                    width = 64,
                    child = render.Text("Good Morning in %s" % greeting["language"], font = font)
                ),
                # Final horizontal colored box
                render.Box(
                    width = 64,
                    height = 1,
                    color = "#78DECC",
                ),
            ],
        ),
    )

💡

We're not using Chuck Norris this time, but hey, it's good to have options :-)

Key points to remember

http.star Module : We use http.star module to interact with REST APIs. This module is part of the Starlark standard library and allows us to make HTTP requests.
render.Root and render.Column: To display the fetched data on the Tidbyt, we use render.Root to set up the root layout and render.Column to vertically arrange the display elements.

Serving the App

To preview the app, open your terminal and run:

pixlet serve greetings.star

This will start a local server and you'll get a URL to preview your app.

Rendering the Content

Run the following command:

pixlet render greetings.star

This will create a .webp file named greetings.webp in your current directory.

Pushing to Your Tidbyt

💡

Note: Make sure you have your Device ID handy for this step. See Get Device ID.

Finally, to see your work come to life on your Tidbyt device, you'll need to push the generated .webp file to it using its device id. Run the following command:

pixlet push <YOUR_DEVICE_ID> greetings.webp

This will display your app on your Tidbyt device.

And there you have it! You should now be able to display greetings from around the world on your Tidbyt.

💡

Pro Tip: To make your app a part of the regular app rotation and have it show up on your iOS app, you'll need to install it permanently. See Installing the App Permanently

Building a Hello World App for Tidbyt Retro Style Display

Amit Jotwani — Mon, 04 Sep 2023 18:38:55 +0000

I recently bought a Tidbyt, a fascinating retro pixelated display that can do everything from showing the weather to displaying memes (h/t to Greg Baugues for the recommendation).

After setting it up and exploring some pre-built apps (mine currently cycles through a Nokia Snake game, a Tetris clock I'm in love with, and even shows my upcoming Google Calendar meetings), I had to explore building my own apps for it. The classic "Hello World" seemed like the best starting point.

Figuring out how to build an app for Tidbyt wasn't overly complex, but took me some time to wrap my head around the basics. Here's a quick guide to get you started with your first Tidbyt app.

What you will learn

Understanding the basic building blocks: Starlark, Pixlet, and so on.
Writing a simple "Hello World" app.
Testing your app locally in a browser.
Testing the app on your Tidbyt device.
Pushing the app so it shows up in the Tidbyt iOS app.

Let's get going!

The Basics: Pixlet and Starlark

The primary tool for building Tidbyt apps is Pixlet - an app runtime and UX toolkit for highly-constrained displays.
You write your Pixlet applets for Tidbyt in Starlark , a Python-like language.
Apps developed with Pixlet can be served in a browser , rendered as WebP or GIF animations , or pushed to a physical Tidbyt device.

Hello World!

Here's how a simple "Hello World" code snippet looks, saved in a file called hello_world.star.pythonstarpyth

`hello_world.star`

load("render.star", "render")

def main():
    return render.Root(
        child = render.Text("Hello World")
    )

Preview Your App in a Web Browser

You can serve your applet on your local machine, essentially previewing it in a web browser much like you would with a Flask app.

To start this preview, run:

pixlet serve hello_world.star

Then navigate to http://localhost:8080 to see your app in action.

Preview Your App on Your Tidbyt Device

Step 1: Preparing the files to be rendered (Gif/Webp format)

This bit had me stumped for a moment. Tidbyt applets need to be in either gif or webp format. You can't just deploy the .star files to Tidbyt. To generate the gif/webp formats, run the render command.

pixlet render hello_world.star

This will produce a .webp file named hello_world.webp in your current directory.

Step 2: Connecting Pixlet to Your Tidbyt Account

Before pushing any apps to your Tidbyt device, you'll first need to connect Pixlet to your Tidbyt account. Run this command:

pixlet login

This will trigger a browser window to open, guiding you through the Tidbyt authentication process.

Step 3: Push to Device

At this point, you can push your app to your Tidbyt device temporarily for a quick preview:

Get Device ID

To know which device to push your app to, you'll need to list the devices connected to your account.

pixlet devices

Push to the device using the Device ID

pixlet push <YOUR_DEVICE_ID> hello_world.webp

Note that this will only display the app temporarily on your Tidbyt and it won't appear on your iOS app.

Installing the App Permanently

To make your app a part of the regular app rotation and also have it show up on your iOS app, you'll need to install it:

pixlet push --installation-id <INSTALLATION_ID> <YOUR_DEVICE_ID> hello_world.webp

Replace <INSTALLATION_ID> with your chosen alphanumeric name and <YOUR_DEVICE_ID> with the specific ID of your Tidbyt device.

You can name the INSTALLATION_ID anything alphanumeric that's meaningful to you. I initially thought you could use hyphens because the manual mentioned it, but I got an error. So, I kept it simple and used helloworld as my installation ID.

That should get your app to show up on your Tidbyt device and also on your list of installed apps on your Tidbyt iOS app.

What's next?

I really enjoyed getting this "Hello World" app up and running on my Tidbyt. It's incredibly gratifying to see your custom creation pop up on such a gorgeous retro display.

My curiosity is even more piqued. Now I've got a bunch of questions I want to answer next:

How do I pull info from a REST API? Maybe even a Greetings API?
How do I change the font?
How can I center the text?
Can I add emojis?

Answers coming soon 🙂

👋 If you enjoyed this, I promise I'm just as fun in 280 characters! Follow me on Twitter 🐦

Quickly Cycling Through and Fixing Spelling Errors in VS Code Markdown Files

Amit Jotwani — Tue, 22 Aug 2023 12:46:17 +0000

I wanted to cycle through and fix all spelling errors in a VS Code markdown file. I already had the spell-checking extension installed but couldn't figure out how to see all the errors together and fix them quickly.

Here's how -

Open the markdown file in VS Code where you want to correct the errors.
Install the Code Spell Checker Extension if you haven't already.
Press CMD + SHIFT + M to see all spelling errors together in the 'Problems' pane.
Click on an error or highlight the word and press CMD + . to see quick fix suggestions.

Changing the Number of Posts Displayed Per Page in Ghost CMS

Amit Jotwani — Thu, 17 Aug 2023 22:05:41 +0000

So, I wanted to customize the number of posts displayed per page on my Ghost CMS website using the Dawn theme. The default setting was showing only 5 posts, and I wanted to bump that up to 25.

Here's how I did it, broken down into simple steps, so it's easy to remember and follow along.

Download the theme you are currently using

First, we need to download the current theme to access the file containing the posts' settings.

Go to **Settings -> Design** in your Ghost CMS admin panel.

Edit package.json File

Open the downloaded theme directory and locate the package.json file.
Find the key posts_per_page and change its value to your desired number (For Dawn theme, by default, this was set to 5. I changed mine to 25).
Save the changes to the file.

Zip Up the Directory

We need to prepare the modified theme for uploading back to Ghost CMS.

Zip up the entire theme directory, ensuring all necessary files are included (on a Mac, you can right click on the directory, and click "Compress").

Upload the Modified Theme to Ghost CMS

Finally, we need to upload the customized theme back to Ghost CMS.

Head back to the **Design** section in your Ghost CMS admin panel.
Upload the zipped directory containing the modified theme.

Fin!

When, why, and how to upgrade spreadsheets to PostgreSQL

Amit Jotwani — Mon, 01 May 2023 20:33:14 +0000

This post was originally written by Kevin Whinnery.

Many businesses are run off of spreadsheets. From your local coffee shop to the US military, spreadsheets handle a lot of heavy lifting in many workflows. More than 3 billion people use Excel and Google Sheets every month. Unfortunately, there comes a time when even this powerful and familiar tool becomes a liability. Spreadsheets can get out of sync when emailed back and forth, and are vulnerable to typos and human error. At a certain level of complexity, you should strongly consider moving from spreadsheets to a relational database, which provides more guarantees about the integrity of your data.

Read on to learn about the warning signs of impending spreadsheet disaster, how to recognize them, and how to move to a database when the time is right.

Recognizing when spreadsheets are falling over

As your spreadsheets grow in complexity, here are some pains you might start to feel - if you read through these pain points and find yourself nodding along, it might be time to consider upgrading to a database.

You’re struggling to manage large amounts of data. Your key spreadsheet has dozens of columns and tens of thousands of rows of data. At around 40,000 rows, you’ve started to notice that scrolling and calculations are slowing down.
You have too many editors, and you keep stepping on one another’s toes. Spreadsheet software like Google Sheets supports multiple people editing at the same time (i.e. multiplayer collaboration), but they lack version and access controls. Your key spreadsheet is difficult to keep in a predictable state, because so many people need to change and update it.
It’s hard to get the insights you want from data in spreadsheets. Maybe your business uses several key spreadsheets, and you have a business intelligence question that needs data from all of them. You find it hard to use the relatively limited query and data modeling capabilities of spreadsheets to get all the insights you’d like from your data.
Costly mistakes and bad data because of human error. Spreadsheets are very malleable by default, and don’t have built-in mechanisms to ensure data integrity, like transactions, foreign key constraints, and data validation. As a result, sometimes your data is inaccurate, which impacts decision making and workflows that depend on spreadsheet data.

These pain points typically appear when your use case evolves from data storage, analysis, and visualization. Spreadsheets actually do those things reasonably well. What spreadsheets don’t do well is manage large, complex data sets, and relationships between different data points. They also don’t work well to model business processes and operations, which often have multiple steps and validations that need to happen at different steps. When your use case shifts from analyzing static data to driving business processes, that’s when you’re likely to encounter trouble.

No-code tools provide a partial solution

Some teams might upgrade their spreadsheet use cases to no-code tools like Airtable. Airtable has become a popular alternative to spreadsheets by providing more structured data storage. And it’s easy to understand why it’s been so compelling - Airtable combines the simplicity of a spreadsheet interface with the basic functionality of a relational database. It also enables teams to create customizable views and simple apps, and integrate with other tools.

This upgrade might be sufficient for some spreadsheet use cases, if your primary pain point is just modeling relationships between data points. There are a few spreadsheet problems that aren’t solved at this stage of evolution, however.

Performance issues and storage limits : As the amount of data you store in Airtable bases grows, interacting with data through the GUI can start to slow down (similar to spreadsheet software). There are also limits to the size of your data set enforced by the product (250k rows in an Enterprise plan as of this writing).
Modeling business workflows : Airtable is a major upgrade over spreadsheets in data modeling, but it’s hard to model more complex, multi-step business processes. The ability to create forms and customized user-facing views is nice, but they are still tightly coupled to pushing data in and out of a spreadsheet-like data store.
Inconsistent interfaces to data : because views and schemas in Airtable are still quite malleable by default, data integrity can still become an issue without great care taken in configuration.
Limited query capabilities : Airtable has a REST API to access data and resources, but its query capabilities are limited when compared to languages like SQL or GraphQL. So while this is probably a step up from a spreadsheet, it might still not be flexible enough.

No-code solutions such as Airtable provide a meaningful upgrade over spreadsheets, but still experience some of the problems at scale that you might be feeling in a spreadsheet. If you find yourself struggling still when using these tools - it’s probably time to consider moving your data to a relational database.

Building with a PostgreSQL database

The final evolution from spreadsheets is a relational database - like our personal favorite PostgreSQL. A relational database like Postgres can potentially address many issues in a spreadsheet that has ballooned into an unmaintainable state.

Performance and scale: A database can store extremely large data sets, far beyond what’s reasonably possible in a spreadsheet-like tool. And because database software is designed to hold such large data sets, it is also designed to access that data fast, at any scale.
Robust data modeling : Relational databases are designed to support very complex data models - whatever types of data your business cares about, it should be possible to model it in a database.
Data integrity : Relational databases are designed to promote data consistency and integrity. The format of data is strictly enforced, and prevents many classes of human error.
Advanced querying : A database that supports SQL queries can enable you to answer just about any question from your data, joining individual data points across multiple tables within the database. SQL is the “lingua franca” of data access for a reason!
Supports building application software on top : A SQL database can be combined with a wide array of software development tools to build applications that interact with your data. Custom software enables you to model any kind of business workflow, rather than just pushing data in and out of a spreadsheet. And if you choose platforms like Retool, those applications can be almost as easy to create as customizing a spreadsheet or arranging a PowerPoint presentation.

So let’s say you are sold on the utility of a relational database, but your data is still locked in Airtable or a spreadsheet. And maybe you’re pretty comfortable with a spreadsheet interface, but have never used a relational database before. If this sounds like you, Retool Database might be the best first step into the world of managing data in a relational database. It offers both an optional graphical interface that is spreadsheet-like, and is integrated into Retool, which makes it much simpler to build software and visualizations on top of your data than typical app development tools. Let’s take a look, at a high level, how you could migrate from a spreadsheet to Retool Database.

From spreadsheets to Retool Database

Your first step in migrating from spreadsheets to a relational database will be exporting your data from either Airtable or your spreadsheet software as CSV files. This bare-bones text representation of your data can make it portable into different environments, like a relational database. Retool Database, through its graphical interface, allows you to import CSV files as new tables in your database.

To migrate your spreadsheet or Airtable data to Retool Database, just upload your CSVs into Retool. Retool will automatically create PostgreSQL tables for you. There’s no setup or configuration required.

Uploading a CSV into Retool Database

Once you’ve uploaded your CSV, Retool Database will create PostgreSQL data type columns and insert your data for you. Double-check that the names and field types match your preferences.

Schema mapping

From there, you can quickly build out your data model and schema with the Retool Database UI—add tables and configure fields in a few clicks.

With Retool, you can start with a PostgreSQL database and build custom applications that work for you and your users. If you want to use Retool Database, you get up to 5GB of data storage free for one year. To get started, create a free Retool account.

Custom apps and workflows

With your data now in a PostgreSQL database, you can use Retool, a developer platform for business software, to build custom apps and workflows on top of your database much faster than coding from scratch. These apps can be complex and custom enough to handle many use cases, and can scale to thousands of users.

Retool comes with several features to help you build faster.

Drag-and-drop interface : Create user interfaces quickly and easily using Retool's visual editor, without needing to write extensive frontend code.
Pre-built components : Leverage Retool's library of hundreds of pre-built components, such as tables, forms, and charts, to streamline your app development process.
Seamless integrations : Connect your Retool app to your Postgres database and other third-party services using Retool's built-in integrations.
Collaboration and source control : Work together with your team on app development and track changes with Retool's source control features.
Automate ETL tasks and alerts : Use Retool Workflows to schedule background tasks that operate on your databases, like periodically ingesting data from 3rd-party APIs or running GPT-4 against your database to answer questions with plain English.

An optimal path forward

There’s a time and place for every tool in your toolkit, and spreadsheets are a valuable arrow in your quiver. But when your spreadsheet becomes the system of record for critical business data, and your business processes rely on manual efforts that modify the spreadsheet - I’m afraid you’re asking for trouble at some point down the line. At some point, human error or software limitations will create problems for your business.

Relational databases provide an alternative to mega-spreadsheets that is scalable and reliable, and opens up the possibility of creating custom software on top of your database to run your business processes in a safe and repeatable way. If you’re coming from the world of spreadsheets, Retool Database can help ease the transition to relational databases with a graphical interface, and the power of the Retool platform to build business processes and data visualizations on top of your data.

If you have questions or want to share your experience working between spreadsheets and PostgreSQL, join us on Discord to chat about it live, or post about your experience in our community forums.

Analyzing LEGO set data with Retool Workflows

Amit Jotwani — Fri, 28 Apr 2023 00:54:56 +0000

Analyzing and visualizing data is a crucial part of any business or organization, but it can be tedious and time-consuming. As someone who likes finding ways to save time and effort, I’m game to explore anything that can make this kind of work faster and easier. That’s where Retool Workflows come in.

Retool Workflows make it easy for developers to create automated processes that send custom alerts, and analyze and visualize data using a graphical interface—and to run those processes on a schedule.

With Python support recently added to Retool Workflows, I wanted to play around and do some fun data analysis with it. I thought about some datasets to use—IMDb movie ratings, global coffee production statistics, UFO sightings... But I kept coming back to something that I love even as an adult—something that felt a little poetic, given the parallels I recognized with Retool: LEGO!

Just like how LEGO pieces of different shapes and sizes connect to build pretty much anything you can dream up, Retool Workflows provide a way to drag and drop blocks of code, customize them, and connect them to create a seamless automation for data analysis. Building with Retool Workflows is, I realized, like building a LEGO model, but instead of using plastic bricks, you’re using Python/JavaScript code blocks. Instead of creating a castle or a spaceship, you can build a data analysis workflow that streamlines your ETL and reporting tasks.

So, with LEGO as inspiration, allow me to demonstrate how Retool Workflows can be used to analyze data and create stunning visualizations using Python libraries like pandas, seaborn, and matplotlib.

We’ll use this dataset available on Kaggle.com to analyze and visualize the following:

Number of LEGO sets released over the years: We’ll dive into the data to see how many LEGO sets were produced each year and track how that number has evolved over time, allowing us to gain insights into the growth of the LEGO world.
Average number of pieces per LEGO set over the years: How many pieces does the average LEGO set contain? We’ll crunch the numbers to find out, and explore how this has changed over time as sets have become more complex and detailed.

You can download the JSON for these workflows here: Workflow 1: Number of sets produced over the years, and Workflow 2: Average number of pieces per LEGO set over the years), and import it into your Retool account.

Ready? Let’s get building.

Analyzing LEGO set data with Retool Workflows

Here’s the basic outline that we'll be following to build the workflows for each of these analyses:

Workflow Start Trigger: This block will be the starting point of our Retool workflow. We can configure this block to trigger our workflow at regular intervals using Schedule/Cron or Webhook.
Fetch your data (getLEGOSetsData): This block will use SQL to query Retool Database for the LEGO set data we want to analyze.
Customize with code (LEGOSetsPerYear): This is where the magic happens! We’ll use Python libraries like pandas, seaborn, and matplotlib to analyze the data and create visualizations. For example, we can use pandas to group our data by year and then use seaborn to create a bar chart showing the number of sets produced each year.
Fire an action (UploadToAmazonS3Bucket and getS3URL): Once we have our visualizations, we can save them to a file buffer and then use Retool’s native integration for Amazon S3 to upload the files to our S3 bucket. This will allow us to easily share the images with others or include them in reports.
SendEmail: Finally, we’ll use Retool’s native integration for SendGrid to send the visualizations in an email. This is a great way to share our analysis with others or to receive automated reports.

Here’s what the final workflow will look like:

Bird's eye view of the final Python workflow to visualize the number of LEGO sets released over the years

Let’s build the workflow

The StartTrigger block is the entry point for the workflow. You can select this to either be a Schedule/Cron job or a Webhook. We’ll just let it be at its default value, but feel free to play around with it if you like.

_You can select the Start Trigger to either be a Schedule/Cron job or a Webhook. _

Click on the “+” button on the left side bar, and then click on “Resource Query”. Then click on the canvas to place it next to the StartTrigger block.

Inside the Resource Query block, choose the source for your database. This could be any of the data integrations supported by Retool, including any REST/GraphQL API, or databases like PostgreSQL, DynamoDB, or MongoDB. In this case, I downloaded this LEGO dataset available on Kaggle.com in CSV format, and imported it data into Retool’s built-in database using the CSV import feature.

So, we’ll choose retool_db (postgresql) as the resource, and then type the SQL query to retrieve the LEGO data set from Retool Database.

You can run each block individually to see the results of that block, like we did to run the SQL query.

Write SQL to get the data for LEGO sets

Now that we have our LEGO data available in our workflow, we’re ready to write some Python code to analyze this data.

Analysis 1: The number of sets produced over the years

We’ll add a “Code” block to our workflow, and write some Python code to analyze the data using the Python libraries mentioned earlier.

Add a Code block to write some Python code to analyze the data

import pandas as pd 
import seaborn as sns 
import matplotlib.pyplot as plt 
import io 
import uuid

# create a pandas dataframe called lego_sets using the lego set data retrieved in the previous block
lego_sets = pd.DataFrame(getLegoSetsData.data) 

# count the number of sets released per year and store it in a new dataframe called sets_per_year
sets_per_year = lego_sets["year"].value_counts().reset_index() 

# set the column names of the sets_per_year dataframe
sets_per_year.columns = ["year", "count"] 

# sort the data by year
sets_per_year = sets_per_year.sort_values("year") 

# create a bar plot
plt.figure(figsize=(14, 6)) # set the figure size
sns.barplot(x="year", y="count", data=sets_per_year) # set the x and y axis, and the data for the plot

# add title and labels to the plot
plt.title("Number of LEGO Sets Released per Year") 
plt.xlabel("Year") 
plt.ylabel("Number of Sets") 

# rotate x-axis labels for better readability
plt.xticks(rotation=90) 

# display the plot
plt.show() 

# save the plot as a PNG image in a file buffer
file_buffer = io.BytesIO() 
plt.savefig(file_buffer, format='png') 
file_buffer.seek(0) 

# get the image data from the file buffer
image_data = file_buffer.getvalue() 

# Generate a random UUID (Universally Unique Identifier)
file_name = str(uuid.uuid4())

# Append a file extension
file_name += ".png"

# return the image data
return {"file_name": file_name,"image_data": image_data}

What’s this Python code doing?

To get started, I’ve imported three handy libraries: pandas, seaborn, and matplotlib.

pandas is a library that helps us work with tables of data. It’s useful when we have a lot of data to work with or when we need to combine different types of data (like numbers and text), like we do with our LEGO dataset.

seaborn helps us make pretty graphs and charts from data. We can use it to visualize data in different ways to better understand patterns and relationships.

matplotlib helps us make all kinds of graphs and charts. It’s very versatile, in the sense that we can use it to make simple or complex visualizations. We can use it to make things like line graphs, scatter plots, and bar charts. We’ll use a combination of seaborn and matplotlib to generate this graph to find out the number of sets released over the years.

We also use the library io which is a part of Python and helps you work with input and output, like reading and writing files, strings, or byte arrays. We use this to create a file buffer object that stores the image data generated by our plotting libraries in memory as bytes.

We then get the image data from the file buffer object for further use—in this case, we’re storing it in an S3 bucket and using that to send an email.

Visualization of the Number of LEGO Sets Released per Year

💡

Side note: Apart from the drop off we see in 2017, which is probably because the dataset only covers up until July of that year, it’s quite evident from this chart that LEGO is now producing more sets than in decades past, which is great news.

Next, we’ll connect this to another resource query, this time for an S3 bucket.

Upload the image file to Amazon S3 Bucket using Retool's built-in Amazon S3 integration

To make it easier to reference the URL in our email, we’ll add another little Python code block to clean up the S3 URL, and we’ll grab the link to our file.

Extract the S3 URL from the response sent by Amazon S3 using a Python code block

Finally, we’ll reference that URL in our SendGrid email block.

Send an email using Retool’s built-in SendGrid integration

Here’s what the final workflow looks like:

Bird's eye view of the final Python workflow to visualize the number of LEGO sets released over the years

Testing and deploying the workflow

Now that we have the workflow setup, we can test it by clicking on the “Run” button at the top. This will manually run the entire workflow, so you can test if it’s functioning correctly. Once you’re satisfied, you can enable and deploy it, so it will fire off using the settings in the StartTrigger block.

Analysis 2: Average number of pieces per LEGO set over the years

We can follow the same process using this code to get the average number of pieces per LEGO set over the years.

import pandas as pd 
import seaborn as sns 
import matplotlib.pyplot as plt 
import io 

# create a pandas dataframe called lego_sets using the lego set data retrieved in the previous block
lego_sets = pd.DataFrame(getLegoSetsData.data) 

# group the dataset by year and calculate the mean of pieces per set for each year
pieces_per_set_per_year = lego_sets.groupby('year')['num_parts'].mean().reset_index()

# create a line plot of pieces per set over the years
plt.figure(figsize=(14, 6)) # set the figure size
sns.lineplot(x="year", y="num_parts", data=pieces_per_set_per_year) # set the x and y axis, and the data for the plot

# add title and labels to the plot
plt.title("Average Number of Pieces per Set over the Years") 
plt.xlabel("Year") 
plt.ylabel("Average Number of Pieces per Set") 

# rotate x-axis labels for better readability
plt.xticks(rotation=90) 

# display the plot
plt.show() 

# save the plot as a PNG image in a file buffer
file_buffer = io.BytesIO() 
plt.savefig(file_buffer, format='png') 
file_buffer.seek(0) 

# get the image data from the file buffer
image_data = file_buffer.getvalue() 

# return the image data
return image_data

Visualization of the Average Number of Pieces per LEGO Set over the Years

We can see from this chart that the sets are becoming larger on average. More LEGOs!

🧱

Fun Fact: As of 2021 the largest LEGO set in terms of the number of pieces is the LEGO Art World Map set, which contains 11,695 pieces.

Putting the pieces together

To wrap up, with Retool Workflows’ support for Python, we were able to analyze a LEGO dataset using popular Python libraries like pandas, seaborn, and matplotlib to create visualizations that helped us gather some pretty interesting insights.

With Retool’s native integrations for Amazon S3 and SendGrid, we were able to have the workflow upload the images to an S3 bucket and send the image in an email—all automatically.

The libraries we used in this post are just the tip of the iceberg. Retool comes pre-installed with over 20 popular Python libraries and packages, including openai, beautifulsoup4, and pillow, making it a powerful tool for any data science project.

I hope you found this post helpful in demonstrating how to use Retool Workflows to automate your data analysis projects. Questions, comments, or favorite LEGO sets? Feel free to reach out to me on Twitter.

P.S. My favorite LEGO set is the Seinfeld set—it has a total of 1,326 pieces!

Building a Custom Amazon EC2 Instance Admin Panel for DevOps with Retool

Amit Jotwani — Wed, 01 Mar 2023 20:41:00 +0000

Custom scripts and tools are a common way for DevOps professionals to manage resources within their infrastructure.

These scripts, however can become challenging to run, share, scale, and maintain, particularly as the complexity of the infrastructure grows and the team size expands.

In this blog post, we'll explore how you can use Retool to build custom easy to use admin panels that can simplify your common DevOps processes.

We will use an example of managing Amazon EC2 virtual servers to demonstrate how Retool can help you build a custom tool that simplifies common tasks, such as launching new instances, starting and stopping instances, and checking instance status.

By the end of this post, you'll understand how you can use Retool’s drag-and-drop UI components and built-in integrations to quickly convert common DevOps processes into flexible software accessible to your entire team.

For the impatient - feel free to simply download the JSON definition of this Retool app, which you can import into your own free Retool instance.

Let’s get started!

Connect Retool with Amazon EC2 API

Log in to Retool and create a new Resource for Amazon EC2 API using Retool's built-in REST API integration. We will be using Retool’s built-in AWS v4 authentication, so we wouldn’t need to worry about writing complex authentication code. Retool will handle that for us in a secure way.

Name -> Amazon EC2
Base URL -> https://ec2.amazonaws.com
Headers -> Accept: application/json
Authentication -> AWS v4
AWS Region -> <YOUR AWS REGION>
AWS Access Key ID -> <YOUR AWS Access Key ID>
AWS Secret Key ID -> <YOUR AWS Secret Key ID>

💡

Note: You would need an AWS Access Key ID and AWS Secret Key ID for the IAM role or user with the AmazonEC2FullAccess policy attached for this step.

Setting up the Resource for Amazon EC2 API in the "Resources" tab

Create a new Retool app

Now that we have connected Retool with Amazon EC2 API, we are ready to build the actual user interface to display it. But before we add UI components to our canvas, let's look at the data we would need to build out the functionality for our EC2 Instance Manager, and how we will get it.

List of EC2 Instances - Send GET request to EC2 API's DescribeInstances Action.
Launch a new EC2 Instance - Send POST request to EC2 API's RunInstances Action
Stop an Instance by Instance ID - Send POST request to EC2 API's StopInstances Action, along with the Instance ID.
Start an Instance by Instant ID - Send POST request to EC2 API's StartInstances Action, along with the Instance ID.

Part 1: Displaying the list of EC2 Instances in a table

We will create a new GET Resource Query for the EC2 API’s DescribeInstances action.

Setting up the getAllEC2Instances Resource Query

Let’s name it getAllEC2Instances. We will choose the resource we created earlier for Amazon EC2 API as the Resource. Choose the Action Type as GET. The base URL for the Resource Query will automatically be set to ec2.amazonaws.com. Add this as URL params -

?Action=DescribeInstances&Filter.1.Name=instance-type&Filter.1.Value.1=t2.micro&Filter.2.Name=architecture&Filter.2.Value.1=x86_64&Version=2016-11-15

In this request, we are using two filters to search for instances with specific attributes -

instance-type: Instance type refers to an EC2 template that specifies the amount of resources (such as CPUs and memory) that a virtual machine will have. In this case, the value for the instance-type filter is "t2.micro", which are instances that have 1 vCPU and 1GB of memory.
architecture: Architecture refers to the type of processor used by the instance. In this case, the value for the architecture filter is "x86_64", which refers to a 64-bit processor architecture commonly used in personal computers.

Let’s try out this query by hitting the Preview button. You will see the Response of the query in the panel under the query.

Parsing the XML returned by Amazon EC2 API

The Amazon EC2 API returns results in XML format. Luckily, Retool parses that XML automatically for us, and presents that in a JSON format. You can view this data by clicking on the State tab in the left sidebar. The instance data we are looking for is nested under data -> parsedXML -> DescribeInstanceResponses -> reservationSet -> 0 -> item

Viewing the result of the EC2 API in the State tab

Transforming the data to make it easy to navigate

The XML data returned by the EC2 API is deeply nested, making it difficult to extract the relevant information for each instance. So, we will write a bit of custom JavaScript code to “transform” the data into an easily readable array of objects using a JavaScript Transformer.

The fields we want to display in our table are - Architecture, DNS Name, Instant State, Image ID, Reservation ID, IP Address, Instance Name, Launch Time, Instance ID, Group Name, and Region.

So, let’s add a new Transformer with this JavaScript code to simplify the data into an array of objects that contain only the relevant information we need for each instance, making it much easier to read and work with. We will then use the output of this function to power other parts of our app, starting with the Table.

Add a new Transformer to "transform" aka restructure the data returned by the EC2 API

// Store data from the EC2 API in the variable data
const data = {{getAllEC2Instances.data.parsedXml.DescribeInstancesResponse.reservationSet['0'].item}};

// Initialize an empty array to hold final data
let finalData = [];

// Function to capitalize the first letter of a string
function capitalizeFirstLetter(string) {
  return string.charAt(0).toUpperCase() + string.slice(1);
}

// Loop through each key in the data object
Object.keys(data).forEach(key => {

  // Retrieve specific data points for each instance and assign to variables
  let architecture = data[key].instancesSet[0].item[0].architecture[0];
  let dnsName = data[key].instancesSet[0].item[0].dnsName[0];
  let instanceState = capitalizeFirstLetter(data[key].instancesSet[0].item[0].instanceState[0].name[0]);
  let imageId = data[key].instancesSet[0].item[0].imageId[0];
  let reservationId = data[key].reservationId[0];
  let ipAddress = typeof(data[key].instancesSet[0].item[0].ipAddress) !== 'undefined' ? data[key].instancesSet[0].item[0].ipAddress[0] : 'N/A';
  let instanceName = typeof(data[key].instancesSet[0].item[0].tagSet) !== 'undefined' ? data[key].instancesSet[0].item[0].tagSet[0].item[0].value[0] : 'N/A'; 
  let launchTime = data[key].instancesSet[0].item[0].launchTime[0];
  let instanceId = data[key].instancesSet[0].item[0].instanceId[0];
  let groupName = data[key].instancesSet[0].item[0].groupSet[0].item[0].groupName[0];
  let region = data[key].instancesSet[0].item[0].placement[0].availabilityZone[0];

  // Create a new object containing all relevant data points
  let newObj = {
    'architecture': architecture,
    'dnsName': dnsName,
    'instanceState': instanceState,
    'imageId': imageId,
    'reservationId': reservationId,
    'ipAddress': ipAddress,
    'instanceName': instanceName,
    'launchTime': launchTime,
    'instanceId': instanceId,
    'groupName': groupName,
    'region': region
  };

  // Add new object to the finalData array
  finalData.push(newObj);
});

// Log finalData to the console and return it
console.log(finalData);
return finalData;

Click on Preview to see the results of this transformer. As you can see, it’s much cleaner and easier to read and navigate.

Click on Preview to see the results of this transformer as a JSON object

Displaying the data in the table

Drag the Table Component on to the canvas, and change the Data property to reference our formatted EC2 instance data using Retool's double curly brace data binding to {{transformInstanceData.value}} .

Changing the data property of the table to refer to the results of the JavaScript Transformer

The table will be populated with the data for your EC2 instances. Nice, we are on our way now with the list of EC2 instances being listed. But, it's a bit hard to know the status of these instances at a quick glance. Let's fix that.

Populate table data with the list of EC2 Instances

Add background colors for “running”, and “stopped” instances

To make it easier to see the state of the instances, let’s add a background color to the Instance State column of our table. Change the Background property of the Instance State column to this conditional -

{{self.toLowerCase() === "running" ? 'rgba(165, 255, 110, 0.5)':self.toLowerCase() === "stopped" ? 'rgba(255, 141, 150, 0.5)':'rgba(255, 188, 99, 0.5)'}}

Add background colors for “running”, and “stopped” instances

If the state is “running”, then the color code rgba(165, 255, 110, 0.5) is returned, representing a light green color with a 50% transparency.

If the state is “stopped”, then the color code rgba(255, 188, 99, 0.5) is returned, representing a light red color with a 50% transparency.

Add background colors for “running”, and “stopped” instances

Part 2: Launching a new EC2 Instance (POST RunInstances)

Create a new POST Resource Query for the EC2 API’s RunInstances action.

We will eventually add a Listbox component to select the image type, but for now, let's hardcode the image ID to ami-0dfcb1ef8550277af, which is a Linux image provided by Amazon in the AWS Free Tier called Amazon Linux 2 AMI (HVM) - Kernel 5.10.

launchNewEC2Instance

Action Type -> POST
Headers -> application/x-www-form-urlencoded; charset=utf-8
Body -> x-www-form-urlencoded
Body Params:
Action -> RunInstances
ImageId -> ami-0dfcb1ef8550277af
InstanceType -> t2.micro
MinCount -> 1
MaxCount -> 1
Version -> 2016-11-15

Setting up the launchNewEC2Instance Resource Query

Now that we have the query for launching a new instance, we can drag a button component to the canvas, and connect this query to it by creating an event handler for the button, and setting its Click event to fire off the launchNewEC2Instance query.

Connect the button's "Click" event handler to the query

Refresh the table after launching a new instance

To ensure that the table automatically displays the newly launched instance, we will add getAllEC2Instances query to the Success section of the launchNewEC2Instance query, so it’s fired off automatically when a new instance is created successfully.

Get the list of EC2 Instances when a new instance is launched successfully

That's it. Let's test it. Clicking the Launch new EC2 Instance button should launch a new T2.Micro EC2 instance using the image ID - ami-0dfcb1ef8550277af, and the table should automatically be updated with the status of the new instance set to "Pending" (it can take a few seconds for the new EC2 instance to complete spinning up, at which point the status will automatically change to "Running")

Part 3: Stop and Start an EC2 instance

To stop and restart our existing instances, we will add two new Resource Queries, which will use EC2 API's StopInstances and StartInstances actions. We will then connect these queries to two actions buttons in the table.ru

Stopping an EC2 Instance

Let's create a query called stopEC2InstanceByInstanceID, using the configuration below:

Action Type -> POST
Headers -> application/x-www-form-urlencoded; charset=utf-8
Body -> x-www-form-urlencoded
Body Params:
Action -> StopInstances
InstanceId.1 -> {{ table1.selectedRow.data['Instance ID'] }}
Version -> 2016-11-15

Setting up the stopEC2InstanceByInstanceID Resource Query

(re)Starting an EC2 Instance

Next, create a query called startEC2InstanceByInstanceID, using the same configuration as above, except the Action, which should be StartInstances

Setting up the startEC2InstanceByInstanceID Resource Query

Adding Buttons to the table to stop/start instance

In the Table’s inspector, navigate to the Actions section, and add two buttons for Start , and Stop.

Adding Action Buttons for each row of the table for "Starting" and "Stopping" Instances

Then, set the Action query property for these buttons to their respective queries we created earlier - Stop button (stopEC2InstanceByInstanceID), Start button (startEC2InstanceByInstanceID).

Set Action query property for “Stop” action button to stopEC2InstanceByInstanceID query

Set Action query property for “Start” action button to startEC2InstanceByInstanceID query

Refresh the table after starting or stopping an instance

To ensure the table automatically displays the updated status of the instance after stopping/starting an instance, we will add getAllEC2Instances query to the “Success” section of the startEC2InstanceByInstanceID and stopEC2InstanceByInstanceID queries, so it’s fired off automatically when an instance stops or starts successfully.

Part 4: Add a Listbox Component to choose the type of instance to launch

Finally, let’s add a Listbox component to the canvas, so we can choose the type of image we would like to launch.

Add a Listbox Component to allow choosing of instance type

Getting the Image Data for the Listbox

You can use Amazon EC2’s DescribeImages action to get details about the images. But since we only want to list the images available in the free tier, instead of calling an API, we will simply create a JSON object with the list of images we want to display.

Write a JavaScript Query that returns a simple JSON object for the images

Create a new JavaScript query called imageDataJSON and paste this code into it.

const imageData = [
  {
    "id": 1,
    "image_id": "ami-0c2b0d3fb02824d92",
    "image_title": "Microsoft Windows Server 2022 Base",
    "show_flag": true
  },
  {
    "id": 2,
    "image_id": "ami-0dfcb1ef8550277af",
    "image_title": "Amazon Linux 2 AMI (HVM) - Kernel 5.10",
    "show_flag": true
  },
  {
    "id": 3,
    "image_id": "ami-0c9978668f8d55984",
    "image_title": "Red Hat Enterprise Linux 9",
    "show_flag": true
  },
  {
    "id": 4,
    "image_id": "ami-0557a15b87f6559cf",
    "image_title": "Ubuntu Server 22.04 LTS",
    "show_flag": true
  }
];

return imageData;

Pre-load the Image data when the app launches

To ensure our Listbox is automatically populated when the app opens, we will check the box Run this query on page load? in the advanced tab for the imageDataJSON query.

Enabling "Run this query on page load" ensure the data is available when the page loads

Add the Listbox Component to show the list of images available for launch

Now that we have the data ready for the EC2 images we want to be able to choose from, we are ready to build the UI for it.

Drag a Listbox component to the canvas, and change the Data Source property to reference the data returned by the imageDataJSON JavaScript query we just created. Also, change the value and label properties of the listbox to item.image_id (value that should be passed when the item is selected) and item.image_title (label value that should be displayed in the Listbox) respectively.

Setup the Listbox Component to display EC2 Image Data returned by imageDataJSON query

Update the launchNewEC2Instance query to reference the image selected in the Listbox Component

Finally, we will update our launchNewEC2Instance to reference the image selected in the Listbox Component by changing the ImageId URL parameter to {{ listbox1.selectedItem.image_id }}.

Reference the image selected in the Listbox to launch a new EC2 instance

Wrapping up

Congratulations! After this long journey, you now have your very own Amazon EC2 Instance Manager. With the power to stop, start, and launch instances, and choose from a variety of EC2 images, you've become a DevOps superhero.

To wrap up this post, we used the example of an EC2 Instance Manager to explore how Retool's drag-and-drop UI components and built-in integrations can help you quickly build custom admin panels that simplify common DevOps tasks.

We went through how to add a Resource for Amazon EC2 API, launch and manage EC2 instances, how to change the background color of columns to reflect the instance state data using RGBA CSS color codes, and how to keep the table refreshed by firing off the list EC2 instances query as a success query trigger.

I hope you enjoyed this post, and that it has inspired you to explore Retool further and use it to create your own custom admin panels for managing virtual servers, databases, or other infrastructure resources.

If you’d like to build a similar app, here’s the JSON you can import into your own free Retool instance.

Thanks for reading, and happy Retooling!

How I Built a Dashboard to Visualize My Amazon Spending using Retool and Google Sheets

Amit Jotwani — Wed, 01 Feb 2023 19:33:13 +0000

My wife and I became parents last year, and let me tell you - babies aren’t cheap. Amazon has profited handsomely from our baby business in recent months. As the little brown boxes piled up, we wondered how much we'd spent on Amazon in general, and on baby supplies in particular. What categories did we spend the most on? What was our biggest baby-related expense? (Spoiler - it was the “SlumberPod 3.0 Portable Privacy Pod Blackout Canopy Crib”)

To answer these questions, I had some mystery-solving to do. I found that Amazon provides a download of your purchase history as a CSV file. With that data at my fingertips, I wondered if I could build a simple “Amazon Purchases Insights Dashboard,” which, at a glance, would provide insights like the total money we spent on Amazon last year, money spent on each category, most expensive month, and more. With Retool, it turned out to be pretty simple.

An Amazon spending data dashboard, powered by Retool

In this post, I'll walk you through how I built this dashboard using a combination of Google Sheets, Retool, and even a splash of ChatGPT. Together, we will build a dashboard that can help us answer the following questions.

How much money did we spend on Amazon in 2022?
How much money did we spend each month in 2022?
What were the reasons for the high expenses in June 2022?
What category did we spend the most on in 2022?
How many items did we return or refund in 2022?

Whether you are a curious parent or just a data nerd, buckle up for a wild ride through Amazon spending data and visualization in Retool. For the impatient - feel free to simply download the JSON definition of this Retool app, which you can import into your own free Retool instance. Let’s get started!

Getting purchase history data from Amazon

This part was pretty easy. Amazon provides a way for you to download your purchase history in CSV format through the “Order History Report” page. For this analysis, I went with “Items" as the report type and chose the start date and end dates as Jan 1, 2022, to Dec 31, 2022. The reports usually take only a few seconds to generate, but if you have lots of orders, it may take longer.

A cutting edge and gorgeous UI for generating Amazon spending data

Once I had the CSV file, I imported it into Google Sheets using Google’s import feature.

Google Sheets allows you to import Sheet data from a CSV on your filesystem

After you import the data, it should look something like this.

We now have a Google Sheet with our Amazon purchase history

Connect Retool with Google Sheets

Now that the data is loaded into a Google Sheet, we are ready to connect it to Retool. Login to Retool and create a new Resource using the built-in Google Sheets integration. Follow the prompts to authenticate with Google. Here’s what the resource would look like once you have successfully authenticated.

Creating a Retool resource for the Google Sheet that contains your Amazon purchases

Create a new Retool app

Now that our purchase data is ready in Retool, we need to build the actual user interface to display it. Our Retool app will need a JavaScript query to fetch and format the Amazon data, which we will then need to access in several user interface components. Let's grab the data first.

Writing a JavaScript query to fetch our data

Before building the front-end, I did a quick mapping exercise to determine what data I would need for the insights I was trying to get. It turned out I could build the entire dashboard with a single resource query to load the data from Google Sheets, and three JavaScript transformers to format the data conveniently for my dashboard.

getDataFromGoogleSheets (query): This is the master query that pulls all the data I need from Google Sheets. It will act as the base for my transformers, which will aggregate specific insights.
getOrdersByMonth (transformer): This transformer will help us answer the question - "How much money did we spend each month?" It will return the total amount spent and a list of orders for each month.
getAnnualSpent (transformer): This will help us answer the question - "What's the total amount of money we spent in 2022?". It will return the total amount spent each month. We can add them together to get the annual spending.
getOrdersByCategories (transformer): This will help us answer the question - "How much money did we spend on each category?". It will return the total amount spent and a list of orders for each category.

Let’s create these four parts of the app.

Querying for all the data in our Google Sheet

Create a new Retool app, and then create a new Resource query to kick things off.

Creating a new resource query for our Google Sheet data

Next, select the Google Sheets resource you created earlier from the Resource dropdown, and then select the actual spreadsheet that contains your Amazon data. Name this query getDataFromGoogleSheets.

Selecting the Google Sheets Resource created earlier

That's it for this query - you can click the "Preview" or "Run" button to see the data from Amazon flowing in.

Create a transformer to group purchase data by month

Next, create a transformer called getOrdersByMonth - this transformer will reference the results of the getDataFromGoogleSheets query, and then return a JavaScript object that contains data in a convenient format for our UI components.

Creating a JavaScript transformer

Use this code for the transformer.

// Declare and assign the results of getDataFromGoogleSheets query to variable 'sheetData'
let sheetData = {{getDataFromGoogleSheets.data}};

// Declare an array of month names for easy reference
const monthNamesShort = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

// Object to store final data
const finalData = {};

// Loop through each month
for (let i = 0; i < 12; i++) {
  // Filter orders for current month
  const monthOrders = sheetData.filter(function(order) {
    const date = new Date(order["Order Date"]);
    return date.getMonth() === i;
  });

  // Calculate total amount for current month
  const totalAmount = monthOrders.reduce(function(total, order) {
    return total + order["Item Total"];
  }, 0);

  // Add current month data to finalData object
  finalData[monthNamesShort[i]] = {
    orders: monthOrders,
    totalAmount: totalAmount
  };
}

// Log and return finalData
console.log(finalData);
return finalData;

Here’s the shape of the object the transformer would return, containing all orders and their total grouped by month.

Output of the getOrdersByMonth transformer

Create a transformer to display total annual spend

Next, we will create a transformer that calculates and returns our total annual spend. Use the same process as before to create a transformer called getAnnualSpent, and use the following JavaScript code to define it.

// Assign the monthly orders to a variable
let monthlyOrders = {{getOrdersByMonth.value}};

// Initialize the total to zero
let annualTotal = 0;

// Loop through each month in monthlyOrders
for (let month in monthlyOrders) {
  // Log the total amount for the current month
  console.log(monthlyOrders[month]["totalAmount"]);
  // Add the total amount for the current month to the overall total
  annualTotal += parseFloat(monthlyOrders[month]["totalAmount"]);
}

// Return the total for the year
return annualTotal;

This transformer returns a single number rather than an object, which we can reference later in the UI.

Create a transformer to group spend by category

Finally, create the getOrdersByCategories query to group purchase data by category. Use this JavaScript code for the transformer.

// Declare and assign the results of getDataFromGoogleSheets query to variable 'sheetData'
let sheetData = {{getDataFromGoogleSheets.data}};

// Get an array of unique Parent Categories from sheetData
let uniqueParentCategories = [...new Set(sheetData.map(obj => obj["Parent Category"]))];

// Object to store final data
const finalData = {};

// Loop through each unique Parent Category
for (let i = 0; i < uniqueParentCategories.length; i++) {
  // Filter data for current Parent Category
  const categoryData = sheetData.filter(function(data) {
    return data["Parent Category"] == uniqueParentCategories[i];
  });

  // Calculate total amount for current Parent Category
  const totalAmount = categoryData.reduce(function(total, data) {
    return total + data["Item Total"];
  }, 0);

  // Add current Parent Category data to finalData object
  finalData[uniqueParentCategories[i]] = {
    orders: categoryData,
    totalAmount: totalAmount
  };
}

// Log and return finalData
console.log(finalData);
return finalData;

When you preview this transformer, the shape of the object returned should look something like this.

Output of the getOrdersByCategories transformer

Here’s the final set of queries and transformers we have now.

Our final list of one query and three transformers

Now that the data access tier of our application is in place, let's start building the UI.

Connecting our data to UI components

Now that we have the data queries and transformers in place, we can begin designing the dashboard and connecting the UI to this data. To build the UI, let's focus on the questions we posed earlier, and show an answer to each of them.

Question 1: How much money did we spend on Amazon in 2022?

The "quick insights" bar of our Amazon Shopping Analysis dashboard

To build the "quick insights" bar at the top of this application, I used a combination of Statistic and Icon UI components. To bind these Statistic components to the appropriate data sources, change their Primary Value property to the following expressions.

Total Spend in 2022: {{getAnnualSpent.value}}
Monthly Average: {{Math.round(getAnnualSpent.value/12)}}
Average Item Cost: {{getAnnualSpent.value/getDataFromGoogleSheets.data.length}}
Number of Items Ordered: {{getDataFromGoogleSheets.data.length}}
Average Item Cost: {{getAnnualSpent.value/getDataFromGoogleSheets.data.length}}

ℹ️

Analysis: How much money did we spend on Amazon in 2022?

In 2022, we emptied our wallets and spent a whopping $12,000 on Amazon, an average of 42 items per month with an average cost of $25 per item. That’s a lot of brown boxes every month.

Question 2: How much money did we spend each month?

Also, how many items did we order each month, and what was the average item cost? To answer these questions, I decided to build a section that shows the amount of money spent each month and a button that lists all the items for that month in the table below. It also shows a positive/negative trend compared to the previous month.

Monthly view of our Amazon Shopping Analysis Dashboard

To build this section, I used the Statistic component again and made these changes to its properties.

Label: January
Primary Value: {{getOrdersByMonth.value.Jan.totalAmount}}
Caption: Average Item Cost: ${{Math.round(getOrdersByMonth.value.Jan.totalAmount/getOrdersByMonth.value.Jan.orders.length)}}
Number of Items Ordered: {{getDataFromGoogleSheets.data.length}}
Average Item Cost: {(getAnnualSpent.value/getDataFromGoogleSheets.data.length}}
Enable trend color: On

I then repeated this for the other months, replacing “Jan” with “Feb”, “Mar”, “Apr”, and so on.

Setting the Primary value of the Statistics component to the total spend of the month

To add the positive/negative trends for Statistics Components, I turned on “Enable trend color” property for Secondary value, and set the “Positive Trend” property for each of the Statistics components (except January, since it's the first month) to {{self.secondaryValue < 0}}.

_Enabling the trend color property for Statistic Component's Secondary value _

ℹ️

Analysis: Most expensive month

The most expensive month for us turned out to be June, followed by December (which makes sense with the holiday shopping) - but what happened in June? We'll explore that further below.

Question 3: Why were expenses so high in June?

I decided to add a button for each month, which includes the number of items in the button text. When clicked, the button would filter and display the items in the table below.

Clicking on the "XX Items" button in a month will change the contents of the table below.

To do this, add a Button component and change the text to this:

{{getOrdersByMonth.value.Jan.orders.length}} Items

Then add an event handler for that button to control the table, setting its data property to this:

{{getOrdersByMonth.value.Jan.orders}}

Duplicate this button for other months, replacing “Jan” in each of those snippets with “Feb”, “Mar”, and so on.

Configuration for each of our Item buttons in Monthly view

ℹ️

Analysis: What happened in June?

Looking at the items ordered in June, I recalled that we traveled internationally with the baby for the first time, which explains the travel-related shopping. The relatively high amount in January also made sense, given that we were getting ready for the baby’s arrival.

Question 4: What category did we spend the most on?

Also, how much did we spend on the baby category? To answer these questions, I built a “Category view” that shows the amount of money spent each month, along with a button that lists all the items for that month in the table below.

Using ChatGPT to group into broader categories

Amazon includes a pretty wide and very niche set of categories. There were over 185 unique categories in my report, including things like towel, toothbrush, lamp, table, etc. This makes the grouping by category pretty useless.

I wanted to regroup the items into broader "parent" categories, which would be much easier to visualize. I decided on 12 parent categories - Health, Baby, Kitchen, Food, Home, Electronics, Office, Clothes, Books, Toys and Games, Personal, Care, Clothing, Gift.

Instead of trying to do the frustrating task of regrouping 500+ records manually, I ran them through ChatGPT.

Here are the steps I followed to do this for 500+ items:

Create two new columns in your spreadsheet - ChatGPT Output, and Parent Category.

Log in to ChatGPT, and use the prompt below to pass the categories for regrouping. Since ChatGPT has character limits on the replies, I found it most effective to pass 50-60 items at a time, which is still much better than doing it manually.

"Group the following items purchased from Amazon into broader categories such as "Baby", "Kitchen", and "Home". Repeat it for each item even if they're not unique. Use this format - Diaper: Baby"

Copy the output and paste it into a text editor (or pastebin.com) to remove any extra bit of formatting and ensure each item is on a new line.

Pasting output from ChatGPT into a simple text editor to remove any formatting

Now paste this into the ChatGPT Output column in the spreadsheet.

Pasting the raw ChatGPT output into the spreadsheet in the ChatGPT Output column

Now, use the "Split text to columns" feature of Google sheets to split by colon, to get the Parent Category column filled out.

Using the "Split text to columns" feature to get the Parent Category

Repeat this step for other items.

💡

Some items, like Gift Cards, may not have a category assigned in the data provided by Amazon. To avoid corrupting the order of the data returned by ChatGPT, you may want to filter these out before passing them to ChatGPT.

We're now ready to build the category view of our dashboard using the new Parent Category column.

Building the Category View

Category view of our Amazon Shopping Analysis Dashboard

To build this section, I used the Statistic Components again and changed the Primary Value for the Statistic Component to {{getOrdersByCategories.value.Baby.totalAmount}}, and Button Component's Text Property to `{getOrdersByCategories.value.Baby totalAmount}}

Similar to the monthly view, I added a button for each category and changed the text to {{getOrdersByCategories.value.Baby.orders.length}} Items. I then added the event handler to control the table and set the data to {{getOrdersByCategories.value.Baby.orders}} when clicked.

I then repeated this for the other categories, replacing “Baby” with “Home”, “Kitchen”, and so on.

Configuration for each of our Item buttons in Category View

Question 5: How many items did we return/refund?

Refunds view using a new Resource query getRefundDataFromGoogleSheets

To get the answer to this, I had to download the “Refunds” report from Amazon (instead of the “Items” report we downloaded earlier). I followed the same process to create a new Resource Query - getRefundDataFromGoogleSheets, and two new transformers called getRefundsByMonth and getAnnualRefunds.

Adding a new query and two additional transformers for Refunds data

The process to create these items is nearly identical to the process for the rest of the dashboard - to see the precise configuration I used, download the JSON definition of this Retool app, which you can import into your own free Retool instance to inspect for yourself.

Final analysis

I shared this dashboard with my wife, and we ran through it together to get some fascinating insights.

Overall, we spent an average of $838 per month , with a return rate of 20%. That was a reminder to be careful of the “Buyer’s remorse,” especially with that easy-to-hit “Buy now” button.
We ordered a total of 506 items at an average of 42 items per month , with an average per-item cost of $24.88. That’s a lot of brown boxes. This was a bit of a shocker. With online shopping so easy, it’s easy to forget how much we’re ordering.
Not surprisingly, the category we spent the most on was baby-related. The category we spent the least on was books, which makes sense given that we had very little time to sleep, let alone read books. We would like to change that this year by reading a bit more.
We agreed this could serve as a good starting point to budget more effectively across categories like home, kitchen, etc., in 2023.
The top 5 most expensive items we bought in 2022 were travel bags, gift cards, and a Slumberpod tent for the baby girl!
The most expensive month for us was June (travel to see family), followed by December (holiday shopping).

Wrapping up and boxing our data visualization adventure

We journeyed through the depths of Amazon purchases and emerged victorious with our "Amazon Purchases Insights Dashboard." We uncovered all sorts of juicy insights, like how much money we spent on baby gear (a lot). Along the way, we learned how to use Retool to create queries and connect them to UI components. If you’d like to build a similar dashboard, here’s the JSON you can import. Also, here’s a sample Google Sheet you can use to start.

I hope this has been helpful and has encouraged you to build your own dashboard. If you end up building it or have any questions, please reach out to me. I would love to help and would be curious to see what insights you uncover.

Happy analyzing!

Did uploading a 6MB file bring down my Ghost site on Digital Ocean?

Amit Jotwani — Tue, 26 Apr 2022 11:07:13 +0000

Woke up to a 504 error this morning. It's hosted on Digital Ocean, so I reached out to support. While their response was prompt, the answer wasn't exactly helpful -

As a self-managed infrastructure as a service (IaaS) provider, we do not have access to your servers so I cannot see your environment. The answer will depend on your webserver and/or database configuration.

They then includes a bunch of not-very-helpful-generic-504-articles from the internets.

So what went wrong with my Ghost site overnight?

I hadn't changed anything on my website, so I knew it probably had nothing to do with webserver/database configurations. A lot of the articles pointed me to the console, but I couldn't even access the console through digital ocean dashboard though. It threw an error of - "Timed out while waiting for handshake".

I then ran into this post (How to restart your Ghost site on Digital Ocean) from Dan Rowden. That gave me a hint that I should look into forcefully restarting my Ghost site. Another quick google search, and I ran into this post - 2 Ways To Reboot a Droplet on DigitalOcean.

Restarting a Droplet on Digital Ocean

To summarize - There are two ways to reboot a Droplet on DigitalOcean, these are:

Gracefully: Similar to that of restarting your machine through the Restart or Shut Down commands.
Forcefully: Similar to holding down the power button on your machine and then powering it back on again.

The graceful way wasn't working out in this case, since I couldn't even access the console. So, I went the forceful way.

Visit the Power tab in the Droplet.
Click the Power Cycle button in the Power Cycle section
Wait for confirmation message and wait a few minutes for the Droplet to reboot.

That fixed it. I could access the console, and the website came right back up.

So why did this happen?

During my troubleshooting, I came across this post on Digital Ocean community, pointing to memory issues being one of the causes for a 504 errors.

This reminded me that I was trying to upload a large size image (>6MB) to my Ghost site yesterday. That was taking forever to load, and timed out each time I tried it. I now wonder if that process consumed most of the RAM, and the server essentially ran out of memory. Rebooting the server must have reset the memory. That makes sense to me. I will go with that argument, and vow never to upload a 6MB PNG to my Ghost site!

How to deploy a Flask app to Digital Ocean's app platform

Amit Jotwani — Tue, 19 Apr 2022 02:19:34 +0000

I recently went through the process to put my side project howbigisthebaby.io on Digital Ocean's new app platform, and wanted to document it in case anyone else found it helpful (or atleast me revisiting this later).

Part 1: Setup the environment for the Flask app

Step 1: Create the virtual environment on your machine

$ virtualenv venv -p Python3

Step 2: Activate the virtual environment

$ source venv/bin/activate

Step 3: Install Flask and gunicorn

$ pip install Flask gunicorn

Step 4: Freeze the requirements in requirements.txt file

Now that you have the flask package installed, you will need to save this requirement and its dependencies so App Platform can install them later. Do this now using pip and then saving the information to a requirements.txt file:

$ pip freeze > requirements.txt

Step 5: Write code for your Flask app in app.py. Here's a simple "hello world" app

from flask import Flask
app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello World!'

Part 2: Setting Up Your Gunicorn Configuration

Since we cannot use Flask's built-in server in production environment, we will use Gunicron web server Why Nginx/Gunicorn/Flask?

While lightweight and easy to use, Flask’s built-in server is not suitable for production as it doesn’t scale well and by default serves only one request at a time.
Source: Flask's docs

Step 1: Create the gunicorn config file

$ touch gunicorn_config.py

Step 2: Add this configuration to gunicorn_config.py file

# inside gunicorn_config.py
bind = "0.0.0.0:8080"
workers = 2

Part 3: Pushing the Site to GitHub

The way the Digital Ocean app platform works is that it syncs with a GitHub repo. Any changes to this GitHub repo will automatically be pushed to your app living on Digital Ocean's app platform.

Step 1: Create a new repo on GitHub.com
Open your browser and navigate to GitHub, log in with your profile, and create a new repository called flask-app.

Step 2: Then, create a .gitignore file, and add .pyc so these files are not pushed to GitHub.

$ git init
$ nano .gitignore

# inside .gitignore
.pyc

Step 3: Add files to your repository, and commit the changes

$ git add app.py gunicorn_config.py requirements.txt .gitignore
$ git commit -m "Initial Flask App"

Step 4: Add the GitHub repo you created earlier as the remote repository

$ git remote add origin https://github.com/your_username/flask-app

example: git remote add origin https://github.com/ajot/hbb-flask-webhook

Step 5: Push to GitHub

$ git branch -M main
$ git push -u origin main

Part 4: Deploying to Digital Ocean's app platform

Create a new "app" on Digital Ocean.
Choose GitHub as source
Choose the GitHub repo you just created
Follow the promots to create the app
Edit the run command to this - gunicorn --worker-tmp-dir /dev/shm app:app

Bonus Tip: You can also set environment variables in Digital Ocean, and then access them in your code like this -

airtable_api_key = os.environ.get('airtable_api_key')
airtable_base = os.environ.get('airtable_base')

Resources:
How To Deploy a Flask App Using Gunicorn to App Platform | DigitalOcean

How to speed Alexa skill development using ASK Command Line Interface and Custom Templates

Amit Jotwani — Mon, 11 May 2020 21:44:35 +0000

As a developer, I spend a large amount of my time writing code everyday. The rest, of course is spent debugging that code and “Googling” the internet for answers. But, I digress. All to say, that I am always looking for ways to speed up the development workflows when it comes to “getting started” with something, and then making it easy to “run”, and “deploy” the code.

One area I spend a significant amount of time every week is creating new Alexa Skills from scratch from a learning and education point of view. This is where I’ve found the ASK CLI to be super helpful in speeding up the workflow to create and update Alexa Skills, using the Alexa Skill Kit CLI tool.

What is Alexa Skills Kit CLI?

The Alexa Skills Kit Command Line Interface(ASK CLI) provides a quick way to perform a lot of Alexa skills tasks from the command line interface, like creating new skills, and deploying the code to Alexa Hosted or AWS. You can even simulate your skill from the command line.

Using Templates with ASK CLI to jump start development of a new Alexa Skill

One feature that I like the most about ASK CLI is that it allows you to create a new skill by using your own custom skill templates. So, if you’re like me, you can create a starter template for all your Alexa Skill projects. For example, if there are certain helper functions you use in every skill, it would make sense to include those in the template, so they’re available in the new skill right off the bat.

Creating a new skill using a custom template

To create a new skill using a custom template, you provide the Git URL of the template with the flag --template-url:

The command to create a new skill using a custom template varies a bit based on the version of the ASK CLI. To check the version of ASK CLI, simply type ask -v or ask -V in the terminal.

If you’re using ASK CLI v2
If you’re using ASK CLI v2, you can create a new skill using a custom template using the command below -

$ ask new --template-url https://example.com/example/example-skill.git --template-branch master

Example:
Here’s a CLI v2 compatible I use to jump start my Python based Alexa Skills -
https://github.com/ajot/python-alexa-skill-starter-template-cli-v2.git. If you like, you can use this to create a new skill by using the command below.

$ ask new --template-url https://github.com/ajot/python-alexa-skill-starter-template-cli-v2.git --template-branch master

You may get a warning, like the one below. Type "y" to continue.

"[Warn]: CLI is about to download the skill template from unofficial template. Please make sure you understand the source code to best protect yourself from malicious usage."

This will create the skill locally on your machine. You’re now ready to deploy the skill. See “Deploy” section below.

If you’re using ASK CLI v1
If you’re using ASK CLI v1, you can create a new skill using a custom template using the command below -

$ ask new --template-url https://example.com/example/example-skill.git

Example:
Here’s a CLI v1 compatible template I use to jump start my Python based Alexa Skills -
https://github.com/ajot/python-skill-basic-template.git. If you like, you can use this to create a new skill by using the command below, and optionally, providing a skill name.

$ ask new --template-url https://github.com/ajot/python-skill-basic-template.git -n <optional-name-for-your-skill> -n <optional-skill-name>

You may get a warning, like the one below. Type "yes"

“Downloading skill template from unofficial resource. Please make sure you understand what each script is doing to best protect yourself from malicious usage".

This will create the skill locally on your machine.

Deploying the code to Alexa Developer Console, and AWS Lambda

At this point, we need to locally install any other modules your skill may need. In the case of the Python template I mentioned above, it needs ask-sdk-core package to be installed.

As is generally a good practice, we first need to create/activate a virtual environment. There are several options available for managing virtual environments for Python. I personally use virtualenv, but you can tailor the steps below to match your virtual environment of choice (like Conda).

Create a new virtual environment
Create a new virtual environment with the following command:

$ virtualenv skill_env -p python3

Our virtual environment is created. Now before we start using it, we need to activate:

Activating a virtual environment

$ source skill_env/bin/activate

You will know it worked if you see (skill_env) in front of the command line, like this:
[Image: Screen Shot 2020-05-07 at 8.01.34 PM.png]Install any modules your skill may need
This specific template, for example needs the ask-sdk-core module. So let’s install that inside the virtual environment we created using the Python's pip command -

$ pip install ask-sdk-core

We are now ready to deploy our skill to the developer console, and AWS lambda -

$ ask deploy

Verify and test the skill deployment

1. Verify the skill deployment on the Alexa Developer Console

2. Verify the code deployment on AWS Lambda Console

3. Test the skill in the Alexa Developer Console

You can now test your skill using the "Test" tab inside the Alexa Developer Console at https://developer.amazon.com/alexa/console/ask/test

More Resources for ASK CLI:

ASK CLI Command Reference
Alexa Skills Kit Command Line Interface (ASK CLI) Overview
Quick Start: Alexa Skills Kit Command Line Interface (ASK CLI)

How to make HTTP Requests from an Alexa Skill to Get Data from an External API using Python 3

Amit Jotwani — Fri, 17 Apr 2020 17:20:45 +0000

As a developer, every day we interact with remote APIs and web servers. Almost all services we consume on the internet today are available in the form of an API: weather forecasts, geolocation services, Twitter feeds, and so on.

It’s only fair to imagine, that you may run into situations in which you would want an Alexa Skill to get meaningful data from one of these remote sources by making HTTP requests to their APIs. HTTP requests allow you to fetch data from a remote source. It could be an API, a website, or something else.

In this post, we will take a quick look how you can get external data from an external API from an Alexa Skill using Python 3.

While there are many libraries to make an HTTP request in Python, including http.client (formerly named httplib ), urllib , httplib2 , treq , etc., the Requests library is among the most popular Python libraries for making HTTP requests. The subtitle of “HTTP for Humans” perfectly captures the philosophy behind it. It abstracts the complexities of making requests behind a simple interface, and makes HTTP requests easy and simple.

If you’re using Alexa Hosted as the backend for your skills, there’s an added advantage of the Requests library being built-in, so you don’t need to install or upload it manually. If you’re using AWS Lambda or any other service as the backend, you may need to install the requests module locally, and then upload it to the service.

So, with that out of the way, let’s jump into how you can use the Requests library to make an HTTP request to an API. For the purposes of this tutorial, we will be using the Chuck Norris API. You can obviously replace that with other APIs of your choice.

Using Requests library to make an API call from an Alexa Skill

Step 1: Import the requests package by adding an import statement to the top of lambda_function.py:



import requests

Step 2: Create a helper function to make the call to the API using the endpoint



def getResponseFromAPI():
    url = 'https://api.chucknorris.io/jokes/random'
    response = requests.get(url)
    print(response.status_code) 

    json_data = response.json()
    joke = json_data['value']
    return joke

Here’s a sample of what the data that would get returned by the Chuck Norris API, and would be stored in the variable

Step 3: Call the helper function from inside the LaunchRequestHandler (or any other intent handler that may be appropriate).



class LaunchRequestHandler(AbstractRequestHandler):
    """Handler for Skill Launch."""

    def can_handle(self, handler_input):
        # type: (HandlerInput) -> bool

        return ask_utils.is_request_type("LaunchRequest")(handler_input) or ask_utils.is_intent_name("AMAZON.YesIntent")(handler_input)

    def handle(self, handler_input):
        # type: (HandlerInput) -> Response
        logging.info("LaunchRequest was trigerred")

        joke = getResponseFromAPI()
        return (
            handler_input.response_builder
            .speak(f"{joke}. Would you like to hear another Chuck Norris joke?")
            .ask("Would you like to hear another joke?")
            .response
        )

Hopefully that makes it easy to make API requests from your Alexa Skills. If you're looking to learn more about building Alexa Skills, I’ll leave you with some resources if you’re looking to learn more about Alexa Skills, and the Python requests library in general.

Create an Alexa Skill in Five Minutes - Python & Node - Step-by-step training course from the Amazon team to build your first Alexa Skill
Alexa Skills Kit SDK for Python
Alexa Skills Kit SDK for Node.js