Broken down to its most elemental, what I want to do from a web app context is:
- Ask for some data from user.
- Display some data to user.
- Ask for some more data from user.
- Use all that data in a web app.
More specifically, I am trying to build a Google Script web app that does the following:
- Presents an html page where the user can input a user ID number.
- Takes that number and finds the last line on a spreadsheet belonging to that user.
- Displays that last line number to the user (this is the first point at which I am stumped—see below for what I have tried).
- Presents a 2nd html input page where the user can either accept the last line info displayed to them, or enter an alternate number (and some other info).
- All of that info is then used to create a Google Doc and add info about that Google Doc on a new row in a Google spreadsheet.
I have tried:
(a) Class PromptResponse [ui.prompt]
(b) alert(Prompt)
(c) showModalDialog
(d) show ModelessDialog
All of these failed as they apparently must be triggered from a bound app.
I considered the concept of having two doGet statements in a single webApp which led me to Linking to another HTML page in Google Apps Script, but that seems to deal with a two-page SINGLE html rather than two separate html pages (which is what I think I need).
I also considered using the Browser.msgBox in the Class CacheService but that produced the same context error as (a) thru (d) above.
Lastly, I thought about—rather than displaying the user ID number from (1) above—saving the variable and inserting it later in the script (i.e., loading it in (4) above). That led me to the CacheService. But I could not see how to make that work and in any event, it’s not really what I want to do.
GS
function doGet() {
return HtmlService
.createTemplateFromFile('Index')
.evaluate();
}
function getSongId(objArgs){
// Get User's Catalog SS URL from Master Users List
var userId = objArgs.user;
var masterSSId = "ID";//This is the ID to the master users list SS.
var userSS = SpreadsheetApp.openById(masterSSId);//Open
var userSheet = userSS.getActiveSheet();
var nameOfUserRange = "User" + userId; //this constructs the user ID, like "user101"
Logger.log("nameOfUserRange = " + nameOfUserRange);
var userNamedRange = userSS.getRangeByName(nameOfUserRange); //this returns "Range" to pass its value on to future code lines
var cell = userNamedRange.activate(); //activates range and first cell in range
var namedUrlRange = userSS.getRange('SongsSheetUrl'); //this gets the SongSheetUrl named range
var userCol = namedUrlRange.getColumn(); //this gets col # of namedUrlRange
Logger.log("userCol = " + userCol);
var userSsUrl = cell.offset(0, userCol-1, 1, 1). getValue(); //this gets the user's Catalog SS URL
Logger.log("userSsUrl = " + userSsUrl);
var ss = SpreadsheetApp.openByUrl(userSsUrl);
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
var songId = lastRow+1;
Logger.log("songId = " + songId);
//some code here that displays songID to user
HTML "Index"
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<center> Enter your User ID below.
<input id="userId" type="text" placeholder="User ID"><br><br>
<button onclick="saveUserInput()">Continue</button>
</center>
<script>
window.saveUserInput = function() {
var user = document.getElementById('userId').value;
console.log('userId: ' + userId)
google.script.run
.withSuccessHandler(openPrompt)
.getSongId({user:user})
}
function openPrompt(results){
window.open(results.url, '_blank').focus();
}
</script>
</body>
</html>
songId Code
function getSongId() {
var masterSSId = "ID";//This is the ID to the master users list SS.
var userSS = SpreadsheetApp.openById(masterSSId);//Open
var userSheet = userSS.getActiveSheet();
var nameOfUserRange = "User" + userId; //this constructs the user ID, like "user101"
var userNamedRange = userSS.getRangeByName(nameOfUserRange); //this returns "Range" to pass its value on to future code lines
var cell = userNamedRange.activate(); //activates range and first cell in range
var namedUrlRange = userSS.getRange('SongsSheetUrl'); //this gets the SongSheetUrl named range
var userCol = namedUrlRange.getColumn(); //this gets col # of namedUrlRange
var userSsUrl = cell.offset(0, userCol-1, 1, 1). getValue(); //this gets the user's Catalog SS URL
var ss = SpreadsheetApp.openByUrl(userSsUrl);
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
var songId = lastRow+1;
}
As noted, I got "context" errors with everything I tried. BTW, I also created a web app that had 2 GS pages and 2 Index pages, and that just displayed both html pages on one page, and I still couldn't figure out how to display the User ID.
Finally, I spent a lot of hours, and used a lot of search terms, both at SO and the web in general trying to find someone else that has tackled this problem—and came up goose eggs.
Note: To respect "minimal, and verifiable," I have not included the script that asks for the 2nd set of info, but it is written and works.
Update: The following SO Question/Answer showed up to the right of this question: "Web apps+ remotely using script" after I posted it
It seems to in part solve my problem. At least it does display the user's User ID input, but I need it to display info I pull from a Google sheet based on the User ID (i.e., the songId). Using the doGet(e) approach, I still don't know where to put the getSongIdcode that gets the songId. I have added that code above.
Revised Code
gs
function doGet() {
return HtmlService.createHtmlOutputFromFile('Index');
}
function getSongId(uObj) {
var userId = uObj.user;
var masterSSId = "ID";//This is the ID to the master users list SS.
var userSS = SpreadsheetApp.openById(masterSSId);//Open
var userSheet = userSS.getActiveSheet();
var nameOfUserRange = "User" + userId; //this constructs the user ID, like "user101"
Logger.log("nameOfUserRange = " + nameOfUserRange);
var userNamedRange = userSS.getRangeByName(nameOfUserRange); //this returns "Range" to pass its value on to future code lines
var cell = userNamedRange.activate(); //activates range and first cell in range
var namedUrlRange = userSS.getRange('SongsSheetUrl'); //this gets the SongSheetUrl named range
var userCol = namedUrlRange.getColumn(); //this gets col # of namedUrlRange
var userSsUrl = cell.offset(0, userCol-1, 1, 1). getValue(); //this gets the user's Catalog SS URL
var ss = SpreadsheetApp.openByUrl(userSsUrl);
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
var songId = lastRow+1;
Logger.log("songId = " + songId);
return songId;
}
html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<center>
Enter your User ID below.
<input id="userId" type="text" placeholder="User ID"><br>
<input type="button" value="Continue" onclick="saveUserInput()">
<div id="results"></div>
</center>
<script>
function saveUserInput() {
var user = document.getElementById('userId').value;
google.script.run
.withSuccessHandler(function(hl){
document.getElementById('results').innerHTML=hl;
})
.getSongId({user:user})
}
</script>
</body>
</html>
