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

Question

If I wanted to publish a Google Sheets spreadsheet so I could embed it on a page within an iframe, I would manually do the following:

Navigate to Google Drive
Open a spreadsheet
File > Publish To Web > Embed > Copy the generated iframe link into an html file

How would I programmatically achieve the above through the Google Sheets API using JavaScript on the front end? I'm generating spreadsheets on the fly in my application and want to immediately embed them on the page once created.

Once the sheet is created, I can dynamically create an iframe element with the necessary attributes (the sheets ID, among others). That throws an error. From this question, it looks like a sheet needs to have a published: true attribute or something, but that requires using the Drive API - I'm trying to avoid that. Is this possible to handle only through the Sheets API?

score 24 · Accepted Answer · edited Apr 27 '20 at 18:23

After searching through the entire Sheets API, googling, and just general soul-searching, I had no choice but to include the Drive API and use it to do my bidding. Here's the solution I came up with. Hope this helps someone else out there!

Used this script from Google for the client-side JS library in the index.html file:

<body>
  ...
  <script type="text/javascript" src="https://apis.google.com/js/client.js"></script>
</body>

Then for the JS stuff:

// Cache the api's into variables.
var sheets = gapi.client.sheets;
var drive = gapi.client.drive;

// 1. CREATE NEW SPREADSHEET
sheets.spreadsheets.create({
  properties: {
    title: 'new-sheet'
  }
}).then(function(newSpreadSheet) {
  var id = newSpreadSheet.result.spreadsheetId;

  // 2. PUBLISH SPREADSHEAT VIA DRIVE API
  drive.revisions.update({
    fileId: id,
    revisionId: 1
  }, {
    published: true, // <-- This is where the magic happens!
    publishAuto: true
  }).then(function() {

    // 3. DISPLAY SPREADSHEET ON PAGE VIA IFRAME
    var iframe = [
      '<iframe ',
      'src="https://docs.google.com/spreadsheets/d/',
      id,
      '/pubhtml?widget=true&headers=false&embedded=true"></iframe>'
    ].join('');

    // We're using jQuery on the page, but you get the idea.
    $('#container').html($(iframe));
  });
});

Where are you running this? In a Google Apps Script project? I am trying to do the same thing you are but from a Google Apps Script project. — IMTheNachoMan, Jun 06 '19 at 01:16
@IMTheNachoMan It's been a while, but I believe this was in Node, logic found behind an Express route. But I was doing a tooon of work in Apps Script too. There are different api's to interact with Google products from within Apps Script. Check out the docs. It's been a while for me. — The Qodesmith, Jun 14 '19 at 00:07
Your answer helped me, thanks. However I also had to provide ` publishedOutsideDomain: true,` before my browser would show the iframe — spassvogel, May 20 '21 at 07:29

score 5 · Answer 2 · answered Jul 25 '16 at 19:19

5

As you have concluded, it is not possible through the Sheets API today and is only possible through the Drive API (using the PATCH https://www.googleapis.com/drive/v3/files/fileId/revisions/revisionId request, documented at https://developers.google.com/drive/v3/reference/revisions/update).

answered Jul 25 '16 at 19:19

Sam Berlin

3,603
12
23

Thanks. Yea, I was originally having issues with OAuth stuff and getting both the Sheets & Drive api's playing nice. I couldn't find a full example online for what I wanted to do, so I eventually figured it out (below). Thanks again! – The Qodesmith Jul 27 '16 at 15:16
1

Does this mean that there is no way to use any API to publish only one sheet (i.e. tab) from a Google Sheets spreadsheet? The manual "Publish to the web" menu item in Sheets has the option to publish "Entire Document" or an individual tab/sheet by name, but presumably the Drive API doesn't know enough about Sheets to be able to specify only a single one to publish? – Mike Shulman Aug 28 '18 at 03:48
1

@MikeShulman I think regardless of how you publish, it publishes the entire document. The URL is what determines what is returned. I just did a test where I created a sheet with 2 sheets. I shared just one sheet but i was able to access the other sheet by changing one of the URL parameters. – IMTheNachoMan Jun 06 '19 at 01:13
@IMTheNachoMan Not true. You can limit which sheet you can publish. In the UI there are two options. In the lower option "Publish settings", you can limit which sheet you can publish. If you try to access the not-published sheet(tab) by changing url query parameters, Google specifically says you do not have permission to access this. – TheMaster Nov 04 '22 at 19:28
@TheMaster They must have fixed this recently because I did extensive tests a while ago and was able to access any/all sheets. – IMTheNachoMan Nov 05 '22 at 03:14

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

2 Answers2

Linked