Array with values coming up with array.length = 0

Question

My goal is to copy and paste contact information from sheet 'callLog' to another sheet based on if a contact is "Bidding". In order to determine where to paste the information, I am collecting data from the first row of sheet 'meh' and putting it into an array. Vars rowGet and rowValues are collecting the correct information, but rowValues.length = 0. This is causing the following for loop to end early, and every value is pasted on top of each other.

Please help me understand why the array length is 0, and how to get the correct value.

Thanks, -Chris

Paste Location Table Setup:

+---+-----------+-----------+-----------+
|   |     A     |     B     |     x     |
+---+-----------+-----------+-----------+
| 1 | Comp Name |    ...    | Comp Name |
+---+-----------+-----------+-----------+
| 2 | Cont Name |    ...    | Cont Name |
+---+-----------+-----------+-----------+
| 3 |  Number   |    ...    |  Number   |
+---+-----------+-----------+-----------+
| 4 |  email    |    ...    |   email   |
+---+-----------+-----------+-----------+

Code:

function distributeBidder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Call Log');
  sheet.activate();
  var range = spreadsheet.getRangeByName("bidList");

  var copySheet = spreadsheet.getSheetByName('This Works');

  // find the ones whose value is "BIDDING", and add them to a list of cells we need to hide
  var vBidders = [];
  for (var r = 1; r <= range.getNumRows(); r++) {
    for (var c = 1; c <= range.getNumColumns(); c++) {
      var cell = range.getCell(r, c);

      if (cell.getValue() == "BIDDING") {
        vBidders.push(cell);
      }
    }
  }

  copySheet.activate();
  // Iterate through confirmed bidders and collect the correct data to paste
  for(var i = 0;i<vBidders.length;i++){
    var cCell = vBidders[i];
    var compName = cCell.offset(0, +1);
    var contName = cCell.offset(0, +2);
    var contNum = cCell.offset(0, +3);
    var contMail = cCell.offset(0, +5);

    //THIS SCRIPT DOES NOT WORK WITH HORIZONTALLY FORMATTED DATA    
    var rowGet = copySheet.getRange("A1:H1");
    var rowValues = rowGet.getValues();

    //rowValues.length is coming up with a value of 0 breaking everything
    for (var j = 0 ; j < rowValues.length ; j++) {
      if(rowValues[j] == "Company Name") {
        var rowValNow = j+1;
        compName.copyValuesToRange(copySheet, rowValNow, rowValNow, 1, 1);
        contName.copyValuesToRange(copySheet, rowValNow, rowValNow, 2, 2);
        contNum.copyValuesToRange(copySheet, rowValNow, rowValNow, 3, 3);
        contMail.copyValuesToRange(copySheet, rowValNow, rowValNow, 4, 4);
        break;
      };
    };  
  };
};

Link to view the spreadsheet in progress: https://docs.google.com/spreadsheets/d/1AmdmQuw7OLH7v2LV-XQ_xW3hSPAHzi289Zt65bx53W8/edit?usp=sharing

JPV · Accepted Answer · 2017-04-25 12:44:54Z

2

This line

var rowValues = rowGet.getValues()

will result in a 2D-array, or: an array with one array as it's only element. This inner array will have the values of cells A1:H1 and will look like this:

[[A1, B1, C1, D1, E1, F1, G1, H1]]

So your loop through rowValues will only run once. rowValues[j] is the inner array [A1, B1, C1, D1, E1, F1, G1, H1] and will never match to a string. It's not clear in what cell the company name is, but assuming it is in A1 you should have your if-statement as follows

rowValues[j][0] == "Company Name"

Another way would be to loop through the columns of the inner array directly by doing

for (var j = 0 ; j < rowValues[0].length ; j++) {
if(rowValues[0][j] == "Company Name") {

Of course, if the company name is in a designated column, you don't need to loop at all and can check directly:

var rowValues = rowGet.getValues();
if(rowValues[0][0] == 'Company Name') { ....

Hope that helps a little.

edited Apr 25, 2017 at 12:44

answered Apr 25, 2017 at 10:20

JPV

27.4k4 gold badges37 silver badges50 bronze badges

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

2 Comments

Chris MMgr Over a year ago

rowValues[0].length and rowValues[0][j] are exactly what I needed. Thank you for the help!

JPV Over a year ago

@ChrisMMgr: most welome !

ReyAnthonyRenacia · Accepted Answer · 2017-04-25 15:02:20Z

2

var rowGet = copySheet.getRange("A1:H1");

Here rowGet gets the value 1, because getRange(a1Notation) returns range, not the items.

var rowValues = rowGet.getValues();

rowValues.length does not return the number of items inside rowValues. it returns the number of range(rows), which is 1.

If the range was "A1:H2", it will return 2. If the range was "A1:H3", it will return 3. You get the idea.

To get the actual length of each rows, know that they're 2d arrays, so you'd do something like:

rowGet[0].length

this will return the number of items within the first row.

rowGet[1].length

this will return the number of items within the second row. You get the idea.

Hope this info helps.

edited Apr 25, 2017 at 15:02

answered Apr 25, 2017 at 12:04

ReyAnthonyRenacia

17.7k6 gold badges42 silver badges63 bronze badges

Collectives™ on Stack Overflow

Array with values coming up with array.length = 0

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related