CODEWITHSUNDEEP
javascriptgoogle-apps-scriptmultidimensional-arraygoogle-sheets
Lolo
Lolo

Reputation: 25

How to set values in a bidimensional array?

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

Upvotes: 0

Views: 771

Answers (1)

0Valt
0Valt

Reputation: 10345

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

  1. 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

  1. push() method reference on MDN
  2. setValues() method reference

Upvotes: 2

Related Questions