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?- Drive.Google.com
- Create -> Spreadsheet
- Script Editor
- Execute Code
- 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:
- 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.
- 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.