I am trying to "install" a Google script as a menu item in a Google Site (a new Google Site--not the "Classic Google Site").
This script is supposed to add a new line to a spreadsheet that is embedded in the Site. That spreadsheet (named "Catalog") is a Google Sheet and the script (I want to run from the Google Site) is already installed as a bound script in the Sheet and works when toggled from the S heet.
The script essentially adds a new line at the bottom of my song catalog to create a new line to add a new song.
The script that is working in the spreadsheet is:
function addSong() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var numRows = sheet.getLastRow()
sheet.appendRow([numRows+1]);
}
My problem is I do not know how to access the script from the site. I.e., I do not know how to reference the script in the site menu (by URL or what?) to get it to run.
The way I envision it working is that I have a sub-page under my "Catalog" menu in the site called "Add Song" and when I toggle it, I want the script to run and add a line to the Catalog Sheet.
Any help?
Updated with a cleaner example:
Firstly, the best way to access your GAS script from elsewhere is probably to publish it as a Web App. That way the script can be called via its published URL. The main requirement for a Web App is that it has a doGet() function as an entry point.
I'm pretty sure that you can only associate a new Google Sites menu item with another page within the same site. So you can’t invoke the WebApp (via it's URL) directly from a menu. But on that new Sites page the menu item takes you to, you can either:
The code below is a simple web app, which is a bound function within a sheet. If you publish this (Publish >> Deploy as a WebApp...) and then grab the url you can associate this with a button or call from the page.
function doGet(request){
addSong(request.parameter.song); // Call your own function.
var response = {status: "SUCCESS", data: request.parameter.song};
// Return the response.
return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}
function addSong(song) {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var numRows = sheet.getLastRow()
sheet.appendRow([numRows+1,song]);
}
To use a button add the following as a link with the button:
https://script.google.com/macros/s/<your web app ref>/exec?song=%22Bohemian%20Rhapsody%22
The code below is an example of automatically calling the WebApp from embedded HTML in the Sites web page accessed via the menu. There are other ways of invoking the Web App from JavaScript, but XMLHttpRequest() works with most browsers.
<!DOCTYPE html>
<html>
<body onload="test('https://script.google.com/macros/s/<your web app ref>/exec?song=StairwaytoHeaven') ;">
<p id="message">? </p>
<script>
function test(url) {
var XHR = new XMLHttpRequest();
XHR.onreadystatechange = function() {
if(XHR.readyState == 4) {
if(XHR.status == 200) {
console.log("SUCCESS: status = "+XHR.status);
document.getElementById("message").innerHTML = XHR.responseText;
console.log(XHR.responseText);
} else {
console.log("FAILURE: status = "+XHR.readystate+" "+XHR.status);
document.getElementById("message").innerHTML = "Failure";
}
}
}
XHR.open("GET", url + ((/\?/).test(url) ? "&" : "?") + Date.now()); // Add timestamp to disable caching, if required.
XHR.send();
return;
}
</script>
</body>
</html>