Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

250
Views
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
    }
    }
    }
about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

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");
      }
    }
  });
}
about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!