Created
October 13, 2017 21:19
-
-
Save nicksmarto/7e9f3996bf9fd9018b274ad062d451fc to your computer and use it in GitHub Desktop.
Google Aps Script for Sheets to automatically pull, format, sort and populate active Todoist tasks in to Google Sheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//********************************************************************************************************* | |
//makeTodo: Call other functions to make the daily Todo | |
//********************************************************************************************************* | |
function makeTodo(){ | |
//Populate Header | |
grabHeader("B1") | |
//Populate Task List | |
grabDueTasks("B5") | |
//Print List | |
//Todo: https://ctrlq.org/code/20061-google-cloud-print-with-apps-script | |
} | |
//********************************************************************************************************* | |
//getTodoistTokenRev: Deal with Todoist Token process, returnes unique API token. | |
//********************************************************************************************************* | |
function getTodoistTokenRev() { | |
var url = "https://todoist.com/api/access_tokens/migrate_personal_token"; | |
var fetchParameters = {}; | |
fetchParameters.method = "post"; | |
fetchParameters.contentType = "application/json"; | |
fetchParameters.headers = { | |
"ContentType" : "application/json" | |
}; | |
//Stuff payload and stringfy | |
fetchParameters.payload = { | |
"client_id": "[POPULATE WITH ID]", | |
"client_secret": "[POPULATE WITH CLIENT SECRET]", | |
"personal_token":"[POPULAE WITH PERSONAL TOKEN]", | |
"scope": "data:read_write" }; | |
fetchParameters.payload = JSON.stringify(fetchParameters.payload); | |
//Send the request | |
var response = UrlFetchApp.fetch(url, fetchParameters); | |
//Parse in to JSON and return | |
var json = response.getContentText(); | |
var data = JSON.parse(json); | |
return(data.access_token); | |
} | |
//********************************************************************************************************* | |
//queryFilters: Grab list of current filters, currently not invoked anywhere, just handy to have around. | |
//********************************************************************************************************* | |
function queryFilters(){ | |
var apiURL = "https://todoist.com/API/v7/sync"; | |
var queryString = "?token=" + getTodoistTokenRev() + "&sync_token=%27*%27&resource_types=[%22filters%22]"; | |
var fetchParameters = {}; | |
fetchParameters.method = 'get'; | |
fetchParameters.contentType = 'x-www-form-urlencoded'; | |
fetchParameters.muteHttpExceptions = true; | |
var response = UrlFetchApp.fetch(apiURL + queryString, fetchParameters); | |
var syncData = JSON.parse(response.getContentText()); | |
//var syncToken = syncData.sync_token; | |
return(syncData); | |
} | |
//********************************************************************************************************* | |
//getAllTasks: Contacts Todoist's API with token from getTodoistTokenRev() and downloads JSON of all items. | |
//********************************************************************************************************* | |
function getAllTasks(){ | |
var apiURL = "https://todoist.com/API/v7/sync"; | |
var queryString = "?token=" + getTodoistTokenRev() + "&sync_token=%27*%27&resource_types=[%22items%22]"; | |
//Get params | |
var fetchParameters = {}; | |
fetchParameters.method = 'get'; | |
fetchParameters.contentType = 'x-www-form-urlencoded'; | |
fetchParameters.muteHttpExceptions = true; | |
//make request and return | |
var response = UrlFetchApp.fetch(apiURL + queryString, fetchParameters); | |
var syncData = JSON.parse(response.getContentText()); | |
return(syncData); | |
} | |
//********************************************************************************************************* | |
//grabDueTasks: Sifts through JSON from getAllTasks(), determines if due/overdue, and populates sheet | |
//********************************************************************************************************* | |
function grabDueTasks(todoRange) { | |
//Grab the Sheet, configure range, delete old data | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheets = ss.getSheetByName('Today'); | |
var sheet = ss.getActiveSheet(); | |
var startLocation = sheet.getRange(todoRange); | |
var maxRows = 10; | |
sheet.getRange(startLocation.getRow(), startLocation.getColumn(), maxRows, 2).clearContent(); | |
//Get all active tasks | |
var data = getAllTasks(); | |
var activeTasks = []; | |
//Compute Drop Date, date which disqualifies tasks from being overdue | |
var dropDate = new Date(); | |
dropDate.setHours(24,00,00,000); | |
Logger.log("Current dropDate: " + dropDate); | |
//Compute Over Due Date, which designates the break point for tasks to be marked as Overdue | |
var overdueDate = new Date() | |
overdueDate.setHours(00,00,00,000); | |
Logger.log("Current overdueDate: " + overdueDate); | |
//sheet.getRange(1, 1, 1, 1).setValue(JSON.stringify(data)); //Dump JSON package in to A1 for debugging. | |
//Clean up due dates, marking NULL if missing | |
for (i = 0; i < data.items.length; i++) | |
{ | |
//Read task date as Date | |
var taskDate = new Date(data.items[i].due_date_utc); | |
//Throw out tasks we don't care about, and push in to new array activeTasks | |
if (taskDate <= dropDate //check if due today or overdue | |
&& data.items[i].due_date_utc != null //check if due date exists | |
&& data.items[i].checked == 0) //check if incomplete | |
{ | |
activeTasks.push([data.items[i].content, data.items[i].all_day, data.items[i].priority, data.items[i].due_date_utc]); | |
} | |
} | |
Logger.log("activeTasks: " + activeTasks.length); | |
// Sort tasks by due date [3] | |
activeTasks.sort(function(a,b) { | |
if (b[3] < a[3]) | |
{return 1;} | |
else | |
{return -1;} | |
}); | |
//Update Sheet with active tasks, marking 'overdue' and due times accordingly | |
for (i = 0; i < activeTasks.length; i++) | |
{ | |
sheet.getRange(i+5, 3, 1, 1).setValue(activeTasks[i][0]); | |
var dueTime = new Date(activeTasks[i][3]); | |
//If task is overdue or all-day, add label, with formatted time stamp | |
if (activeTasks[i][1] == false || dueTime < overdueDate) | |
{ | |
if (dueTime < overdueDate) | |
{ | |
sheet.getRange(i+5, 2, 1, 1).setValue("Overdue"); | |
} | |
else | |
{ | |
//Silly nuance, getSpreadsheetTimeZone() corrects for daylight time, could use 'America/New_York' format instead. | |
var prettyDate = Utilities.formatDate(dueTime, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "HH:mm"); | |
sheet.getRange(i+5, 2, 1, 1).setValue(prettyDate); | |
} | |
} | |
} | |
} | |
//********************************************************************************************************* | |
//grabHeader: Format today's date and populate header row | |
//********************************************************************************************************* | |
function grabHeader(headerRange) { | |
//Grab the Sheet, configure range, delete old data | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheets = ss.getSheetByName('Today'); | |
var sheet = ss.getActiveSheet(); | |
var startLocation = sheet.getRange(headerRange); | |
sheet.getRange(startLocation.getRow(), startLocation.getColumn(), 1, 1).clearContent(); | |
var todaysDate = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "EEEE, MMMM d, yyyy"); | |
sheet.getRange(headerRange).setValue(todaysDate); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@nicksmarto Seems like a cool idea here of pulling in Todoist into Google Sheets but must be missing a step on my end to get it working. What's need for setting this up to run? Still working on your end? Thanks!