1

I need a table to be in a web page, and it need to be total clickable. If someone clicks in a cell that is at the begging of the table , it can be redirect to a cell in another place far away from it.

Inside the google sheets I can make it happen, but in the webpage it doesn't work, the link just open another sheet in a new window.

Another thing is, I would like that any link that I put in the table would open at the same window.

This is the code I'm using:

<!DOCTYPE html>
<html>
<p><iframe title="table" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSW38yyodki0VWjMg9pziJB-PJHTdrjIiNMBdQMmbbPesA7dEHMIMaJ0xibsUaaAMQHW2G8y_zCllAF/pubhtml?widget=true&amp;headers=false" width="900" height="900" allow="pointer-lock" zoom: 1.75;
  -moz-transform: scale(1.75);
  -moz-transform-origin: 0 0;
sandbox="allow-forms | allow-modals | 
     allow-orientation-lock | allow-pointer-lock | 
     allow-popups | allow-popups-to-escape-sandbox |
     allow-presentation | allow-same-origin | allow-scripts |
     allow-top-navigation | allow-top-navigation-by-user-activation">
     </iframe>
</p>

<style type="text/css">    
@font-face
{         
font-family: 'Roboto', sans-serif; 
src:url('https://fonts.googleapis.com/css2?family=Roboto:wght@300;400&display=swap');
} 

</style>
</html>

I tried to made it clickable by using this parameters:

sandbox="allow-forms | allow-modals | 
     allow-orientation-lock | allow-pointer-lock | 
     allow-popups | allow-popups-to-escape-sandbox |
     allow-presentation | allow-same-origin | allow-scripts |
     allow-top-navigation | allow-top-navigation-by-user-activation">

Then I tried to make it clickable by making a code in the google sheets app script like this:

function linkCellContents() {
 var range = SpreadsheetApp.getActive().getRange("'+'!L9");
 var richValue = SpreadsheetApp.newRichTextValue()
   .setText("TESTE LINK")
   .setLinkUrl("https://docs.google.com/spreadsheets/d/1Aks5t1b08U4mA_-A1r6Js66tPkI24iQj-fYNNSyH1Ck/edit#rangeid=1955833310")
   .build();
 range.setRichTextValue(richValue);
}

The code in app script made the link work, but the problem is that it goes off site domain and goes to a new window in google sheets domain, that is not so good for me either...

I need a way that the cell is clickable and the link works to navigate between cells and the navigation remain inside the iframe structure in site domain (I'm using Wordpress)

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Fabi
  • 11
  • 2
  • Welcome to [so]. The question needs more focus, in other words, [edit] this question to make it specific about a single programing problem, either by using a Google Sheets published to the web embeded using an iframe or whatever you are doing that works with the Google Apps Script function. You could post the other part a new question at any time, but I suggest you to wait until you have a better understanding of the problem described on the first question. – Rubén Oct 29 '22 at 16:36
  • @Rubén I don't think that's the second part. OP is just adding what he has tried(and the problem with what he has tried). – TheMaster Oct 29 '22 at 17:24
  • @TheMaster Becuase the OP is focused on solving their "business" / "functional" problem. They tried different approaches each of them having their own "technical" / "programming" problems. IMHO they should do the mind shift to focus the question in a *single programming* problem. – Rubén Oct 29 '22 at 17:32
  • Fabi, I'm not sure whether you can link to a specific cell range in a published Google sheets url(at least not without hiding everything else[`&range=`]). @Rubén I see the second as "What they tried"(directly setting the url) and "Why it didn't work"( because the link goes offsite). The question is only one -the first: `someone clicks in a cell that is at the begging of the table , it can be redirect to a cell in another place far away from it.` The rest don't matter and can be ignored. The second is just "their attempt" and doesn't require us solving "why their attempt didn't work" – TheMaster Oct 29 '22 at 17:39
  • Having said that, I believe the first question can benefit from more details on the first question. @Fabi Kindly add, what is the `=hyperlink()` formula you used in the first one. – TheMaster Oct 29 '22 at 18:05
  • I found maybe a better title, in someone having the same problem as mine, and not yet solved in Google Community "Link to range of cells in Sheets not working on embedded Sheet in webpage" – Fabi Oct 29 '22 at 21:19
  • 1
    Now I tried to use excel embed from OneDrive, and the link worked very well, just some design aspects that are little different and the image quality is little low, but now it's a totally different problem, thanks for the correction in the text, I will keep that instructions in mind for further questions – Fabi Oct 29 '22 at 21:22
  • @TheMaster I think that it's possible. Please see my answer. it has two sections, one about embedding a spreadsheet the other about using Google Apps Script to insert links to specific ranges. – Rubén Oct 31 '22 at 15:53
  • @Rubén Do you have a `/pubhtml` working `range=*` parameter? I don't think `/edit` or `/preview` is a valid workaround as you have to share Google sheets url publicly, which I hate for privacy reasons(https://webapps.stackexchange.com/questions/114961/share-google-sheets-anonymously) – TheMaster Oct 31 '22 at 16:01
  • @TheMaster yes... i.e. `/pubhtml?gid=0&single=true&range=F8` – Rubén Oct 31 '22 at 16:05
  • @Rubén I know `&range=A1:B1` would display only that range, but it won't jump to that range while displaying all the rest of the cells. Correct? In my previous comment, I say this: `...at least not without hiding everything else[&range=` – TheMaster Oct 31 '22 at 16:07
  • @TheMaster Just because you hate a workaround it doesn't make it invalid, but you have a point... I agree that it's better to warn the user – Rubén Oct 31 '22 at 16:08
  • 1
    @Rubén Agree. "Invalid" isn't the right word or Probably just invalid to me :) – TheMaster Oct 31 '22 at 16:10
  • P.S. Putting aside the privacy conderns I hate receiving request to share spreadsheets that I have shared with anyone with the link as demos/examples posted on forums and my old blogs, especially those that have instructions to make a copy. – Rubén Oct 31 '22 at 16:15
  • @Rubén Also, I only meant it's not possible in a [published Google sheets](https://stackoverflow.com/questions/74246235/how-to-make-the-link-to-one-cell-to-another-cell-in-a-published-google-sheets-ur?noredirect=1#comment131084549_74246235). So, when you said, `I think that it's possible`, it didn't surprise/impress me that much( that's what I meant by not valid), because I already knew it was possible by embedding it directly, but ruled it out in my mind completely. This question only interested me because of the `/pubhtml` aspect. – TheMaster Oct 31 '22 at 16:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249206/discussion-between-ruben-and-themaster). – Rubén Oct 31 '22 at 17:55
  • Hi, . I tried target="_blank" and didn't work in Sheets ass well... I manage to accomplished by doing the process of iframe with javascript parameters in an excel file made in OneDrive instead of Google Sheets, now it work perfectly the click between cells range.. – Fabi Nov 17 '22 at 14:26

1 Answers1

1

Tl;Dr

The feature File > Share > Publish to the web apparently doesn't support links to jump to ranges. You might use JavaScript to update the <iframe> URL to add/update a range URL parameter.

  • On links intended to jump to a range use only the URL fragment i.e. #rangeid=1955833310.
  • Links from a document inside an <iframe>, by default are opened inside this element. This might be overrided by using the target attribute, as target="_top", target="_blank", etc., or JavaScript.
    • The links in your published to the web spreadsheet have target="_blank".
    • To fix this you might have to use web browser scripting, a web browser extension, or to copy the source code from the /pubhtml resource, edit it and host on your own server or another service like Google Apps Script.
    • The /pubhtml file hasn't JavaScript to handle the clicks on the links to jump to a range.
    • A better workaround might be to create a web apps using Google Apps Script to be embeded in your webpage.

Embeding a Google spreadsheet

When working with links in Google Sheets, please bear in mind that it only support the title and URL attributes. On HTML the anchor (<a>) tag also has the target attribute and work together with the <base> tag. The target attribute is used to set where the link will be opened, i.e. the same iframe, another iframe, the same window, a new window, etc.

The URL set in the src attribute of the <iframe> included in the question includes /pubhtml, this means that Google spreadsheet was shared by publishing it to the web. Unfortunatelly the official documentation doesn't offer details about how the spreadsheet is converted so you will have to use the web browser developers tool to figured out the relevant details about it, i.e. if the resulting HTML includes target attributes.

If /pubhtml it's not working as you expect and you aren't able to work with it, i.e. make a copy and host it your web server, but if you are able / willing to share the spreadsheet with anyone on the web, instead of the /pubhtml URL use the regular spreadsheet URL and try several variations like changing the last path element and the query string.

NOTE: Sharing a Google spreadsheet with anyone to the web will expose your name and your email address. Anyone that open the spreasheet will find it in Google Drive > Shared with me, which shows the owner name and using some "hacks" it's possible to figure out the owner email address.

Things to try:

  • Replace /edit by /preview
    • this will show a view similar to the URL /pubhtml but the embeded elements like charts and drawings will not be shown. If you aren't using these element it might be worthy to try it.

Using Google Apps Script

When inserting a link to be used to jump to specific area, instead of adding the full spreasheet URL using SpreadsheetApp.newRichTextValue() you have to add only the URL fragment, i.e. #rangeid=range_id_number.

The "Ultimate Workaround"

Instead of struggling with Google Sheets to create something that looks and works according to your specific needs, consider to create a web application using the HtmlService from Google Apps Script. This will allow you to take full advanges ot <base> and <a> tags (with some limitations). The web app will have to parts, server side code where you could continue using the Class SpreadsheetApp and client side code where you build a user interfase / webpage using HTML/CSS/JavaScript.

Start by reading

Related

URL parameters for embeded spresheets

Google spreadsheets links

HTML

From Web Applications

Reference

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 1
    `or to copy the source code from the /pubhtml resource, edit it and host on your own server or another service like Google Apps Script.` This will surely take days. `A better workaround might be to create a web apps using Google Apps Script to be embeded in your webpage` Agree. – TheMaster Oct 31 '22 at 20:00