google-sheetsgoogle-sites

Is there a method to display just a single cell value from a Google sheet on a Google site?

On the bottom line I want to read/display a value (and nothing else) from a Google sheet on a Google site. This is with a common Google account. The preparations I have made is that I have a sheet, which is completely open for anyone. The sheet is published on the web (file/share) and the script for the sheet is:

function doGet() {
  var sheet = SpreadsheetApp.openById('1qegJvvMUTR8B_CYNB9bnG2Ht20RPjXQ8uiK0pOiI4mU').getActiveSheet();
  var value = sheet.getRange('A1').getValue();
  return ContentService.createTextOutput(value);
}

The script is deployed as a web app, with the following configurations:

enter image description here

The web address is: https://script.google.com/macros/s/AKfycbxHtjx31MHklzaRBXMIBUc21XWxrf4NmIQLeQ0KE1DgPg2-_3ub56Joyh0ZEbBgcB9i/exec - Which works fine with a browser.

The problem is that when I try to embed the URL in a Google Site I get a no access message. Both files (sheet and site) can be seen in this drive folder: https://drive.google.com/drive/u/0/folders/1-0RR2J5WOehQ4axaBdyJsTs7djPFTY7r

I am open to other ways to display a the cell value.

Solution

  • When the script is tested, it seems that the error You need access shows up after being embedded in Google Sites, regardless if the account being used is a Google Workspace or a regular Google account.

    Upon reviewing the code, the cause of the error is

    return ContentService.createTextOutput(value);

    The documentation of createTextOutput(content) doesn't state that it requires additional access, so I recommend that you submit a bug report to let Google know about the unusual behavior that's going on.

    In the mean time, you may change that to:

    return HtmlService.createHtmlOutput("<p>" + value + "</p>");

    After being embedded, it should give something like this:

    OUTPUT

    To increase the font size of what'll show up in Google Sites, you may change that to:

    return HtmlService.createHtmlOutput("<p style='font-size: 25px'>" + value + "</p>");

    OUTPUT

    REFERENCE