As another commenter said, it is like VBA except you get to use JS which is nicer. You can do quite a bit with these scripts. More than you might think. But... It has serious drawbacks.
1. The script implementation has been changing often and without warning.
2. It fails unpredictably with no indication of what the problem is (the built in log is limited and it is difficult to tell where the source of trouble is -- Google Docs? the spreadsheet you're working on? a bug in the code?)
3. Undocumented bugs are rampant. Real example: you add a small script in the responses spreadsheet to send e-mail confirmation to people who submit a Google form. It uses the built-in onFormSubmit trigger. By all appearances, this will start to e-mail everyone going forward. Instead it e-mails everybody who has ever submitted since the form went live.
4. Sometimes runs incredibly slowly with no apparent reason.
Like everything else in Google Docs, there are lots of things you just can't do -- some by design, some not.
The documentation is thin and you'll find lots of long-standing issues only through digging through various support/group threads, the debugging is obtuse and unsuitable for triggered actions - there's no logging.
The killer though is that the performance/rate limiting is crap - as your document gets bigger, your functions will fail and time-out semi-randomly, even if you're not making a significant # of API calls. Presumably this is because of the way that GDocs are XML streams internally - but even doing single getsheets in calls (no cell iterating) seems to cause problems.
I couldn't find good caching/global storage mechanisms in the past - but hadn't seen ScriptDB before, so maybe that can help (storing in scratch sheets doesn't help since you need to use the API calls to get that stuff)
Anyway just some of my experiences for those who are interested in working w/ App Script for Google Spreadsheets. It's been years since I touched any Excel Macros, but they worked a lot better than my experience w/ App Script.
Till about a few days ago, saving a script before running used to take 5 min or so.
Plus you run the risk of getting the rug pulled out from under you when some big shot at Google decides that it 'distracts' from some $important 'core mission' and does not 'align well'.
It's possible to build a full web application on Apps Script - someone has even ported Bootstrap to it, and most things work - but most people who try quickly hit the limits and move to Google App Engine.
The spreadsheets aren't a reliably way to store information. Can't query spreadsheets to get information, had to do it by very slow processes.
Everything is slow.
I love the idea, but will not try again until some working large-scale app appears.
It's the best thing I've seen so far for giving non-technical colleagues visibility into whatever organisation data you have that's programatically available. They already understand spreadsheets, so there's no complexity to overcome when they come to play with them. It's trivial to base the output on a few user-defined cells, so you can put together fairly powerful features without ever having to build a user interface. You even get to do stuff with the logged in user's Google account. And since the toy apps seldom get too big, JS that you write in your browser is the perfect low-friction development environment.
1 - how well can you integrate. As macarthy12 asked can you add links or other changes to gmail.
2 - what's stopping google from looking at the products that work well and just copy them. Basically stealing what you spent 6 months or a year to built and then offer it as part of a built-in feature.
Google started as "do no evil" however, it seems like you can't be a dominating company and not do evil.
For example, if you receive an email from email@example.com then it would automatically select firstname.lastname@example.org (or whatever you set it to) as the "from" field.
I'd be willing to pay for this as I don't think this is possible with Google Apps Script
Also there are no tabs :(
If you code locally you leverage years and years of tooling made for it, if you code in that google scripts you are just fucked up. And that's the big difference for me, not the UI.