How to find if the tasks for a particular date in a tasklist using google app-script

Question

I am using an installed edit trigger of Google Sheets to create Google Tasks. However, when a row containing a task that has already been created as a Task is edited, a duplicate Task is created for the same day.

I'd like to find all the Tasks in a given list with a particular due date. Then I will be able to check their titles, to compare with the title of the task that would be created, so the script may decide if it should create a new task or update the existing one.

Here's my current triggered code:

function addTask(event){
  if (spreadsheet.getActiveSheet().getName() === "Task List") {
    var RowNum = event.range.getRow();
    var taskproperties = spreadsheet.getActiveSheet().getRange(RowNum, 1, 1, 5).getValues();

    var Title = taskproperties[0][1];
    var Frequency = taskproperties[0][2];
    var StartDate = taskproperties[0][3];
    var Recurrence = taskproperties[0][4];
    if (Title.trim().length !== 0 && Frequency.trim().length !== 0 &&
        StartDate.toString().trim().length !== 0 && Recurrence.toString().trim().length !== 0)
    {
      //Code to Create a new task
      //Code Get the task date                              
      //Some codes to set Date parameters for use in script functions
      //Some codes to set Date parameters for use in sheet functions
      //Set the task parameters
      //add task to list
      //--------------------------------------------------------------

      //Assign a cell in the spreadsheet for calculation of new dates for recurring task            
      var tempdatecell= spreadsheet.getSheetByName("Task List").getRange("F1")

      //Insert new tasks based on the number of recurrence
      for (i = 1; i < Recurrence; i++) {
        //Insert a formula in a cell the spreadsheet to calculate the new task date
        tempdatecell.setFormula('=WORKDAY.INTL("' + shTaskStartDate + '",' + i + '*VLOOKUP("' + Frequency + '",tasktype,2,false),"1000011")')

        //Get task date from the cell                  
        TaskDate = tempdatecell.getValue()

        //Date parameters for use in script functions                  
        var TaskDate = new Date(TaskDate);
        var taskmonth = Number(TaskDate.getMonth()) 
        var taskDay = TaskDate.getDate() + 1
        var taskyear = TaskDate.getYear()

        //Create a new task
        var task = Tasks.newTask();

        //Set the task parameters
        task.title = Title;
        task.due = new Date(taskyear, taskmonth, taskDay).toISOString()

        //add task to list
        task = Tasks.Tasks.insert(task, tasklistID);
      }

      tempdatecell.clearContent()
    }
  }  
}

What is "a checklist"? And if you can provide your current script, it will help users think of the solution. — Tanaike, Apr 11 '19 at 09:06

score 0 · Answer 1 · answered Apr 11 '19 at 12:29

0

You might consider having your script write to another cell (probably in another column) that indicates the status of the task, such as added or updated and then write in a conditional statement that checks that cell to determine what to do with it. This is a really vague answer, but as Tanaike stated in their comment "provide your current script" or a generic version of it and we can be of greater help.

answered Apr 11 '19 at 12:29

Paul Murray

95
2
9

Hi Guys, I have added an extract of my script to the post. @Paul adding a single column to the spreadsheet is not going to work because the function is creating recurring task. I need a way to find out if the recurrence for a particular date is in the task list. I will also need that at the next stage of coding because each day I would like to pull the task for the day into a Today's Task sheet. – Sheils Apr 11 '19 at 19:03

score 0 · Accepted Answer · edited Apr 14 '19 at 09:58

I have managed to find a work around which involves filtering the entire tasklist. It seems to work find with the few task that I have now. I am not sure how it will perform with a large volume of tasks. Any further contribution welcomes.

The code that I am using in the work around is as follows and replaces the line below //Create a new task in my original code:-

  //Check if the task exist for the task date

  var listoftasks = Tasks.Tasks.list(tasklistID)

  var filtermonth="0" + shTaskStartMonth

  var filterdate=scTaskStartYear + "-" + filtermonth.substr(filtermonth.length-2) + "-" + shTaskStartDay + "T00:00:00.000Z"

  var filteredtask=listoftasks["items"].filter(function(item){

      return item["due"]== filterdate && item["title"]===Title

    })

    if(filteredtask.length==0){

        //Create a new task
        var task = Tasks.newTask()

        //Set the task parameters
        task.title = Title;
        task.due=new Date(scTaskStartYear,scTaskStartMonth,scTaskStartDay).toISOString()

        //add task to list
        task = Tasks.Tasks.insert(task, tasklistID)

    }

    else{

        //Get the existing task
        task = Tasks.Tasks.get(tasklistID, filteredtask[0].id)

        task.setStatus("completed")
    }

NB:- The setStatus does not work as expected but I will post a separate question for that.

How to find if the tasks for a particular date in a tasklist using google app-script

2 Answers2