Things → Google Calendar automation

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:

  1. It executes a shell script, which executes sqlite3 command to query the local sqlite3 database.
  2. If a task matches that timestamp then it means the task is in “Today”.
  3. I add time tag to the tasks that I want on calendar. For example, if a task might take 15 minutes, I tag ⏰ 15m.
  4. So in the query it strips the unnecessary part and give 15 as tag.
  5. The result of the query is like this:
8h9f1PDA83KRjPUGZAsU6x|Test task|15
AWXmzW3rKgbtkbyWx2i5Xt|Another task|30
  1. I need the id like 8h9f1PDA83KRjPUGZAsU6x to compose a link like things:///show?id=8h9f1PDA83KRjPUGZAsU6x.
  2. It loops over all the tasks and create calendar events with name, given time and the task link.
  3. 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.
  4. 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!

3 Likes

Thank you sooooo much! This was just reaching the point where it was going to scratch my automation itch

What are action are you running this within in BTT?

Forgot to mention it. It’s “Run Real JavaScript”. It’s all it takes :slight_smile:

(added to the post)

1 Like

Woah! I’d never have thought about querying the local database. Awesome hacking! :clap:

Inspired by GitHub - AlexanderWillner/things.sh: Simple read-only comand-line interface to your Things 3 database

Thanks :slight_smile:

1 Like