Look through named ranges and print values to new sheet

Question

I am relatively new to JavaScript, so please bear with me.

I have a seating chart in a google spreadsheet which labels the desk number and name of the person who sits there in two adjacent cells via a named range. I want my code to loop through all the named ranges, and get the values for the desk numbers and people and spit it into a new sheet in two columns-desk number in one column and the user name in another. A major issue I am having is that some desk number/user ranges are vertical (ex: desk_1 = Sheet1!F6:F7 where F6 is the desk number and F7 is the user) and some ranges are horizontal (ex: desk_2 = Sheet1!G2:H2 where G2 is the usere and H2 is the desk number) so I am not sure how to search from multiple directions. Here's a bit of my code, which is currently not working. Any advise would be greatly appreciated!!

enter code here
function findSeats() {
var sheet = SpreadsheetApp.getActiveSheet();
var wbk = SpreadsheetApp.getActiveSpreadsheet(); 

var rng_convention = "desk_"; 
var desk_name = rng_convention; 

var users = new Array();

for ( var i = 0; i <= 1000; i++) { 
  desk_name = rng_convention; 
  desk_name = desk_name + i; 

  var rng = wbk.getRangeByName(desk_name); 

if( !rng ) { 
  continue; }
else { 
  var vals = rng.getValues(); 
  var k = 0; 

  if(vals.length == 1) {
    //look left right horizontally for user
    if(vals[0][0] == i) { 
      // create a user for this named range. 
      var user = new Object(); 

      user.name = vals[0][1]; 
      user.position = vals[0][0]; 
      users[k] = user; 
      k++;
    }
    else { 
      //look right left horizontally for user
      if (vals [0][1] == i){
      // create the user for this named range. 
      var user = new Object();

      user.name = vals[0][0]; 
      user.position = vals[0][1]; 
      users[k] = user; 
      k++;
      }
    }
  } 
/*      else {
    /* something is wrong, I need to look up-down, and then down up for users */
  }
 } 
return users; 

};

Answer 1

You were definitely on the right track. Your only real bug is that you're setting var k = 0 inside a loop, but trying to increment it in the same loop. The result is that you get just one user.

You want to be able to determine if a desk / user pairing is arranged horizontally or vertically. To do that, you're checking the length of the two-dimensional array returned by .getValues(), which works well. No problem there.

In your question, you mention two arrangements of desk / user pairs, but it appears in your code that you are concerned about four, because in the horizontal orientation you'd coded, you're looking for the location of the desk number, and assuming the name is in the opposite cell. That works, as long as the desk number is always a match for the range name. (You could just check to see if the value is a number, instead.)

So, here's the changes I've made to make this work.

• First, got rid of k altogether, and relied on push() to build the users array - much simpler and less error-prone.
• I moved the user object creation outside of the if-then-else block that tests for orientation, and set the desk number at the same time. (That value needn't be read from the spreadsheet, since it's already a looping value.)
• I completed the handling for vertical orientations, mirroring the cleaned-up horizontal handling. (You know... you could further optimize this, but it would lose clarity.)
• Added comments, and tidied indenting! (Actually, did that as I was code inspecting.)

Here's the resulting, functioning code.

function findSeats() {
  var wbk = SpreadsheetApp.getActiveSpreadsheet(); 

  var rng_convention = "desk_"; 
  var desk_name = rng_convention; 

  var users = new Array();

  for ( var i = 0; i <= 1000; i++) { 
    desk_name = rng_convention + i; 

    var rng = wbk.getRangeByName(desk_name); 

    if ( !rng ) { 
      // No matching range name
      continue; 
    }
    else { 
      var vals = rng.getValues(); 

      // create a user for this named range. 
      var user = new Object(); 
      user.position = i; 

      var valsLength = vals.length;
      // Check orientation of the range:
      //    Horizontal...  vals.length == 1 (row)
      //    Vertical means vals.length == 2 (rows)
      if(vals.length == 1) {
        // Check whether we have desk / user, or user / desk
        if(vals[0][0] == i) { 
          // desk / user
          user.name = vals[0][1]; 
        }
        else { 
          // user / desk
          user.name = vals[0][0]; 
        }
      }
      else {
        // vals.length == 2
        // Check whether we have desk / user, or user / desk
        if(vals[0][0] == i) { 
          // desk / user
          user.name = vals[1][0]; 
        }
        else { 
          // user / desk
          user.name = vals[0][0]; 
        }
      }

      // Add this user to our array
      users.push(user);
    }
  }
  // Return array of user objects
  return users; 
};

Sample Data

Debug Output

Edit: Record all collected data in a spreadsheet

To answer the follow-up question, it's straight-forward to get all the collected desk & user information into a spreadsheet. To prepare for the .setValues() function, we need to convert our objects to a 2-d Array, which is easy. Alternatively, the previous code could be refactored to generate an array in the first place.

Here's how we can perform the conversion, and write all the data a sheet. If the named sheet does not exist, we'll create it first - otherwise, we'll just replace the previous contents.

// Record all desk / user data to a (new) sheet
function recordAllDeskUsers() {
  var users = findSeats();
  var userArray = new Array();
  userArray.push(["Desk #","User Name"]);

  for (var i in users) {
    var thisUser = [users[i].position,users[i].name];
    userArray.push(thisUser);
  }

  // Open the target sheet - if it doesn't exist, insert it.
  var userSheetName = "All Desks";
  var userSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(userSheetName);
  if (!userSheet) {
    SpreadsheetApp.getActiveSpreadsheet().insertSheet(userSheetName)
    userSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(userSheetName);
  }

  // Clear the sheet, then write our collected values to it.
  userSheet.clear();
  userSheet.getRange(1, 1, userArray.length, userArray[0].length).setValues(userArray);
}