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