Puedo borrar el estado de la backlog
en el programa y enviar cambios a la matriz segment = data [x]
, pero no puedo obtener esos cambios en la celda a la que hace referencia var range = sheet.getRange("E2:J10");
. Mi problema está específicamente en la segunda declaración if del bucle for.
// Description: //The task will be available again after 14 days //First the program will scan `Task`s to detect backlog tasks //Then for each task the program will take the current date `cDate` - `dateOfTask` =`diff` (inDays) //If days past is greater than 14 days, then the task will be set to an empty cell `Task`.setblank //Once the cell is empty the task will be available to the algo task manager for prioritization
function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange("E2:J10"); //debugged // test task on line 9 // // Fetch values for each row in the Range. //const d = dRange.getValues(); var cDate = range.getCell(1,6); console.log(cDate.getValue()); var data = range.getValues(); console.log(data); //forloop for (x=0; x<data.length; x++) { var segment = data[x]; if (segment[1] == "Backlog"){ var now = new Date(); var dateOfTask = new Date(segment[5]); var diff = now - dateOfTask; var timeValue = Math.floor(diff / 1000 / 60 / 60 / 24); //console.log("Difference in milliseconds: ", diff); // 11140829739 console.log("Difference in days: ", timeValue) //var timeValue = DAYS(cDate, segment[5]); if(timeValue > 14){ var refreashBacklog = segment.splice(1,1,"clear contents"); range.setValue(refreashBacklog.segment); Logger.log(segment); //ask for help on stackOverflow } else{ continue } } else{ continue // remember to set a stop at a certain row so it doesn't go to 999 } } }
Creo que esto se acerca a lo que deseas:
function myfunk101() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName('Sheet0') var rg = sh.getRange("E2:J10"); var vs = rg.getValues(); vs.forEach((r,i) =>{ var r = vs[i]; if (r[1] == "Backlog") { var now = new Date().valueOf(); var dateOfTask = new Date(r[5]).valueOf(); var diff = now - dateOfTask; var days = Math.floor(diff / 86400000); if (days > 14) { sh.getRange(i + 2,6).setValue("clear contents"); } } }); }