Using Google AppScript, Is it possible to programmatically publish to the web a _single sheet_ within a Google Sheets document?

Question

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.

Answer 1

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.

Answer 2

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

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>