As part of a water meter reading database I’m working on, I thought it would be nice to have a daily/monthly rain collection trend/tracking as well. We have a rain gauge that I read every morning…we’re remote in the mountains, so the local weather information doesn’t help, as they are about 1 hour away. It’s like a micro climate here.
The part I’m stuck on is adding the days that it doesn’t rain. I don’t want to open the app every single day it doesn’t rain, just to enter “0”, but I need that to take the monthly averages, etc.
How do I add a value of 0 on those days that it doesn’t rain, without opening the app?
Run a bot on a daily schedule.
1 Like
If I’m the only one using this app/DB, I don’t need to publish it. I thought the scheduling of bots was only for those Databases that are published…scheduling doesn’t work otherwise.
It is not a matter of publishing. You have to buy a paid subscription.
There is no way around this if you want to execute something offline.
One way I can suggest to make this process somewhat easier is as follows (only the strategy is shown.)
-
Create a helper table with 31 rows, keys from 1 - 31
-
Create actions (say, for the current month and the previous month, you can go as far back as you want). Also create virtual columns to show the action icons inline. Here you add a new row to another table (your target table) using values from this row. You can construct a Date expression something like this
DATE(YEAR(TODAY()) & "/" & MONTH(TODAY()) & "/" & [MONTH_DAY])
Here [MONTH_DAY] is the key column.
You also need to put Only if this condition is true like
AND(
ISBLANK(FILTER("TABLE01",AND(YEAR([DAYS])=YEAR(TODAY()),MONTH([DAYS])=MONTH(TODAY()),DAY([DAYS])=[_THISROW]))),
ISNOTBLANK(DATE(YEAR(TODAY()) & "/" & MONTH(TODAY()) & "/" & [MONTH_DAY]))
)
For some reason unknown “ISNOTBLANK(DATE(YEAR(TODAY()) & “/” & MONTH(TODAY()) & “/” & [MONTH_DAY])” part did not work properly so I had to create a VC with this expression and use it in the expression above…
You could also add another constraint that the day of the month is before today to make sure you do not create entries for future dates.
- For the previous month(s), you need to take care of the cases where you cross over two years..
This looks something like this (only partially implemented. Just a POC)
The first column is to create records for the current month and the second for the previous month.
Another simpler suggestion is to create a bunch of records with zero precipitation manually or via an Apps Script and overwrite those dates when it rains..
1 Like
Edit: yeah you’re right I guess you can’t run the scheduled automation if the app isn’t published. Have ChatGPT write you an appscript for Google Sheets if that’s where your db is…
I think you can run scheduled automations in an unpublished app, you just can’t send emails to anyone but the owner which shouldn’t matter. Obviously you have a field capturing date, so you could schedule an automation to run each morning which evaluates the criteria “is there not a table entry for yesterday’s date?” If yes, your bot proceeds, see below. If no, bot doesn’t proceed because you already created an entry for the rain total yesterday.
For the bot process, configure an action which adds a new row to your table with date = yesterday and rain total = 0.
When it doesn’t rain, do nothing and you’ll get a zero rain entry for that day. If it rains and you forget or whatever, just go edit the rain total.
1 Like
Thanks for the very detailed thought process.
I also just realized, I don’t need to enter every day to get the monthly averages. Just sum the days that do have a value > 0, and divide by the number of days in the month. But, it would be nice to have to show on a chart/graph…but I could write a google script for that.
I found a way to automatically enter a record each day.
Since this database is based on Google Sheets, I wrote a script to add a new record with required date and add values to the required columns. Then, in the script, you can add a trigger based on time. So, at 1am every morning, the script runs based on the trigger, and adds a line.
Then, if I need to change the value based on the rain, I edit the record instead of adding a new one.
