1

I am new to coding and have put together some code to reference Google Sheet values to create Google Tasks. However, when I run the script it doesn't seem to check the existing task title and dueDate and creates duplicate tasks. I don't know if I coded the task IF statement wrong or missed something in the section of the code with my comment.

/// This code doesn't seem to validate Task Title and dueDate but creates duplicate tasks  
if (tasks[i].getTitle() == 'taskTitle' && tasks[i].getdue() == 'startDate') {
    return;
}

Google Sheet Sample

function CreateTask() {
const ID = '1MkimspZROYFPFApQECWHETIwMkWfQ5aizj0Da17cVhs';
const sheet = SpreadsheetApp.openById(ID).getSheetByName('Database');
var TASK_LIST_ID = '@default';
var index = 2; // This indicates start looking at row 2
var LastRow = sheet.getLastRow(); 
for (;index<=LastRow; index++){
  var taskTitle = sheet.getRange(index,2,1,1).getValue();
  var startDate = sheet.getRange(index,1,1,1).getValue();
  var checkOnMe = sheet.getRange(index,7,1,1).getValue();   
  var status = sheet.getRange(index,8,1,1).getValue();  
              
  if (checkOnMe == "Yes" && taskTitle && startDate && status != "Complete")
  {
    /// This code doesn't seem to validate Task Title and dueDate but creates a 
  duplicate tasks
   var tasks = Tasks.Tasks.list('MDk4MjA3ODcxNzU1OTc3NjQxOTk6MDow').getItems();
   for (var i = 0; i < tasks.length; i++) {
   if (tasks[i].getTitle() == 'taskTitle' && tasks[i].getdue() == 'startDate') {
    return;
    }
   }
   var task = Tasks.newTask();{
   task.title = sheet.getRange(index,2,1,1).getValue();
   task.notes = 'Checking In';
   }
   
         var dueDate = sheet.getRange(index,1,1,1).getValue();
         dueDate.setDate(dueDate.getDate() + 0);
         task.due = dueDate.toISOString();
                      
         var newTask = Tasks.Tasks.insert(task, TASK_LIST_ID);
         console.log('Task with title = %s, id = %s and notes = %s was created. ' +
         'Task is due on %s.',
         newTask.title, newTask.id, newTask.notes, newTask.due);

  }

 }

}
opeonikute
  • 494
  • 1
  • 4
  • 15
Roe
  • 43
  • 5

1 Answers1

0

Modification points:

  • When getValue() is used in a loop, the process cost becomes high. Ref
  • I think that Tasks.Tasks.list can be used only one time.
  • In the case of if (tasks[i].getTitle() == 'taskTitle' && tasks[i].getdue() == 'startDate') {, 'taskTitle' and 'startDate' are used as the string of taskTitle and startDate. I think that this might be the reason for your current issue.

When these points are reflected in your script, how about the following modification?

Modified script:

Please set your Spreadsheet ID and Task list ID.

function CreateTask() {
  const ID = '###';
  const TASK_LIST_ID = '###';

  const taskObj = Tasks.Tasks.list(TASK_LIST_ID).getItems().reduce((s, e) => s.add(e.title + "_" + (e.due ? e.due.split("T")[0] : "")), new Set());
  const sheet = SpreadsheetApp.openById(ID).getSheetByName('Database');
  const values = sheet.getRange("A2:H" + sheet.getLastRow()).getValues();
  values.forEach(([dueDate, taskTitle, , , , , checkOnMe, status]) => {
    if (checkOnMe == "Yes" && taskTitle && dueDate && status != "Complete") {
      dueDate.setDate(dueDate.getDate() + 0);
      const due = dueDate.toISOString();
      const k = taskTitle + "_" + due.split("T")[0];
      if (!taskObj.has(k)) {
        const task = Tasks.newTask();
        task.title = taskTitle;
        task.notes = 'Checking In';
        task.due = due;
        const newTask = Tasks.Tasks.insert(task, TASK_LIST_ID);
        console.log('Task with title = %s, id = %s and notes = %s was created. ' + 'Task is due on %s.', newTask.title, newTask.id, newTask.notes, newTask.due);
        taskObj.add(k);
      }
    }
  });
}

  • When this script is run, the title and the due are searched from the task items. When the row value is not included in the task items, a new task is created.

  • If you want to use 2 task list IDs, please modify const taskObj = Tasks.Tasks.list(TASK_LIST_ID).getItems().reduce((s, e) => s.add(e.title + "_" + (e.due ? e.due.split("T")[0] : "")), new Set()); and const newTask = Tasks.Tasks.insert(task, TASK_LIST_ID);.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165