Forum Discussion

mahbag-e8b66d7e's avatar
mahbag-e8b66d7e
Community Member
2 years ago

Order the creation of a new sheet in google sheet from storyline

Hi,Is it possible to order the creation of a new sheet in Google Sheets from within the story line?
storyline 360
Dave-Ruckley's avatar
Dave-Ruckley
Community Member
2 years ago

You can with some Javascript and using Apps Script within the Google Sheet. I've used this method as part of a Top Trumps game I'm building.

Javascript

const player = GetPlayer();
const GoogleSheetURL = player.GetVar("Game_Variable_Google_Sheet_URL");
const sheetName = "TopTrumps";
const newSheetName = player.GetVar("YOUR_SHEET_NAME_VARIABLE");

// Specify the 'action' parameter for sheet creation
const action = "create";

// Construct the URL with all necessary parameters
const urlWithParameters = `${GoogleSheetURL}?sheetName=${encodeURIComponent(sheetName)}&newSheetName=${encodeURIComponent(newSheetName)}&action=${encodeURIComponent(action)}`;

fetch(urlWithParameters, {
method: "GET",
cache: "no-cache",
})
.then(response => response.text()) // Parse the response as text
.then(result => {
// Set the Storyline variable with the received sheetName
player.SetVar("YOUR_SHEET_NAME_VARIABLE-ACTUAL_NAME", result);
})
.catch(error => {
console.error('Error creating new sheet:', error);
});

Apps Script

function createNewSheet(sheetName) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Check if the sheet with the given name already exists, if yes, append a unique suffix
  let counter = 1;
  let finalSheetName = sheetName;
  while (spreadsheet.getSheetByName(finalSheetName) !== null) {
    finalSheetName = sheetName + "_" + counter;
    counter++;
  }

  // Create a new sheet with the final name
  const newSheet = spreadsheet.insertSheet(finalSheetName);

  // Return the name of the created sheet as plain text
  return finalSheetName;
}

function doGet(e) {
  const params = e.parameter;
  const action = params.action;  // 'create' or 'delete'
  const sheetName = params.sheetName;

  if (action === 'create') {
    const newSheetName = params.newSheetName;
    const createdSheetNamesJSON = createNewSheet(newSheetName);
    return ContentService.createTextOutput(createdSheetNamesJSON)
      .setMimeType(ContentService.MimeType.JSON);
  }
}
  • mahbag-e8b66d7e's avatar
    mahbag-e8b66d7e
    Community Member
    2 years ago

    Hello and thanks, I will try , thanks

  • mahbag-e8b66d7e's avatar
    mahbag-e8b66d7e
    Community Member
    2 years ago

    Hello, I created the appScrit , when it runs, a new sheet is created, but nothing happens from inside the storyline?The following code was placed in a button and it should run when clicked.

    const player = GetPlayer();

    const GoogleSheetURL = player.GetVar("GoogleSheetURL");
    GoogleSheetURL="https://docs.google.com/spreadsheets/d/1Qc_58NsmmpnZHTw4et8_9HtyNqsC7ewT-0XB95d6Mzc/edit#gid=0";
    const sheetName = "Screen1";
    const newSheetName = player.GetVar("Screen2");

    // Specify the 'action' parameter for sheet creation
    const action = "create";

    // Construct the URL with all necessary parameters
    const urlWithParameters = `${GoogleSheetURL}?sheetName=${encodeURIComponent(sheetName)}&newSheetName=${encodeURIComponent(newSheetName)}&action=${encodeURIComponent(action)}`;

    fetch(urlWithParameters, {
    method: "GET",
    cache: "no-cache",
    })
    .then(response => response.text()) // Parse the response as text
    .then(result => {
    // Set the Storyline variable with the received sheetName
    player.SetVar("newSheetName", result);
    })
    .catch(error => {
    console.error('Error creating new sheet:', error);
    });

Related Content