Did you ever know that you can create custom scripts for Google Spreadsheets? The ones that would allow you easily manage your data, i.e. copy it, move between spreadsheets, calculate complex cases and create specific functions for further re-use in other projects. Do almost anything you want… just like in a regular application.
It can be simple and easy to do, if you’re familiar with JavaScript syntax.
Let me briefly describe main cons and pros of Google Spreadsheet scripts:
Strong points
- There is a quite convenient Google Script Editor that is used for Google Docs, Spreadsheets, Sites, etc.;
- Google Scripts can be also used for calendars, mailboxes, Google Drive files, Google Sites, etc.;
- You can format cells UI in a software way (styles, background, font, font size, etc.);
- You can add own menus after standard menu set to allow other people call function in usual way;
- Forms to get some additional input from user can be built;
- There are different ways to notify users about smth – emails, message boxes, pop-up’s, etc.;
- Get external data, parse CSV;
- Ability to run scripts on schedule or specific events with triggers;
- You can use external libraries or create your own to use in different projects;
- There is a simple team collaboration.
Weak points
- Network issues on Google side; for example, DNS errors a few times a day that do not allow to get external data (it also affects spreadsheet cell formula function – ImportData);
- Misleading error messages; for example, too big sheet 800 rows x 60 columns can produce ‘Document … is missing (perhaps it was deleted?)’ on SpreadsheetApp.openById(Master) although the document exists and can be opened manually;
- Triggers to run scripts are visible to a creator only and can not be shared with other team members. Also,
team collaboration is not good enough and there are no warnings when 2 persons edit and save the same script file simultaneously. The last save will replace another person’s changes without a warning.
Conclusion
You can most certainly use Google Scripts when you’re not limited in time (e.g. you can wait a few hours for results or get it even next day). If you need to automate a process that requires hourly (or more frequent) synchronisation, Google Scripts might not be a perfect fit.
Additionally, there’re some usage scenarios that can not be called a Pro due to unstable scripts execution, but they’re definitely worth mentioning:
- You can work with data from 10+ spreadsheets shared with different people;
- Do bulk updates to 10+ spreadsheets;
- Make spreadsheets more lightweight, more speedy (to exclude formulas and make all calculations in scripts with scheduled triggers to run when everyone’s offline).
Brief instructions how to start
There’re plenty of guidelines available, so I’ll try to provide a concise version:
Open spreadsheet (if you have a team - share your spreadsheet for editing to other team members)Click Tools -> Script Editor.Choose blank project or spreadsheet (there will be some example functions).First save of your project.
You’re now ready to write the code. Stay tuned for my next article with useful code examples!