How to get the ordinal index of a column from a Google Sheets row feed?

Question

I'm trying to figure out the most robust way to determine the ordinal position of a column in a google sheet from a row feed, not a cell feed.

So, for example, I have the following spreadsheet:

                        A          B          C         D     
              +----+-----------------------------------------+
 Header Row   | 1  |  name   |  address  |  phone  |  email  |
              +----+---------+-----------+---------+---------|
              | 2  | Jane    | 1001      | 6139023 | w@gmail |
              +----+---------+-----------+---------+---------+
              | 3  | Jon     | 1102      | 4320909 | x@gmail |
              +----+---------+-----------+---------+---------+
              | 4  | Jeff    | 1203      | 4132323 | y@gmail |
              +----+---------+-----------+---------+---------+
              | 5  | Jenny   | 1304      | 3346044 | z@gmail |
              +----+---------+-----------+---------+---------+

In my code, I'm able to successfully fetch a row-based feed from the sheet via Google's Sheet's API. The object I get back for each row in the feed includes various properties, most of which are the values in the header row. So for example, a single row object looks something like:

{
    id           : 'https://spreadsheets.google.com/...'
  , 'app:edited' : '2017-02-24T23:59:56:56.520Z'
  , _links       : [ self: 'https://...', edit: 'https://...']
  , name         : 'Jane'
  , address      : '1001'
  , phone        : '6139023'
  , email        : 'w@gmail'
}

I want to find the ordinal position of any given column from the row-based feed, but it doesn't seem immediately obvious how to do this. It does seem the object's properties are ordered according to their position in the header row, but I'm hard pressed to find which column number the property refers to.

The reason I want the column number is that I want to get a cells-based feed for a particular column and row, but it appears Google's API requires that you know the ordinal position of the column. That is, you need to know the min-column and max-column, as well as a min-row and max-row for a cells based feed. Now, I know that I can obviously look at the column in the Sheets UI and determine which position the column is in, but how can I do it programatically?

In sum, I'm trying to find a programmatic way to answer a simple question such as question:

"What is the ordinal position of the phone column?"

Is this even possible with Google's row-feed response?

noogui noogui · Accepted Answer · 2017-02-26T16:33:07

There is no such feature yet in Sheetsv4. You have to manually perform the Basic Reading methods and parse the response and loop through it.

Here I was looking for the position of "phone" which is C3

  function findPosition(){
         var arrayBuffer;
         var myArr;
         var xhr = new XMLHttpRequest();
         xhr.open('GET', 'https://sheets.googleapis.com/v4/spreadsheets/'+myspreadsheetId+'/values/'+"Sheet1!"+A+":"+Z);
         xhr.setRequestHeader('Authorization', 'Bearer ' + myAccessToken);

         xhr.onload = function (oEvent) {
             arrayBuffer = xhr.response; // Note: not oReq.responseText
             console.log("arrayBuffer "+ arrayBuffer);
             myArr = JSON.parse(arrayBuffer);

              for(var i = 0; i < myArr.values.length; i++){

                 if(myArr.values[0][i] === "phone"){
                     console.log("column position is " + (i+1)); // +1 because counting starts at 0
                 }
              }
         };
         xhr.send(null);
      }

Output:

There may be other ways to do this. I just showed you my version.

How to get the ordinal index of a column from a Google Sheets row feed?

1 Answers