Point me in the right direction - Troubleshooting API

Tim Starkey

I don't expect you can help me without seeing the API code, but wondering if you can point me in the right direction.

We have an API that was developed by a Smartsheet provided consultant during onboarding of our enterprise plan. The API queries our accounting database, then updates rows in various (500+) sheets, using a common ID code between the accounting system and rows in the sheet. This API runs nightly.

The API has not run for the past week. When I browse through the error log it provides I find a few things:

• 2024-01-08 12:27:49.510 -07:00 [ERR] The 'Project Plan' row could not be found
• 2024-01-08 12:27:49.512 -07:00 [ERR] SS_ERR_08 - Row couldn't be found

The error codes it provides seem to be specific to this API. But I have determined that it is looking for a row with 'Project Plan' in the task name. It turns out our template has had that row renamed to 'Work Plan' since the API was written. However, this error does not seem to stop the API, and it continues on, successfully updating rows, until it hits this error (### are mine replacing specific sheet and row ID's):

• 2024-01-08 12:27:53.309 -07:00 [INF] Task Row is available for Accounting ID - ### in Project sheet ###. Updating the Same Row
• 2024-01-08 12:27:56.103 -07:00 [ERR] SS_ERR_09 - Error Updating or Inserting - An unexpected error has occurred. Please contact the Support team at https://help.smartsheet.com/contact for assistance.

This is followed by several lines referencing the API library, including:

• SmartSheetConnect.UpdateRowsHandler(Int64 sheetId, List`1 rows)
• SmartSheetConnect.InsertUpdateRows(SheetOps sheetOp, Int64 sheetId, List`1 rows)
• SmartSheetConnect.UpdateRows(Int64 sheetId, List`1 rows)
• UpdateSmartSheet(List`1 Reports)
• Services.ReportService.Run() i
• Program.Main(String[] args)

The API appears to be updating some rows, but inserting the numbers as text values (preceded by an '), which it did not used to do.

Smartsheet isn't supporting even though it was an API developer they provided... so we are likely to going to need someone. Again, I know you can't assist without seeing the code... this is not my area of expertise... But any suggestions... even if its "you need to hire this person" :)

KevinFansler

Hi Tim, I don't have a complete answer; however, I can guess a few things:

(1) Whoever built this is using the "allow partial success" toggle, which means that successive rows will populate despite that first batch of errors where it can't find the row. So, that's one bit of your question.

(2) I'm guessing a row was deleted somewhere along the line and this missing row is what is causing some of these other errors. You can look at the document history to see if something like this happened last week when it was running correctly.

(3) If the code is really old, it might not be using "objectValue" which is a more specific type of value. Without it, I can see numbers getting changed to text strings. You should look over this section on working with complex objects: https://smartsheet.redoc.ly/#section/API-Basics/Multi-contact-or-Multi-picklist:-Working-with-Complex-Objects

(4) Finally, I suggest reviewing this section on Bulk Operations: https://smartsheet.redoc.ly/#section/Work-at-Scale/Bulk-Operations

Lindsay Whitbread

Hi Tim,

I can help you with the issue around the apostrophe (') in front of the numbers.

We first saw this behaviour around the 4th of January. By entering your numeric values as a float, it will bypass the behaviour of the API saving numbers as text (preceding with a ').

We're working with Smartsheet support around this change in behaviour (bug?).

Cheers,

Lindsay

KevinFansler

Hi Tim, I don't have a complete answer; however, I can guess a few things:

(1) Whoever built this is using the "allow partial success" toggle, which means that successive rows will populate despite that first batch of errors where it can't find the row. So, that's one bit of your question.

(2) I'm guessing a row was deleted somewhere along the line and this missing row is what is causing some of these other errors. You can look at the document history to see if something like this happened last week when it was running correctly.

(3) If the code is really old, it might not be using "objectValue" which is a more specific type of value. Without it, I can see numbers getting changed to text strings. You should look over this section on working with complex objects: https://smartsheet.redoc.ly/#section/API-Basics/Multi-contact-or-Multi-picklist:-Working-with-Complex-Objects

(4) Finally, I suggest reviewing this section on Bulk Operations: https://smartsheet.redoc.ly/#section/Work-at-Scale/Bulk-Operations

Tim Starkey

Thanks @KevinFansler. I will take a look at those links, and see if I can review the actual API code for more information.

KevinFansler

The quickest fix might be restoring the row that was deleted. But that could also be looking for a needle in a haystack considering you have 500+ rows.

Tim Starkey

Any recommendations on a developer that works in .NET, SQL, and the Smartsheet API? In summary, our app is a .NET that queries Microsoft Dynamics SL (SQL database) for any changes since last query and creates a list of updates. It then uses the accounting ID (key) to find the sheet and then the row in Smartsheet, and updates it. If its a new entry, it creates a new row in the sheet. These errors started on 1/4/2024 if that makes any difference. I am not sure if the errors are on the SQL query side, or the Smartsheet API side, but guessing API side.

Currently, it is having two issues:

1. Its throwing errors on some row updates. I have confirmed that the rows do exist, and were not deleted. The rows it updated prior to this row are indeed updated. The row it throws the error on exists, but is not updated.
2. The updates it is completing are now preceded with an ', making them a text string instead of a number. So all the formulas in smartsheet associated with that row are not functioning.
   1. Note, all updates prior to 1/4, the cells were populated with numbers correctly... starting 1/4 and after, the cells that are updated have the correct number, but are preceded with a '.

In the meantime, I am reviewing the API files. I am not a developer, and not expecting free services here, but any advice appreciated. In follow the chain of errors in the log file:

1. [INF] Task Row is available for Accounting ID - 114011120608017 in Project (1695690821789572). Updating the Same Row
   1. Accounting ID is a key between the SQL data and Smartsheet. The Project # is the Smartsheet sheet ID.
2. [ERR] SS_ERR_09 - Error Updating or Inserting - An unexpected error has occurred.
3. [ERR]  at [this is followed by a list of errors and what I assume are library objects outlined below... again, not a developer]

In looking at the individual lib references I find:

Error at libs.SmartSheetConnect.UpdateRowsHandler(Int64 sheetId, List`1 rows) which references SmartSheetConnect.cs:line 175 which is in the following function ("Throw new exception" is row 175)

    private IList<Row> UpdateRowsHandler(long sheetId, List<Row> rows)
    {
      try
      {
        return client.SheetResources.RowResources.UpdateRows(sheetId, rows);
      }
      catch (Exception ex)
      {
        throw new CVLReportException("SS_ERR_09", "Error Updating or Inserting - " + ex.Message.ToString());
      }
    }

Error at SmartSheetConnect.InsertUpdateRows(SheetOps sheetOp, Int64 sheetId, List`1 rows) and references SmartSheetConnect.cs:line 124 which is in the following function (IList<Row> is line 124):

private List<Row> InsertUpdateRows(SheetOps sheetOp, long sheetId, List<Row> rows)
    {
      var result = new List<Row>(0);

      Func<long, List<Row>, IList<Row>> insertUpdateHandler = GetInsertUpdateRowsHandler(sheetOp);
      string actionName = sheetOp == SheetOps.InsertRows ? "Insert" : "Update";

      // Log.Debug($"{actionName}: {rows.Count} rows in sheet {sheetId}");

      // Set the initial counters.

      int rowCount = rows.Count;
      int handledCount = 0;

      // Handle the records in batches.
      for (int i = 0; i < rowCount; i += BatchCount)
      {
        var actionRows = rows.Skip(i).Take(BatchCount).ToList();
        // Log.Debug($"{actionName}: Rows {handledCount} - {handledCount + actionRows.Count}");

        IList<Row> actionResult = insertUpdateHandler(sheetId, actionRows);

        handledCount += actionResult.Count;
        result.AddRange(actionResult);
      }

      // Log.Debug($"{actionName}: {handledCount} rows in sheet {sheetId}");
      return result;
    }

Error at libs.SmartSheetConnect.UpdateRows(Int64 sheetId, List`1 rows) and references SmartSheetConnect.cs:line 101 which is in the following function:

public List<Row> UpdateRows(long sheetId, List<Row> rows)
    {
      return InsertUpdateRows(SheetOps.UpdateRows, sheetId, rows);
    }

Error at Services.CVLReportService.UpdateSmartSheet(List`1 CVLEarningReports) and references CVLReportService.cs:line 275 which is in the following function (List<Row> is line 275):

CVLEarningReport.accounting_id.ToString());

            Row row = new()
            {
              Id = updatedTaskRow.Id,
              Cells = FormTaskCells(accountingIDMatchRow, CVLEarningReport, subCategoryRow)
            };

            List<Row> updateResult = smartsheetManager.UpdateRows(projectSheet.Id.Value, new List<Row> { row });
            Log.Information($"Task Row for Accounting ID - {CVLEarningReport.accounting_id} updated successfully in Project

Error at Services.CVLReportService.Run() and references CVLReportService.cs:line 49 which is in the following function (return UpdateSmartsheet is line 49):

    public CVLReportService(IConfiguration _iconfiguration)
    {
      try
      {
        CVLReportService._iconfiguration = _iconfiguration;
        smartsheetManager = new(CVLReportService._iconfiguration);

        configSheet = smartsheetManager.GetConfigSheet();
        dbConnect = new(configSheet);
      }
      catch
      {
        throw;
      }
    }

    public TaskStatus Run()
    {
      try
      {
        return UpdateSmartSheet(RetriveData());
      }
      catch
      {
        throw;
      }
    }

and last, Error at Program.Main(String[] args) and references Program.cs:line 108 which is in (taskStatus = cvlReportService.Run() is row 108):

    public static void Main(string[] args)
    {
      TaskStatus taskStatus = new();
      JobStatus jobStatus = new();
      try
      {
        jobStatus.ID = $"job_{DateTimeOffset.Now.ToUnixTimeMilliseconds()}";
        jobStatus.StartTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        InitConfigurations();

        CVLReportService cvlReportService = new(_iconfiguration);
        taskStatus = cvlReportService.Run();
      }
      catch (CVLReportException ex)
      {
        isExceptionThrown = true;
        Log.Error(ex.ErrorCode + " - " + ex.ErrorMessage);
        Log.Error(ex.StackTrace);
      }
      catch (Exception ex)
      {
        isExceptionThrown = true;
        Log.Error("APP_ERR_01 - Internal Server Error");
        Log.Error(ex.Message);
        Log.Error(ex.StackTrace);
      }
      finally
      {
        InsertJobStatus(taskStatus, jobStatus);
      }
    }

Lindsay Whitbread

Hi Tim,

I can help you with the issue around the apostrophe (') in front of the numbers.

We first saw this behaviour around the 4th of January. By entering your numeric values as a float, it will bypass the behaviour of the API saving numbers as text (preceding with a ').

We're working with Smartsheet support around this change in behaviour (bug?).

Cheers,

Lindsay

Tim Starkey

Hi @Lindsay Whitbread ,

Thanks for the response. I don't know how to change the numeric values to float. I will reach out to you to discuss. But, it sounds like this is a recent change by Smartsheet if you are also seeing new behaviour around the same timeframe. Hopefully this will get fixed soon. Please let me know if you get an update from Smartsheet.

Thanks!

kskarz

Hi @Tim Starkey , @Lindsay Whitbread

When updating the row you can specify the new_cell.strict attribute.

If I set this attribute to False and I send the update request via API where the value for the update is:

• Integer 123 [Column2]
• String 123 [Column3]

The cells in the row will be updated as a INT number regardless of what values I passed to update as per below:

But if I do the same where the new_cell.strict is set to True then based on both examples above the row in Column2 will be updated with INT and in Column3 with a string (with an ' in front).

• Integer 123 [Column2]
• String 123 [Column3]

If you use API to collect the data from the sheet the API response might have two different values:

new_cell.strict = True

new_cell.strict = False

So if you are using the data to compare with the database or any other system this can create some errors...

Lindsay Whitbread

https://community.smartsheet.com/discussion/comment/412697#Comment_412697

Those changes have been reversed by Smartsheet as of a week or two ago - now the only work required is to fix up all of the corrupted data that was created in sheets. We ended up creating some internal tools to clean up a sheet's data.