Pull data from a google sheet and display as simple html text

javascript html google-apps-script google-sheets

10,968

Solution 1

You can create an HTML result in apps script see: HTML service and just add the result as an iFrame in your google site.

Here you have a more complete example of the use of HTML Service ctrlq.org

Here is a short example from the docs:

gsCode:

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}

function getData() {
  return SpreadsheetApp
      .openById('[spreadsheet_ID]')
      .getActiveSheet()
      .getDataRange()
      .getValues();
}

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <table>
      <? for (var i = 0; i < data.length; i++) { ?>
        <tr>
          <? for (var j = 0; j < data[i].length; j++) { ?>
            <td><?= data[i][j] ?></td>
          <? } ?>
        </tr>
      <? } ?>
    </table>
  </body>
</html>

Solution 2

This is a general idea for your request rather than direct answer:

Create a Google App Script in your Google Site Setting Page
Within the Script, use SpreadsheetApp Class to read the data from your Google Sheet into array
Within the script, use HTML service to output your array as texts.
Deploy the Google App Script as Web App
Back to your page, insert your Script.

It will work and you can customise the dynamic contents you want to display but you will need to learn Google Script (just javascript with their apis) and code it out yourself.

Some References:

https://developers.google.com/apps-script/guides/sheets

https://developers.google.com/apps-script/guides/web

10,968

Author by

Mike K

Updated on June 13, 2022

Comments

Mike K almost 2 years

I would like to use data from my google sheet and have it displayed on my google site but not be embedded, just plain text so that the data can be made part of a sentence (eg, the bold bits in this sentence "We have 35 new lines this week and 12 of them are on magazines" ).

I have looked at other post and these two posts come close but as they are old, the info doesn't quite work anymore or are just not fully answered.

How do I grab data from only 1 cell and input it into HTML?

How do you Import data from a Google Spreadsheet to Javascript?

If anyone could help or even point me to maybe another topic which I have missed which answers this question that would be great, thanks in advance for the help.