Smartsheet data into Gridview

Question

I am quite new to Smartsheet and programming using c#. Mostly a beginner and trying to use visual studio 2013 to integrate my web application with Smartsheet. I am currently struggling with getting a gridview control to populate with the returned data from Smartsheet(in json format). So far, I have been able to get the json string which contains:

{  
   "id":4705121406871428,
   "name":"Distributors",
   "version":3,
   "totalRowCount":2,
   "accessLevel":"OWNER",
   "effectiveAttachmentOptions":[  
      "DROPBOX",
      "GOOGLE_DRIVE",
      "BOX_COM",
      "FILE"
   ],
   "ganttEnabled":false,
   "dependenciesEnabled":false,
   "permalink":"https://app.smartsheet.com/b/home?lx=xlJ7OtqknyOndgOnClj4qg",
   "createdAt":"2015-06-22T16:55:02+10:00",
   "modifiedAt":"2015-06-23T14:17:47+10:00",
   "columns":[  
      {  
         "id":3238895438587780,
         "index":0,
         "title":"id",
         "type":"TEXT_NUMBER",
         "primary":true,
         "width":150
      },
      {  
         "id":7742495065958276,
         "index":1,
         "title":"distName",
         "type":"TEXT_NUMBER",
         "width":150
      },
      {  
         "id":2112995531745156,
         "index":2,
         "title":"agency",
         "type":"TEXT_NUMBER",
         "width":150
      },
      {  
         "id":6616595159115652,
         "index":3,
         "title":"profile",
         "type":"TEXT_NUMBER",
         "width":150
      }
   ],
   "rows":[  
      {  
         "id":3999757174630276,
         "rowNumber":1,
         "expanded":true,
         "createdAt":"2015-06-23T11:05:26+10:00",
         "modifiedAt":"2015-06-23T14:17:47+10:00",
         "cells":[  
            {  
               "columnId":3238895438587780,
               "type":"TEXT_NUMBER",
               "value":1.0,
               "displayValue":"1"
            },
            {  
               "columnId":7742495065958276,
               "type":"TEXT_NUMBER",
               "value":"ABS",
               "displayValue":"ABS"
            },
            {  
               "columnId":2112995531745156,
               "type":"TEXT_NUMBER",
               "value":"ShedsRus",
               "displayValue":"ShedsRus"
            },
            {  
               "columnId":6616595159115652,
               "type":"TEXT_NUMBER",
               "value":"OK",
               "displayValue":"OK"
            }
         ]
      },
      {  
         "id":8503356802000772,
         "rowNumber":2,
         "siblingId":3999757174630276,
         "expanded":true,
         "createdAt":"2015-06-23T11:05:26+10:00",
         "modifiedAt":"2015-06-23T14:17:47+10:00",
         "cells":[  
            {  
               "columnId":3238895438587780,
               "type":"TEXT_NUMBER",
               "value":2.0,
               "displayValue":"2"
            },
            {  
               "columnId":7742495065958276,
               "type":"TEXT_NUMBER",
               "value":"Barns",
               "displayValue":"Barns"
            },
            {  
               "columnId":2112995531745156,
               "type":"TEXT_NUMBER",
               "value":"BarnsRus",
               "displayValue":"BarnsRus"
            },
            {  
               "columnId":6616595159115652,
               "type":"TEXT_NUMBER",
               "value":"OK",
               "displayValue":"OK"
            }
         ]
      }
   ]
}

This string looks to contain too much information such as id's, and types etc... All i want is my column names, and their associated data. Then, display this info as a gridview control.

This is my json request:

        string sURL;
        sURL = "https://api.smartsheet.com/1.1/sheet/4705121406871428";

        WebRequest wrGETURL;
        wrGETURL = WebRequest.Create(sURL);
        wrGETURL.ContentType = "application/json";
        wrGETURL.Method = "GET";
        wrGETURL.Headers["Authorization"] = "Bearer ******************";

        Stream objStream;
        objStream = wrGETURL.GetResponse().GetResponseStream();

        StreamReader objReader = new StreamReader(objStream);

        string jsonResult = objReader.ReadToEnd();

If anyone can provide some guidance to get this working for me, it would be greatly appreciated...please excuse my total ignorance on this topic...complete newb here! Thanks.

Answer 1

In the response from the Smartsheet API there is a columns object, which is made up of an array of the columns from your sheet. Loop over this array to get the column titles and the column IDs.

Then, go through the rows object and loop over each row. For each row you'll want to loop over the array of cells for that row and output the displayValue. Make sure that each columnId for the cell matches the id of the column you are printing, because if you have a blank cell in Smartsheet, no cell object will be returned by the Smartsheet API.

You can find more information about the Smartsheet API here. And, If you haven't already done so, I would also recommend checking out the C# SDK for the Smartsheet API.