-1

I wrote a script to store range of values from Row 1 to be stored in value array1. But when i try to see the length of the array it gives 1.

Below is the code i used:`

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var lastCol = sheet.getLastColumn();
var array1 = sheet.getRange(1,1,1,lastCol).getValues();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(2,1).setValue(array1.length);

I have data from A1:D1 as 1, 2, 3, 4. Ideally I should receive the length of the array1 as 4 but I get 1 as the length value. Please help me correct this problem.

Ideally I need the array to be 'array1 = [1,2,3,4]'

User1090
  • 859
  • 6
  • 13
  • 19

1 Answers1

0

Explanation:

  • getValues() returns Object[][], a two-dimensional array of values, indexed by row, then by column.
  • in your code getValues() is used for a particular row, so it returns something like that: [[1.0, 2.0, 3.0, 4.0]].

Solution:

Replace that:

    var array1 = sheet.getRange(1,1,1,lastCol).getValues();

with:

    var array1 = sheet.getRange(1,1,1,lastCol).getValues().flat();
  • This approach flattens the array from [[]] => [].

Result:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var lastCol = sheet.getLastColumn();
var array1 = sheet.getRange(1,1,1,lastCol).getValues().flat();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(2,1).setValue(array1.length);

References:

Marios
  • 26,333
  • 8
  • 32
  • 52
  • i tried to flatten using the below method however i was not able to achieve it. because i need the array to be 1D array – User1090 Sep 03 '20 at 08:58
  • @User1090 but the "below" method does not flatten the array. Please just stick to the first one. – Marios Sep 03 '20 at 08:59
  • @User1090 check updated answer. Maybe now it is more clear. Please accept the answer if it worked for you and consider also up voting. It helps both the community and future readers. – Marios Sep 03 '20 at 09:01