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 e 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.
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 e 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.
Share Improve this question edited May 23, 2017 at 12:09 CommunityBot 11 silver badge asked Feb 22, 2017 at 11:27 Mike KMike K 992 gold badges3 silver badges7 bronze badges 3- Question1: Are your google sheet accessible by everyone? Question2: Why you don't want to use embeded google script? – Chris Chen Commented Feb 22, 2017 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 Commented Feb 22, 2017 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.developers.google./apps-script/guides/web – Chris Chen Commented Feb 22, 2017 at 11:59
2 Answers
Reset to default 3You 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 plete example of the use of HTML Service ctrlq
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>
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./apps-script/guides/sheets
https://developers.google./apps-script/guides/web