Excel Instructable Tracking - Automated API / Web-Scraping

One of the things I love most about Instructuables is being able to share my creations and see the community response to them. I find it's a great motivator to get more projects completed and out there for the world to see, which is why most of us are here I'm sure! Therefore, being able to track my Instructables and profile stats is a great way for me to keep track of my progress and stay motivated to create more.

This project details how to access the Instructables public API and use HTML scraping to access all of the details on your account to graph/format them all within a single excel document. I have also created this document as a template so all that is required is to download the workbook (from last step) and follow the prompts to add your page URL and page API request URL.

Steps 2 - 6 explain how to use the document template provided, so if you only intend to download the template and use it as is feel free to skip on the rest. (Files in Step 12)

Steps 7 - 11 will detail the creation of the document and how you can integrate similar HTML elements into excel documents.

Also, there is a Youtube video attached at step 1 below walking through the steps to use the template provided, as well as explaining parts of the code afterwards, so please give that a watch also!

Excel - (of course!), and access to internet explorer as well. Other than that it helps to have an Instructables account!

See step by step guided video attached! (Full code rundown included)

The first step is the simplest! Can't go wrong with this. All you need to do is navigate to your profile page and copy your page URL. It should be in the format: https://www.instructables.com/member/Your Page Name/

Once you've copied this go to the template and the "Data" worksheet at the bottom and paste it in the value for the merged cell box in the top left.

Next, we need the request link to the public API of our page. It is as simple as using the inspect element on your profile page, heading to the network tab, and then finding the json file listed that contains the data you're after. For us, this will be in the form: "showAuthorStats…"

Once you find the one, double clicking to open it will present you with a "request URL" that can be copied.

Once we have our API link, it's a simple as importing excel data from web, pasting the link, and inserting it as a table!

First, make sure you have the "Data" sheet open down the bottom. Then we do this through the "Data" tab which should appear on your excel ribbon at the top, then select "From Web". A pop-up box will appear with a space to paste our link and press "Ok". This will open a new pop-up where our data should be displayed. You will then be prompted to convert to table by hitting "Into Table", and lastly you can load by selecting the dropdown under "Close and Load" and pressing "Close and Load To". Finally, the last message box will appear and you can tick the "Existing Worksheet" dot and then select the B5 cell. And that's that!

Note: If your system is running on windows 11, this might look a little bit different once you add the link and press "Ok". You can select "Anonymous" for accessing the web content and select the lowest level to apply to: https://www.instructables.com/member/Your Page Name/. Then select connect and you will move to the same interface as above.

Now, this connection will also be able to be refreshed at a certain time interval or when opening depending on the properties you specify. We want the refresh rate to be every time we open the file since this is how the VBA macro will run. Select the "Data" excel tab again, then "Existing connections" and select the connection we have just made - it should be the only one. Then all we have to do is click properties on the pop-up box and then select: "Refresh data when opening the file".

And that's it! Now make sure macros are enabled and close and save the spreadsheet. Now, every time you open the sheet, all of your data will refresh and you'll be greeted with the complete dashboard! With totals, views, and favorites time averages, as well of all of your individual Instructables which you can sort by performance.

The following video will show you the Instructables web tracking excel sheet I have created and how each user can access their own data from simply adding two URLs. Later on in the video and following steps, I will walk through the code to give you an idea of how the code functions and how else you can use it.

Sometimes, not all of the data visible on the webpage that you are after is neatly laid out in a publicly available API. This is where some web scraping is necessary to obtain this data for use in excel. This can be pretty easily done in excel using the Visual Basic for Applications language - an event-driven language for Microsoft office programs.

The setup is quite simple and just uses the Internet Explorer application. You can route through chrome or other browsers instead but this often means extra steps like installing selenium or other programs that aren't required with the default internet explorer.

The highlighted code above from my VBA script outlines the declaration of "IE" as an object and setting it up as the Internet Explorer application. Changing the visibility then ensures the window doesn't open with the search and finally the URL is navigated to and the page refreshed. Note, the while loops here are to make sure the site finishes loading before we continue.

Now, on the website if you use the inspect element, you can see the HTML elements for the current page. An example is the element for total number of Instructables above. This can be easily read once the IE object is setup above, and with the .getElementsByClassName function. We want the number 11 - the data between the tags - so we use .innertext and there is only the one element so we parenthesize 0, the first index.

This is a really simple way to obtain a lot more detailed or customized data than what is available in the public API.

However, it isn't always so simple. As you can see above, I have shown the HTML code for one of my Instructables that has multiple favorites as well as one that has none at all. Now, because the one without favorites at all has no class="ible-favorites" we can't rely on this to pull our values because it will simply be skipped. To fix this, I use a loop that checks for the location of a space in the "ible-stats-right-col pull-right" class output. If the space is at the start, or 0 position, then we assume that there aren't any favorites and loop to the next list.

As shows in the list elements above, sometimes the data is not set in individual class containers for our easy access and is instead stored in lists. In this case, the data itself is not stored in innertext and therefore we need to make the distinction between innertext and outerHTML elements. innertext describes what is between the tags in the element and outerHTML contains all of the data of the HTML element, which includes the outer tags - where our data is in this case. The VBA loop used to extract the outertext data from each list is shown above as well - this involves looping through each list, and separating the outerHTML string by Chr(34) - aka quotations marks to find our values.

Finally, table values are added using the standard Cells("...").Value function and looping for the number of Instructables and charts are added according to the code above. To make sure alignment is consistent the charts are placed based on the pixel locations of desired reference cells.

And that's it! Please find both the ready to go template attached as a .xlsm file, and the sub script itself in a .txt format if you prefer. Hopefully this is useful and please show me your dashboards under the I Made It section if you do use the tool. Because of the excel base, the possibilities for customization and expansion are pretty limitless as well given the data provided.

Excel Sheet (download before use!)

Note: You may have to unblock the macro by right clicking the file download and going to file properties. There should be a checkbox somewhere to unblock there and apply the change. However, if you prefer the plain text is also attached so you can add it yourself to the workbook scriptsheet.