Submitting a row to two sheets broken by SDK updates

Michael Steffel

We have a form on our site that adds a row to two sheets in Smartsheet.  This broke in June at which time the form added a row to the first sheet, but failed in the API call to Smartsheet in adding a row to the second sheet.

Example Code that submitted to the first sheet (this was not affected):

        protected void SendFormInfoToSmartsheet() {

            string smartsheetAPIToken = ConfigurationManager.AppSettings["SWTCSmartsheetToken"];

            Token token = new Token();

            token.AccessToken = smartsheetAPIToken;

            // Use the Smartsheet Builder to create an instance of SmartsheetClient

            SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();

            // Specify cell values for first row.

            Cell[] cellsA = new Cell[] {

                new Cell.AddCellBuilder(645402597xxxxxx, DateOfIncident.Text).Build(),

                new Cell.AddCellBuilder(2897202411xxxxxx, name.Text).Build(),

                new Cell.AddCellBuilder(7400802038xxxxxx, phone.Text).Build(),

                new Cell.AddCellBuilder(1771302504xxxxxx, email.Text).Build(),

                new Cell.AddCellBuilder(6274902132xxxxxx, complaintType.SelectedItem.Text).Build(),

                new Cell.AddCellBuilder(4023102318xxxxxx, protectedClass.Text).Build(),

                new Cell.AddCellBuilder(363927621xxxxxx, serviceArea.SelectedItem.Text).Build(),

                new Cell.AddCellBuilder(8526701945xxxxxx, issue.Text).Build()

            };

            // Specify contents of first row.

            Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsA).Build();

            // Add rows to sheet.

            smartsheet.SheetResources.RowResources.AddRows(272967129xxxxxx, new Row[] {rowA});

The code that follows (which is within the same aspx.cs file) started failing some time between June 5, 2018 and July 27, 2018.  The IDs such as nameID, were declared in a case statement which set the IDs for each sheet column within the submitted row.

                case "Billing Dispute":

                    sheetID = 463297182xxxxxx;

                    DateOfIncidentID = 453226612xxxxxx;

                    nameID = 6784065941xxxxxx;

                    phoneID = 115456640xxxxxx;

                    emailID = 565816603xxxxxx;

                    complaintTypeID = 3406366xxxxxx;

                    protectedClassID = 790996584xxxxxx;

                    serviceAreaID = 509521608xxxxxx;

                    issueID = 59161645xxxxxx;

                    emailReturn = "'Fname Lname' <email@domain.com>";

                    leadStaff = "Fname Lname";

                    break;

            Cell[] cellsB = new Cell[] {

                new Cell.AddCellBuilder(DateOfIncidentID, DateOfIncident.Text).Build(),

                new Cell.AddCellBuilder(nameID, name.Text).Build(),

                new Cell.AddCellBuilder(phoneID, phone.Text).Build(),

                new Cell.AddCellBuilder(emailID, email.Text).Build(),

                new Cell.AddCellBuilder(complaintTypeID, complaintType.Text).Build(),

                new Cell.AddCellBuilder(protectedClassID, protectedClass.Text).Build(),

                new Cell.AddCellBuilder(serviceAreaID, serviceArea.Text).Build(),

                new Cell.AddCellBuilder(issueID, issue.Text).Build()

            };

            // Specify contents of first row.

            Row rowB = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsB).Build();

            // Add rows to sheet.

            smartsheet.SheetResources.RowResources.AddRows(sheetID, new Row[] { rowB });

            SendEmailNotifications(emailReturn, leadStaff);

The issue was somehow fixed by attempting to update the SDK in our solution within Visual Studio. 

As we did not change any of our code during this time, I can only assume that something on the Smartsheet API 2.x was updated and not backward compatible.

Just thought I would attempt to document this issue.  Hopefully it will be helpful to others.

Shaine Greenwood

Hi Michael—

Thanks for posting this fix.

It appears that you were able to resolve the issue by updating your SDK such that it's on the latest version.

This is always an important preliminary troubleshooting step to take when working with any APIs, and I think that our community members who work with APIs will find this useful!

Michael Steffel

UPDDATE:

Actually this issue was not resolved.  It appears the API is only writing to sheets I (as a user) have access to.  

This is a problem for our latest project as it is a complaint form what (when completed) I should not have access to the sheet for governance reasons.

The master sheet is owned by HR and supplemental sheets are owned by individual areas of the College (Academics, Facilities, Financial Aid, HR, ...).  For governance reasons we would like only the proper people to have access to these sheets, but the API fails when submitting to any sheet I do not have access to.

How is it that I (my user) have to be a user of the sheet in order for the API to write to that sheet?  

J. Craig Williams

re: How is it that I (my user) have to be a user of the sheet in order for the API to write to that sheet?

Security. 

The API token provided has the authorizations of the user that created them. Otherwise, anyone could create an API token and have access to any object in the system. We wouldn't want that, would we?

In the API, there are ways to assume the credentials of another user, but those also have secure authorizations to function.

Craig

Michael Steffel

Sorry, I disagree, but if I have something wrong here correct my logic. 

Our organization is set up with multiple departments all of which have governance over their own information: our complaint form for instance.  As the developer, I should have access to the sheets while in development, but when released, I should not have access to confidential information.

In the same way, one department (in this case person in HR) has oversight of the master sheet all complaints get written to. Those complaints are then written to separate sheets for different areas of the college.  That said, the person in HR should not have access to facilities, student services, ... .

Governance is not possible in this case of a site utilizing an API token for an individual.  However, if there was an organization API token that would be used to communicate from our systems to Smartsheet, security would be maintained, but so would governance.

Also if the individual who’s API token is being used leaves the organization and that account is removed, I assume all forms feeding into those sheets would fail.

Tying the API token to an individual is a flaw in my opinion.

As I said, please correct me where I have made incorrect assumptions.

Mike

J. Craig Williams

I do not speak for Smartsheet, so take this as someone that is just a paranoiac.

If system wide functionality is required, then a system wide key is required. If a user should not have access to the system, then giving them a key negates that.

Smartsheet's security is actually better than many of the other tools I use. I can create a licensed user that I shared only the things I want the API to see and then build the API using that token. Other tools do not have this capability or it is not so easily implemented, in my (limited) experience. 

Either the parts of the system are locked or they aren't.

If the system needs to check every sheet for something, then it needs to be created by a user than can do that. In Smartsheet, this is the SysAdmin. 

For my customers, I recommend a limited number of SysAdmin's, with procedures in place for replacement and/or substitution when necessary.

In the end, SOMEONE needs to have the key. In many cases, this is a limited number of people and the developer(s) of the software.

Craig

 

Shaine Greenwood

Hi Michael,

The Smartsheet API currently doesn't have an organization-wide API token. Access tokens must be generated by users and access scopes/permission levels on sheets reflect that of the users making the API calls.

A principle of RESTful API design is to authenticate all actions through a specific user such that the actions can be traceable to that user.

With that being said, if you're a System Admin on the Smartsheet account, you can assume a user and make calls on their behalf. Details on this are available in the help center: https://smartsheet-platform.github.io/api-docs/?javascript#permission-levels