Pull data from a Google Spreadsheet to Hype

Elements
1

Hi,

I want to pull data from a Google Spreadsheet to Hype.

Here is what i am trying ti achieve:

wanted-result
wanted-result964×500 169 KB

Is there an easy way to do this? I am not a developer, so the simpler code the better :slight_smile:

Google sheet:

Link to a similar thread.

580x500-gsheet.zip (48.8 KB)

1 Like
2

Did you read that thread ??

4

First the spread sheet entries need to be like this

42
Screen Shot 2018-03-13 at 01.27.42.JPG1162×236 31.1 KB

1, Go to file->publish to web.

2, Publish the doc.

Ignore any new link you get.

Use the ID in the original link (NOT the Publiish to web one )in the javascript code to get the json.

So an example of one like yours would be.

After publishing (NOT SHARING)

I have the original link from the address bar.

https://docs.google.com/spreadsheets/d/1sOyd9OXBmAmUkMThzuyIVDNkWfFoS-F_1wOeSyeQ8Oo/edit#gid=0

We take the ID “1sOyd9OXBmAmUkMThzuyIVDNkWfFoS-F_1wOeSyeQ8Oo” and use it in the code like this:

"https://spreadsheets.google.com/feeds/list/1sOyd9OXBmAmUkMThzuyIVDNkWfFoS-F_1wOeSyeQ8Oo/od6/public/values?alt=json"

The code

    $.getJSON("https://spreadsheets.google.com/feeds/list/1sOyd9OXBmAmUkMThzuyIVDNkWfFoS-F_1wOeSyeQ8Oo/od6/public/values?alt=json", function(data) { 
console.log( data); //You can see all entries and there item codes// 
// --> 
 
 	console.log(data.feed.entry[0].gsx$heading.$t );
  console.log(data.feed.entry[0].gsx$subheading.$t );
  console.log(data.feed.entry[0].gsx$cta.$t );
  
  
  var heading = hypeDocument.getElementById('heading').innerHTML = data.feed.entry[0].gsx$heading.$t
  var subheading = hypeDocument.getElementById('subheading').innerHTML = data.feed.entry[0].gsx$subheading.$t
   var cta = hypeDocument.getElementById('cta').innerHTML = data.feed.entry[0].gsx$cta.$t
  });

580x500-gsheet_MHv1.hype.zip (52.7 KB)

7 Likes
5

Hi Guys, I have this working!! I have a special request if you have a moment… is there a way to have multiple results populate the text box (uniqueID) in a way where it would add all rows that were added to the spreadsheet later? In other words, can I add to the scrip so that it looks for and adds all rows in the sheet? Thanks in Advance!

6

If I understand you correctly then the answer is yes but no.

What I mean is yes it can be done but you would not generate new hype elements without cloning.

But for the idea of what you may need to do:

Code that polls and gets the sheet json every #minutes.
Code that iterates over the json.
Code that adds the entries.
In my case I avoid cloning and generate a table into an existing Hype element. The table cells will hold the new data.

You can see an example of this at http://ourmusicradio.com/
Click the Schedule button.

This is subjectively involved codewise to go into here but I will be putting up a special events button shortly that will be more dynamic ( not set days). If I get a chance I may break out some of the code and post here but no promises and also no promise will go into a lot of detail or help further at the mo. And remember this type of coding is subjective to data, display. So one thing including the fetch code will not work for all.

3 Likes
7

Mark you are fantastic. However, please hold because I am one of those who won’t have the full mental capacity to reach the All-Aboard on your Thought Train! In the meantime (hack that I am), I found in Google Design Studio that I can use their data engine to populate into graphics for embed into my hype doc…including tables and more. Since I’m using a G-Sheet to collect, this maybe easier for me at this time. However I am excited that I could actually understand a bit of the logic you did here and thank you for that! You guys who get this stuff, I am very grateful and impressed.

1 Like
8

@MarkHunte turned the stream on -> nice relaxing music -> like

1 Like
9

@h_classen, @jonathan , @Daniel

My Brother should be giving you guys a shoutout on the radio station between 3pm-5m tomorrow (Uk Time )

4 Likes
10

i’ll turn the stream on :wink: funny thing :slight_smile:

1 Like
11

In about 10mins…the shoutout…

1 Like
12

ha ha :slight_smile: got it! thx Mark

1 Like
13

Ha,ha no probs. Recorded it just incase you missed it.
He took a sec there to get his head around what he was saying… :laughing:

14

yeah, noticed it too ... :slight_smile:

1 Like
15

:roll_eyes: :thinking: :crazy_face:

16

I have tried to replicate this. I tried the script and followed MarkHunte but it doesn't work for me.
I tried both local and on a server.

The idea we have is that a number of people can add the status of a zoom call. Full, Empty etc. on the google doc and this will update the text next to the corresponding room button. So people don't have to blindly try rooms in vain.

I have a question... Can I specify which cell to show?

Hope you can help...

1 Like
17

Marks example still works as is ... so you might do something wrong.

18

Most probably :slight_smile:

Here is what I have done. I would appreciate any help.

Here is the google doc.

image
image2556×826 180 KB

I have published it to web.

This is my version of your code.

image
image2282×686 98.7 KB

The textbox id the other id is "room"

image

And here is the "on load"

image

I exported the hype to html5 and put it on a server. Nothing works...

If I get it to work how would I chose other cells in the java?

Any ideas?

19

Can you share the Hype document?

20

Sorry not as it stands, my client would not want that. But I can try to fix a simple freestanding test to try the code on it's own. I will fix very soon.

21

google link test.zip (9.5 KB)