How to embed a single Google Sheets cell's text content into a web page?

Question

Background: I have a Google Sheet that automatically manages key organization event dates, but occasionally one has to be manually overridden. I have been duplicating those auto calc'd event dates using PHP on the organization's web page but that doesn't handle it when the event dates are manually changed back in the Sheets database.

Need Statement: I wish to grab the text content of the Google "DateCalc" Sheet, cell "A33" (which contains the corrected next Date) and embed it automatically in a web page sentence that states, "The next event is scheduled for ???." where "???" is whatever is needed for the HTML embed effort for the text content of cell A33.

All the embedding approaches I have tried using the publish to web feature of Sheets, just display a frame with the entire Sheets page content, and I have not been able to find a simple single text display approach, which is what I seek. All the solutions I have found so far are for much more complex situations. I believe I don't need any more code on the Sheet (source) end, just an appropriate "embed code snippet" at the destination end.

I can embed a complete sheet but haven't found a way to embed a simple text string in line with the surrounding text in the sentence.

This web script is the closest I have been able to come...

Our next meeting is on <object data="https://docs.google.com/spreadsheets/d/e/2PACX-1vQq6sXBel4SOW5U_XLnQ1xyyl4P-aK2v1R-5uofUvstMLV89_yCSqX3Lmsy4B7E21gojeG88efGFjZ0/pubhtml?gid=604484136&amp;single=true&amp;range=A29&amp;chrome=false" type="text/html" width="171" height="15"></object>.

but, if the Code Snippet really ran (I changed the file ID to protect the innocent), you would see it doesn't produce an in-line text string but an isolated "block" with the text inside it, as shown below....

The text seems to be an image, not text, that is displaced above the text baseline and has a small gray blob following it that I just can't get rid of.

Answer 1

I'll assume that your server uses a combination of PHP and HTML, so one approach can be to use that PHP code to make a call to the Sheets API to retrieve the data. In the PHP Sheets API Quickstart you can find a example of how to set up a project to achieve a working connection between your server and the API. After you complete the tutorial, you can modify your script to include the following function so you can achieve your original request.

function getNextDate() {
  $client         = getClient();
  $sheetsService  = new Google_Service_Sheets($client);
  $spreadsheetID  = '{YOUR SPREADSHEET ID}';
  $cellRange      = 'DateCalc!A33';
  $serverResponse = $sheetsService->spreadsheets_values->get($spreadsheetID, $cellRange);
  $cellValue      = $serverResponse->getValues();
  if (empty($cellValue)) {
    return "NO MEETINGS AHEAD!\n";
  } else {
    foreach ($cellValue as $value) {
      return $value[0];
    }
  }
}

A totally different alternative is to use a webapp. You can use this tutorial to deploy a webapp that can be later embedded in your webpage to show the following meeting dates.

So there are two free alternatives to accomplish your objectives: using the Sheets API or a webapp; and in this anwser I provided a function for the API option. Please, don't hesitate to offer information for further clarifications or request for help.

Answer 2

Our next meeting is on .

I built a free, open source, and easy to use service and badge generator called https://cellshield.info. It works out of the box for public Google spreadsheets. What you see above is a live Markdown output version of a badge that is synced from a spreadsheet with the value formatted as what was shown in your example with a formula of TODAY(). The Markdown just makes an image tag which I suppose you can do with anything else that can make an image tag as well.

If you want the yellow or any formatting to to go away, try this:

Our next meeting is on .

This was done by adding &color=rgba%28255%2C255%2C255%2C0.0%29&style=flat-square to the arguments. The text doesn't match perfectly, it's servicable.

Anyway, I see you went with tabletop but if you or anyone else is just looking to do what you wanted to do, then my service should be sufficient.

For example, it is used for this Game Boy development contest to present the prize pool amount.

---

If a private spreadsheet solution is needed, one could fork my code, run it on Cloud Run in their Google Cloud Platform account and explicitly share the spreadsheet to the service account on their Google Cloud Platform instance.

Answer 3

Jacques- Merci beaucoup !

It has been a challenging several days of non-stop effort but you put me on the right track.

Using less than 20 lines of JavaScript code, including one line for the free TableTop.js library call (see https://github.com/jsoma/tabletop#if-your-publish-to-web-url-doesnt-work) and inserting a string in the middle of my HTML sentence, yielded EXACTLY what I was seeking and the solution was INFINITELY SIMPLIER than virtually everything else I found that required multiple libraries, subscription fees, complex system calls, etc.

The hardest part was inserting the JavaScript code into WordPress which my web site is constructed with, as you can essentially only insert plain text into a WordPress page, but with the an added WordPress plugin (Insert Header and Footers) you can put code into a WordPress page.

Many thanks for getting me headed in the right direction, and having a great learning experience along the way. :-)