Issue with Duplicate Rows When Using Smartsheet API in Integration with NetSuite

Noterfull

Hello!

I've encountered an issue while integrating NetSuite and Smartsheet via API. The script works inconsistently and occasionally creates duplicate rows in the Smartsheet table.

Current Script Logic:

1. A record is created in NetSuite, triggering the script.
2. The script collects data about the parent row.
3. A request is sent to add a row to the table via the API method .../sheets/{sheetId}/rows.
4. The script waits for a response with the ID of the created row.
5. If no ID is received (error or empty response body):
   • A 2-minute wait period is initiated.
   • The API search method .../search?query= is called to find the row by its content.
   • If the row is found (status code 200), its ID is retrieved, and the script continues.
   • If the row is not found, another attempt is made to add it using .../sheets/{sheetId}/rows.
6. This process repeats up to 5 times before the script terminates.

Issue:

The error SSS_REQUEST_TIME_EXCEEDED (request timeout exceeded) occurs in NetSuite. However, the key problem is:

1. When adding the parent field, a timeout error sometimes occurs.
2. A search request using .../search?query= returns a 200 status code. However, the row does not actually exist in the table! Even after waiting for 2 minutes.
3. When attempting to add the row again, it successfully gets added. As a result, duplicate rows appear in the table.

Example: I attempt to add a row with the value “IT3775”, encounter a timeout error, wait 2 minutes and search for “IT3775”, get a status code of 200 (although the row is still not there), then try to add the row again and end up with duplicates.

Below I've provided the script execution logs from NetSuite (some fields are hidden as a security measure), as well as screenshot from the SmartSheet table so that you can see the problem clearly.

line 2347 - First attempt to add a parent line. (An error was occurred in the script, but eventually this field appears in the SmartSheet table).
line 2348 - Second attempt to add a parent line (successful).
line 2349 - Child fields.

NetSuite-ErrorLog.txt

P.S.I will provide the script code if needed.

Questions:

1. Why does the .../search?query= method return status code 200 if the row is not in the table yet? (Why don't they return 204 or 404, for example?).
2. How can this situation be handled properly to avoid duplicate rows?
3. Could this issue be caused by a delay in data processing on Smartsheet’s side?

I’d appreciate any ideas or experiences in solving similar issues!

Thank you!

prime_nathaniel

@Noterfull you will want to build more robust processing flags / handling into your sheet. For example, when you add the parent row, you can also add a specific flag for "needsChild" = true. Now you can search for needs child = true which is much easier to process. You should also use webhooks to process events rather than wait for indexing (this isn't a huge deal on SMAR but it is awful on microsoft graph and is in general good practice).

Recommended workflow

create row
create row webhook
get row
add child to row and uncheck needsChild

in event webhook fails
use the needsChild flag to cron process any missed webhook events

Georgie

Hi @Noterfull,

It looks like the problem might be due to the fact that you’re attempting to add parent and child rows. I was able to determine that one user found the solution to this problem was to loop through the predefined items list and send POST requests to add child rows, linking them to the parent using the parentID. 

Since it appears that your parent row is created successfully and the ID is provided, but the row is not showing until the second attempt, I’d recommend trying the above potential solution. If that doesn’t work, I’d suggest asking your question here: Community Forum for Smartsheet API.

Thanks, 

Georgie

prime_nathaniel

@Noterfull you will want to build more robust processing flags / handling into your sheet. For example, when you add the parent row, you can also add a specific flag for "needsChild" = true. Now you can search for needs child = true which is much easier to process. You should also use webhooks to process events rather than wait for indexing (this isn't a huge deal on SMAR but it is awful on microsoft graph and is in general good practice).

Recommended workflow

create row
create row webhook
get row
add child to row and uncheck needsChild

in event webhook fails
use the needsChild flag to cron process any missed webhook events

Noterfull

@prime_nathaniel Hello!
Sorry for not responding to your advice sooner. I’ve implemented what you described above, and I believe it will completely solve my problem. It’s currently going through the testing phase. I’m very grateful for your help!

prime_nathaniel

@Noterfull

No worries, happy its working out. don't forget to add a cron handler for backup since webhooks are by design not guarantees of processing.