Notes on everything: Send & receive information from google spreadsheet and use it in a function of google apps script

We will learn how to send information from excel to the web app of Google apps script and use it in a function. Namely, we will learn how to use "google.script.run.withSuccessHandler(onSuccess).yourFunction();"

This Client side API (google.script.run.withSuccessHandler(onSuccess).yourFunction(); ) allows you to run a server-side function, then the result of the server side function is returned to the call-back function, namely onSunccess function above, so that the call-back function can run with the result.

1. Make a html which is named "page1" and "page2".

For copy and paste:

page1:

<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function onSuccess(res){
if(res){
<?var url = getScriptUrl();?>window.top.location.href = '<?=url?>?page=page2';
}else{
alert("This fruit was not found in the list");
}
}
</script>
</head>
<body>
<h1>Page 1</h1>
<div align="center">
<form name="form1">
<table border="0">
<tbody>
<tr>
<th>Fruit check</th>
<td>
　　　　　 <input type="text" name="fruit">
</td>
</tr>
</tbody>
</table>

<br>
<input type="button" name="btn" onclick="google.script.run.withSuccessHandler(onSuccess).processForm(this.parentNode);" value="Check!">
</form>
</div>
</body>
</html>

page2:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1>Page 2</h1>
<h2>Great!</h2>
<?var url = getScriptUrl();?><a href='<?=url?>?page=page1'>Link to page1</a>
</body>
</html>

By the way, this.parentNode means the <form></form> because it is the parent node of the input tag.

edit: because it uses iframe, please use window.top.location.href to jump to another page.

2. Change the code of "Code.gs" as follows:

For your copy and paste:
function doGet(e) {
Logger.log( Utilities.jsonStringify(e) );
if (!e.parameter.page) {
return HtmlService.createTemplateFromFile('page1').evaluate();
}
return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}

function getScriptUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}

function processForm(form){
var id = "Your_spreadsheet_ID";
//Your spreadsheet ID can be found in spreadsheet's URL such as:
//https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0
var myDoc = SpreadsheetApp.openById(id);
var sheet = myDoc.getSheetByName("Sheet1");

for(var i=2;;i++){
var dfruit = String(sheet.getRange(i,2).getValue());
if(dfruit === String(form.fruit) && dfruit !== ""){
return true;
}else if(dfruit === ""){
return false;
}
}
}

3. Save them and make a new version, and publish it. You will see a simple quiz web app is opened.

edit:
This doGet function might be better if you get error when moving to other page.

function doGet(e) {
Logger.log(Utilities.jsonStringify(e));
if (!e.parameter.page) {
//return HtmlService.createTemplateFromFile('index').evaluate();
e.parameter['page'] = "page1";
}
return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}

Saturday, February 11, 2017

Send & receive information from google spreadsheet and use it in a function of google apps script