• Empleos
  • Sobre nosotros
  • profesionales
    • Inicio
    • Empleos
    • Cursos y retos
    • Preguntas
    • Profesores
  • empresas
    • Inicio
    • Publicar vacante
    • Nuestro proceso
    • Precios
    • Pruebas Online
    • Nómina
    • Blog
    • Comercial
    • Calculadora de salario

0

128
Vistas
How do I edit a nested array and set changes to a range of cells in Google Sheets?

I'm able to clear the backlog status in the program, and push changes to the segment = data [x] array, but I'm unable to get those changes in the cell referenced by var range = sheet.getRange("E2:J10");. My issue is specifically in the second if statement of the for loop.

// 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

enter image description here

    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
    }
    }
    }
almost 3 years ago · Juan Pablo Isaza
1 Respuestas
Responde la pregunta

0

I think this is close to what you wish:

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");
      }
    }
  });
}
almost 3 years ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Nuestro proceso Comercial
Legal
Términos y condiciones Política de privacidad
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recomiéndame algunas ofertas
Necesito ayuda