How to iterate through a range and build a "table" based on a criteria, using Google Apps Script?

Question

I got the following table to populate (range D6:J15) as I search the data in another sheet, based on a date criteria found in row 4:

This is where I'm to look for the data, considering Col A as the basis for the criteria:

My difficulty is to concatenate the data, as they meet the criteria.

This is the code I'm working on:

/* @OnlyCurrentDoc */
function editarPrevProd() { 
  const lock = LockService.getScriptLock();
  lock.tryLock(3000);
  if (lock.hasLock()) {
    var sourceSheet = 'PrevProdDB2';
    var destinationSheet = 'Previsão Entreposto';
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheetByName(sourceSheet);
    var ActiveSheetName = ss.getActiveSheet().getName();
    var LastRowSource = sheet.getLastRow();
    var LastColumnSource = sheet.getLastColumn();
    var values = sheet.getRange(2,1,LastRowSource,9).getValues();
    var csh = ss.getSheetByName(destinationSheet);
    var itens = csh.getRange("I40:J57");
    var data = [];
    var weekNo = csh.getRange("B4").getValue();
    var weekDates = csh.getRange("D4:J4").getValues();
    
    if (weekNo == "") {
      Browser.msgBox("Escolher uma data e tente novamente!");
      return;
    }

    //var clearRng = ["K34:K35", "N34:N35", "I40:K"];
    //csh.getRangeList(clearRng).clearContent();

    for (var i = 0; i < values.length; i++) {
      if (values[i][7] == weekNo) {
        data.push(values[i]);
        //break;
      } 
    }
  var dias = 0;
  var prevData = [];
  for (var j = 0; j < weekDates.length; j++) {
    dias = dias + 1;
    Logger.log("Dias da Semana: " + dias);
      for (var a = 0; a < data.length; a++) {
        if (weekDates[j].valueOf() == data[a][0].valueOf()){         
          prevData.push(data[a][4]);
        }  
      }
    }
    
    //map columns whose data will be set in the header.
    var user = data.map(function(e){return e[5];});
    var lastUpdate = data.map(function(e){return e[6];});

    //Copy data array to destination sheet
    csh.getRange("I1").setValue(user);
    csh.getRange("I2").setValue(lastUpdate);
    
    //csh.getRange("E6").setValue(timeStamp);

    //If you wanted to set arrays in the form of 
    //a table, you'd use this below instead
    var seg = data.map(function(e) {return [e[3]];});
    var ter = data.map(function(e) {return [e[4]];});
    var qua = data.map(function(e) {return [e[5]];});
    var qui = data.map(function(e) {return [e[6]];});
    var sex = data.map(function(e) {return [e[7]];});
    var sab = data.map(function(e) {return [e[8]];});
    var dom = data.map(function(e) {return [e[9]];});

    //csh.getRange(6,4,data.length,1).setValues(seg);

    lock.releaseLock();
  }
}

Here's a sample of the file. Note that the gs file I'm working on is named SalvaPrevProducao.

https://docs.google.com/spreadsheets/d/1NOWkzQIAPPdZdxeeTR7Id2v8LR00_u06uPhHs3tzLuU/edit?usp=sharing

Answer 1

I believe your goal as follows.

• You want to convert the above image to the bottom image using Google Apps Script.
• The date header is the cells "D4:J4".
• The source values are the cells "A6:M".
• The column "M" of ID is Semana in the destination sheet.

In this case, I would like to propose the following flow.

1. Retrieve values from the source sheet.
2. Create an array for putting to the destination sheet.
3. Put the array to the destination sheet.

When this flow is reflected to the Google Apps Script, it becomes as follows.

Sample script:

Before you use this script, please set the variables of srcSheetName and dstSheetName.

function editarPrevProd() {
  const srcSheetName = "Data Source"; // This is the source sheet name.
  const dstSheetName = "destSheet"; // Please set the destination sheet name.


  // This is from https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const lastRow = srcSheet.get1stNonEmptyRowFromBottom(1);
  const [[, , , ...header1], header2, ...srcValues] = srcSheet.getRange("A4:M" + lastRow).getValues();

  // 2. Create an array for putting to the destination sheet.
  const values = header1.reduce((ar, h, i) => {
    srcValues.forEach(([a, b, c, ...dm]) => ar.push([h, a, b, c, dm[i] || 0, "", "", dm.pop(), h]));
    return ar;
  }, [["Data", "Tipo", "Cod", "Descrição", "Qtd", "Usuário", "TimeStamp", "Semana", "Data"]]);

  // 3. Put the array to the destination sheet.
  const dstSheet = ss.getSheetByName(dstSheetName);
  dstSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When above script is run, the values are retrieved from srcSheetName and the converted values are put to dstSheetName .

Result:

When above script is run, the following result is obtained.

Note:

• Unfortunately, from your question and sample Spreadsheet, I couldn't understand about Usuário and TimeStamp of the columns "F" and "G". At the sample output situation of Turn the data from the left into the format on the right side, Usuário and TimeStamp have no values.

References:

• reduce()
• forEach()

Answer 2

It is unclear why you would need to resort to scripting to look up those values, when a filter() formula would seem capable to do the same. Try this formula in cell D6:

=sum( iferror( filter(PrevProdDB2!$E$2:$E, PrevProdDB2!$B$2:$B = $A6, PrevProdDB2!$H$2:$H = $B$4, PrevProdDB2!$I$2:$I = D$4) ) )