15

Do any of you use the javascript macro capabilities of Google Apps, particularly for spreadsheets? How do the capabilities compare? Is there anything that Google Apps can't do that can be done with VBA macros?

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
notnot
  • 4,472
  • 12
  • 46
  • 57

7 Answers7

12

I am in the middle of conversion of a complex project from VBA to Google Apps Script as an experiment to see what can be done and what can't.

In general, the development environment for google apps script is primitive and frustrating. The language is of course javaScript so if you already know that then you have a head start. The problem though is that many of the advantages of javaScript are not realizable since you don't actually have access to DOM elements, and neither do you have an equivalent of Excel shapes - except through the UI object- which is essentially the same as an excel form, with the same kind of events and objects etc.

Another issue is general slowness. You need to be careful how you structure, so that you minimize calls to the spreadsheet data (I got round this by building a values cache), and scalability is very questionable.

I am equally at home with javaScript or VBA, so putting language aside, VBA is currently more fulfilling and quicker to get things done, although there are increasing capabilities built in to apps script to make it extremely promising.

I am logging the progress of my migration, and the things I come across and the battles to figure out how to minimize structural change (I am trying to see if I can come up with something that would allow dual maintenance on both platforms), so if you are interested, you can follow along here

http://ramblings.mcpher.com/Home/excelquirks/gooscript

Bruce

bruce
  • 1,408
  • 11
  • 33
  • GAS has moved to new level of utility that really leverages its 'cloud existence'. You can now create libraries of scripts and share them with your own projects, or indeed share them publicly or with specific groups. Here's how with some library examples - http://excelramblings.blogspot.co.uk/2012/06/managing-libraries-with-google-apps.html – bruce Jun 21 '12 at 14:51
  • What about DDE support? I'm guessing google apps don't support that. – User Dec 05 '12 at 03:45
  • Well no.. Dde doesn't really translate to a cloud based platform. You can of course access any other sheet from a script, and of course use triggers and so on to deal with many of the dde type use cases. Does dde even exist any more in Windows anyway? I thought it had been dropped back at win xp release 2. – bruce Dec 06 '12 at 09:52
3

I'm trying to create charts in google spreadsheet using google apps script and is seems not possible... It is fairly easy in VBA, maybe vb6 is 10 years old but on the other hand you're able to do everything what you need with your spreadsheet. This is not the case in google scripts though.

Krzysztof Rosinski
  • 31
  • 1
3

The VBA in Office has been the same for about 10 years now, still using old VB6. The limitations of that language are endless. No data structures, no logic short-circuiting, limited types, non-object oriented. Google Apps and javascript are presumably on the forefront of technology and so it should not have many of these limitations.

As for what each is capable of doing that the other can't, that's a little more difficult to assert. I would argue that both languages are more or less turing complete, so while it may be more or less difficult to do something in each language, in theory, a good enough team of programmers could do just about anything in either.

Cheers :D

Alain
  • 26,663
  • 20
  • 114
  • 184
  • 3
    I don't doubt the completeness of the languages themselves. I'm more interested in the differences between the event models, interaction with environment, other files & data sources, etc. The devil's always in the details. – notnot Dec 03 '10 at 18:36
  • Well given that office VBA sees no improvements on any releases, and Google Apps is the latest and greatest, the answer to which will have a better event models and interaction will certainly be the latter. My bet is that new development will be easier in Google Apps, but you would be forgoing 10 years of experience in VBA readily available on the Internet. VBA is easier if you primarily rely on existing material. If it were a design decision for a new app, I would say Google Apps offers the most promise, Excel VBA offers the most support. – Alain Dec 03 '10 at 18:40
  • 1
    I've been looking at the API and Google Apps just looks amazingly superior. http://code.google.com/googleapps/appsscript/service_spreadsheet.html For simplicity, they've used a lot of the same keywords, like range, formulaR1C1, etc, but they have tons of additional features that let you quickly and easily do in one command things that used to take entire subroutines and complex workarounds in VBA. – Alain Dec 03 '10 at 18:48
  • your answer looks like a biased guess ! – iDevlop Dec 04 '10 at 11:05
  • I don't see VBA being largely unchanged, for 18 years now, as bad; it means that it's stable. If I wanted a shiny new programming language, I'll probably go to C#/VB.Net. Following the links from @Bruce, it looks like GAS operates at a speed at least 5 time slower then VBA. Since I and most people who are writing office automation work for medium/large companies, it doesn't make sense to make everyone switch to a slower system. I for one use Excel/VBA as the middleman/checkpoint to automating CAD software/Databases; I'm not sure how that would work using GAS & Google Sheets. – Profex May 18 '18 at 22:05
2

One of the major hurdles is that Google Apps is web based. Thats were most companies cringe at the thought that you would have your own data not stored securely within your own network. I would never trust Google with that sort of data. I'm sure they have secure data connectors, but why take that risk?

Sure VBA is outdated, and not as sexy as javascript. However, its a great tool for quickly writing small applications, and getting it to the user fast. You only really hear nightmare stories about applications that were built by non-programmers using VBA.

It really depends on your project, and what you want to accomplish. Both have their limitations.

Fink
  • 3,356
  • 19
  • 26
0

Having taken a quick look at Google Spreadsheet API I get the impression that its currently somewhat limited, for instance:

fewer Events, no control of calculation?, no way of writing UDFs?

Has anyone tried using it for serious work?

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • I think you can write UDFs, but I've played with it all of five minutes, so I don't what limitations there are. – jtolle Dec 06 '10 at 14:48
0

By its nature, a Google spreadsheet will not be able to do many things a local program will do. Example: a macro that would import all csv files in a user specified folder and consolidate them.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
0

One thing that springs to mind is that VBA has full access to the WinAPI through 'Declare Function' declarations. Google Apps script won't have this access. This is something to watch for if you're converting a spreadsheet between the two.

Chris Spicer
  • 2,144
  • 1
  • 13
  • 22