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, 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);
      });

  • Dave-Ruckley's avatar
    Dave-Ruckley
    Community Member
    2 years ago

    There are a few issues I can spot that may cause problems. First you're defining GoogleSheetURL from a variable and then you've got it in the code but not defined with "const". You should choose one method or the other.

    Have you checked that the URL is the one you get when you deploy the apps script?

    Also const sheetName = "Screen1"; should be the name of the the first sheet in the worksheet.

    I'm not an expert on Javascript, a lot of this was developed with the help of ChatGPT - but those would be the first things I fixed/checked.

Related Content