2

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.

Community
  • 1
  • 1
Mike K
  • 89
  • 2
  • 2
  • 7
  • Question1: Are your google sheet accessible by everyone? Question2: Why you don't want to use embeded google script? – Chris Chen Feb 22 '17 at 11:30
  • They are just accessible for the business (anyone that has a link and is part of the business/ has a business email) and because when I have tried it, it just doesn't display correctly, the default add google sheets works but just is not user friendly enough and I would like the info just there where as with the embedded I have to click it most of the time to show the info/ the updated info. Maybe there are some tweaks I could make to make it look better than go that route? Would though prefer if I could have it as just text that I could fit into a sentence, thanks. – Mike K Feb 22 '17 at 11:44
  • OK, so 1: if the sheet is not public, you can not use javascript to get them on Google site page directly 2: Yes, embeding google sheet directly will not meet your requirement. Thus you may need to think Embed Google App Script using its Web Service.https://developers.google.com/apps-script/guides/web – Chris Chen Feb 22 '17 at 11:59

2 Answers2

3

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>
Juan Diego Antezana
  • 902
  • 4
  • 16
2

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

  1. Create a Google App Script in your Google Site Setting Page
  2. Within the Script, use SpreadsheetApp Class to read the data from your Google Sheet into array
  3. Within the script, use HTML service to output your array as texts.
  4. Deploy the Google App Script as Web App
  5. 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

Chris Chen
  • 1,228
  • 9
  • 14
  • This is with classic sites, not the new version, right? – Mike K Feb 22 '17 at 12:24
  • So create the script in classic sites (steps 1-4) then with the url created put that into a text (code) box on the new site? Just making sure I'm on the right page, thanks so far for the help...or does it need to be all made in google classic sites ? – Mike K Feb 22 '17 at 12:35
  • If you want it work on Google Site, then yes, it will only work properly in Classic site, please ignore my last comment since embed url will not work. Alternatively you can create a standalone Google App Script and share the web link from step 4 for all of your users. Although this doesn't meet your initial requirement – Chris Chen Feb 22 '17 at 12:47