Google App Scripts

This topic is about writing a Hello World script that will enable us start learn on how to automate tasks in Google Spreadsheets or Google Docs.

It is more similar to the MS Office VBA, but the coding is done in Javascript. With MS Office VBA, the code is executed by VB editor. With Google Scripts, Google server executes the code. Ok. It is enough comparing VBA and Google App Scripts. Lets get into visit cockpit. What do we have?

1. Drive.Google.com
2. Create -> Spreadsheet
3. Script Editor
4. Execute Code
5. View Output

Where to Find Google Documents?

In your Internet browser find and navigate to website Drive.Google.com – You need a Google Login to create Google Docs.

How to Create Google Document?

Once you login to Drive.Google.Com, click on “Create” button. You can choose to create a new Folder, Document, Presentation, Spreadsheet, Form or Drawing. For this tutorial, we can create spreadsheet (because, I am comfortable with Spreadsheets and Excel.).
Pretty much excited to see a fully functional Spreadsheet in Internet browser, without any need to install any additional software.

Where to this Hello World Write Script?

Navigate to Menu: Tools -> Script Editor. A Popup will appear with multiple Project Options. For now, click on “Close”. A Script editor with below code appears.
function myFunction() {
}
Replace the above lines with below code.

function myFunction() {

  var SheetTest = SpreadsheetApp.getActiveSpreadsheet();
  var SheetUrl = SheetTest.getUrl();
  Logger.log ("Hello World - Url of this SpreadSheet: " + SheetUrl);

  var CurrSheet = SheetTest.getActiveSheet();
  var CurrCell = CurrSheet.getActiveCell()
  CurrCell.setValue("Hello World. Here I Come")

  var CurrCell1 = CurrSheet.getRange(2, 3)
  CurrCell1.setValue("Hello World. Here I Come Again")

}

Note: Commands are Case Sensitive

Script Explanation:

1. Display Message in Log:
   • Assign ActiveSpreadsheet object to variable SheetTest.
   • Get the SpreadSheetUrl and
   • Display it in the log. To see the output message in log, read till end of topic.
2. Enter Value into Sheet:
   • Assign ActiveSheet object to variable CurrSheet.
   • Assign ActiveCell (Cell(1,1) to CurrCell
   • Enter value into cell(1,1) using the command  setValue.

How to Execute Script?

So simple. Hit the “Run” option in the Scripts page Menu.

A Popup will ask to save the file before executing the code. Save it and Hit Run button again.

Where to See the Output?

Click View -> Log (or Control+Enter Keys) to see the output after code execution. Log Output will have the TimeStamp of Log and the message that we passed to Logger.log command.

The saved script resides in Google Drive in your Login account. You dont have to carry it. It can be viewed fro any place that has Internet. If required, Google provides the option to download the Spreadsheet to Local machine in different formats like MS Office Format, PDF, CSV, Web Page etc.,

Portions of this page are modifications based on work created and shared by Google and used according to terms described in the Creative Commons 3.0 Attribution License.