2

I am using a JSON string produced from smartsheets with the intention of selecting specific values and adding them to an sql database. Because this is set up by a user the structure of the smartsheet isn't ideal for putting into an SQL database so I am trying to find a way to tell the program to select specific values to put into the database. What the user has done is turned the cells that are to be put into the database green in smartsheets. The JSON file I get back though only shows columnID, type, value, and display value. I'm not sure if this is possible but I was wondering if there was a way to detect if he had selected the color of that cell to be green?

Open to other suggestions too. I was thinking of adding an extra column that says input so I know that that set of columns in that row need to be entered but thought I might see if the above option is viable in smartsheets using the REST API. At the moment this is (basically) my code.

smartsheetToJSON class. 

        Console.WriteLine("Requesting data from smartsheets");
        //This is the GET request component. Depends on what is in the URL as to what you get back. 
        HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create("https://api.smartsheet.com/1.1/sheet/8385518611261316");
        httpWebRequest.ContentType = "application/json";
        httpWebRequest.Accept = "*/*";
        httpWebRequest.Method = "GET";
        httpWebRequest.Headers.Add("Authorization", "Bearer " + authorisation);

        Console.WriteLine("Reading data to JSON");
        HttpWebResponse httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
        string responseString = new StreamReader(httpResponse.GetResponseStream()).ReadToEnd();
        return responseString;

And this is currently in the static main. 

        smartsheetToJSON getSheetData = new smartsheetToJSON();
        string jsonString = getSheetData.getSheet();
        Console.WriteLine("Printing JSON");
        JObject rss = JObject.Parse(jsonString);
        Console.WriteLine(jsonString);
Kim Brandl
  • 13,125
  • 2
  • 16
  • 21
Danrex
  • 1,657
  • 4
  • 31
  • 44

1 Answers1

4

Add this query string parameter to your URL: ?include=format

When you do that, the cells that come back will contain a "format" attribute in the form of a list of numbers. Here's the documentation on how to make sense of that list:

https://www.smartsheet.com/developers/api-documentation#h.up1zf3ui4dyr

It's not the most convenient representation in the world to parse, as it was designed to optimize payload size for sheets containing large numbers of cells. But it does give you the information you need.

I tried it out, and when I made gave cells in my sheet a background color of green, they contained this format: ",,,,,,,,,30,,,,,,". You may want to experiment to find the exact format values that you're looking for.

KevinT
  • 76
  • 3