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.