Covalent API + Google Sheets + NFTs = friends

NFTs are fun and we will explore them with the Covalent API.

In the previous post, I talked about how you could use the Covalent API to extract price data from crypto assets on the ETH chain. In this post, we will go further and explore how you could actually visualize NFTs in Excel!

Ok, so let’s start. First, let’s look into the Covalent docs.

Covalent Blockchain Data API Reference

There, we see a nice example in https://gist.github.com/gane5h/e0e5f9ae2517d353324465d9cbe64ae9

Let us use the same example as in that gist. The requested URL is https://api.covalenthq.com/v1/1/tokens/0xe4605d46fd0b3f8329d936a8b258d69276cba264/nft_metadata/123/

This request gives us a large response, as follows:

{
  "data": {
    "items": [
      {
        "contract_decimals": 0,
        "contract_name": "Meme Ltd.",
        "contract_ticker_symbol": "MEMES",
        "contract_address": "0xe4605d46fd0b3f8329d936a8b258d69276cba264",
        "logo_url": "https://logos.covalenthq.com/tokens/0xe4605d46fd0b3f8329d936a8b258d69276cba264.png",
        "type": "nft",
        "balance": null,
        "quote_rate": null,
        "quote": null,
        "nft_data": [
          {
            "token_id": "123",
            "token_balance": null,
            "token_url": "https://api.dontbuymeme.com/memes/123",
            "external_data": {
              "name": "I wish I was understood",
              "description": null,
              "image": "https://images.dontbuymeme.com/artist-series/fewocious/static/i-wish-i-was-understood.jpg",
              "external_url": "https://dontbuymeme.com/artist-series/fewocious",
              "attributes": [
                {
                  "trait_type": "Set",
                  "value": "Artist Drop 8 - Fewocious"
                },
                {
                  "trait_type": "Artist",
                  "value": "Fewocious"
                },
                {
                  "trait_type": "Type",
                  "value": "Pop Surreal"
                },
                {
                  "display_type": "date",
                  "trait_type": "birthday",
                  "value": 1609267020
                },
                {
                  "trait_type": "Max Supply",
                  "value": "100"
                }
              ]
            }
          }
        ]
      }
    ],
    "pagination": null
  },
  "error": false,
  "error_message": null,
  "error_code": null
}

We see that the “image” element has the URL of the NFT. Hm, interesting. What if we could use the same import JSON function that we used last time to get access to that image URL and somehow visualize it, all in Google Sheets?

Ok, so, here we go! First, we craft a function call:

=importjson("https://api.covalenthq.com/v1/1/tokens/0xe4605d46fd0b3f8329d936a8b258d69276cba264/nft_metadata/123/","data.items.0.nft_data.0.external_data.image")

This function will extract the URL of the NFT image. To visualize the NFT, you do the following:

=image(C9, 2) # cell C9 holds the results from the previous importjson call

And there you have it! If you have done everything correctly, you should see the following response:

That concludes my second article. If you have comments, feel free to contact me.