Getting Data from Google Sheet

Something like that: http://mikeymckay.github.io/google-spreadsheet-javascript/sample.html ?

Hi Zsolt. That seems to work on the website to pull the data, but I want to parse it so it only pulls a single cell and then commits it to a variable.

Try JQUERY + Tabletop. Follow the Tabletop configuration instructions for building your shared Google Sheet. One thing to be aware of, the sheet needs to be public.

var public_spreadsheet_url = Google-SPREADSHEETID;

function init() {
Tabletop.init( { key: public_spreadsheet_url,
callback: showInfo,
simpleSheet: true } );
}

function showInfo(data) {
// data comes through as a simple array since simpleSheet is turned on
player.SetVar("SLVariable",data[index].columnTitle);
}

Hi Steve,

Thanks for the reply. To confirm a couple details, forgive me for my lack of coding knowledge, once I make the sheet public I need to add the Tabletop.js file available in my Storyline folder? Also, in the code above, you have a couple sections I think I need to fill in:

Google-SPREADSHEETID - Should this be the full url, or is it actually typed out?

public_spreadsheet_url - Is this the full url, or just the sheet identifier?

Thank you for your help!

Hi Steve,

As a follow up, I figured out the two items above, but I cannot get it to work. If you don't mind, I attached my Story file as well as the zip folder. Could you check my Java code as well as the Story.html file to make sure I have it all correct? The google sheet I am referencing has some sample words typed in so there is data to pull.

Thanks!

Hi Richard - 

Couple of issues. I'd send it back to you except I don't have Storyline 1 installed. This should still work fine in SL1. 

1. Tabletop.js needs to be included in the header of your document. It also needs to load completely before you call in a function. I usually do this on slide load either on the master slide or the slide where the function is going to be called. Give enough time for it to load before calling the function. 
   
   If you don't want to use a CDN, you can "carry on" files using a web object. Add the library files to a folder containing an index.html file (the index.html can be completely empty) and import that as a web object to a slide in your story file. Publish, then navigate to story_content/WebObjects and find the folder name that contains your files. Copy this into your "load function below"
   
   Here's the function I use to wedge libraries I have in a web object into the document header. Again, these need time to load as they're added after the page loads. Breaks the normal order and your function will fail if you try to call before the libraries load unless you use a mechanism to check for the library load.
   
   

   function add_script(scriptURL,oID) {
   var scriptEl = document.createElement("script");
   var head=document.getElementsByTagName('head')[0];
   scriptEl.type = "text/javascript";
   scriptEl.src = scriptURL;
   scriptEl.id=oID;
   head.appendChild(scriptEl);}

   //only want to add these once!
   if(document.getElementById('jquery')==null){
   add_script("story_content/WebObjects/6FuXJuJNUbY/jquery.min.js","jquery");
   add_script("story_content/WebObjects/6FuXJuJNUbY/tabletop.js","tabletop");
   add_script("story_content/WebObjects/6FuXJuJNUbY/taffy.min.js","taffy");
   }
   
   //6FuXJuJNUbY is the folder containing my carry on files in the WebObjects folder.

2. The spreadsheet url is fine. But it needs to be a string. See the added " " below.
3. The index value is the value in the returned array. So 0 should be the first non-header row. This is the complicated part. You'll need to figure out which row it is in. There are a few ways to handle this. The way I've done this is either with another sheet that references a main sheet. A way to make the returned values known. That or use something like TaffyDb or similar to query the returned values. 
4. In data[0].Test, Test is the name of the column in the first row.

var player = GetPlayer();
var public_spreadsheet_url = "https://docs.google.com/spreadsheets/d/1VueAdPilexFcAYsgWe9End9yqMnHcaFq-yV-iP7k9dE/pubhtml";
function init() {
Tabletop.init( { key: public_spreadhseet_url,
simpleSheet: true } );
}
function showInfo(data) {
// data comes through as a simple array since simpleSheet is turned on
player.SetVar("Testing",data[0].Test);
}

Looks like you can just use the key instead of the entire URL as well. Both seem to work. Jquery doesn't seem to be required from what I'm reading. Looking at my implementation, I was using Jquery for something else.

Hi Steve,

Thanks for getting back to me. I am not as up to date on the coding side and I am still hitting a bit of a roadblock. I can add the code files in a web object just fine and I updated the JavaScript per your recommendation. However, I am still not seeing the update happen. Do I need to add <script type = "text/javascript> before the call function in my Story.html file to wedge in the web object files?

Do you have a story.html file, even from Stroyline 2, which I could see or a sample published zip I can inspect to see the function working? Once I see how you coded the HTML files, I may get a better idea of what you are suggesting. Otherwise, if possible, screenshots might help me grasp where all this is added to make the function work.

I apologize for not getting this quicker; I can pick up the logic behind the code, but I don't know how to actually code myself.

HI All,

I am still looking for a little help on this. I can enter all the code and call the files, but it does not seem to be working. If anyone has figured this out and can send me screenshots of what they changed and the triggers they set up, that would be great.

Hi Richard - 

Can you post your file? Happy to have a look but tough to tell what's going on without seeing it:)

HI Steve,

I attached both my Storyline file and the published zip folder with the .story file edited. I added per your suggestions above and for some reason I am not seeing it work. I tested it both locally from my computer as well as through Scorm Cloud to simulate an LMS.

I have also tried to load JQuery and Tabletop using the string style:

<script type="text/javascript" src="story_content/WebObjects/6SmwRnTCCIz/jquery.min.js"></script>

Which worked when I had to load Ajax for another project.

Any suggestions are greatly appreciated.

Hi All,

I got it all to work. I moved the "Var player=GetPlayer();" inside of the tabletop init function(bolded below to show what I moved) so it looks like such:

var public_spreadsheet_url = 'https://docs.google.com/spreadsheets/d/1VueAdPilexFcAYsgWe9End9yqMnHcaFq-yV-iP7k9dE/pubhtml';

Tabletop.init( { key: public_spreadsheet_url,
callback: showInfo,
simpleSheet: true } );

function showInfo(data) {
var player = GetPlayer();
player.SetVar("Testing",data[0].Test);
}

This allowed it to work. Not sure exactly why, though the logic would be you need to call the player after initialize Tabletop so it is active to write to (total non-coder guess).

I will post streamlined instructions and a sample project later for anyone else looking to write to Google Docs and/or pull data from Google Docs. Thanks for all your help team!

Hi All, sorry for the delay. Should anyone need to know how to push variables to a Google Sheet and then pull back a variable, I have attached a sample Storyline File as well as the published folder which I had to edit. Feel free to reach out if you need more specific directions on what I did.

Hi, Richard! Many thanks for stopping back in to share the solution that worked for you! As we are not able to provide support for JS, I am sure lots of others in the community will find this very helpful. :)