How to set values in a bidimensional array?

Question

First of all, sorry about my skills; my coding background is zero and I am just trying to modify an script to fit my needs.

I need to setValues in a two-rows datasheet data range.

The current script sets the values in the first row. How should I modify the script so I can get the data from my 4 values in 2 rows?

Current behaviour:

current

My go-to behaviour:

go-to

Function to run: Print

function Print() {

   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet").getRange(1, 1, 1, 2).setValues(Data());    

}

function Data() {

    var array = [];

                      var score = "retrieved score";
                      var time = (2+2);
                      var device = "device";
                      var speed = 3+3;

    array.push([score,time]);

    Utilities.sleep(1000);

    return array;
}

Expand range that you get via getRange() - currently, you only get first row, first column, one row, wo columns (hence the parameters 1,1,1,2) — 0Valt
– 0Valt, Commented Jun 2, 2020 at 19:58
Unfortunately, it does not work. I set getRange(1, 1, 2, 2) and then array.push([score,time,device,speed]) but I get this error: "The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 2." — Lolo
– Lolo, Commented Jun 2, 2020 at 20:09
oh, an yes, sure - you need to make sure that each row has only two columns - currently you create a one-row, two-col matrix, and you need to [ [score, time], [device, speed] ] ofc — 0Valt
– 0Valt, Commented Jun 2, 2020 at 20:14
Just updated this line: array.push( [[score,time],[device,speed]] ); but I guess I need to redefine the var because var array = []; seems a one row-one col matrix. I am unsucessfully trying to define the var :-( . — Lolo
– Lolo, Commented Jun 2, 2020 at 20:32
I meant [[ ]] as a whole, of course you should only push two rows in the array, not the matrix I mentioned. Please, read up on how push() method works — 0Valt
– 0Valt, Commented Jun 2, 2020 at 20:34

0Valt · Accepted Answer · 2020-06-12 08:26:30Z

2

Problem

Trying to set 2 x 2 matrix of values to 1 x 2 range

Solution

Your values have to match dimensions of the range. The sheet is a matrix consisting of cells:

|       |       col 1       | col 2 |
| ----- | ----------------- | ----- |
| row 1 | "retrieved score" |   4   |
| row 2 | "device"          |   6   |

2-dimensional arrays are basically the same, where outer level represents rows and inner - columns:

[
  [ "retrieved score", 4 ],
  [ "device", 6 ]
]

Thus, with a simple modification, the cells should be filled correctly:

function Print() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const rng = ss.getSheetByName("sheet").getRange(1, 1, 2, 2);

  const values = Data();

  rng.setValues(values);    

}

function Data() {

  var array = [];

  var score = "retrieved score";
  var time = 2+2;
  var device = "device";
  var speed = 3+3;

  array.push([score, time], [device, speed]);

  Utilities.sleep(1000);

  return array;
}

Notes

It does not make sense to me that you need to use the sleep() utility method here. Operations on an in-memory array are synchronous and atomic (which is not true for setValues, as it is an I/O operation).

References

push() method reference on MDN
setValues() method reference

edited Jun 12, 2020 at 8:26

answered Jun 3, 2020 at 2:09

0Valt

10.5k9 gold badges41 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lolo Over a year ago

Thank you, this is as good as it gets. The code is not mine, I can't code, but I think sleep() is there because in the real project setValues is within a print function where a loop changes data and range . So, maybe, sleep() is needed to make work both functions. Does it make sense?

0Valt Over a year ago

@Lolo - thank you for the feedback. No worries - just curious. Yes, that makes sense, but not in Data() as it has nothing to do with input / output ( i.e. setValues(), which, alongside giving a database time to propagate changes, is one of the main uses of slepp) - the setValues() method will use the result of the Data() call, so you are waisting a full second. It belongs to after the setValues() call and only if the script depends on changes to propagate (but that's what flush is for)

Collectives™ on Stack Overflow

How to set values in a bidimensional array?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related