I have a spreadsheet, in which I need the script to fetch, over two columns, two pieces of information. And when that's true, I add a formula that adds "1 day" into a third column.

I can make the conditionals run individually. But when I put both, they don't work.

function fillColADia() {
  var s =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Afazeres');  
  var dataA = s.getRange("H1:H").getValues();
  var dataB = s.getRange("A1:A").getValues();
    for(var i=0; i<dataA.length; i  ) {
      for(var j=0; j<dataB.lenght; j  ) {
        if (dataB[j][0] == false) {
          if (dataA[i][0] == 'Zenon') {
          s.getRange(i 1,3).setValue(" 1");
         }
        }
      }
    }
}

And I also don't know how to add the "add 1 day" formula to the end.

Thanks a lot for the help.

COPY FILE with dummy data

CodePudding user response：

Faaala! Something along these lines as food for thought! ...although I'm certain there are more performant ways to accomplish it:

function fillColADia() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Page1');
  var data = s.getRange("A1:H").getValues();
  for (var a = 0; a < data.length; a  ) {//Percorre cada linha do intervalo
    if (data[a][0] == false && data[a][7] == 'Zenon') {//Aplica critérios
      let date = data[a][2];//Pega a data daquela linha
      date1 = Utilities.formatDate(addDays(new Date(date), 1), Session.getTimeZone(), "dd/MM/yyyy");//Formata o objeto data, usando a função abaixo para incrementar o dia
      date2 = Utilities.formatDate(addDays(new Date(date), 1), Session.getTimeZone(), "dd/MM/yyyy HH:mmss");
      s.getRange(a   1, 3).setValue(date1);//Põe a data de volta
      s.getRange(a   1, 4).setValue(date2);//Põe a data de volta
    }
  }
}

//GERA DATAS INCREMENTANDO OS DIAS 
function addDays(date, days) {
  var result = new Date(date);
  result.setDate(result.getDate()   days);
  return result;
}