Forum Discussion

storyline33hita's avatar
storyline33hita
Community Member
2 years ago

Exporting Storyline variables to Google Sheets

Hi, I'm creating a registration form directly in Storyline.

the input fields are saving the filled text to variables,

and I try to export the variables data to Google Sheets by using the next code in the Storyline Javascript trigger that is linked to a button:

var player = GetPlayer();
var firstname = player.GetVar("firstname");
var lastname = player.GetVar("lastname");
var role = player.GetVar("role");
var id = player.GetVar("id");
var phone = player.GetVar("phone");

// Create an XMLHttpRequest object to send the data
var xhr = new XMLHttpRequest();
xhr.open('POST', 'https://script.google.com/macros/s/AKfycbwQemCAMG2o5N9tS0319Jy5BGSagAY-Lr_jUEI8gCEffSq_Qvn4HTNjtrkf20-nuldR/exec', true);
xhr.setRequestHeader('Content-Type', 'application/json');

// Define the data to send
var data = JSON.stringify({ 
  firstname: firstname,
  lastname: lastname,
  role: role,
  id: id,
  phone: phone
});

// Set up the callback function for when the request completes
xhr.onload = function() {
  if (xhr.status === 200) {
    console.log('Data sent to Google Sheets successfully.');
  } else {
    console.error('Error sending data to Google Sheets:', xhr.statusText);
  }
};

// Handle network errors
xhr.onerror = function() {
  console.error('Network error occurred while sending data to Google Sheets.');
};

// Send the request
xhr.send(data);

the code for the AppsScript in Google Sheets is the next code:

function doPost(e) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var data = JSON.parse(e.postData.contents);




  // Get the values from Storyline variables.

  var firstname = data.firstname;

  var lastname = data.lastname;

  var role = data.role;

  var id = data.id;

  var phone = data.phone;




  // Get the next available row in the spreadsheet.

  var lastRow = sheet.getLastRow() + 1;




  // Update the desired cells with the Storyline variable data.

  sheet.getRange("A" + lastRow).setValue(firstname);

  sheet.getRange("B" + lastRow).setValue(lastname);

  sheet.getRange("C" + lastRow).setValue(role);

  sheet.getRange("D" + lastRow).setValue(id);

  sheet.getRange("E" + lastRow).setValue(phone);




  // Return a success message.

  return ContentService.createTextOutput("Data added to Google Sheets.").setMimeType(ContentService.MimeType.TEXT);

}

I got the code using ChatGPT, it doesn't seem to work.

Any suggestions?