This post is to acknowledge that I’ve now written 200 of them. A milestone. It has taken me 8 months to write them.
I hope you have enjoyed some of the posts and have received some useful insights/help that have assisted you in your work or interest.
I have enjoyed writing most of these and learning some varied skills along the way, not all in the areas I expected when I started the journey.
A personal thank you to all the people who have posted stuff that has helped me. I have tried, wherever possible to point to the reference Idea or resource in my post to acknowledge the debt I owe in learning that new subject/skill.
In thisd post I’m trying to explore different ways of displaying the posts.
The first table below is setup, using DataTables to list the last 100 posts, similarly to what I’d done when I’d reached the 100 post mark.
This time though as well as doing that, I wanted to try and use Google Sheets as an alternative method to display the information as I’ve currently been exploring the free Google Docs offerings. As I explored that process I also thought I’d do the Excel embed process too and compare them.
This is an embedded excel table with the file on OneDrive. The tabel below is filterable by column. So, if you are familiar with spreadsheet filters, you can use them in the table below
3. Google Sheets Embedded
This is the most limited table of the 3 , as a table, but the strength of google sheet embed is that you can change permissions from View to Edit, so others can download the sheet and then apply filters and even print results. To do that you have to make sure that the correct permissions are set for the embedded sheet. Link to Google Sheet HERE.
the Datatables at the top are dynamic. They are reading the WordPress MySQL database and reading the table for the posts directly. Any changes and those changes will be automatically displayed. The free DataTables also allows for searching, saving to CSV and printing to PDF or printer, which is useful.
The Excel & Google Sheet are displaying the results from a CSV file which is an export of a Query on the MySQL database. So the data they display is as current as when the CSV file is created.
The actual query I hads a few issues on, one of the columns was content, and as this was text, there were spaces, colons, semi colons and other syntax in the text. This caused major problems with the MySQL Workbench export to CSV as this syntax caused all sorts of chaos with comma delimitation between cells. In the end I had to remove the content column (not that I was using it anyway) from the SQL query and the data came out fine.
I also did an inner join on 2 tables so I got the number of views per post. I have not tried this on the DataTables, maybe at the 300 post milestone I may investigate it.
So, as I’ve used an inner join on 2 tables the results in the Excel & Google Sheet have a slightly richer content than the DataTables.
The Excel Embed allows you to query the table online with the filters, unfortunately you cannot export or save the results, which is a bit of a shame.
Another thing I found out, doing this exercise, was that, since I’ve finished my Microsoft Office subscription I can still use the Online Free Office tools so can take the csv export from the Database and use the online excel to set it up on oneDrive with filters so I can still embed the excel table.
Actually , in Sheets the URL links are automatically live for the posts. They are not so in Excel when opening from a CSV file (I dont know about a xlsx) . I had to use =hyperlink (b3) to take the url text and convert it into an acrtive hyperlink.
A bit limited in the embed on the website. Link to Google Sheet HERE.
If you “Share” the sheet with others and give them editing privileges then they can filter the sheet and print, pdf or send to a CSV as well. You can do this also with the Excel Sheet link above too.
I wouldn’t have any issues with sharing this table as its from a static CSV file, so if they mess the file up you can always use a backup. They are not corrupting original data . I would be nervous giving someone direct access to the Database where they could edit the source data though.
These are 3 alternative ways of displaying the 100 posts. I think the DataTables is still the best as its dynamic, although in this instance its only rreading from one table in the database whereas the Excle & Sheets tables are reading from 2 so show more content.
I like the excel filter flexibility in the web page but am disappointing by its limits for output, although now I think I’ll give the link to the source file on OneDrive so that can be downloaded file so that users can create prints, pdfs or other results from their analysis.
Google is the poor cousin in this instance as far as filtering online goes, but it does the automation online process from forms that Excel cannot do, so they both have their strengths and weaknesses.
Thank you all who visit the site, I hope you gain some useful knowledge from it.
Thank you again for those who’s tools, programmes , techniques and learning I’m repeating or using here. I have learned a lot by writing this blog and I hope, by acknowledging your work , it will further your efforts.