Apps Script is really powerful and often overlooked as a way to easily automate workflows that involves Google workspace apps.
I’ve built an app similar to this example but that takes things a bit further. It lets you send notifications to a variety of platforms and have them triggered based on conditions such as a cell containing a certain value or a date being reached etc
If you want to check it out the site is https://checksheet.app, happy to answer any questions about it.
Over the past year I've been working on Check Sheet in my spare time which is an add-on for Google Sheets. It lets you define rules or "checks" for your spreadsheet and will then send you a notification when they match via Email, Slack or Teams. Google used to have a similar thing built in directly but decided to remove it, there still seems to be demand for such a thing so I've been working on something that adds that functionality back.
The add-on runs primarily in Google's Apps Script environment which is essentially like Lambda/Cloud Functions although it's been around for way longer. In terms of the tech stack the frontend is a fairly simple Vue app and the backend is written in TypeScript. Apps Script is quite restrictive in terms of some of the quotas and limitations they apply so over time I've moved some of the logic and data out into Firestore and Cloud Functions.
Check Sheet is a free add-on for Google Sheets that adds the ability to define rules that will trigger an email notification when your spreadsheet changes. For example if a certain cell exceeds a value or if all values in a column match a string.
Google Sheets does have very basic notification functionality built in but it can only trigger notifications on any change, you can't narrow it down further than that.
This is the MVP I've built in a couple of months, any feedback is much appreciated.
"Sable has been hugely successful in the company: it provides storage and computation environment to over 850 teams, running 2450 unique applications, hosts 10,000 data-sets comprising of 2.05PB of (replicated) data across all retail regions. It handles about 2.2 trillion transactions per day with average client-side latencies of 3ms. In the first half of 2016, there were two outages of the cluster in North America due to operator errors, and this brought down multiple lines of business, not just Amazon.com. Leading the project to reduce SABLE blast radius. This involves segregating the data-sets and providing seamless fail-over to alternate storage solutions (for example DynamoDB) for data that is very critical such as Amazon catalog."
"SABLE is Amazon's e-commerce storage and computation platform. It hosts some of the most important applications running on Amazon.com. Altogether there are about 400 teams (including Shopping Cart, Items, Prices) that use SABLE within Amazon.com for high performance storage, caching, and new business object derivation services.
Part of the team that built SABLE from ground up. SABLE uses BDB (Berkley Database) for persistence and Libevent for non-blocking I/O. My specific feature contributions include: repartitioning support (add/reduce fleet size without any impact to live production traffic), SABLE reactors (computation platform for propagating prices, availability to the website). I optimized SABLE reactors by eliminating worklogs (which is the bookkeeping mechanism used to keep track of the amount of work that needs to be done in the system) which resulted in 30% reduction in our fleet size.
As a manager, I had five directs, and our team launched: Quality of Service for SABLE reactors (this helped in faster prices propagation to the website), Shopping Cart on SABLE platform (increased the availability of Shopping Cart application, more than what Oracle could provide)."
> Our NoSQL storage platform processes more than 1 trillion transactions per day to serve Amazon country-specific and private-label websites and internal Amazon systems.
Yes. Currently you get at most 50ms CPU time per request, and memory usage of your Javascript VM is limited to 128MB. These numbers may change before launch.
In practice, though, a typical script probably uses much less than 1ms of CPU time per request and probably needs only a couple megs of RAM. Because we're applying limits on the level of a V8 Isolate, not on an OS process, the limits go a lot further.
Keep in mind that Cloudflare Workers are not intended to be a place where you do "bulk compute", but rather where you run little bits of code that benefit from being close to users.
I would very much like to see a pay-as-you-go cloud pricing model that allows you to pay to go over these limits (within reason.) For the use case I'm envisaging, filtering largish buffers of Cap'N Proto data (I'm tickled that both of these things are your babies!) I can use streaming fetch API to stay within the memory limit, but the 50ms limit would really be a hard wall that would cause requests to fail on larger amounts of data. I'd be happy to pay in those cases for extra execution time.
Currently, an error, because default handling might not be correct in many cases -- imagine a script implementing access control or expanding an HTML template. But I'd be open to the idea of letting the script call, like, `event.fallbackOk()` upfront to specify that default behavior as a fallback is safe.
Note that the 50ms is CPU time, not wall time. This is actually a pretty huge amount of time for handling one request.
Sure, that's a pretty important bit to understand so I'll try and add some more information on that.
Yeah, so to convert a cron that ran `ls` to use minicron you would change it to `minicron run ls`. Currently the full command that gets added to the crontab is something like `/bin/bash -l -c 'minicron run ls'`, I use /bin/bash -l so it's easier to get working when you are using something like rvm for managing ruby versions. The command then gets run by minicron in a pseudo terminal so I can capture output line by line or even character by character and send it on.
At the moment the hub only knows about jobs which you create via it or for when you first run a job it gets created, so if you ran `minicron run ls` manually from server1 a job called ls on server1 would be stored in the db. Currently there is a potential for them to get out of sync yes, I had some ideas about how a push/pull type sync feature could work but I ran out of time to work on it. It's definitely something I want to improve in the future though!
Yeah, so the hub knows about any 'schedules' i.e cron expressions that you set up from it, so if you set up a schedule of '* * * * *' i.e every minute and that cron doesn't execute every minute it will send an alert via email/sms/pagerduty if you have enabled them. It does this by polling the database for executions in the background. I'm sure a way to expose those alerts to Nagios could be added.
I hadn't heard of rcron, I will have to look into that.
Yes and I agree! I did have SQlite support at one point but I dropped it because I was using triggers for cascading deletes, I'm not doing that anymore though so it should be possible to add it back pretty easily.
Thanks for your responses. This all sounds really sensible.
Concerning rcron, you don't actually need explicit support for it, as it's just part of the scheduled command. However, some integration could make it obscenely pleasant to use. Probably, i would need to manually set up rcron on a group of servers, then configure some sort of 'virtual server' in the hub which contains the servers in the group. When i schedule a job on the virtual server, it is added to the crontabs on all the real servers, but guarded with rcron, so it only executes on one. The reporting would then deal with the fact that only the master in the group actually runs the job: if the rcron wraps the minicron, then it would deal with the fact that only one server sent a report; if the minicron wraps the rcron, it would just hide the empty reports from the non-master servers.
We currently manage most of our cron jobs through Puppet. If we adopted minicron, we'd want to keep doing that. That means we would write a Puppet defined type which adds a job in the hub. It would be really useful if there was an API, or ideally a command-line tool, to do that. It would be nice if there was then an easy way to distinguish Puppet-managed and interactively defined jobs in the hub, so that people didn't attempt to fiddle with the Puppetted ones. Maybe a flag that makes them read-only in the UI, but allows them to be managed in the API? That's probably something we would address in a fork if we did adopt minicron.
Some kind of auditing for changes to job definitions would be useful. We currently use Jenkins for ad-hoc scheduled jobs, and our very weak audit trail for it is a recurrent pain point. Would it make any sense to keep job definitions in a Git repository? Might be quite a pain to provide an interactive UI on top of that, though.
Thanks for the info on rcron, it sounds like a really useful tool. I think I prefer the approach of rcron wrapping minicron so it doesn't require added complexity to minicron. I've added an issue here for potentially doing this https://github.com/jamesrwhite/minicron/issues/66 if you want to follow along with the progress.
The fronted is actually all API based, in theory I don't see any reason why external apps couldn't use the API. I'll try and add some documentation for it. I quite like the idea of puppet/chef/whatever defined jobs. When I add permission support to the hub/api you could set up a user for puppet which would have its own API credentials so the API would be able to show who the job was created by. On of the features of the permissions system will most likely be that users can only edit jobs they created unless they are an admin or some kind of user with extra privileges. I think this feature would be useful for a lot of people so hopefully it can be added without forking the project!
Logging activity and some kind of revision control is definitely something I've thought about and plan to add in the future. I remember seeing a ruby gem a little while back that could basically track changes to arbitrary ActiveRecord models, that might be useful here. I've added two tickets for this to remind me! https://github.com/jamesrwhite/minicron/issues/67 + https://github.com/jamesrwhite/minicron/issues/68
1. Thanks, I put a lot of effort into the README :)
2. That was one of my aims really, cron is a great tool it's just missing a few things that hopefully this can fill.
3. I was thinking about this recently actually, I don't see why I couldn't add a config option that toggles between WebSockets and HTTP(s) for the transport of data to/from the hub. I did a bit of research into WebSockets when I was planning this and from what I understand they put a lot of effort into making sure it would work behind proxies/firewalls etc but I can't remember the exact specifics.
4. That SQL actually comes from ActiveRecord (`rake db:setup`) so I don't have any direct control over it. Long term I want to add proper migrations.
5. If it has an API of some kind I don't see why it couldn't be added as an alerting option alongside email, sms and pagerduty.
