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

In your sample images, both images are the sample input situation? If my understanding is correct, can you provide the sample output situation? Because I'm not sure about the values you want to put to `range D6:J15`. I apologize for this. And, in order to confirm each value in the sheet, can you provide the sample Spreadsheet? Because in your situation, I cannot understand whether the date is the date object. — Tanaike, Jun 13 '21 at 23:57
Is this an [XY problem](https://meta.stackexchange.com/a/233676)? Consider sharing a publicly editable [sample spreadsheet](https://webapps.stackexchange.com/a/138383/269219) with realistic-looking data, and showing your _hand-entered_ expected results there. Use **Insert > Note** to describe what you want in the spreadsheet. — doubleunary, Jun 14 '21 at 06:12
Yes, please share a sample sheet or something with sample data with which we can test the function. — iansedano, Jun 14 '21 at 07:51
Thank you for replying and providing the sample Spreadsheet. From your additional information, I proposed an answer. Could you please confirm it? If that was not the result you expect, I apologize. — Tanaike, Jun 15 '21 at 02:22

score 2 · Accepted Answer · answered Jun 15 '21 at 02:21

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.

Retrieve values from the source sheet.
Create an array for putting to the destination sheet.
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:

I'll come back a year later, after I learn the concepts shown here. Mind blowing! Thank you! — onit, Jun 16 '21 at 01:43

score 1 · Answer 2 · answered Jun 14 '21 at 06:11

1

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

answered Jun 14 '21 at 06:11

doubleunary

13,842
3
18
51

I'll be transferring the data from one sheet to another, in the expected format, so the user must be able to hit a button Save and the data gets transferred/appended. Thanks. – onit Jun 15 '21 at 01:37

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

2 Answers2

Sample script:

Result:

Note:

References: