Hi all- I’m looking for advice on the best way to build a simple database out of information generated every day from about half a dozen iOS and MacOS apps, mainly about time use. I want to generate a dashboard and trigger some actions based on the data.
I know AppleScript and Keyboard Maestro, and I can use them to do almost everything I want. I can collect the data I need as CSV or JSON files and load it into lists of records (with dictionaries). As far as I can tell, neither Applescript nor KM make it easy to query those records without a lot of repeat loops.
Essentially all I want is a statement like this:
Set DashboardValue to
[sum, average, min, max, or count] of Field A
for records where Field B = X and Date is between Y and Z
Is there an easy way to do that in AppleScript or KM? Or is there a tool I can learn just enough of to solve my problem:
SQL
A scripting language (JavaScript, Python, etc.)
A webapp (Airtable, FireBase, etc.)
A spreadsheet (Numbers or Excel) or other Mac app.
Other than spreadsheets, I don’t have any experience with these other languages or platforms. All of these could solve my problem. Does anyone have a view as to what the most painless path to take?
If your use case for your database is creating a Dashboard, then perhaps an easy way is to import the data to a Google Sheet and then use Siteoly to create the dashboard:
the simplest way is probably to use Pandas in Python to merge all that data together. You can also use Plotly in Python to either create charts or an actual dashboard app. This would require learning Python and basics of Pandas and Plotly. It is not so difficult to learn (way easier than Applescript in my opinion). You could probably get something up in a weekend.
I may not even need to use Siteoly, if it’s easy enough to use Google sheets. I can write formulas to get the data I need.
What would be the easy way to automaticaly upload records multiple times a day tio append to worksheets in Googke sheets?
Then is there a way to query specific cells and use the results in AppleScript?
Dustin- Thank you. I don’t care whether the automation is in the cloud or on my mac. And I think i distracted from my real question with the mention of dashboard.
My first question is simply to find the easiest way to load records into a database, query the database, and use the results of the queries in AppleScript, etc.
Essentially, I’m asking if there is an easy way to query records in Applescript with the equiviloent of the spreadsheet fuctions SUMIF, SUMIFS, AVERAGEIF, etc. And if I can’t do that easily in Applescript, what is the alternative with the lowest learning curve.
If it turns out that the way to do that also uses a tool (or easily connects to a tool) that can make the data pretty, that’s a bonus.
Victor- I know that Python has a reputation as a very easy language to learn and there are lots of online resources.
What I don’t know is how to set up my Mac to be able to write and test a Hello World program. What do I need to download? What editor or environment to I write and test code in? How do I get a Python Script to run every hour? Can a Python script interact with other applications like AppleScript does or can it exchange data with an AppleScript script?
Thanks for indulging me with these basic questions. I haven’t needed to write code before on my Mac other than applescript (and a bit of Word Basic).
In my case, I am working on a project currently to use Devonthink as a database and automate uploading its data to Google Sheets, then using Siteoly from there.
My goal is thus to use Devonthink as the basis for a database-driven website.
I can let you know when the project is completed if you are interested.
You probably already have Python installed on your mac. Run this in terminal to see: python3 -V if it finds the command you are good. For this kind of things I use Jupyter notebook because it is very visual. You can actually use googles version (kinda like google docs): https://colab.research.google.com/ to test everything in the cloud. And when you are happy you download the .py file to your computer. Regarding how to schedule it you can do it as a cron-job in terminal or just use keyboard maestro.
I think you’ll find https://automatetheboringstuff.com to be a great resource. (It includes how to get setup with Python and examples/walkthroughs of automating excel, css, resources for how to run a script on a schedule, etc.)
Unfortunately, I don’t know of any tools that would do this automatically,
Richard- I use Devonthink for research but never really thought of it as a database. How do you use it that way?
Also, how do you upload the data to Google sheets? Do you use Google App Scripts, API calls, or something else?
I do look forward to taking a look at your script when it’s done.
The metadata and custom metadata in Devonthink fundamentally make it a database.
Tools - Create Metadata Overview is a first-pass “data dump” of the database if you will.
My plan is to use Google App Scripts to upload the equivalent of Create Metadata Overview to Google Sheets, then use Siteoly from there. Then automate this process so the website updates when I update data in Devonthink.