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

Dave Ruckley

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

7 months ago09/27/23 at 5:23 pm (UTC)

This post was removed by the author

7 months ago09/27/23 at 5:26 pm (UTC)

Hello and thanks, I will try , thanks

7 months ago09/29/23 at 6:43 am (UTC)

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

7 months ago09/29/23 at 9:03 am (UTC)

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.

4 Replies