Exporting data to Google Sheets from Storyline – JavaScript in Storyline 360 #4

August 21, 2022

Sometimes we need a way of exporting data out of a module and saving it somewhere, and this can often be tricky, especially without a way to query a database.

Surprisingly, Google sheets has the capacity to store information like a database. In this example, we’re going to create a method of recording feedback from a learner and storing that in a Google Sheet. Bear in mind this may not be the best place to store any sensitive or personal information!

You can click on the images below to enlarge them in a new tab! 🙂

 

How to:

Step 1:  Start by creating a Storyline 360 project, that has a Text Entry to allow the user to record their thoughts, and a Submit button that will be used to submit the data to the Sheet.

Step 2: Set up a new Variable called “UserFeedback” with the type of Text, and update the built-in trigger on the Text Entry to ‘When the Text Entry loses focus, Set UserFeedback equal to the typed value.’

Step 3: Add a trigger to the Submit button, to ‘Execute JavaScript, when the user clicks Submit button’

Step 4: Open the JavaScript editor and paste in the following code:

 
// Replace this URL with your Web App URL
const url = "https://script.google.com/macros/s/#############/exec";

const player = GetPlayer();
let feedBack = player.GetVar("UserFeedback"); // Replace "UserFeedback" with your Storyline Variable.

fetch(url,{
  method: 'POST',
  mode: 'no-cors', 
  cache: 'no-cache', 
  headers: {'Content-Type': 'application/json'},
  redirect: 'follow',
  body: JSON.stringify({text: feedBack}) 
});

 

The URL in the code is where we will paste a link to the Google Sheet that we’re about to set up.

Step 5: Next we need to set up the Google Sheet ready for the data to be sent to it. Go to Google Sheets and Sign in to your google account, if you don’t have one, you’ll need to create one. Once you’re signed in and on the dashboard, click Blank to create a new sheet.

Step 6: Start your sheet by giving the Sheet a name, in our demonstration we call ours Learner Feedback, put a title in the A1 column and increase the width. But this can be styled anyway you prefer.

Step 7: To set up our code, click Extensions and then Apps Script. When the Apps Script project opens, give it a name, we call ours Learner Feedback, to reflect the name of the Sheet.

Step 8: Delete the code on screen, and then paste in the following code

 

function doPost(e){
const body = e.postData.contents;
const bodyJSON = JSON.parse(body);
let sheet = SpreadsheetApp.getActiveSheet();
let values = sheet.getRange("A:A").getValues();
let maxIndex = values.reduce(function(maxIndex, row, index) {
return row[0] === "" ? maxIndex : index;
}, 0);
myRange = sheet.getRange(maxIndex + 2, 1);
myRange.setValue(bodyJSON.text);

}

Step 9: Next we need to deploy this as a Web application, Click Deploy and then New deployment. When the dialog opens, click the Cog next to Select Type, and choose Web App. Give the app a description, make sure the Execute as is your email address/google account, and under Who has Access, select Anyone. Then click Deploy

Step 10: Next we need to Authorize access to the data in the Sheet, so click the Authorize access button, then chose your google account. At this point it will warn you that Google hasn’t verified this app yet, but since we are building this app, we know this is safe. So click Advanced and then click Go to Learner Feedback (This may say something different if you named your sheet something else). Then click Allow.

Step 11: If all has gone according to plan, you should be presented with a dialog box that contains your Web app URL. Copy this URL.

Step 12: Go back to Storyline, open the JavaScript editor for the trigger on the button and paste the URL into the URL in the code.

Step 13: Publish and View your project, type some text in your text entry, go back to your sheet that you created earlier and you should see the text you typed has now been saved in the Sheet! Awesome, right!

Watch our video tutorial

If you found this useful and there are other topics you would like us to cover, feel free to leave a comment on the video! We look forward to hearing from you.

Be sure to Subscribe to our channel for future tips and tricks.