CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
fogueira87
fogueira87

Reputation: 25

SetValues from array rearranged

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","","",""

Upvotes: 1

Views: 120

Answers (1)

PatrickdC
PatrickdC

Reputation: 2496

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

Upvotes: 1

Related Questions