Spreadsheet programming with Google Apps Script – An example

Spreadsheet programming with Google Apps Script – An example


Let’s familiarize with a popular yet unnoticed Google product – The Google Apps Script. Mostly , apps script is used in collaboration with Google spreadsheets, Google docs in the recent scenario to manage the data within them. But with a strong foundation of JavaScript, definitely it can be used as a shortcut for making functional and practical web applications. Google Apps Script uses JavaScript as it’s scripting language.Tutorials and examples provided within the site will be an aid in the journey.

Keeping the talks of app script aside, let’s build a simple web application to append data to a row i.e, add a row of data to a spreadsheet. A prior knowledge of HTML fundamentals and JavaScript basics are essential to code.

A reminder to the reader’s, from 2014 some User interface functions and objects like UiApp, createApplication(), Widget, IDE for GUI..etc have been deprecated. It is advisable to use HTMLServices instead for simplicity and troubleshooting while developing the GUI.

Get started..!!

1. Open Google Drive:

Login to your Google Drive account and click New->Google Spreadsheet. When an empty spreadsheet opens, rename it. Click on Tools->Script editor.

If you want to deploy any stand- alone web applications that does not involve any documents or spreadsheets from Google Drive, you can go to the link directly.





The blank script editor will look like this shown below.

Screenshot from 2016-12-18 22-56-14




2.Create a new Html file:

Create a new html file from File->New->Html file. Give the name of new html file as index.html. Type in the code given below inside the body tag(<body></body>). Keep the other tags given by default untouched.

Screenshot from 2016-12-19 23-51-26




3. Manipulate Code.gs file:

Include Html Services:

Now manipulate the Code.gs file provided with a blank myfunction() by default. If you want you can rename it according to your purpose. It is the function where the essentials to build GUI are specified. Code.gs is basically a pure JavaScript file.

I’ve renamed the default myfunction() as demoHtmlService() as it is the function where the html file created before needs to be included. Type in the following code to get your UI built.
function demoHtmlServices() {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
html = HtmlService.createHtmlOutputFromFile('index');

To append data to the spreadsheet:

Google App script is developed solely with the idea to manage spreadsheets. To insert data to the spreadsheet, create a function named getValuesFromForm()  below the previous code. JavaScript has many inbuilt library functions like Appendrow() to make the coding less sophisticated. Insert the code below inside the user defined function.

function getValuesFromForm(form){
var firstName = form.firstName;
sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

The final Code.gs file will look like this:

Screenshot from 2016-12-19 23-51-07





Debug your code and hit Run to execute the code. Test the code to check the working by giving in some data. The final output will be like:

Screenshot from 2016-12-19 23-52-18




If you’re stuck somewhere in between or require any additional features to this example, Check out the following links:

Web apps- Google developers

Guide to include Html Services

Also read:Tutorials- App Script

Try this out and have fun with Google Apps Script..!! 🙂

Comments are closed.