SetValues from array rearranged-CodePudding

General View

https://docs.google.com/spreadsheets/d/e/2PACX-1vSNW9Gibu5OSFs7Os73vbfwtloJnyw4PnzPLs9IXGzDSshC2Y10jxKHChDYpScJTfHf2viqAADwPmya/pubhtml

after editing previous code thanks to theMaster now I have:

function layout_inc() {
  
  var semanal = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Semanal');
  semanal.getRange('D4').clearcontent;
  var rangoEmpleados = semanal.getRange(3, 1, semanal.getLastRow() - 2, 10).getDisplayValues();
  var ly_inc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Layout_incidencias');
  ly_inc.clearContents();
  var rangely_inc = ly_inc.getRange(1, 1, rangoEmpleados.length, 1);
for (let i = 0; i < rangoEmpleados.length; i  ) {
  var innerArrayLength = rangoEmpleados[i].length;
  for (let j = 3; j < innerArrayLength; j  ) {
    let outputArray = [];
    if (rangoEmpleados[i][j] == 'F') {
  outputArray = '"'   rangoEmpleados[i][0]   '"'   ','   rangoEmpleados[0][j]   ','   ','   '"F"'   ","   '""'   ","   '""'   ","   '""'
  rangely_inc.setValue(outputArray);
  SpreadsheetApp.flush()
  Logger.log(outputArray);
    } else if (rangoEmpleados[i][j] == 'V') {
      var v = [ '"'   rangoEmpleados[i][0]    '"'   ","   rangoEmpleados[0][j]   '""'   ','   '"V"'   ","   '""'   ","   '""'   ","   '""'];
    }
  }
}

}

So in order to create a new array that shows the emp_id (A4:A), date(C3:I3) and a string "F", if and only if theres an F where there isnt any clockin/out movement, Im trying to setvalues in a different sheet and that sheet will be a csv later. But, setValue will only return a string, the very first one and duplicate it trough the entire array. yet

Logger.log(outputArray); it does show the new array how I want it, I just need to set it in the other sheet.

4:19:22 AM  Info    "547",20/06/2022,,"F","","",""
4:19:23 AM  Info    "855",17/06/2022,,"F","","",""
4:19:23 AM  Info    "855",18/06/2022,,"F","","",""
4:19:23 AM  Info    "855",20/06/2022,,"F","","",""
4:19:24 AM  Info    "855",21/06/2022,,"F","","",""
4:19:24 AM  Info    "855",22/06/2022,,"F","","",""
4:19:24 AM  Info    "855",23/06/2022,,"F","","",""

but:

rangely_inc.setValue(outputArray); returns:

"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""
"507",18/06/2022,,"F","","",""

CodePudding user response：

Treat the data as an array.

Every spreadsheet in Google Sheets is treated as a 2D array. In this solution, I introduced a secondary output variable out2 and pushed the desired outputs into it. Afterwards, I declared the rangely_inc variable last so that its range may adjust dynamically to the size of out2. With that, there will then be no issues in using the .setValues() function.

function layout_inc() {
  var semanal = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Semanal');
  semanal.getRange('D4').clearcontent;
  var rangoEmpleados = semanal.getRange(3, 1, semanal.getLastRow() - 2, 10).getDisplayValues();
  var ly_inc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Layout_incidencias');
  ly_inc.clearContents();
  var outputArray = [];
  var out2 = [];
  for (let i = 0; i < rangoEmpleados.length; i  ) {
    var innerArrayLength = rangoEmpleados[i].length;
    for (let j = 3; j < innerArrayLength; j  ) {
      if (rangoEmpleados[i][j] == 'F') {
        outputArray= ['"'   rangoEmpleados[i][0]   '"'   ','   rangoEmpleados[0][j]   ','   ','   '"F"'   ","   '""'   ","   '""'   ","   '""'];
        out2.push(outputArray);
      } else if (rangoEmpleados[i][j] == 'V') {
        var v = ['"'   rangoEmpleados[i][0]   '"'   ","   rangoEmpleados[0][j]   '""'   ','   '"V"'   ","   '""'   ","   '""'   ","   '""'];
      }
    }
  }
  var rangely_inc = ly_inc.getRange(1,1, out2.length, 1);
  rangely_inc.setValues(out2);
}