I just wrote a script to create calendar events based on Things tasks.

Discussion & Questions
1

Hi all,
I just wrote a script for my workflow, and thought it might be useful to others. So here it is:

(async function () {
  const today = new Date();
  const beginningOfToday = new Date(today.getFullYear(), today.getMonth(), today.getDate())
  const timestamp = (beginningOfToday.getTime() - beginningOfToday.getTimezoneOffset()*60*1000)/1000;

  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.startDate = ${timestamp} AND  \\
       TMTask.startBucket = 0 AND  \\
       TMTask.status = 0 AND  \\
       TMTask.trashed = 0 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 hope it's helpful to someone.
Let me know!

(Uploading the gif here didn't work. So if you're interested to see it action, visit here)

edit @ 25 feb 2021:
The SQL query was wrong. It didn't scrap all the tasks from Today.
I should've used

trashed = 0 AND
status = 0 AND
type = 0 AND 
start = 1 AND
startdate IS NOT NULL

this condition for that, which means

  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 '⏰%';"  
  `;
3 Likes
2

I wish I could get this to work using Keyboard Maestro (I've been learning that for now and have BTT on hold). It sounds like it'd help me a lot!

I'm getting errors about the Things authentication token and idk where to put that in the script. Is there any way you can help me out?

3

Hi there, I'm sorry but I'm not used to Keyboard Maestro.
What have you done and exactly what errors did you get?
Me or other people might help you with further information.

4

I have made 3 changes to the code:

  1. Set the start time for all created events 12:00 instead of the current time. This way times like 12:03 are avoided. Otherwise :03 part were kept when moving these events.

  2. Instead of showing the item in Things, it updates the item as completed
    For this you are going to need to change TOKEN in the code with your auth-token:
    const link =things:///update?auth-token=TOKEN&id=${id}&completed=true&reveal=true;
    You can get your token from:
    Things > Preferences > General > Manage (in front of "Enable Things URLs", and also enable it if disabled) > Copy

  3. it adds the Things link to the URL tab in calendar instead of notes.

Note: You also need to change the name of the calendar.
in the code. change CALENDARname with the name of your calendar.
tell calendar "CALENDARname"

Code itself:

(async function () {
  const today = new Date();
  const beginningOfToday = new Date(today.getFullYear(), today.getMonth(), today.getDate())
  const timestamp = (beginningOfToday.getTime() - beginningOfToday.getTimezoneOffset()*60*1000)/1000;

  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:///update?auth-token=TOKEN&id=${id}&completed=true&reveal=true`;
	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 hours of theStartDate to 12
    set minutes of theStartDate to 0
    set theEndDate to theStartDate + ((${task.minutes}) * minutes)

	  tell application "Calendar"
		tell calendar "CALENDARname"
			make new event with properties {summary:${JSON.stringify(task.name)}, start date:theStartDate, end date:theEndDate, URL:${JSON.stringify(task.link)}}
		end tell
	end tell
`;
    runAppleScript(appleScript)
  });  
  
  returnToBTT();
})();
1 Like
5

Issue: It creates an empty event in calendar if there is no todos with time tag.