I have a Web App that runs fine and creates a Google Doc. That app is installed in a button on a new Google Site. Sometimes, I need to create a new doc from the Site, but sometimes I need to create a new doc when I have a Google Spreadsheet open (which sheet contains data relating to the Site and that is embedded in the site).
I have a custom menu on that sheet that does a number of different things. I want to add an item to that menu that accesses the Web App described above and runs it.
First of all: Is this possible?
Second, I assume if it is possible, then somehow, I have to call the Web App script using its URL (as I do with the button it's attached to on my Google Site).
My Web App code is below:
function doGet() {
return HtmlService
.createTemplateFromFile('Index')
.evaluate();
}
function createNewLandscapeSong(objArgs) {
var docName = objArgs.docName;
var songTitle = objArgs.songTitle;
var songWriters = objArgs.songWriters;
Logger.log('songTitle: ' + songTitle)
var doc = DocumentApp.create(docName);
var url = doc.getUrl();
var body = doc.getBody();
var paragraph = body.insertParagraph(0, "");
var text = paragraph.appendText("© "+songWriters);
text.setFontSize(8);
var rowsData = [['PUT FIRST VERSE/CHORUS HERE.', 'PUT SECOND VERSE/NEXT CHORUS/BRIDGE/ETC HERE.']];
var style = {};
body.insertParagraph(0, songTitle)
.setHeading(DocumentApp.ParagraphHeading.HEADING3);
table = body.appendTable(rowsData);
style[DocumentApp.Attribute.BORDER_WIDTH] = 0;
table.setAttributes(style);
return {
url: url,
songTitle: songTitle
};
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
Fill in fields below to name Google Lyric Document<br>
and add the song title and writers.<br>
Then click button to create new song lyric docunent.<br><br>
<input id="idNewDocName" type="text" placeholder="Google Doc Name"><br><br>
<input id="idNewSongTitle" type="text" placeholder="Song Title"><br><br>
<input id="idNewSongWriters" type="text" placeholder="Song Writers"><br><br>
<button onclick="saveUserInput()">Create New Lyric Doc</button>
<script>
window.saveUserInput = function() {
var docName = document.getElementById('idNewDocName').value;
var songTitle = document.getElementById('idNewSongTitle').value;
var songWriters = document.getElementById('idNewSongWriters').value;
console.log('songTitle: ' + songTitle)
google.script.run
.withSuccessHandler(openNewDoc)
.createNewLandscapeSong({docName:docName,songTitle:songTitle, songWriters: songWriters})
}
function openNewDoc(results){
window.open(results.url, '_blank').focus();
}
</script>
</body>
</html>
/*This menu in use.
This function creates the custom menu. Note: Line 3 throws error, but this script works.*/
function onOpen() {
//SpreadsheetApp.getActiveSpreadsheet().toast('Task started');
var ui = SpreadsheetApp.getUi();
ui.createMenu('SP')
.addItem('Website', 'openSite')
.addItem('Open Google Drive', 'openDrive')
.addItem('Open Old SP Site', 'openOldSP')
.addItem('Create', 'openCreate')
.addItem('Perform', 'openPerform')
.addItem('Catalog', 'openCatalog')
.addItem('New Lyric - Landscape', 'createLandscapeLyricDoc')
.addItem('New Lyric - Landscape Test', 'newLyricTest')
.addItem('New Lyric - Portrait', 'openPortrait')
.addItem('Add Song to Catalog', 'addSong')
.addToUi();
}
function openSite() {
var selection = SpreadsheetApp.getActiveSheet();
var html = "<a href='https://sites.google.com/view/sp-site/catalog'; target='_blank'>Open SP</a>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open SP');
}
function openDrive() {
var selection = SpreadsheetApp.getActiveSheet();
var html = "<a href='https://drive.google.com/drive/my-drive'; target='_blank'>Open My Google Drive</a>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open My Google Drive');
}
function openOldSP() {
var selection = SpreadsheetApp.getActiveSheet();
var html = "<a href=url'; target='_blank'>Open Old SP Site</a>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open My Google Drive');
}
function openCreate() {
var selection = SpreadsheetApp.getActiveSheet();
var html = "<a href='url'; target='_blank'>Open Create Spreadsheet</a>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Create Sheet');
}
function openPerform() {
var selection = SpreadsheetApp.getActiveSheet();
var html = "<a href= 'url'; target='_blank'>Open Perform Spreadsheet</a>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Perform Sheet');
}
function createLandscapeLyricDoc() {
var doc = DocumentApp.create('Rename');
var title = "replace with song title and then link this text to song title cell in Catalog Spreadsheet"
var url = doc.getUrl();
var body = doc.getBody();
var paragraph = body.insertParagraph(0, "");
var text1 = paragraph.appendText("© replace with writer(s)");
text1.setFontSize(8);
var rowsData = [['PUT FIRST VERSE/CHORUS HERE. (SUGGEST USE ALL CAPS.)', 'PUT SECOND VERSE/NEXT CHORUS/BRIDGE/ETC HERE.']];
var style = {};
body.insertParagraph(0, title)
.setHeading(DocumentApp.ParagraphHeading.HEADING3);
table = body.appendTable(rowsData);
style[DocumentApp.Attribute.BORDER_WIDTH] = 0;
table.setAttributes(style);
var html = '<a href= "' + url + '"; target="_blank">Open new lyric doc</a>;'
var selection = SpreadsheetApp.getActiveSheet();
var userInterface = HtmlService.createHtmlOutput(html);
/*Note: The following line throws error when you debug,
but the script works from the Catalog Sheet SP Menu.*/
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Landscape New Lyric Doc');
}
function newLyricTest() {
// This is where I want to put call to run the SPSiteNewSongButtonScript (Web App)
}
function openPortrait() {
var html = "<a href= 'url'; target='_blank'>Open 1-Column Lyric Template</a>";
var selection = SpreadsheetApp.getActiveSheet();
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Portrait New Lyric Doc');
}
function addSong() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow()
sheet.appendRow([lastRow+1]);
SpreadsheetApp.flush();
var range = sheet.getRange(sheet.getLastRow(), 1);
var songTitle = Browser.inputBox('New Song', 'Enter the song title', Browser.Buttons.OK_CANCEL);
var namedRange = sheet.getRange("Title");
var range=sheet.getRange(sheet.getLastRow(), namedRange.getColumn())
range.setValue(songTitle);
SpreadsheetApp.setActiveRange(range);
}
There are few ways to approach this,
1) Use sidebar or custom dialogs to render the HTML page in the google spreadsheet. And it behaves like a webapp within the spreadsheet.
Pseudocode:
Firstly make a copy of your webapp in your spreadsheet (html and code.gs) and then modify your newLyricTest()
function newLyricTest()
{
var html = HtmlService.createHtmlOutputFromFile('Index')
.setTitle('My custom sidebar')
.setWidth(300);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(html);
}
When you run the above code, the value for html page will be rendered in the side of the spreadsheet where you can enter your value and run the save the file.
2) You can just use prompts to get the values for docName, songTitle and songWriters. Pseudocode:
function newLyricTest()
{
var ui = SpreadsheetApp.getUi();
var result = ui.prompt(
'New Lyric Test',
'Please enter Document name:',
ui.ButtonSet.OK_CANCEL);
// Process the user's response.
var button = result.getSelectedButton();
var text = result.getResponseText();
if (button == ui.Button.OK) {
// User clicked "OK".
var docName = text
} else if (button == ui.Button.CANCEL) {
// User clicked "Cancel".
return // exit function
} else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
return // exit function
}
// Now repeat the process as above to get values for songTitle and songWriters.
// once you have the values of all these variables you can run the remaining code
// of function createNewLandscapeSong()
Logger.log('songTitle: ' + songTitle)
var doc = DocumentApp.create(docName);
var url = doc.getUrl();
var body = doc.getBody();
var paragraph = body.insertParagraph(0, "");
var text = paragraph.appendText("© "+songWriters);
text.setFontSize(8);
var rowsData = [['PUT FIRST VERSE/CHORUS HERE.', 'PUT SECOND VERSE/NEXT CHORUS/BRIDGE/ETC HERE.']];
var style = {};
body.insertParagraph(0, songTitle)
.setHeading(DocumentApp.ParagraphHeading.HEADING3);
table = body.appendTable(rowsData);
style[DocumentApp.Attribute.BORDER_WIDTH] = 0;
table.setAttributes(style);
return {
url: url,
songTitle: songTitle
};
}
The above code is modified copy of your code from your webapp. I have given an example of use prompt to get the value of docName, you can similarly get values of songTitle and songWriters. Once you have these values you can simply the run the remaining of your code from the web app.
Note: This by no means an exhaustive list of ways to approach this problem. Also, note both these methods rely on having a copy of the web app codes in the spreadsheet script editor. Finally, all the code above are pseudo codes and have not been tested. The codes were copied from examples given in the sidebar and prompt documentation listed above.