Search code examples
jsonwebhookssmartsheet-api

Automatically add new child rows to sheet when a new row is added in Smartsheet API using json

for my project in Smartsheet, I have a sheet that requires multiple child rows to be added whenever a new row is created in the sheet.

I'd like to take certain rows in sheet 1, and copy them to another location in sheet 1 as child rows, automatically, every time a new row is created.

Is there a way to do this??

Things I've tried: The copy rows to another sheet method sends an error if I tried to copy within the same sheet. I did relocate the template rows to another sheet so that it would copy from sheet 2 back to sheet 1, but it wouldn't let me specify the location, unless I'm doing it wrong. But it returns the error:

The app returned "Unable to parse request. The following error >occurred: Unknown attribute "parentId" found at line 2, column 65".

For reference this is the code I'm using:

     sheets/6237392404277124/rows/copy

     {
     "rowIds": [8864322101372804, 279335311697796, 4782934939068292], 
     "to": {"sheetId": 6730248491427716, "parentId": 2384552453924740}
     }
Solution

  • Your scenario can be solved for in multiple steps.

    Step 1: Create webhook

    The first aspect of your scenario is: your app needs to be automatically notified when a new row is added to the specified sheet. To do this, you'll need to create a webhook that'll send a callback notification to your app when an event occurs in the specified sheet.

    The notification that your app receives will contain a Callback object. You'll want to evaluate the events property (an array of CallbackEvent objects) of that Callback object -- looking for an event where:

    • objectType = row
    • eventType = created

    When you see a CallbackEvent object that meets this criteria -- save the value of the id property -- that's the id of the newly added row (which will be the parent row for the rows you want to add under it).

    Step 2: Copy rows to target sheet

    Next, you'll want to do like you've described in your post -- i.e., copy the rows from another sheet to the target sheet, by using the Copy Rows operation. Note that this operation will always put the copied rows at the bottom of the target sheet. (It's not possible to specify another location.) A successful response from the Copy Rows operation will return a CopyOrMoveRowResult object, which looks like this:

    {
  "destinationSheetId": 2258256056870788,
  "rowMappings": [
    {    
      "from": 145417762563972,
      "to": 4508365800925060
    },   
    {    
      "from": 8026717110462340,
      "to": 2256565987239812
    }    
  ]    
}

    In this response, the rowMappings array will contain an object for each copied row. You'll want to save off the value of each to property in this array -- those are the ids of the new rows you've copied to the target sheet.

    Step 3: Move copied rows to desired location in target sheet

    So, at this point, you know the following:

    • the id of the newly created row that triggered this workflow
    • the id of each row that has been copied to the bottom of the target sheet

    Finally, you need to move the copied rows from the bottom of the target sheet to the desired location in the target sheet (i.e., under the newly created row that triggered this workflow). To do this, you'll use the Update Rows operation. You can issue a single Update Rows request to move all 3 rows.

    For example, the following Update Rows request moves three rows (identified by the id property within each object) to be child rows of the specified parent row (identified by the parentId property within each object). Be sure to specify the rows in the order that you want them to appear. In this example -- assuming that there were previously no child rows under the specified parent row -- row ID 3170202670262148 becomes the first child row, followed by row ID 7673802297632644 as the second child row, followed by row ID 2044302763419524 as the third and final child row.

    PUT /sheets/{sheetId}/rows

[
    {
        "id": 3170202670262148, 
        "parentId": 5225480965908356, 
        "toBottom": true
    },
    {
        "id": 7673802297632644, 
        "parentId": 5225480965908356, 
        "toBottom": true
    },
    {
        "id": 2044302763419524, 
        "parentId": 5225480965908356, 
        "toBottom": true
    }   
]

    Hope this helps. It's obviously a bit of work to implement the scenario you've described, but it's certainly possible.