2

I would like, through Google Appscript, to (a) programmatically publish to the web selected sheets within a Google Sheets document, (b) obtain programmatically the URL where each sheet is published, and (c) have the published version of each sheet automatically update whenever the corresponding sheet is updated (this should happen automatically, right?). Right now, I can accomplish this only through File/Publish to Web...

The following question and answer is highly related to this question:

Google Sheets API: How to "publish to web" for embeddable sheet?

However, it appears to apply only to publishing an entire Google Sheets document, not a single sheet within a Google Sheets document. Any solution ideas would be most appreciated.

Improve this question

2 Answers 2

Reset to default
4

I have gained some insight into this question. It is possible to obtain a URL to a published HTML version of a single sheet in a Google Sheets document simply by modifying the URL used to access that sheet.

For example, here is the URL of a sheet I'm working on in Google Sheets, copied directly from my browser's URL bar:

https://docs.google.com/spreadsheets/d/1fTx3dUsvdbVKgP2nXs1LcyG_7oBp-MoFZTXn7MtdEZg/edit#gid=1711661074

I can then modify the URL as follows to get a published HTML version of that single sheet:

https://docs.google.com/spreadsheets/u/0/d/1fTx3dUsvdbVKgP2nXs1LcyG_7oBp-MoFZTXn7MtdEZg/htmlembed/sheet?gid=1711661074

Summary of URL modifications I made:

  1. Replace "/d" after "spreadsheets" with "/u/0/d"
  2. Replace "edit#" with "htmlembed/sheet?"

Other inferences one can make:

  1. The long string after "/u/0/d" is the ID of the Google Sheets document.
  2. The shorter string after "sheet?" is the ID of the single sheet within that document.

These new insights transform my question into a new one: namely, how can I programmatically obtain (through Google Appscript) the ID of the Google Sheets document I'm working on, together with the ID of the spreadsheet I'm working on?

Here's the answer:

  1. To get the ID of the current Google Sheets document within Appscript:
    var ss = SpreadsheetApp.getActiveSpreadsheet().getId();
  2. To get the ID of the current sheet within the current Google Sheets document:
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
  3. I can now build a URL for a published html version of any single sheet within a Google Sheets document through string concatenation as follows:
    var publishedURL = "https://docs.google.com/spreadsheets/u/0/d/" + ss + "/htmlembed/sheet?gid=" + sheet;

There's still one lingering issue, though: It appears that users of this published URL must manually refresh the browser in order to sync the HTML with the spreadsheet. At the present time, I do not have a solution to this problem, other than to request that users of the URL install an auto URL refresher or manually refresh the page periodically. I'd welcome any ideas on this.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Could the manual refresh problem be addressed by embedding a meta refresh tag in the html source? See link. The problem is that I don't know how to alter the content of the published html version of a Google sheet.
Review my answer. You could also add a javascript timeout function to initiate a refresh.
Thanks for what this means to me, help me solve my problem !!!
You may also add &range=A1:A3 to the URL to make a link to range: docs.google.com/spreadsheets/u/0/d/…
1

It looks like you can publish individual sheets according to these dialogs:

enter image description here

enter image description here

It does update the published sheets although I've noticed quite a bit of delay in the process occasionally.

Since the Publish to the Web simply shows a readonly version of an html table that contains sheet values then you could do that with one webapp. Here's an example below that displays all sheets in tabular form.

A Webapp to display all sheets:

function publishAllSheets() { var ss=SpreadsheetApp.getActive(); var allShts=ss.getSheets(); var s='All my Sheets'; for(var i=0;i<allShts.length;i++) { var sh=allShts[i]; var rg=sh.getDataRange(); var vA=rg.getValues(); s+=Utilities.formatString('Sheet: %s <br /><table border="1">',allShts[i].getName()); for(var j=1;j<vA.length;j++) { s+='<tr>'; for(var k=0;k<vA[j].length;k++) { s+=Utilities.formatString('<td>%s</td>', vA[j][k]); } s+='</tr>'; } s+='</table>'; } return s; } function showAllMySheets() { var ui=HtmlService.createHtmlOutputFromFile('allsheets').setWidth(1000); SpreadsheetApp.getUi().showModelessDialog(ui, 'All My Sheets') } function doGet() { var ui=HtmlService.createHtmlOutputFromFile('allsheets'); return ui.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); }

allsheets.html

<!DOCTYPE html> <html> <head> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script> <script> $(function(){ google.script.run .withSuccessHandler(updateDiv) .publishAllSheets(); }); function updateDiv(hl) { document.getElementById('c1').innerHTML=hl; } </script> </head> <body> <div id="c1"></div> </body> </html>

Here's the code for getting any one of your sheets:

function getSheetNames() { var ss=SpreadsheetApp.getActive(); var allShts=ss.getSheets(); var shts=[]; for(var i=0;i<allShts.length;i++) { shts.push(allShts[i].getName()); } return shts; } function getOneSheet(name) { var ss=SpreadsheetApp.getActive(); var sh=ss.getSheetByName(name); var rg=sh.getDataRange(); var vA=rg.getValues(); var s=''; s+=Utilities.formatString('Sheet: %s <br /><table border="1">',sh.getName()); for(var j=1;j<vA.length;j++) { s+='<tr>'; for(var k=0;k<vA[j].length;k++) { s+=Utilities.formatString('<td>%s</td>', vA[j][k]); } s+='</tr>'; } s+='</table>'; return s; }

onesheet.html

<!DOCTYPE html> <html> <head> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script> <script> $(function(){ google.script.run .withSuccessHandler(updateSelect) .getSheetNames(); }); function updateDiv(hl) { document.getElementById('c1').innerHTML=hl; } function updateSelect(vA) { var select = document.getElementById("sel1"); select.options.length = 0; for(var i=0;i<vA.length;i++) { select.options[i] = new Option(vA[i],vA[i]); } } function getSelectedSheet() { var name=$('#sel1').val(); google.script.run .withSuccessHandler(updateDiv) .getOneSheet(name); } console.log('MyCode'); </script> </head> <body> <select id="sel1"> <option value="" selected></option> </select> <input type="button" value="Select" onClick="getSelectedSheet();" /> <div id="c1"></div> </body> </html>
Improve this answer

6 Comments

Thanks, @Cooper. I am looking for a solution that will allow me to do this programmatically through Google Appscript.
Well if all you want is what they do in publish to the web then I'd just go with one webapp that has a drop down list of sheet names. The user picks the sheet name and the webapp sends that back to server and the server sends the webapp the html for your sheet on in an html table and the webapp puts it into a div with document.getElementById().innerHTML=
please see my new answer. I think it addresses the problem.
You don't need to know all of that if you build a contained Webapp in your spreadsheet. See my answer.
That's an interesting approach, @Cooper! Thanks for sharing it! Alas, it won't work for my application, since I am using the webapp functionality of the spreadsheet (doGet()) for a completely different purpose, and cannot utilize that functionality to dish out html versions of spreadsheet content. Instead, I need to be able to provide a user with links to spreadsheet content hosted by Google. For this, I think some variation of my proposed solution will be the best route. If I ever decide to build an expanded webapp capable of serving spreadsheet content, I will consider your solution.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.