CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheets
Chris MMgr
Chris MMgr

Reputation: 49

Array with values coming up with array.length = 0

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

Upvotes: 1

Views: 7393

Answers (2)

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17613

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.

Upvotes: 2

JPV
JPV

Reputation: 27252

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.

Upvotes: 2

Related Questions