Hello everyone,
I’ve been enjoying listening to Automators podcast lately. It’s been so inspiring and help me think about practical workflows.
One of them is my automation to create calendar events based on the tasks in Things app.
I posted it in the BTT community but I guess the folks here might like this, so I’m cross posting.
Put the following in “Run Real JavaScript” action.
(async function () {
const shellScript = `
sqlite3 ~/Library/Group\\ Containers/JLMPQHK86H.com.culturedcode.ThingsMac/Things\\ Database.thingsdatabase/main.sqlite " \\
SELECT TMTask.uuid, \\
TMTask.title, \\
substr(TMTag.title, 3, length(TMTag.title) - 3) AS tag \\
FROM TMTask, \\
TMTaskTag, \\
TMTag \\
WHERE TMTask.trashed = 0 AND \\
TMTask.status = 0 AND \\
TMTask.type = 0 AND \\
TMTask.start = 1 AND \\
TMTask.startdate IS NOT NULL AND \\
TMTask.uuid = TMTaskTag.tasks AND \\
TMTag.uuid = TMTaskTag.tags AND \\
TMTag.title LIKE '⏰%';"
`;
const shellScriptWrapper = {
script: shellScript, // mandatory
launchPath: '/bin/bash', //optional - default is /bin/bash
parameters: '-c', // optional - default is -c. If you use multiple parameters please separate them by ;; e.g. -c;;date
environmentVariables: '' //optional e.g. VAR1=/test/;VAR2=/test2/;
};
const result = await runShellScript(shellScriptWrapper);
const tasks = result.split("\n").map(line => {
const pieces = line.split("|");
const id = pieces[0];
const link = `things:///show?id=${id}`;
const minutes = Number(pieces[pieces.length - 1]);
const name = pieces.slice(1, pieces.length - 1).join("|");
return { name, minutes, link };
});
tasks.forEach(task => {
let appleScript = `
set theStartDate to current date
set theEndDate to theStartDate + ((${task.minutes} - 1) * minutes)
tell application "Calendar"
tell calendar "Tasks"
make new event with properties {summary:${JSON.stringify(task.name)}, start date:theStartDate, end date:theEndDate, description:${JSON.stringify(task.link)}}
end tell
end tell
`;
runAppleScript(appleScript)
});
returnToBTT();
})();
To give you a little bit of explanation:
- It executes a shell script, which executes
sqlite3
command to query the local sqlite3 database. - If a task matches that
timestamp
then it means the task is in “Today”. - I add time tag to the tasks that I want on calendar. For example, if a task might take 15 minutes, I tag
⏰ 15m
. - So in the query it strips the unnecessary part and give
15
as tag. - The result of the query is like this:
8h9f1PDA83KRjPUGZAsU6x|Test task|15
AWXmzW3rKgbtkbyWx2i5Xt|Another task|30
- I need the id like
8h9f1PDA83KRjPUGZAsU6x
to compose a link likethings:///show?id=8h9f1PDA83KRjPUGZAsU6x
. - It loops over all the tasks and create calendar events with name, given time and the task link.
- However it creates all the events at the same start date. It’s intentional. I didn’t want to make it too complicated because there might be already other meetings, etc.
- Once the script creates all the tasks on the calendar, I go to the calendar, and arrange all the tasks into its own time slot.
I couldn’t upload the gif here. You can see it in action here.
Thanks for reading!