Saturday, December 31, 2011

AppsScript - Macro - Javascript

Google Apps script and VBA Macro and Javascript are the powerful scripts which work on worksheets. Depending on the usage of the worksheet we can choose any of the above scripts. let say, If you need online automation then Appscript would be the best of the choice. For local automation Macro and Javascript play vital role. Many of us are not sure about similarities on these. In this series, I would like to share the similarities on these scripts. In this post let see how to read the spreadsheets

For reading the worksheet:
Google Apps Script for reading another spread sheet

  var myBook = spreadsheetApp.openById("0Aoh6e6FJ10dSGc"); // open id is the key string able to find in the spread sheet url 
var mySheet = myBook.getSheetByName("sheet1");   //name of the sheet
var myRange = mySheet.getRange(1,1).getValue();   // range represent first row first column
   
VBA Macro Script for reading another Worksheet
Dim myBook as Excel.workbook
set myBook = Getobject("C:\sample\book1.xls")
myRange =  myBook.worksheets(1).Cells(1,1).Value
myBook.Close 'make sure this needs to be closed, if it is declared early.


JavaScript for reading another Worksheet

var myBook = new ActiveXObject("Excel.Application").workbooks.open("c:\sample\book1.xls");
var mySheet = myBook.Sheets(1);
var myRange = mySheet.cells(1,1).Value;  //ActiveXObject is supported by IE only & other browsers are not supportable

  myRange is the Read value 


on simple way we can compare these as
SpreadsheetApp.openById("0Aoh6e6FJ10dSGc").getSheetByName("sheet1").getRange(1,1).getValue());

new ActiveXObject("Excel.Application").workbooks.open("c:\sample\book1.xls").Sheets(1).cells(1,1).Value;

Getobject("C:\sample\book1.xls").worksheets(1).Cells(1,1).Value