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?
Forum Discussion
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);
}
}
Hello and thanks, I will try , thanks
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);
});
