Embedded Google Sheets have stopped working

[Troy]

Hi. I want to report a recent issue. Embedded Google Sheets worked well until recently, but now they have stopped working. This affected some of my popular threads, like these two:

Tesla Supercharger Stats
Tesla Service Center Stats

As far as I know, Xenforo, the forum software we are using now, does not support embedding Google Sheets by default. This feature was added using an add-on software. To embed a Google Sheet, you would type the key from the sheet's URL within GS tags. Then the add-on would convert that into an iframe URL. The URL would contain a link that starts with "spreadsheets.google.com...". However, a few years ago Google changed their embed links to the "docs.google.com/spreadsheets..." format. The old format was still supported after the change but recently they ended the support. Now the embed codes that contain the "spreadsheets.google.com..." link format don't work anymore.

A possible solution to this problem would be to modify the current add-on so it generates "docs.google.com/spreadsheets..." type links for the GS tags as well. Another solution would be to switch to another add-on. I think this might work: s9e Media BBCodes pack

Embedding Google Sheets was a nice feature we had. I hope we can restore that feature. Thanks.

 

[doug]

Looks like google quit supporting the way were forming the URLs. I'll have to figure out the new way.

 

[doug]

<groan> Ok.. so I updated the gs embed code so it mostly works. But what goes inside the bbcode has to change.

So where as before you might have been able to get away with this:

[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg&chrome=false&gid=1182965810#[/gs]

You now have to do it like this:

[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg[/gs]

Result:

 

[doug]

Hi. I want to report a recent issue. Embedded Google Sheets worked well until recently, but now they have stopped working. This affected some of my popular threads, like these two:

Tesla Supercharger Stats
Tesla Service Center Stats

As far as I know, Xenforo, the forum software we are using now, does not support embedding Google Sheets by default. This feature was added using an add-on software.

I edited the first posts of those two thread to work with my edited bbcode. FYI, I made those as custom codes by hand back when we were using vB and redid for XF when we made the switch. Not using an addon.

 

[Troy]

@doug, that's excellent. Thanks for the super quick solution. I have managed to get the string parameters work under this new format as well. Could you help me out and edit the two opening messages again for me as I can't edit them myself? This will instantly restore the two threads. Thanks a lot.

Thread: Tesla Supercharger Stats
Opening message:

[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=1458216633#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=2069371326#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=1302613061#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=388797483#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=901262383#[/gs]

Thread: Tesla Service Center Stats
Opening message:

[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=508601280#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=2127072447#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=863082372#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=555996876#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=927264087#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=1182965810#[/gs]
[gs]1sxjuCM9VMtLXsIOvw_uSNbUZG4SCY4w7e_inyEDw-Kg/pub?chrome=false&gid=588437048#[/gs]

 

[doug]

done

 

[Troy]

Thanks a lot. That helped me out tremendously.

In case it is something easy to do, it would be great to have a second custom tag for Google Sheets to display a taller iframe than 500 pixels. Let's say it could be called GS750 with an iframe set to 750px. Sometimes 500px is not enough for a single table and I can't glue two 500px embeds together to make a single table because when they are displayed in a forum message there is a gap (padding) between them.

By the way, on an unrelated note, just for your information, in Google Chrome sometimes the forum pages keep loading forever. In the bottom left it says things like "Waiting for track1.aniview.com..." and "Waiting for googleads.g.doubleclick.net".

 

[doug]

Maybe I can get to it next week. The height really should be dynamic on content.

 

[Troy]

Hi. Dynamic height could work but I hope it is possible to try that as a new tag so the current GS tags don't break. For example, in the Tesla Europe Registration Stats I'm using the range parameter in embed codes, like this:

[gs]1Vobg29R1t3FphlWjb8dwG4nkAWqyc_qMwkCjUT8SUno/pub?chrome=false&gid=1805961368&range=A3:I22#[/gs]
[gs]1Vobg29R1t3FphlWjb8dwG4nkAWqyc_qMwkCjUT8SUno/pub?chrome=false&gid=0&range=A5:V24#[/gs]

The first code displays a small section of a huge table here. The second code displays one of three tables on the same tab here. The 500px height works fine in most situations and I'm happy with it but sometimes 750px would be great. I haven't seen anybody else use the GS code. I'm not sure implementing the dynamic height idea is worth the effort. My idea was because you already have the custom "GS" tag, duplicating that and renaming the duplicate to "GS750" and changing the height to 750px would be easy. I'm 90% happy with what we have now. I would be 98% happy if we had the GS750 tag as well but if you want to try the dynamic height idea, why not.

By the way, I think the GF tag needs also an update. The help page here has a demo for a Google form and it works. However, the Google form used in that demo happens to be a very old form that worked differently. The current Google forms, don't work with the embed codes we have.

This is how the current GF code converts the key to iframe codes:

<iframe src="https://spreadsheets.google.com/embeddedform?formkey=1FAIpQLScHk1Tm9MRB8mQnS6I2bUKSkQiO4yNKZ3CSk3itCKLkJyT_yA" width="95%" height="1021" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>

This is how it should look like:

<iframe src="https://docs.google.com/forms/d/e/1FAIpQLScHk1Tm9MRB8mQnS6I2bUKSkQiO4yNKZ3CSk3itCKLkJyT_yA/viewform?embedded=true" width="760" height="500" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>

 

[juliusa]

@doug I know this is an old thread, but found it searching in iframe.

We have a large google sheet for tracking supercharger visits: Superchargers Visited
We can insert the "Leaderboard" tab in a post using the gs tag. Superchargers visited 3.0

But we also have google sheets publish the "Leaderboard" as a webpage. Superchargers Visited

Is there a way to put in the "Leaderboard" from the webpage instead of the google sheet? They would be pretty much functionally equivalent, but it seems it can take a long time to open the big google sheet just to display that one part. Also, people have reported issues opening the big google sheet on mobile devices, and I thought the webpage would be better.

 

[Troy]

@tes-s, it already embeds the published version. If you open the forum page here and then click CTRL+U to view the source, it shows this code:

<div align="center"><iframe width='95%' height='500' frameborder='0' src='https://docs.google.com/spreadsheet...me=false&amp;gid=1369186092/pub?embedded=true'>IFRAME Not Available. <a href="https://spreadsheets.google.com/pub?key={param}&hl=en&single=true&gid=0&output=html">Direct Link to Spreadsheet.</a></iframe></div>

"pub" in the link means published (/pub?chrome=false&). It embeds this published page. Here are two ways you can test this:

• If you stop publishing the Google Sheet (File > Publish to the web > Stop publishing), then the embed in your forum message will stop within 5 minutes.
• You can add a droplist or a checkbox to a cell within the embedded section (right click to a cell and select Data validation etc). The published version doesn't show either. If it was displaying the sheet within an iframe, these features would work.

@doug, I wish you would increase the frame height from 500px to 600px. 500 is too low. It makes things difficult.

 

[juliusa]

Thanks @Troy. That makes sense. Working great.

 

[doug]

increase the frame height from 500px to 600px. 500 is too low.

Done. These are custom bbcodes I added myself. They may break someday if google changes the URL format, but whenever that happens, I'll just need to update them.

 

[Troy]

Done. These are custom bbcodes I added myself. They may break someday if google changes the URL format, but whenever that happens, I'll just need to update them.

Excellent. I have tested it and it works fine. We use embedded sheets in our project here. 600px is much better. Thanks a lot.