Embedding Google Sheets in webpage

I wrote a post on using Excel filterable Heatmaps for Asset Condition over time online. This was an excel file on my OneDrive that I then embedded in a web page. I had filtered the columns so that you could filter columns to do forecasting for building items that would need work to be done on them in the next 20 odd years. There is a video about it here:

The nice thing about embedding a Document or Spreadsheet, rather than pasting the data into the webpage, is that if you update the source data, the webpage will update to reflect that change. So your web page is dynamic rather than static.

So, I wanted to look at Google Docs in relation to doing the same process. As I’m moving away from a paid office subscription plan to using the free programmes from Google & LibreOffice.

Embedding from Google Docs

Forms

I have already been using the embedding process for Google Forms. This works very well indeed, and the forms are linked to a Google Sheet to capture responses. There is a bit of issue about aesthetics and all the footer information that comes across in the embedded form, and I may look at doing a post on this at a later date. The process works very nicely.

Docs export to .docx

I have used a different process for transferring between Google Docs and WordPress, this is because the formatting is different between a Document in Docs and a page created in WordPress. So for consistency, instead of embedding the Doc in WordPress I’m saving the doc to .docx and then uploading into WordPress with the mammoth plugin. This brings the images across and also changes text to WordPress standard.

Sheets

This is the one I am interested in for embedding into Web pages.  So that I can replicate the Excel example above.

Process

For the process I’ve taken a copy of the excel file and put it on my Google Drive. I’ve then opened the Excel file with Sheets.

I added a filter to the columns so you can sort by date to analyse the data. Then I went to File & Publish to Web.

The Embedment code is :

 <iframe src=”https://docs.google.com/spreadsheets/d/e/2PACX-1vQUnF5qFucEwCmCbZjvUdVgVEEvlHU6NTamOULXbMQI0BXvUM8xR0XirgHnz68ZsZ0mAocjebQ4PtiG/pubhtml?widget=true&amp;headers=false”></iframe>

This ends up with a very small embedded window as shown below.

To change window size we need to add  a width and height to the iframe  adding these to the code  =<iframe width=”100%”, height=”1200″src=”https:/    . Note, the width = “100%” or width = 100% worked so I substituted width = “1600” and that worked fine.

Unfortunately the filters did not come across to the embedded  spreadsheet.
`

So in this situation the data is not filterable.

Also, on the actual web page, the width factor only shows some of the columns:

So, an alternative method for Google Sheets

The above method is trying to replicate what the Excel/OneDrive process does. But this is not working in Google Sheets.

So an alternative way is to add a Link to Spreadsheet to filter . Then others could follow the link to the spreadsheet.

 Now, to do this you have to either make the sheet public, or give the link to others in your team or peers and let them access it.

Also with permissions, do you want them to mess with it or only let them view it?

Within the view mode, when clicking on the link, they have a lot of choices, including setting up a Filter and using that filter on the spreadsheet , as well as downloading the spreadsheet and printing it out.

This is brilliant.

My issue with the Excel Embed document is that you can analyse the data with filters but the only way to keep that setup is to do a screenshot.

I was initially disappointed that the Google Sheet Embedded file did not show filters, but in fact this method is far more versatile and people are able to save their work after filtering the data, either as a PDF or to a Hard Copy.

If they are reasonably confident with Macros they could record the filtering process to show future issues and allow others to play them back.

End comment

This result was not as I anticipated. I was expecting to replicate a previous excel process but ended up with a superior process. Instead of embedding the sheet in the WordPress site I would do a tutorial on how to access, filter, analyse and export the data from the spreadsheet.

I wonder if I should explore the macro recorder in Google Sheets. I think I would need to give a copy of the macro so they could play it through after downloading (or logging in) to be able to access this feature.

I suppose an alternative to both of the above is a YouTube video demonstrating the process and use of the sheet.

This amount of interaction by users was my original intention of the process that the Excel Embed process did not quite achieve.