C# is a language which was made relatively recently (it has longer history compared to recent languages like python or ruby though). C# was created by Microsoft and is a great language if you want to make applications for devices that are working with Windows OS (computers, tablets, windows phones and so on...).
And I think C# has a lot of good functions and thus is a good language for begginers to start learning programming.
So I will write a tutorial for C#. I hope this will help someone who wants to learn programming. The name is visual tutorial because I think will use a lot of images and pictures in the tutorial.
Wednesday, February 22, 2017
Saturday, February 11, 2017
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">
</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.
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>page2:
<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>
<!-- end table -->
<br>
<input type="button" name="btn" onclick="google.script.run.withSuccessHandler(onSuccess).processForm(this.parentNode);" value="Check!">
</form>
</div>
</body>
</html>
<!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();
}
Jump to other page on google apps script
We have made a web app in previous posts: (Hello World, Use variables in html page, With google spreadsheet). But you would realize that, even if you make 2 or more html pages, you can not easily move to another html page in Google apps script.
In the first place, only one URL is generated for all html pages published for the web app. You can not use the URL simply to link to another page of the web app.
But according to this post in stackoverflaw: http://stackoverflow.com/questions/15668119/linking-to-another-html-page-in-google-apps-script, you can move to another page using google apps script if you can change the doGet() function a bit.
1. Make two html files "page1" and "page2".
page1:
page2:
Note: if you don't write <base target="_top"> tag on the html pages, your URL will not be changed prorerly.
If you use script to change the page:
Move to Page2:
<?var url = getScriptUrl();?>window.top.location.href = '<?!=url?>?page=page2';
Move to Page1:
<?var url = getScriptUrl();?>window.top.location.href = '<?!=url?>?page=page1';
In the first place, only one URL is generated for all html pages published for the web app. You can not use the URL simply to link to another page of the web app.
But according to this post in stackoverflaw: http://stackoverflow.com/questions/15668119/linking-to-another-html-page-in-google-apps-script, you can move to another page using google apps script if you can change the doGet() function a bit.
1. Make two html files "page1" and "page2".
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1>Page 1</h1>
<?var url = getScriptUrl();?><a href='<?=url?>?page=page2'>Link to page2</a>
</body>
</html>
page2:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1>Page 2</h1>
<?var url = getScriptUrl();?><a href='<?=url?>?page=page1'>Link to page1</a>
</body>
</html>
2. Change the doGet() function as follows: (please note this code was cited from stackoverflow)
For copy and paste:
/**3. Check your web app. You will see a link was added that allows you to jump to other pages.
* Get "home page", or a requested page.
* Expects a 'page' parameter in querystring.
*
* @param {event} e Event passed to doGet, with querystring
* @returns {String/html} Html to be served
*/
function doGet(e) {
Logger.log( Utilities.jsonStringify(e) );
if (!e.parameter.page) {
// When no specific page requested, return "home page"
return HtmlService.createTemplateFromFile('page1').evaluate();
}
// else, use page parameter to pick an html file from the script
return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}
/**
* Get the URL for the Google Apps Script running as a WebApp.
*/
function getScriptUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}
Note: if you don't write <base target="_top"> tag on the html pages, your URL will not be changed prorerly.
Move to Page2:
<?var url = getScriptUrl();?>window.top.location.href = '<?!=url?>?page=page2';
Move to Page1:
<?var url = getScriptUrl();?>window.top.location.href = '<?!=url?>?page=page1';
Use Google apps script to make it interact with google spreadsheet
In previous posts (Hello World, Use variables in html page). In this post, we will learn how to use values written google spreadsheet in the web app made by google apps script.
1. Go to the spreadsheet and make a simple list on a sheet whose name is "sheet1" as follows.
A simple list like this:
2. Go to the script editor and write code in the html named as "index" as follows:
For your copy and paste:
Maybe you will be asked this authorization after clicking "Update". Please authorize it.
4. Go to the URL which you published. You will see apple (that was cited from the spreadsheet) is written in the page.
The google apps script always refers to the spreadsheet, so if you change the value to "strawberry", the web app also shows "strawberry" instead of "apple".
Then reload the page... You can see "apple" is changed to "strawberry".
Of course you can use programming things such as for loop, so write codes between "<?" and "?>". For example, change the index as follows:
For your copy and paste:
1. Go to the spreadsheet and make a simple list on a sheet whose name is "sheet1" as follows.
A simple list like this:
| ID | List of fruits |
| 1 | apple |
| 2 | orange |
| 3 | pineapple |
| 4 | tangerine |
| 5 | grape |
2. Go to the script editor and write code in the html named as "index" as follows:
For your copy and paste:
<!DOCTYPE html>3. Save all and make a new version, then publish it. If you don't know how to make versions and publish them, please see previous posts (Hello World or Use variables in html page).
<html>
<head>
<base target="_top">
</head>
<body>
<?
var sheetApp = SpreadsheetApp.getActiveSpreadsheet();
//Or .openById("YOUR_SPREAD_SHEET_ID"); can be used too
//spreadsheet ID can be found in URL of the spreadsheet:
//https://docs.google.com/SPREAD SHEET ID/edit#gid=0
var sheet = sheetApp.getSheetByName("Sheet1");
var data = sheet.getRange(2,2).getValues();
?>
<h1>I like <?= data ?>. </h1>
</body>
</html>
Maybe you will be asked this authorization after clicking "Update". Please authorize it.
4. Go to the URL which you published. You will see apple (that was cited from the spreadsheet) is written in the page.
Then reload the page... You can see "apple" is changed to "strawberry".
Of course you can use programming things such as for loop, so write codes between "<?" and "?>". For example, change the index as follows:
For your copy and paste:
<!DOCTYPE html>Make a new version and publish it. The result is:
<html>
<head>
<base target="_top">
</head>
<body>
<?
var sheetApp = SpreadsheetApp.getActiveSpreadsheet();
//Or .openById("YOUR_SPREAD_SHEET_ID"); can be used too
//spreadsheet ID can be found in URL of the spreadsheet:
//https://docs.google.com/SPREAD SHEET ID/edit#gid=0
var sheet = sheetApp.getSheetByName("Sheet1");
for(var i=2;i<7;i++){
var data = sheet.getRange(i,2).getValues();
output.append('<h1>' + data + '</h1><br>');
}
?>
</body>
</html>
Make a website with google apps script: use variables in html page
We have made a website that displays "Hello World" in the previous post. Now we will use a variable and show its value in a html page.
In Google apps script, you can use variables in html as follows:
<? ?> (used to write any code of google apps script.)
<?= ?> (used to output a value of a variable. The value is automatically escaped.)
<?!= ?> (used to output a value of a variable. The value is not escaped.)
We will learn how they can be used in practice.
1. Go to your spreadsheet and then code editor. Open the html file called "index".
2. Write code as follows:
For your copy and paste:
...then publish the new version as follows:
Please note that you need to choose "2" as "Project version". Otherwise the update will not be reflected.
3. Access to the web app that you have published just now. Now you see the value of the variable is being displayed.
In Google apps script, you can use variables in html as follows:
<? ?> (used to write any code of google apps script.)
<?= ?> (used to output a value of a variable. The value is automatically escaped.)
<?!= ?> (used to output a value of a variable. The value is not escaped.)
We will learn how they can be used in practice.
1. Go to your spreadsheet and then code editor. Open the html file called "index".
2. Write code as follows:
For your copy and paste:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<?
var value = "test";
?>
<h1><?= value ?> </h1>
</body>
</html>
Save all and click "Manage version" from the "File" tab. Make a new version.
Please note that you need to choose "2" as "Project version". Otherwise the update will not be reflected.
3. Access to the web app that you have published just now. Now you see the value of the variable is being displayed.
Make a website with google apps script: Hello World
1. Go to google spreadsheet's website.
2. Make a spreadsheet.
3. Click "Tools" from the menu and then click "Script editor."
4. You will see this will be displayed.
5. Add a html file called "index" as follows.
Now a html is added to your project.
Then change the html code as follows:
You can see "<h1>Hello World</h1>" was added to the original code.
7. Change the function on Code.gs to doGet() from nyFunction() as follows.
Namely, delete this code that had been written:
And copy and paste this code instead:
8. Save it.
9. Click "Manage versions" from "File" tab.
Then, you will see this will be displayed. Write something in the box and click "Save New Version."
And a new version will be added.
10, Now click "Deploy as web app" from "Publish" tab.
11 This will be displayed. If you want to publish the web app to anyone (even for those who don't have google account), choose "Anyone, even anonymous" from "Who has access to the app." When you finish adjusting this setting, click "Deploy".
12, A URL to the web app will be shown. Copy the URL and access to this URL from your browser. This is the link for your web app.
13, Hello World!!!
2. Make a spreadsheet.
3. Click "Tools" from the menu and then click "Script editor."
4. You will see this will be displayed.
5. Add a html file called "index" as follows.
Now a html is added to your project.
Then change the html code as follows:
7. Change the function on Code.gs to doGet() from nyFunction() as follows.
Namely, delete this code that had been written:
function myFunction() {
}
And copy and paste this code instead:
function doGet() {
// This reads the html called "index".
var html = HtmlService.createTemplateFromFile("index");
// This takes HTML code and interprets it into what you see visually.
return html.evaluate();
}
8. Save it.
9. Click "Manage versions" from "File" tab.
Then, you will see this will be displayed. Write something in the box and click "Save New Version."
And a new version will be added.
10, Now click "Deploy as web app" from "Publish" tab.
11 This will be displayed. If you want to publish the web app to anyone (even for those who don't have google account), choose "Anyone, even anonymous" from "Who has access to the app." When you finish adjusting this setting, click "Deploy".
12, A URL to the web app will be shown. Copy the URL and access to this URL from your browser. This is the link for your web app.
13, Hello World!!!
