Databases
StackOverflow2013
Postsdbo
POLook through named ranges and print values to new sheet
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLook through named ranges and print values to new sheet
    primarykey
    data
    text
    <p>I am relatively new to JavaScript, so please bear with me. </p> <p>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!!</p> <pre><code>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 &lt;= 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; }; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1916075
    1. USuser1916075
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload