Forum Discussion

lemidadigital's avatar
lemidadigital
Community Member
6 months ago
Solved

Exporting Storyline variables to Google Sheets

Hello everyone, I have no programming background and would appreciate some help.

I am trying to export variables from Storyline to a Google Sheet. The course is published on the REACH 360 platform.
I used the following code for the excutejavascript trigger: 

console.log("JavaScript from Trigger is running!");
var player = GetPlayer();

$(document).ready(function() {
  $.ajax({
    url: "https://script.google.com/macros/s/.../exec",
    type: "POST",
    dataType: "json",
    contentType: "application/json",
    data: JSON.stringify({
      "FullName": player.GetVar("FullName"),
    }),
    success: function(data) {
      console.log("Data sent successfully:", data);
    },
    error: function(error) {
      console.error("Error sending data:", error);
    }
  });
});

return false;

I receive the following response in the console:
Javascript from Trigger is running!
and immediately after that the error: actionator::exeJavaScript - $ is not defined

I read the thread: https://community.articulate.com/discussions/articulate-storyline/exporting-variables-into-a-google-spreadsheet

But because the course is published on the reach 360 platform, I have no idea how to get to the files: story.html and story_html5.html to add the missing JQUERY library as explained in the thread in the link.
I would appreciate any help. What can I do?

reach 360
storyline 360
  • lemidadigital's avatar
    lemidadigital
    6 months ago

    Eventually I realized that I had to look for a good AI tool to help me solve the problem. I found WINDSURF, an AI tool that wrote me a script for the execute javascript trigger without having to load the JQuery library, and a script for the apps script.
    If anyone needs the scripts - I can upload them

4 Replies

  • SBP_Inc's avatar
    SBP_Inc
    Community Member
    6 months ago

    I ended up having to add additional javascript in the trigger to load the popular jquery library itself via javascript.

    • lemidadigital's avatar
      lemidadigital
      Community Member
      6 months ago

      Eventually I realized that I had to look for a good AI tool to help me solve the problem. I found WINDSURF, an AI tool that wrote me a script for the execute javascript trigger without having to load the JQuery library, and a script for the apps script.
      If anyone needs the scripts - I can upload them

  • Genebaba's avatar
    Genebaba
    Community Member
    5 months ago

    Appreciate if can share the script, and a how to get it done path. me, an SME and ID, absolutely alien to scripts and codes. copy paste is the only option for me to work with them.

  • lemidadigital's avatar
    lemidadigital
    Community Member
    5 months ago

    It's quite long, so I asked WINDSURF to prepare step-by-step instructions.
    I really recommend using WINDSURF. It's free, and it allows me to explain in simple language what I want, and get code + explanations on how to use it. In case something doesn't work, I just write what the error is, and WINDSURF looks for a solution.

    Additional note: I was unable to pass built-in variables from Storyline to google sheets, so I defined variables like Custom_Quiz12_Score that would get their values ​​from the built-in variables, using the SET VSLUE trigger.

    Anyway, here are the instructions:

    1. Google Apps Script Setup
    1. Create a New Google Sheet:
      • Go to Google Sheets and create a new blank spreadsheet.
      • Rename the spreadsheet to something relevant (e.g., "Course Data").
      • Important: Note down the Sheet ID from the URL. It's the long string of characters between /d/ and /edit.
        • Example: https://docs.google.com/spreadsheets/d/THIS_IS_THE_SHEET_ID/edit#gid=0
    2. Prepare Your Sheet:
      • Open the first sheet (usually named "Sheet1").
      • (Recommended) Rename this sheet to match your desired sheet name (this will be configured in the Apps Script).
      • Set up the header row (the first row) with the column names you expect to receive from Storyline. At a minimum, based on the scripts, you'll likely need:
        • YOUR_ID_COLUMN (replace with your actual ID column name)
        • FullName
        • Timestamp (this will be auto-populated by the script)
        • YOUR_SCORE_COLUMN (replace with your actual score column name)
    3. Create and Configure the Apps Script:
      • In your Google Sheet, go to Extensions > Apps Script. This will open a new Apps Script project.
      • Delete any default code in the Code.gs file.
      • Copy the entire content of Apps Script code.txt and paste it into the Apps Script editor.
      • Save the script project (e.g., "Storyline Data Handler").
    4. Set Script Properties:
      • In the Apps Script editor, go to File > Project properties > Script properties.
      • Click Add script property.
      • For Property, enter KEY.
      • For Value, paste the Google Sheet ID you copied in Step I.1.
      • Click Save script properties.
    5. Review and Update Script Configuration (TODOs):
      • Carefully review the Apps Script code for lines marked with // TODO:.
      • SHEET_NAME: Ensure this matches the name of your sheet in Google Spreadsheet.
      • YOUR_ID_COLUMN: Update this to match your actual ID column name in the Google Sheet.
      • Score column: Update the score column name to match your actual score column name in both the Google Sheet and Storyline.
      • Timestamp column: The script expects a column named Timestamp. If your sheet uses a different name, update the code accordingly.
    6. Deploy as Web App:
      • In the Apps Script editor, click the Deploy button (top right) and select New deployment.
      • Click the gear icon next to "Select type" and choose Web app.
      • For Description, you can add something like "Storyline Data Receiver".
      • For Execute as, select Me (your email address).
      • For Who has access, select Anyone (if your Storyline course will be accessed by users not logged into a Google account) or Anyone with Google account (if users will be logged in). For widest compatibility, Anyone is often preferred, but be mindful of data privacy.
      • Click Deploy.
    7. Authorize the Script:
      • Google will ask you to authorize the script. Click Authorize access.
      • Choose your Google account.
      • You might see a "Google hasn't verified this app" warning. Click Advanced and then Go to (your script name) (unsafe).
      • Review the permissions and click Allow.
    8. Copy Web App URL:
      • After successful deployment, a "Deployment updated" dialog will appear.
      • Copy the Web app URL. You will need this for the Storyline setup.
      • Click Done.
    1. Articulate Storyline Setup
    1. Open Your Storyline Project.
    2. Create Storyline Variables:
      • Ensure you have the necessary variables created in Storyline:
        • YOUR_ID_VARIABLE (Text variable to store the user's ID)
        • FullName (Text variable to store the user's full name)
        • YOUR_SCORE_VARIABLE (Number variable for the quiz score).
          • Crucial: The name of this score variable must exactly match the one used in the JavaScript and the corresponding column header in your Google Sheet.
    3. Add JavaScript Trigger:
      • Go to the slide where you want to send the data (e.g., the results slide of a quiz, or a "Submit" button).
      • Add a new trigger:
        • Action: Execute JavaScript
        • When: (e.g., User clicks, Timeline starts, etc. - choose what's appropriate)
        • Object: (e.g., a "Submit" button)
    4. Insert JavaScript Code:
      • Click the [...] button (or "Open script") in the JavaScript trigger window.
      • Copy the entire content of Storyline code.txt and paste it into Storyline's JavaScript editor.
    5. Configure JavaScript (TODOs):
      • Carefully review the JavaScript code for lines marked with // TODO:.
      • WEB_APP_URL: Replace the placeholder URL with the Web App URL you copied from the Google Apps Script deployment (Step I.8).
      • player.GetVar("YOUR_SCORE_VARIABLE"): Ensure the variable name exactly matches the name of your Storyline variable holding the quiz score.
      • YOUR_SCORE_VARIABLE (in userData object): Ensure this key also exactly matches your Storyline variable name and the Google Sheet column header.
    1. How It Works
    1. When the trigger in Storyline is activated, the JavaScript code collects the values from your specified Storyline variables (YOUR_ID_VARIABLE, FullName, YOUR_SCORE_VARIABLE).
    2. It sends this data to the WEB_APP_URL (your Google Apps Script).
    3. The Google Apps Script (handleResponse function) receives the data.
    4. It checks if a row with the given ID already exists in the Google Sheet.
      • If it exists, and the new score is higher than the existing score, it updates the row with all new data provided.
      • If it doesn't exist, it appends a new row with the data.
    5. The script also adds a Timestamp when a new row is added or an existing one is updated.
    1. Testing
    1. Publish your Storyline course (for Web or LMS).
    2. Access the course and trigger the JavaScript action (e.g., complete the quiz and click submit).
    3. Check your Google Sheet. The data should appear.
    4. Check the Apps Script dashboard for executions (Executions tab in the Apps Script editor) if you encounter issues. Logger.log statements in the script will appear here.
    1. Important Considerations & Troubleshooting
    • Case Sensitivity: Variable names, sheet names, and column headers are often case-sensitive. Ensure they match exactly across Storyline, the JavaScript code, and your Google Sheet.
    • Permissions: If data isn't appearing, double-check the Apps Script Web App deployment permissions (Who has access).
    • URLs: Ensure the WEB_APP_URL in Storyline is correct and doesn't have typos.
    • Storyline Variables: Verify that the Storyline variables are being populated correctly before the JavaScript trigger fires.
    • Pop-up Blockers: While less common for this type of data sending, be aware that browser settings could interfere, though usually not an issue for fetch requests.
    • Google Apps Script Quotas: For very high traffic, be aware of Google Apps Script quotas.
    • Error Handling: The provided scripts have some basic error logging. Check the Apps Script execution logs for details if something goes wrong.