SetValues from array rearranged

Question

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

Regarding `setValues()`, You need to learn the difference between `string` and `array`. After understanding the difference, read [this](https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept). Regarding `setValue`, try adding `SpreadsheetApp.flush()` after `rangely_inc.setValue(outputArray);` — TheMaster, Jun 23 '22 at 18:13
thanks, well I tried that flush() thing but now it updates over and over until comes back to the first value and repeated for the whole array — fogueira87, Jun 23 '22 at 18:34
outputArray is only a string, not an array. So when you try to assign a string to a range, I would imagine it duplicates it for each row in that range. — James, Jun 23 '22 at 19:24
@James That is correct, then why when i log it, it works fine, it shows an array of different values, but, if I set it, behaves like a string? — fogueira87, Jun 23 '22 at 19:37
What you are doing each iteration is logging the value, and setting the whole range to be that value. So I imagine you end up with all the logs being different as you expect, and the range being equal to the final value, but duplicated. — James, Jun 23 '22 at 20:31
Can you share a sample spreadsheet file so that you may provide us with a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example)? — PatrickdC, Jun 24 '22 at 00:47

PatrickdC · Accepted Answer · 2022-06-25T03:11:30.643

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

SetValues from array rearranged

1 Answers1

Treat the data as an array.