Forem: Osvaldo Brignoni

Import API data to Google Sheets

Osvaldo Brignoni — Sat, 25 Feb 2023 23:57:06 +0000

A simple use case

I like using low-code tools to provide simple solutions. I will show you how to import data from any API directly into Google Sheets. This can be useful in a wide variety of scenarios and will work with almost any API that returns data in JSON format.

In the first example, I will import stories from the Hashnode GraphQL API and explain how it works. It is free to use and does not require an authorization key. After that, I will show you three additional examples. You can be creative with this technique and develop your formulas. The possibilities are endless.

Importing stories from the Hashnode API

Let's take a look at the first example. As soon as we visit the spreadsheet, the formula on cell A7 uses the IMPORTJSONAPI function to request the Hashnode API and populates the rows with the results. Notice the Auto-refresh option in the following screenshot. When this option is enabled, the function makes a new request each time you revisit or edit the spreadsheet. In this case, you will always get the latest Hashnode stories.

This formula is made easy by using an open-source Google app script called IMPORTJSONAPI. It provides a custom function to selectively extract data from an API in a tabular format suitable for importing into a Google Sheets spreadsheet.

Installation steps

Access your free Google Sheets account at https://docs.google.com/spreadsheets.
Open Extensions > Apps Script from the top menu.
Create a blank script file called IMPORTJSONAPI.gs.
Copy and paste the entire contents of the IMPORTJSONAPI.gs file from the Github repository found here.
You should now be able to use the =IMPORTJSONAPI() function in your sheet.

About Google Sheets

You are probably already familiar with Google Sheets. I think it is worth going over what makes Google Sheets such a popular tool and how you can leverage Google Apps Script's extensions.

Why is Google Sheets so popular?

Its popularity stems from its user-friendly interface, cloud-based accessibility, and cost-effectiveness, as it is a free tool. Additionally, it offers collaborative features that enable multiple users to work on the same sheet simultaneously from anywhere in the world, making it easy for team members to collaborate on a project. Users can also customize their sheets with various formatting options, charts, and formulas to analyze data and make informed decisions.

What is Google Apps Script?

Google Apps Script is a scripting language that allows you to extend and automate the functionality of various Google Workspace applications, including Google Sheets, Docs, Forms, and more. Some of the benefits of using Google Apps Script include:

Automating repetitive tasks, such as data entry or report generation.
Customizing your Google Workspace applications to fit your specific needs. For example, you can create custom functions or menus to simplify your workflow.
Integrating seamlessly with other Google services, allowing you to easily access and manipulate data from different sources.
Collaborating with multiple users on the same project simultaneously.
Developing powerful scripts with a wide range of capabilities, including sending emails, creating Google Calendar events, accessing Google Drive files, and more.
Free to use, making it a cost-effective solution for individuals and businesses that need to automate and extend their Google Workspace applications.

A closer look at the formula

=IMPORTJSONAPI(
    "https://api.hashnode.com/graphql",
    "$.data.storiesFeed[*]",
    "title, author.username, slug",
    "method=post",
    "payload={
        'query': '{
            storiesFeed(type: FEATURED) { 
                title
                slug
                author {
                    username
                }
            }
        }'
    }",
    "contentType=application/json",
    'Auto-refresh'!$B$1
)

When you read this formula it should be easy to understand the purpose of the arguments. The required arguments are the URL, JSONPath query and columns. The rest of the arguments are optional.

IMPORTJSONAPI Function Arguments

URL

I used the GraphQL API URL https://api.hashnode.com/graphql. You can use any API that returns the data in JSON format.

JSONPath query

JSONPath allows you to write expressions to selectively extract data from JSON objects. I used the $.data.storiesFeed[*] expression to extract the array of stories from the response. Each JSON object in the stories feed will become a row in the spreadsheet. To learn more about how to use JSONPath, you can take a look at the article written by Stefan Goessner, referenced at the end of this article.

Columns

I selected the title, username and slug from each story object. The columns will be populated left to right in that order, starting from the formula's cell.

Parameters

The rest of the parameters are optional but may be necessary for the request to work properly. For example, this GraphQL request requires that we include the query with the request body (payload). The request method and headers are additional parameters you can use. Some APIs may require an Authorization header.

Hashnode stories feed response

{
  "data": {
    "storiesFeed": [
      {
        "title": "On-Demand Code Review With ChatGPT",
        "slug": "on-demand-code-review-with-chatgpt",
        "author": {
          "username": "DamoGirling",
          "name": "NearForm"
        }
      },
      {
        "title": "React-ing to TypeScript",
        "slug": "react-ing-to-typescript",
        "author": {
          "username": "TreciaKS",
          "name": "Trecia Kat "
        }
      },
      {
        "title": "Next.js and Rust: An Innovative Approach to Full-Stack Development",
        "slug": "nextjs-and-rust-an-innovative-approach-to-full-stack-development",
        "author": {
          "username": "joshuamo",
          "name": "Josh Mo"
        }
      }
    ]
  }
}

Other Examples

I explained how importing works by walking through the Hashnode API example. Now I will show you three additional examples with some minor differences.

First, let's take a look at the Serply REST API. It allows us to perform various kinds of web searches and gather market data. In the following example, I used the Serply News API.

Search Google news with Serply API

=IMPORTJSONAPI(
    CONCATENATE("https://api.serply.io/v1/news/q=", ENCODEURL(B3)),
    "$.entries[*]", 
    "title, link, source.title",
    CONCATENATE("headers={
        'X-Api-Key': '", B4, "'
    }"),
    'Auto-refresh'!$B$1
)

As you can see, this example requires an X-Api-Key header. Instead of hardcoding the API key as part of the argument, I concatenated it by referencing cell B4. The following screenshot shows the news populated in the spreadsheet.

I am referencing the value from cell B3 for the search term. CONCATENATE and ENCODEURL are built-in functions in Google Sheets.

Serply API news response

{
    "feed": {
        "generator_detail": {
            "name": "NFE/5.0"
        },
        "generator": "NFE/5.0",
        "title": "\"stock market\" - Google News",
        "title_detail": {
            "type": "text/plain",
            "language": null,
            "base": "",
            "value": "\"stock market\" - Google News"
        },
        "links": [
            {
                "rel": "alternate",
                "type": "text/html",
                "href": "https://news.google.com/search?q=stock+market&hl=en-US&gl=US&ceid=US:en"
            }
        ],
        "link": "https://news.google.com/search?q=stock+market&hl=en-US&gl=US&ceid=US:en",
        "language": "en-US",
        "publisher": "news-webmaster@google.com",
        "publisher_detail": {
            "email": "news-webmaster@google.com"
        },
        "rights": "2023 Google Inc.",
        "rights_detail": {
            "type": "text/plain",
            "language": null,
            "base": "",
            "value": "2023 Google Inc."
        },
        "updated": "Sun, 12 Mar 2023 00:59:28 GMT",
        "updated_parsed": [
            2023,
            3,
            12,
            0,
            59,
            28,
            6,
            71,
            0
        ],
        "subtitle": "Google News",
        "subtitle_detail": {
            "type": "text/html",
            "language": null,
            "base": "",
            "value": "Google News"
        }
    },
    "entries": [
        {
            "title": "Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates - CNBC",
            "title_detail": {
                "type": "text/plain",
                "language": null,
                "base": "",
                "value": "Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates - CNBC"
            },
            "links": [
                {
                    "rel": "alternate",
                    "type": "text/html",
                    "href": "https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5"
                }
            ],
            "link": "https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5",
            "id": "CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s",
            "guidislink": false,
            "published": "Fri, 10 Mar 2023 18:38:00 GMT",
            "published_parsed": [
                2023,
                3,
                10,
                18,
                38,
                0,
                4,
                69,
                0
            ],
            "summary": "<ol><li><a href=\"https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5\" target=\"_blank\">Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates</a>  <font color=\"#6f6f6f\">CNBC</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiSWh0dHBzOi8vd3d3Lm55dGltZXMuY29tLzIwMjMvMDMvMTAvYnVzaW5lc3Mvc3RvY2stbWFya2V0LWpvYnMtcmVwb3J0Lmh0bWzSAU1odHRwczovL3d3dy5ueXRpbWVzLmNvbS8yMDIzLzAzLzEwL2J1c2luZXNzL3N0b2NrLW1hcmtldC1qb2JzLXJlcG9ydC5hbXAuaHRtbA?oc=5\" target=\"_blank\">Silicon Valley Bank Collapse Jolts Stock Market</a>  <font color=\"#6f6f6f\">The New York Times</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiTWh0dHBzOi8vZmluYW5jZS55YWhvby5jb20vbmV3cy9tdWx0aXBsZS1hbGFybS1tYXJrZXRzLWZpcmUtbWF5LTIyMzIyMzAyMC5odG1s0gFVaHR0cHM6Ly9maW5hbmNlLnlhaG9vLmNvbS9hbXBodG1sL25ld3MvbXVsdGlwbGUtYWxhcm0tbWFya2V0cy1maXJlLW1heS0yMjMyMjMwMjAuaHRtbA?oc=5\" target=\"_blank\">Traders Brace for More Market Shocks After Week of Wild Swings</a>  <font color=\"#6f6f6f\">Yahoo Finance</font></li></ol>",
            "summary_detail": {
                "type": "text/html",
                "language": null,
                "base": "",
                "value": "<ol><li><a href=\"https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5\" target=\"_blank\">Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates</a>  <font color=\"#6f6f6f\">CNBC</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiSWh0dHBzOi8vd3d3Lm55dGltZXMuY29tLzIwMjMvMDMvMTAvYnVzaW5lc3Mvc3RvY2stbWFya2V0LWpvYnMtcmVwb3J0Lmh0bWzSAU1odHRwczovL3d3dy5ueXRpbWVzLmNvbS8yMDIzLzAzLzEwL2J1c2luZXNzL3N0b2NrLW1hcmtldC1qb2JzLXJlcG9ydC5hbXAuaHRtbA?oc=5\" target=\"_blank\">Silicon Valley Bank Collapse Jolts Stock Market</a>  <font color=\"#6f6f6f\">The New York Times</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiTWh0dHBzOi8vZmluYW5jZS55YWhvby5jb20vbmV3cy9tdWx0aXBsZS1hbGFybS1tYXJrZXRzLWZpcmUtbWF5LTIyMzIyMzAyMC5odG1s0gFVaHR0cHM6Ly9maW5hbmNlLnlhaG9vLmNvbS9hbXBodG1sL25ld3MvbXVsdGlwbGUtYWxhcm0tbWFya2V0cy1maXJlLW1heS0yMjMyMjMwMjAuaHRtbA?oc=5\" target=\"_blank\">Traders Brace for More Market Shocks After Week of Wild Swings</a>  <font color=\"#6f6f6f\">Yahoo Finance</font></li></ol>"
            },
            "source": {
                "href": "https://www.cnbc.com",
                "title": "CNBC"
            },
            "sub_articles": [
                {
                    "url": "https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5",
                    "title": "Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates",
                    "publisher": "CNBC"
                },
                {
                    "url": "https://news.google.com/rss/articles/CBMiSWh0dHBzOi8vd3d3Lm55dGltZXMuY29tLzIwMjMvMDMvMTAvYnVzaW5lc3Mvc3RvY2stbWFya2V0LWpvYnMtcmVwb3J0Lmh0bWzSAU1odHRwczovL3d3dy5ueXRpbWVzLmNvbS8yMDIzLzAzLzEwL2J1c2luZXNzL3N0b2NrLW1hcmtldC1qb2JzLXJlcG9ydC5hbXAuaHRtbA?oc=5",
                    "title": "Silicon Valley Bank Collapse Jolts Stock Market",
                    "publisher": "The New York Times"
                },
                {
                    "url": "https://news.google.com/rss/articles/CBMiTWh0dHBzOi8vZmluYW5jZS55YWhvby5jb20vbmV3cy9tdWx0aXBsZS1hbGFybS1tYXJrZXRzLWZpcmUtbWF5LTIyMzIyMzAyMC5odG1s0gFVaHR0cHM6Ly9maW5hbmNlLnlhaG9vLmNvbS9hbXBodG1sL25ld3MvbXVsdGlwbGUtYWxhcm0tbWFya2V0cy1maXJlLW1heS0yMjMyMjMwMjAuaHRtbA?oc=5",
                    "title": "Traders Brace for More Market Shocks After Week of Wild Swings",
                    "publisher": "Yahoo Finance"
                }
            ]
        }
    ],
    "ts": 1.8348586559295654,
    "device_type": null
}

If you want to try this example, you can get your API key by creating a free account at serply.io.

Cute puppy images generated with Openai

Let's try something fun and import images generated with Openai. I am selecting only one column: the image URL. Similar to the previous example, I concatenated the search term as the prompt for the request payload. I also concatenated the Openai API key as the Authorization header.

=IMPORTJSONAPI(
    "https://api.openai.com/v1/images/generations",
    "$.data[*]", 
    "url", 
    "method=post", 
    CONCATENATE("payload={
        'n': 3, 
        'size': '256x256', 
        'prompt':'", B3, "' 
    }"), 
    "contentType=application/json", 
    CONCATENATE("headers={
        'Authorization': 'Bearer ", B4, "'
    }"), 
    'Auto-refresh'!$B$1
)

Cute puppy images

Rendering images

An additional formula is needed to render the images. Without it, the script would just populate the rows with URLs. The solution is simple. I used the first formula in column B and the image() function in column A. I combined it with the Arrayformula() function which allows iterating through the rows of a single column to apply the image formula. Both of these functions are built-in into Google Sheets. What I would like for you to take away from this example is that you can process cells after the data has been imported.

=Arrayformula(image(B7:B9))

Openai API image generation response

{
  "created": 1679707702,
  "data": [
    {
      "url": "https://oaidalleapiprodscus.blob.core.windows.net/private/org-mnywxW4v2jQ8xH3mh4x7znhs/user-rLuI09VBzVeNpWdV3HZy0mRm/img-Ks1LtvvPJBWwlbmPVqG8mtiG.png?st=2023-03-25T00%3A28%3A22Z&se=2023-03-25T02%3A28%3A22Z&sp=r&sv=2021-08-06&sr=b&rscd=inline&rsct=image/png&skoid=6aaadede-4fb3-4698-a8f6-684d7786b067&sktid=a48cca56-e6da-484e-a814-9c849652bcb3&skt=2023-03-24T22%3A33%3A50Z&ske=2023-03-25T22%3A33%3A50Z&sks=b&skv=2021-08-06&sig=bwsEsZqY3hrCsT4/6zsyAoIEkasCfZ2xt6mw9jGphTc%3D"
    },
    {
      "url": "https://oaidalleapiprodscus.blob.core.windows.net/private/org-mnywxW4v2jQ8xH3mh4x7znhs/user-rLuI09VBzVeNpWdV3HZy0mRm/img-36HPr7Y1rTSvlf1kMHKhFus0.png?st=2023-03-25T00%3A28%3A22Z&se=2023-03-25T02%3A28%3A22Z&sp=r&sv=2021-08-06&sr=b&rscd=inline&rsct=image/png&skoid=6aaadede-4fb3-4698-a8f6-684d7786b067&sktid=a48cca56-e6da-484e-a814-9c849652bcb3&skt=2023-03-24T22%3A33%3A50Z&ske=2023-03-25T22%3A33%3A50Z&sks=b&skv=2021-08-06&sig=j4sLyf/C0Vfi8FmUCeDFjUHDyothhp2Nc2GCD1xcdRg%3D"
    },
    {
      "url": "https://oaidalleapiprodscus.blob.core.windows.net/private/org-mnywxW4v2jQ8xH3mh4x7znhs/user-rLuI09VBzVeNpWdV3HZy0mRm/img-OnD3pixbq0kEFoDN0LqPuoWP.png?st=2023-03-25T00%3A28%3A22Z&se=2023-03-25T02%3A28%3A22Z&sp=r&sv=2021-08-06&sr=b&rscd=inline&rsct=image/png&skoid=6aaadede-4fb3-4698-a8f6-684d7786b067&sktid=a48cca56-e6da-484e-a814-9c849652bcb3&skt=2023-03-24T22%3A33%3A50Z&ske=2023-03-25T22%3A33%3A50Z&sks=b&skv=2021-08-06&sig=SY2jkUtS2mVIO3Ji%2BZUJeHJl2eMMp53%2B8ncGvwGekBA%3D"
    }
  ]
}

You can get your Openai API key by creating your account at platform.openai.com. You will get a generous amount of free credits to play with.

Funny cat images with Serply API

Here is another fun example using the Serply Image Search API. This is very similar to the Openai example, except these images are not generated. These come from Google Image search results.

=IMPORTJSONAPI(
    CONCATENATE("https://api.serply.io/v1/image/q=", ENCODEURL(B3)),
    "$.image_results[*]",
    "link.title, image.src",
    CONCATENATE("headers={
        'X-Api-Key': '", B4, "'
    }"), 'Auto-refresh'!$B$1
)

Funny cat images

Serply API image search response

{
  "ads": [],
  "ads_count": 0,
  "answers": [],
  "results": [],
  "shopping_ads": [],
  "places": [],
  "related_searches": { "images": [], "text": [] },
  "image_results": [
    {
      "image": {
        "src": "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSpcPBqqdl0GyyJldY3wynsNbK5RVQpBavKabUPq-MRjHxmKHlZTRP_gdog0A&s",
        "alt": ""
      },
      "link": {
        "href": "https://www.google.com/url?q=https://www.nationalgeographic.com/animals/mammals/facts/domestic-cat&sa=U&ved=2ahUKEwiQ4_ym2db9AhWaU2wGHVtZD2sQr4kDegQIBBAC&usg=AOvVaw0i8bYceafnn6-F69n1uHjS",
        "title": "Domestic cat www.nationalgeographic.com",
        "domain": "Domestic cat www.nationalgeographic.com"
      }
    },
    {
      "image": {
        "src": "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSQVJ8iIWM5ZRV2OyXAz2qN-JtpNbHmSgGbF9gkW5yzQx9llnHlAu-zhT-z4A&s",
        "alt": ""
      },
      "link": {
        "href": "https://www.google.com/url?q=https://www.britannica.com/animal/cat&sa=U&ved=2ahUKEwiQ4_ym2db9AhWaU2wGHVtZD2sQr4kDegQIExAC&usg=AOvVaw1SvlVb_lV7ZpZ5ePxNCCmg",
        "title": "Cat | Breeds & Facts |... www.britannica.com",
        "domain": "Cat | Breeds & Facts |... www.britannica.com"
      }
    }
  ],
  "total": null,
  "knowledge_graph": "",
  "related_questions": [],
  "ts": 2.8050615787506104,
  "device_type": null
}

Again, I am using the same formula to render images here.

=Arrayformula(image(C7:C20))

Endless possibilities

The Google Apps Script platform is a very powerful scripting language and automation tool. I only scratched the surface of what you can do with it. You can build sophisticated solutions that are easy to use for Google Sheets users and seamlessly integrate with other applications within and outside the Google Workspace ecosystem.

Reference Links

Serply API Spec Documentation

Serply API - Google News

Serply API - Google Images

Openai Documentation - Image Generation

Hashnode API

Introduction to JSONPath expressions by Stefan Goessner

JSONPath Plus open-source repository

IMPORTJSONAPI open-source repository

App Script Samples

Serply Notifications Part 1: Search Engine Result Pages (SERP)

Osvaldo Brignoni — Sat, 11 Feb 2023 03:58:30 +0000

What is Serply Notifications?

Serply Notifications is an open-source notification scheduler for the Serply API. It allows you to schedule and receive notifications for Google Search Engine Result Pages (SERP) on your Slack account.

The application is developed with AWS serverless services: API Gateway, Lambda, DynamoDB and EventBridge. All of its resources are defined as a Cloud Development Kit Stack. This is a detailed walk-through of all its components. First, let's look at the application features from the user's point of view.

As a Slack user, I can...

Schedule SERP notifications for specific search queries.
Receive notifications daily, weekly or monthly.
List notification schedules.
Disable and re-enable scheduled notifications.

How can you track the SERP position of a website?

To track the position of a website in Google Search Engine Result Pages (SERP), you can use a few methods:

Manual Search : You can manually search for a specific keyword related to your website and note the position of your website in the SERP.
SERP Tracking Tool : There are various online tools available that can track the position of your website in the SERP. Some popular tools include Ahrefs, SEMrush, Moz, and SERPstat.
Google Search Console : This is a free tool provided by Google that allows you to track your website's performance in Google search. It provides information about the keywords for which your website is ranking and the position of your website for each keyword.

Regardless of the method you use, it's important to track your website's position regularly to monitor its performance and make necessary adjustments to improve its ranking in the SERP.

Example manual search

https://www.google.com/search?q=professional+network&num=100&domain=linkedin.com

Slack /serply command

The command structure is really simple. It starts with the slash command /serply, followed by the sub-command and specific parameters. There are two sub-commands available: serp and list.

/serply serp - schedule a SERP notification

The serp sub-command creates a scheduled notification for the linkedin.com domain with the search query "professional+network". You will see a confirmation message with the Schedule details, shortly after entering the command. You will receive the notification on the specified interval: daily, weekly, or monthly.

/serply list - list all scheduled notifications

The list sub-command returns a list of all schedules.

Disable a scheduled notification

You can disable a scheduled notification by clicking the Disable button on a given Slack message. The history of notifications will remain on the database. You can also re-enable it by clicking the Enable button.

Application Design

Let's start with the request patterns

Since this is an event-driven application, it will be very helpful to plan for all the request patterns before coding. You won't need to code because you can just clone the Serply Notifications repository. However, I do want to go over the thought process for designing this application. In simple terms, this is the sequence of requests.

After a user enters the /serply command...

Slack will perform a POST request to our application webhook with the message payload.
The webhook will respond to Slack with a 200 status code, acknowledging the receipt of the request.
The backend will parse the payload and parameters from the slash command string.
The backend will transform that payload to a Schedule object and send it to the event bus.
A function will receive that event and perform 2 tasks:
Another function will send a confirmation message to Slack letting the user know that the schedule was created.
The Schedule will call another function that performs 3 tasks:
Another function will receive the SERP data from the event bus and send the notification back to Slack for users to see.

DynamoDB: Planning for access patterns

We should also anticipate how we will model, index and query the data. Let's make a list of DynamoDB access patterns and the query conditions that we will use to accommodate them.

Adjacency list design pattern

When different entities of an application have a many-to-many relationship between them, the relationship can be modeled as an adjacency list. In this pattern, all top-level entities (synonymous with nodes in the graph model) are represented using the partition key. Any relationships with other entities (edges in a graph) are represented as an item within the partition by setting the value of the sort key to the target entity ID (target node).

The advantages of this pattern include minimal data duplication and simplified query patterns to find all entities (nodes) related to a target entity (having an edge to a target node).

~ Amazon DynamoDB Developer Guide

Primary Key, Sort Key Design

In addition to the request and DynamoDB access patterns, we need to take into consideration other service constraints.

Map the Slack command to the Schedule database key.
The Schedule Primary Key is the concatenated attributes prefixed with schedule_.
The Schedule Sort Key is intentionally the same as the Schedule Primary Key.
The SERP Notification is similar to the Schedule Primary Key prefixed with notification_ and suffixed with an ISO 8601 datetime string.
Map the Schedule database key to the EventBridge Schedule Name.
The EventBridge Schedule Name has a maximum limit of 64 characters.
The Slack command string and database key attributes might exceed 64 characters.
Generate a deterministic hash from the database key for the EventBridge Schedule Name that is always 64 characters in length.

Example Keys

The Slack command will always produce the same keys. The EventBridge Schedule Name hash is generated from the Schedule PK.

EventBridge Schedule API Reference: Name Length Constraint

Ready for Multi-Tenancy: The Global Secondary Index

In this case, the main reason for creating a Global Secondary Index is that we need to query all the schedules for a given Slack account when we enter the /serply list command. Another benefit of this index is that we could develop multi-tenancy for this application if we need it later. We could scope notifications for each Slack account. For now, there is only one default account ID.

SerplyStack

All the AWS resources are defined in the SerplyStack. When we run the cdk deploy command, the CDK builds the application's infrastructure as a set of AWS CloudFormation templates and then uses these templates to create or update a CloudFormation stack in the specified AWS account and region. During deployment, the command sets up the required AWS resources as defined in the CDK application's code and also configures any necessary connections between the resources. The result is a fully functioning, deployed application in the target AWS environment.

# src/cdk/serply_stack.py

class SerplyStack(Stack):

    def __init__ (self, scope: Construct, construct_id: str, config: SerplyConfig, **kwargs) -> None:
        super(). __init__ (scope, construct_id, **kwargs)

        RUNTIME = _lambda.Runtime.PYTHON_3_9

        lambda_layer = _lambda.LayerVersion(
            self, f'{config.STACK_NAME}LambdaLayer{config.STAGE_SUFFIX}',
            code=_lambda.Code.from_asset(config.LAYER_DIR),
            compatible_runtimes=[RUNTIME],
            compatible_architectures=[
                _lambda.Architecture.X86_64,
                _lambda.Architecture.ARM_64,
            ]
        )

        event_bus = events.EventBus(
            self, config.EVENT_BUS_NAME,
            event_bus_name=config.EVENT_BUS_NAME,
        )

        event_bus.apply_removal_policy(RemovalPolicy.DESTROY)

        scheduler_managed_policy = iam.ManagedPolicy.from_aws_managed_policy_name(
            'AmazonEventBridgeSchedulerFullAccess'
        )

        scheduler_role = iam.Role(
            self, 'SerplySchedulerRole',
            assumed_by=iam.ServicePrincipal('scheduler.amazonaws.com'),
        )

        scheduler_role.add_to_policy(
            iam.PolicyStatement(
                effect=iam.Effect.ALLOW,
                actions=['lambda:InvokeFunction'],
                resources=['*'],
            )
        )

        slack_receive_lambda = _lambda.Function(
            self, 'SlackReceiveLambdaFunction',
            runtime=RUNTIME,
            code=_lambda.Code.from_asset(config.SLACK_DIR),
            handler='slack_receive_lambda.handler',
            timeout=Duration.seconds(5),
            layers=[lambda_layer],
            environment={
                'STACK_NAME': config.STACK_NAME,
                'STAGE': config.STAGE,
            },
        )

        event_bus.grant_put_events_to(slack_receive_lambda)

        slack_respond_lambda = _lambda.Function(
            self, 'SlackRespondLambdaFunction',
            runtime=RUNTIME,
            code=_lambda.Code.from_asset(config.SLACK_DIR),
            handler='slack_respond_lambda.handler',
            timeout=Duration.seconds(5),
            layers=[lambda_layer],
            environment={
                'SLACK_BOT_TOKEN': config.SLACK_BOT_TOKEN,
                'STACK_NAME': config.STACK_NAME,
                'STAGE': config.STAGE,
            },
        )

        slack_notify_lambda = _lambda.Function(
            self, 'SlackNotifyLambdaFunction',
            runtime=RUNTIME,
            code=_lambda.Code.from_asset(config.SLACK_DIR),
            handler='slack_notify_lambda.handler',
            timeout=Duration.seconds(5),
            layers=[lambda_layer],
            environment={
                'SLACK_BOT_TOKEN': config.SLACK_BOT_TOKEN,
                'STACK_NAME': config.STACK_NAME,
                'STAGE': config.STAGE,
            },
        )

        slack_notify_lambda.role.add_managed_policy(scheduler_managed_policy)

        # More resources...

List of CloudFormation resources

AWS::ApiGateway::RestApi
AWS::ApiGateway::Account
AWS::ApiGateway::Deployment
AWS::ApiGateway::Method
AWS::ApiGateway::Resource
AWS::ApiGateway::Stage
AWS::DynamoDB::Table
AWS::Events::EventBus
AWS::Events::EventRule
AWS::IAM::Policy
AWS::IAM::Role
AWS::Lambda::Function
AWS::Lambda::LayerVersion
AWS::Lambda::Permission
AWS::Scheduler::ScheduleGroup

Useful tip

All the CloudFormation resources are well indexed on Google. Searching for any of them will quickly return the relevant documentation. In the documentation, you will be able to see all the available parameters and validation criteria.

API Gateway Rest API /slack/{proxy+}

The CDK creates an AWS::ApiGateway::RestApi resource that will act as a catch-all webhook for all Slack events. The request is forwarded to the slack_receive_lambda function via the LAMBDA_PROXY Integration Request.

Lambda functions

The business logic lives entirely in Lambda functions developed with Python. These functions are triggered by three sources: API Gateway, EventBridge Events and EventBridge Schedules.

src/slack/

slack_notify_lambda
slack_receive_lambda
slack_respond_lambda

src/schedule/

schedule_disable_lambda
schedule_enable_lambda
schedule_save_lambda
schedule_target_lambda

slack_receive_lambda

This function has a few important tasks:

Receives and processes the event forwarded by API Gateway.
Checks the Slack Challenge string if present in the request body.
Verifies the X-Slack-Signature header to make sure it is the Slack app calling our Rest API.
Parses the /serply command string with the SlackCommand class.
Forwards all the data to the SerplyEventBus.
Returns an acknowledgment response to Slack within 3 seconds.

This confirmation must be received by Slack within 3000 milliseconds of the original request being sent, otherwise, a "Timeout reached" will be displayed to the user. If you couldn't verify the request payload, your app should return an error instead and ignore the request.

~ Confirming receipt | api.slack.com

import json
from slack_receive_response import (
    command_response,
    default_response,
    event_response,
    interaction_response,
)

def get_challenge(body):
    if not body.startswith('{') and not body.endswith('}'):
        return False
    return json.loads(body).get('challenge', '')

responses = {
    '/slack/commands': command_response,
    '/slack/events': event_response,
    '/slack/interactions': interaction_response,
}

def handler(event, context):

    challenge = get_challenge(event.get('body'))

    if challenge:
        return {
            'statusCode': 200,
            'body': challenge,
            'headers': {
                'Content-Type': 'text/plain',
            },
        }

    return responses.get(event.get('path'), default_response)(event=event)

Serply Event Bus

An event bus is a channel that allows different services to communicate and exchange events. EventBridge enables you to create rules that automatically trigger reactions to events, such as sending an email in response to an event from a SaaS application. The event bus is capable of triggering multiple targets simultaneously.

Receiving the Slack command

After receiving the initial request from Slack, the slack_receive_lambda function puts an event into the SerplyEventBus.

The event bus triggers 2 functions:

slack_respond_lambda
schedule_save_lambda

slack_respond_lambda

This function sends a SERP Notification Scheduled message to the Slack channel. I could have done this in the slack_receive_lambda function. However, I needed to keep that function as light as possible to acknowledge receipt within 3 seconds. The response message is delegated to the slack_respond_lambda function intentionally.

import boto3
from pydash import objects
from slack_api import SlackClient, SlackCommand
from slack_messages import ScheduleMessage, ScheduleListMessage
from serply_config import SERPLY_CONFIG
from serply_database import NotificationsDatabase, schedule_from_dict

notifications = NotificationsDatabase(boto3.resource('dynamodb'))
slack = SlackClient()

def handler(event, context):

    detail_type = event.get('detail-type')
    detail_input = event.get('detail').get('input')
    detail_schedule = event.get('detail').get('schedule')

    if detail_type == SERPLY_CONFIG.EVENT_SCHEDULE_SAVE:
        schedule = schedule_from_dict(detail_schedule)
        message = ScheduleMessage(
            channel=detail_input.get('channel_id'),
            user_id=detail_input.get('user_id'),
            command=schedule.command,
            interval=schedule.interval,
            type=schedule.type,
            domain=schedule.domain,
            domain_or_website=schedule.domain_or_website,
            query=schedule.query,
            website=schedule.website,
            enabled=True,
            replace_original=False,
        )
        slack.respond(
            response_url=detail_input.get('response_url'),
            message=message,
        )

    elif detail_type == SERPLY_CONFIG.EVENT_SCHEDULE_LIST:
        schedules = notifications.schedules()
        message = ScheduleListMessage(
            channel=detail_input.get('channel_id'),
            schedules=schedules,
        )
        slack.notify(message)

    elif detail_type in [
        SERPLY_CONFIG.EVENT_SCHEDULE_DISABLE_FROM_LIST,
        SERPLY_CONFIG.EVENT_SCHEDULE_ENABLE_FROM_LIST,
    ]:
        schedules = notifications.schedules()
        message = ScheduleListMessage(
            schedules=schedules,
            replace_original=True,
        )
        slack.respond(
            response_url=detail_input.get('response_url'),
            message=message,
        )

    elif detail_type == SERPLY_CONFIG.EVENT_SCHEDULE_DISABLE:
        schedule = SlackCommand(
            command=objects.get(detail_input, 'actions[0].value'),
        )
        message = ScheduleMessage(
            user_id=detail_input.get('user').get('id'),
            command=schedule.command,
            interval=schedule.interval,
            type=schedule.type,
            domain=schedule.domain,
            domain_or_website=schedule.domain_or_website,
            query=schedule.query,
            website=schedule.website,
            enabled=False,
            replace_original=True,
        )
        slack.respond(
            response_url=detail_input.get('response_url'),
            message=message,
        )

    elif detail_type == SERPLY_CONFIG.EVENT_SCHEDULE_ENABLE:
        schedule = SlackCommand(
            command=objects.get(detail_input, 'actions[0].value'),
        )
        message = ScheduleMessage(
            user_id=detail_input.get('user').get('id'),
            command=schedule.command,
            interval=schedule.interval,
            type=schedule.type,
            domain=schedule.domain,
            domain_or_website=schedule.domain_or_website,
            query=schedule.query,
            website=schedule.website,
            enabled=True,
            replace_original=True,
        )
        slack.respond(
            response_url=detail_input.get('response_url'),
            message=message,
        )

    return {'ok': True}

schedule_save_lambda

This function saves the schedule data to the DynamoDB Notifications table.

EventBridge Schedules are not provisioned via the CDK. Instead, there is a specific schedule for each /serply serp command that is created by the schedule_save_lambda function via the boto3.client('scheduler') client.

EventBridge Schedule

import boto3
import json
from serply_database import NotificationsDatabase, schedule_from_dict
from serply_scheduler import NotificationScheduler

notifications = NotificationsDatabase(boto3.resource('dynamodb'))
scheduler = NotificationScheduler(boto3.client('scheduler'))

def handler(event, context):

    schedule = schedule_fro_dict(event.get('detail').get('schedule'))

    notifications.save(schedule)

    scheduler.save_schedule(
        schedule=schedule,
        event=event,
    )

    return {'ok': True}

schedule_target_lambda

This function is triggered by its corresponding schedule and it has 3 tasks.

Get the SERP data from the Serply API https://api.serply.io/v1/serp endpoint.
Save the event and response data to the database as a SerpNotification.
Forward all the data to the SerplyEventBus.

import boto3
import json
from dataclasses import asdict
from serply_api import SerplyClient
from serply_config import SERPLY_CONFIG
from serply_database import NotificationsDatabase, SerpNotification, schedule_from_dict
from serply_events import EventBus

event_bus = EventBus(boto3.client('events'))
notifications = NotificationsDatabase(boto3.resource('dynamodb'))
serply = SerplyClient(SERPLY_CONFIG.SERPLY_API_KEY)

def handler(event, context):

    detail_headers = event.get('detail').get('headers')
    detail_schedule = event.get('detail').get('schedule')
    detail_input = event.get('detail').get('input')

    schedule = schedule_from_dict(detail_schedule)

    if schedule.type not in [SERPLY_CONFIG.SCHEDULE_TYPE_SERP]:
        raise Exception(f'Invalid schedule type: {schedule.type}')

    if schedule.type == SERPLY_CONFIG.SCHEDULE_TYPE_SERP:

        response = serply.serp(
            domain=schedule.domain,
            website=schedule.website,
            query=schedule.query,
            mock=schedule.interval == 'mock',
        )

        notification = SerpNotification(
            command=schedule.command,
            domain=schedule.domain,
            domain_or_website=schedule.domain_or_website,
            query=schedule.query,
            interval=schedule.interval,
            serp_position=response.position,
            serp_searched_results=response.searched_results,
        )

    notifications.save(notification)

    notification_input = asdict(notification)

    event_bus.put(
        source=schedule.source,
        detail_type=SERPLY_CONFIG.EVENT_SCHEDULE_NOTIFY,
        schedule=schedule,
        input={
            **detail_input,
            **notification_input,
        },
        headers=detail_headers,
    )

    return {'ok': True}

Scheduled notification

slack_notify_lambda

This function builds the notification message and sends it to Slack. The SerpNotificationMessage data class serves as a template for formatting the message using Slack Message Blocks.

import boto3
import json
from serply_config import SERPLY_CONFIG
from slack_api import SlackClient
from slack_messages import SerpNotificationMessage
from serply_database import schedule_from_dict
from serply_scheduler import NotificationScheduler

slack = SlackClient(SERPLY_CONFIG.SLACK_BOT_TOKEN)
scheduler = NotificationScheduler(boto3.client('scheduler'))

def handler(event, context):

    detail_schedule = event.get('detail').get('schedule')
    detail_input = event.get('detail').get('input')

    schedule = schedule_from_dict(detail_schedule)

    if schedule.type not in [SERPLY_CONFIG.SCHEDULE_TYPE_SERP]:
        raise Exception(f'Invalid schedule type: {schedule.type}')

    if schedule.type == SERPLY_CONFIG.SCHEDULE_TYPE_SERP:
        message = SerpNotificationMessage(
            channel=detail_input.get('channel_id'),
            serp_position=detail_input.get('serp_position'),
            serp_searched_results=detail_input.get('serp_searched_results'),
            command=schedule.command,
            domain=schedule.domain,
            domain_or_website=schedule.domain_or_website,
            interval=schedule.interval,
            query=schedule.query,
            website=schedule.website,
        )
        slack.notify(message)

    if schedule.interval in SERPLY_CONFIG.ONE_TIME_INTERVALS:
        scheduler.delete_schedule(schedule)

    return {'ok': True}

SerpNotificationMessage Slack Message Blocks

@dataclass
class SerpNotificationMessage:

    blocks: list[dict] = field(init=False)
    domain: str
    domain_or_website: str
    command: str
    interval: str
    query: str
    serp_position: int
    serp_searched_results: str
    website: str
    channel: str = None
    num: int = 100
    replace_original: bool = False

    def __post_init__ (self):

        TEXT_ONE_TIME = f'This is a *one-time* notification.'
        TEXT_YOU_RECEIVE = f'You receive this notification *{self.interval}*. <!here>'

        website = self.domain if self.domain else self.website
        total = int(self.serp_searched_results or 0)
        google_search = f'https://www.google.com/search?q={self.query}&num={self.num}&{self.domain_or_website}={website}'
        results = f'<{google_search}|{total} results>' if total > 0 else f'0 results'

        self.blocks = [
            {
                'type': 'section',
                'text': {
                    'type': 'mrkdwn',
                    'text': f'> `{website}` in position `{self.serp_position or 0}` for `{self.query}` from {results}.'
                },
            },
            {
                'type': 'context',
                'elements': [
                    {
                        'type': 'mrkdwn',
                        'text': f':bell: *SERP Notification* | {TEXT_ONE_TIME if self.interval in SERPLY_CONFIG.ONE_TIME_INTERVALS else TEXT_YOU_RECEIVE}'
                    }
                ]
            },
        ]

Serply API

The schedule_target_lambda function makes a GET request to the Serply API that is equivalent to the following CURL request.

Request

curl --request GET \
  --url 'https://api.serply.io/v1/serp/q=professional+network&num=100&domain=linkedin.com' \
  --header 'Content-Type: application/json' \
  --header 'X-Api-Key: API_KEY'

Response

{
    "searched_results": 100,
    "result": {
        "title": "Why professional networking is so important - LinkedIn",
        "link": "https://www.linkedin.com/pulse/why-professional-networking-so-important-jordan-parikh",
        "description": "7 de nov. de 2016 Networking becomes a little clearer if we give it a different name: professional relationship building. It's all about getting out there ...",
        "additional_links": [
            {
                "text": "Why professional networking is so important - LinkedInhttps://www.linkedin.com pulse",
                "href": "https://www.linkedin.com/pulse/why-professional-networking-so-important-jordan-parikh"
            },
            {
                "text": "Traduzir esta pgina",
                "href": "https://translate.google.com/translate?hl=pt-BR&sl=en&u=https://www.linkedin.com/pulse/why-professional-networking-so-important-jordan-parikh&prev=search&pto=aue"
            }
        ],
        "cite": {
            "domain": "https://www.linkedin.com pulse",
            "span": " pulse"
        }
    },
    "position": 8,
    "domain": ".linkedin.com",
    "query": "q=professional+network&num=100"
}

Reference

GitHub Repository

You can find the repository here. It includes all the installation steps: to set up your Slack app, Serply account and AWS CDK deployment. Deploy it on your AWS account within the AWS Free Tier. You can also fork it and customize it to your own needs.

serply-inc/notifications

Possibilities

Serply Notifications is structured such that all the scheduling is decoupled from the messaging logic using an event bus. More integrations and notification types could be added such as email notifications, other chatbot platforms and more notification types from the Serply API.

Stay tuned for Part 2 of this Serply Notifications series!

The Python Five Minute Journal

Osvaldo Brignoni — Wed, 18 Jan 2023 21:27:52 +0000

"Not another day..."

That was my first thought waking up.

Every day in 2021 and the first quarter of 2022, I felt burned out. I could have blamed many things: the pandemic, a failed startup, or too much work. The root of the problem was... I was not taking care of my mental health.

The Five Minute Journal changed my life

The first time I saw The Five Minute Journal by Intelligent Change in a Tim Ferris YouTube video titled How I Journal and Take Notes | Brainstorming + Focusing + Reducing Anxiety, I thought "This is what I need. It will only take me five minutes. I can easily do this every morning". And I did. Every day since April 2022.

I ordered it from Amazon and received it in three days. On the first day, it took me more than fifteen minutes to fill out. I wasn't sure what to put in each section. I had to think about it for a moment. The next day it took me around three minutes. I got used to it quickly.

Day by day, I felt better and better. I woke up more and more with a positive frame of mind. Within three weeks, that poisonous thought had disappeared. "Not another day..." did not cross my mind ever again. I woke up thinking "Thank you for another day of life".

The journal is pricey. Why should I buy it?

You can pick a blank piece of paper and write every day. You could get a cheaper notebook. I think what makes The Five Minute Journal worth it is its simple structure, daily quotes and weekly challenges. It has pages to last for 6 months.

The words you tell yourself every day

If you wake up every day and tell yourself that you hate your current situation, it's an indication that you need to change it. Repeating your actions will reinforce what you think. Your thoughts will influence your actions and what you say to yourself. It is a feedback loop.

The Four Agreements by Don Miguel Ruiz is one of my favorite books on this subject. Be Impeccable With Your Word, is the most relevant agreement of the four. It's about the impact that your words have on yourself and the people around you.

Be Impeccable With Your Word

Speak with integrity. Say only what you mean. Avoid using the word to speak against yourself or to gossip about others. Use the power of your word in the direction of truth and love.

~ The Four Agreements - Don Miguel Ruiz

The actions you repeat every day

I am a big proponent of daily practice in small increments. Atomic Habits by James Clear is the book that changed the way I think about habits, repetition and its compound effect on learning. In the same way, your repeated frame of mind has an impact on your mental health, every day and it compounds over months. That is why depression can be very insidious. It starts small and builds over time.

Every action you take is a vote for the person you wish to become.

~ Atomic Habits - James Clear

Filling out the journal every morning

The Five Minute Journal allows you to start every day on a positive note. This is the structure for one day. Remember, it is so easy. It takes less than five minutes. But it has a huge impact on the rest of your day.

Morning (recommended)
Evening (optional)

PyFiveMinuteJournal: A simple Python application inspired by the physical journal

After 6 months, I ran out of pages in The Five Minute Journal. I decided to create a Python application inspired by it. These are some of the features.

1. It gets the current date and time

This part is simple but very important. The date is used as the generated file name.

2. It generates a random quote

It requests the Zen Quotes API (https://zenquotes.io/api/today) to get a random quote. That quote is saved in your daily journal.

3. It prompts questions for the morning

It detects when you are filling the journal the first time. It assumes it is the morning and prompts you with the morning questions.

4. It prompts questions for the evening

It detects when you are filling the journal the second time. It assumes it is the evening and prompts you with the evening questions.

5. It stores each day as a Markdown file

The date, the quote of the day, and the questions and answers are all stored in a Markdown file each day grouped in a directory by year.

2022/5MJ-2022-12-31.md
2023/5MJ-2023-01-01.md
2023/5MJ-2023-01-02.md
2023/5MJ-2023-01-02.md
2023/5MJ-2023-01-04.md
2023/5MJ-2023-01-05.md

The markdown file.

Five Minute Journal | Wednesday, Jan 18 2023

> Dwell on the beauty of life. Watch the stars, and see yourself running with them.
> 
> ~ Marcus Aurelius, Meditations

---
07:55 AM

### I am grateful for...
1. My family
2. My incredible friends
3. The warm bed that a sleep in

### What would make today great?
1. Walk around the park
2. Go to the coffeeshop
3. Publish this blog article

### Daily affirmations
1. I traveled to Portugal
2. I am grateful for everything I have
3. I am a better Software Engineer than yesterday

---
09:05 PM

### Highlights of the day
1. Had breakfast with family
2. Met a friend at the coffeeshop
3. Practiced Portuguese with my language partner

### What did I learn today?
1. New Portuguese vocabulary
2. Completed an AWS hands-on lab
3. Studied for AWS Certified SysOps Admin Associate

To Do: Journal Stats

I might add this feature later. I would like to run a stats.py command, parse the journals for a given year and get some statistics like those shown below.

$ python3 stats.py 2023

-------------------------- 2023 JOURNAL STATS ------------------------

JOURNALS COMPLETED
246 of 365 days

TOP 5 WORDS MENTIONED
- Family: 178
- Friends: 109
- AWS: 83
- Portugal: 64
- Study: 52

PLACES MENTIONED
- Portugal: 64
- Spain: 33
- Puerto Rico: 11

AVERAGE SENTIMENT BY MONTH
January: 0.9 positive
February: 0.7 positive
...

I traveled to Portugal

No. I haven't traveled to Portugal yet, as of the date I published this article. I write this affirmation almost every day. Most of the things I have accomplished, I have affirmed for months as if they had already happened.

Affirmations are timeless and powerful. You can affirm where you see yourself in the future. Or you can affirm where you are today compared to yesterday.

Be careful not to think "I have not achieved enough"

Be careful not to turn affirmations into expectations. That can lead to feelings of disappointment. Things take time and hard work. Treat affirmations loosely as a state of mind. Let your daily actions take you a step closer to your goals.

I fill out the journal every day

Consistency is key. It felt great to complete the first journal by hand. I might buy it again. There is something very satisfying about opening the journal in your hands and looking back at six months of pages full of transformational gratitude, optimism and affirmations turned into reality. For now, I've been using the PyFiveMinuteJournal Python application.

It is NOT the be-all and end-all

Some people pray.
Some people meditate.
Some people go for a run.
Some people fill out a journal.
Some people combine all these things.

Find the way that is best for you. The important thing is that you start every day on a positive note. Make your bed, no matter what happened the day before.

I highly recommend it

If you are a developer, you can clone the PyFiveMinuteJournal from the GitHub repository and start journaling on your computer. If you are not into command-line applications or you prefer writing by hand, then order The Five Minute Journal from Intelligent Change. It might change your life.

GitHub Repository

Find the repository here. Take it for a spin.

brignoni/py-five-minute-journal

Take care of yourself

If this article helps just one person, then it would have been worth writing. Be your best friend. Step back from it all. Take a breath. Make time for yourself. And get back to work in a way that is in balance with your life.

Scrape, Convert & Read on Kindle

Osvaldo Brignoni — Sun, 15 Jan 2023 03:32:41 +0000

Why I created PyWebDoc2Ebook?

I enjoy reading tech documentation every day. I am also a big fan of the Kindle e-reader, simply because it helps me to read for hours without eyestrain.

I've been reading a lot of the AWS documentation to study its wide range of services. Some of the documentation is available for Kindle, but the majority is not. That is why I created PyWebDoc2Ebook. It scrapes AWS documentation for a given service and converts it to an EPUB file. I can send that file to my Kindle device and read it offline.

The first version was hard-coded to work with AWS documentation. Then, I refactored it to support any documentation on the web via a plugin system.

Using PyWebDoc2Ebook

Very simply, you can execute the following script with the documentation URL.

$ python3 epub.py \ 
https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html

How does it work?

At a high level, the Python script will take the following steps.

Gets the table of contents.
Downloads the HTML content.
Converts the HTML to Markdown.
Improves the Markdown formatting.
Parses and downloads all the images.
Converts the Markdown file to EPUB.

Each step in detail

Step 1. Gets the table of contents

The script is capable of parsing the table of contents from the initial HTML response or loading a TOC JSON file. In the case of AWS, a toc-contents.json file is available for the documentation of each service. You can see an example here.

Let's take the Amazon Virtual Private Cloud documentation as an example. The following image shows three steps in the conversion of the table of contents. On the left is the original table of contents from the web documentation. The middle shows the console output when running the script. On the right is the resulting table of contents on the Kindle e-reader.

Step 2. Downloads the HTML content

You will see the console output when you run the script. For each TocItem , the URI is resolved to an absolute URL and the HTML is downloaded.

<AWSPlugin domain="docs.aws.amazon.com"/>
<TocItem uri="what-is-amazon-vpc.html"/>
<TocItem uri="how-it-works.html"/>
<TocItem uri="vpc-getting-started.html"/>
<TocItem uri="configure-your-vpc.html"/>
<TocItem uri="working-with-vpcs.html"/>
<TocItem uri="default-vpc.html"/>

Step 3. Converts the HTML to Markdown

The downloaded HTML is converted to Markdown and appended to a single file.

markdown = markdownify(html, heading_style=markdownify.ATX)

Step 4. Improves the Markdown formatting

The script cleans up the markdown content and removes excess whitespace. By defining a markdown() function within a plugin, you can hook into the markdown pre-processing and perform additional modifications before it is converted to EPUB.

def markdown(self, md) -> str:
    # Modify the markdown content before it is converted to EPUB.
    return md

Step 5. Parses and downloads all the images

The documentation often has diagrams, concepts and code samples that are included in the conversion. The script automatically parses all images from the content and downloads them to the resources/ directory. Those image files are included in the final packaging of the EPUB file. These images can be viewed on the e-reader, the same as in the original web documentation.

Processing image: egress-only-igw.png
Processing image: connectivity-overview.png
Processing image: dhcp-custom-update-new.png
Processing image: inter-subnet-appliance-routing.png
Processing image: subnet-diagram.png
Processing image: vpc-migrate-ipv6_updated.png
Processing image: default-vpc.png
Processing image: dns-settings.png

Step 6. Converts the Markdown file to EPUB

The conversion from Markdown to EPUB was made simple with the Pandoc conversion tool. The script is run automatically at the end of the conversion process.

pandoc --resource-path ./resources --metadata \ 
title="What is Amazon VPC" \
author="docs.aws.amazon.com" \
language="en-us" \
-o amazon-vpc.epub amazon-vpc.md

Plugin System

Within a plugin, you can define the patterns of the documentation that you want to scrape and convert. You can drop a custom python plugin into the plugins/ directory and define a custom class that extends the base Plugin class.

Example: AWSPlugin

In the code below, you can see some of the most essential properties.

domain - activates the plugin when a matching URL is requested.
html_content_selector - the DOM tag where the page content is found. This helps ignore the other page sections like navigation, sidebar and footer.
toc_filename (optional) - By default, the table of contents is parsed from the original HTML response. This option allows specifying a filename that explicitly contains the TOC data.
toc_format - html or json . Defaults to html.
toc(response) - you can add one of two functions: toc_html() or toc_json() to define how the table of contents should be parsed and registered.

In the AWSPlugin the TOC is a JSON file with nested items. In this case, the toc_json() function searches recursively for child items and registers them in a flattened array ofTocItem.

from Plugin import Plugin

class AWSPlugin(Plugin):

    domain = 'docs.aws.amazon.com'

    html_content_selector = '#main-col-body'

    toc_filename = 'toc-contents.json'

    toc_format = 'json'

    def toc_json(self, json):

        if 'title' in json and 'href' in json:
            self.add(json['title'], json['href'])

        if 'contents' not in json:
            return self.items()

        for child in json['contents']:
            self.toc(child)

        return self.items()

Included Plugins

AWSPlugin | docs.aws.amazon.com | stable
AzurePlugin | learn.microsoft.com | experimental
GCPPlugin | cloud.google.com | experimental

The challenges of web scraping

There are many know challenges with web scraping. In the context of developing this script, the challenges were:

Some sites lazy load their table of contents with JavaScript. I've considered crawling the content with the Selenium Crawler. I may implement this in the next version of this script. For now, it was simpler to work with the available JSON table of contents.
Different TOC HTML and JSON structures. It was challenging to develop a plugin system that would allow customized parsing in the simplest way possible.
Extracting the relevant links and asset URLs with RegEx. The challenge has been striking a balance between standardizing the plugin interface and its precision. To remedy this, I added the ability to customize the RegEx for links.

The challenges of developing the script

Malformatted content resulting from the conversion from HTML to Markdown. These were corrected via RegEx replacements and the hooks in the plugin system allow for further customized corrections.
Nailing the plugin system was the biggest challenge and required a few days of refactoring and testing out different documentation sites and structures. There is still a lot of room for improvement.

Thanks to great open-source packages

I developed this script by combining these awesome packages. It would not have been possible without them.

BeautifulSoup - https://pypi.org/project/beautifulsoup4/

Used to extract the table of content links and to remove unnecessary content sections with DOM selectors.
Markdownify - https://github.com/matthewwithanm/python-markdownify

Used to convert all downloaded HTML into a single Markdown file.
Pandoc - https://pandoc.org/

Used to convert the Markdown file to an EPUB file and generate the EPUB table of contents based on the Markdown headings.
Ebook Convert by Calibre - https://manual.calibre-ebook.com/generated/en/ebook-convert.html

Used to convert the EPUB file to other formats like MOBI.

Development Status

The most stable plugin is the AWSPlugin. The rest of the plugins are experimental and the overall plugin system might evolve. Eventually, I plan to turn this into a proper Python package and publish it. For now, you can fork or clone it from GitHub and take it for a spin. You are welcome to send issues and pull requests there.

You can find it here.

https://github.com/brignoni/py-webdoc-2-ebook

Final Thoughts

I enjoy developing useful automation tools. For me, this was a great exercise for practicing Python object-oriented programming, designing plugin systems and using various parsing and conversion tools. These are patterns I can reuse in other projects. Overall, it was a very fun experience.