I'm trying to determine the length of an array, so that I can use the length to determine how many times a for loop will need to iterate.

I'm trying to read from 1 row across many columns to determine which cells need to be written to. The code will write data to the rows, but will need to determine if the header has already been written or if the header needs writing also. In this function I am focusing on checking the headers to see if the value is already written.

I understand that the range values D2:Z2 is inefficient and I am open to another way around this. I'd be interested to know if anyone is able to determine why the length is 1.0 instead of 4, and if anyone else can think of a way to pare the values, the then determine the column to write the new set of headers in.

Code

 function getCurrentHeader() {

  //gets active spreadsheet, saves to array
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Records");
  var rangeValues = Array;
  //D2:Z2 is the current largest range available 15/12/21 
  rangeValues = sheet.getRange("D2:Z2").getValues(); 
  return rangeValues;
}


function checkCurrentHeaders(){
  //gets the array of headers
  var current = getCurrentHeader();
  //Logger.log(current);
  var leng = current.length;

  Logger.log(leng);

}

The above is what i've written so far, but even with the array being logged to view the values, the .length is returning a value of 1, there are 4 values in the array so i was expecting 4 as the return. I've also noted that the output is in two sets of square brackets. Please see the log information below.

Logger

11:52:32 AM Notice  Execution started
11:52:32 AM Info    [[B, A, C, DF, , , , , , , , , , , , , , , , , , , ]]
11:52:32 AM Info    1.0
11:52:32 AM Notice  Execution pleted

I'm trying to determine the length of an array, so that I can use the length to determine how many times a for loop will need to iterate.

I'm trying to read from 1 row across many columns to determine which cells need to be written to. The code will write data to the rows, but will need to determine if the header has already been written or if the header needs writing also. In this function I am focusing on checking the headers to see if the value is already written.

I understand that the range values D2:Z2 is inefficient and I am open to another way around this. I'd be interested to know if anyone is able to determine why the length is 1.0 instead of 4, and if anyone else can think of a way to pare the values, the then determine the column to write the new set of headers in.

Code

 function getCurrentHeader() {

  //gets active spreadsheet, saves to array
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Records");
  var rangeValues = Array;
  //D2:Z2 is the current largest range available 15/12/21 
  rangeValues = sheet.getRange("D2:Z2").getValues(); 
  return rangeValues;
}


function checkCurrentHeaders(){
  //gets the array of headers
  var current = getCurrentHeader();
  //Logger.log(current);
  var leng = current.length;

  Logger.log(leng);

}

The above is what i've written so far, but even with the array being logged to view the values, the .length is returning a value of 1, there are 4 values in the array so i was expecting 4 as the return. I've also noted that the output is in two sets of square brackets. Please see the log information below.

Logger

11:52:32 AM Notice  Execution started
11:52:32 AM Info    [[B, A, C, DF, , , , , , , , , , , , , , , , , , , ]]
11:52:32 AM Info    1.0
11:52:32 AM Notice  Execution pleted

• javascript
• arrays
• google-sheets

Share Improve this question Follow asked Dec 15, 2021 at 12:05 br4dl3ybr4dl3y 3511 gold badge11 silver badge55 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 2

getValues() returns 2 dimensional array. In your script, rangeValues = sheet.getRange("D2:Z2").getValues() is the 2 dimensional array like [[D2, E2, F2,,,]]. By this, when you check current.length of var current = getCurrentHeader(), 1 is returned. In this case, 1 is the number of rows. I think that this is the reason of your issue.

When you want to retrieve the number of columns, please modify as follows.

From:

var leng = current.length;

To:

var leng = current[0].length;

or

From:

rangeValues = sheet.getRange("D2:Z2").getValues(); 

To:

rangeValues = sheet.getRange("D2:Z2").getValues()[0];

or

From:

rangeValues = sheet.getRange("D2:Z2").getValues(); 

To:

rangeValues = sheet.getRange("D2:Z2").getValues()[0].filter(String); // In this case, the empty cells are ignored.

References:

• getRange(a1Notation)
• getValues()