Google Aps Script for Sheets to automatically pull, format, sort and populate active Todoist tasks in to Google Sheet

makeTodo.js

//*********************************************************************************************************
//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);
  
}