Connecting AgilePoint form data to a Smartsheet

Michael Buckingham

Hello all,

 

I'm looking for some information on how to go about connecting a form and workflow to a Smartsheet. I would like the AgilePoint form to update a SmartSheet when anyone makes a submission. AgilePoint workflows support REST Service method. I've looked at the service and done some setup, as well as setup my token on the Snartsheet side. I'm working on setting up the connectin in AgilePoint, however i'm just not certain what the payload should be for Smartsheet. Thank you for any help on this. 

 

Here are some screenshots of the REST service setup

dAVE Inden

Michael,

 

Are you looking to have the response from your form added as a new row to your sheet? If so, you'll want to make sure and use the Add Row(s) endpoint as your Service URL. It looks like https://api.smartsheet.com/2.0/sheets/{sheetId}/rows

 

Sending a POST there you can send a JSON payload to create the new row in your sheet. There is an example of this kind of request body for adding a new row in the Smartsheet API docs here:

http://smartsheet-platform.github.io/api-docs/#add-row(s)

 

If you have any further issues on this feel free to reach out to us at api@smartsheet.com.

Michael Buckingham

Thank you very much for the pointers Dave. I did find the example for adding the Row. I noticed the examples need columnId information, how do i go about getting the columnids?

Michael Buckingham

I was able to run a get for all the columns information, which returned the following

{"pageNumber":1,"pageSize":100,"totalPages":1,"totalCount":5,"data":[{"id":8314053929002884,"index":0,"title":"Task 

 

Name","type":"TEXT_NUMBER","primary":true,"width":230},

{"id":995704534525828,"index":1,"title":"Due Date","type":"DATE","width":80},

{"id":5499304161896324,"index":2,"title":"Done","type":"CHECKBOX","width":55},

 

{"id":3247504348211076,"index":3,"title":"Assigned To","type":"CONTACT_LIST","width":125},

 

{"id":7751103975581572,"index":4,"title":"Comments","type":"TEXT_NUMBER","width":700}]}

 

I then attempted to use these columnid to test the POST function with the following

// Specify cell values for first row.

Cell[] cellsA = new Cell[] { new Cell.AddCellBuilder(995704534525828, true).Build(), new Cell.AddCellBuilder(7751103975581572, "New status").SetStrict(false).Build() };

 

// Specify contents of first row.

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

 

// Specify cell values of second row.

Cell[] cellsB = new Cell[] { new Cell.AddCellBuilder(995704534525828, true).Build(), new Cell.AddCellBuilder(7751103975581572, "New status").SetStrict(false).Build() };

 

// Specify contents of second 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[] { rowA, rowB });

 

 

Server responds with Bad Request when testing. Any suggestions?

Thanks

dAVE Inden

Hi Michael,

 

Apologies as I'm not totally familiar with AgilePoint. I would suggest trying out your POST request by interacting with the API directly first. There are cURL examples in the documentation you can try in a terminal or you can use Postman to make requests. If you need an exmaple of a request body you can use this:

[

  {

   "toBottom": true, 

   "cells": [

        {"columnId": {columnId}, "value": "Boo"},

        {"columnId": {columnId}, "value": "Boooooo"}

    ]

  },

  {

    "toBottom": true,

   "cells": [

      {"columnId": {columnId}, "value": "yay"},

      {"columnId": {columnId}, "value": "hurray"}

    ]

   }

]

 

This will create two rows in your sheet. If you can get the request working by connecting to the API directly I would suggest reaching out to AgilePoint support for how to get body of your POST request to work.

Michael Buckingham

Dave,

 

Thank you very much for the reply with example, this really helped. Together with POSTMAN i was able to succesfully test a POST to Smartsheet and then take this code and bring it to AgilePoint to sucessfully have the workflow accomplish the samething.

 

Thanks again, Mike

dAVE Inden

Glad to hear you got it working Mike! 

Michael Buckingham

Dave would be able to provide an example using a variable? Reason i ask is i'm attempting to now map variables to the values. I know this is somewhat dependent on Agilepoint, however i'm wondering it's possible to have variables substute into this example? 

 

A variable in AgilePoint looks like this '${/pd:AP/pd:formFields/pd:RequesterName}'

Which is a field that would accept someone's name on a form. I've tried replacing the value's with this and also mapping via schema without luck.

 

Thanks, Mike

 

dAVE Inden

Mike, I imagine you should be able to place the AgilePoint variables in place where you would put the value for the cell. The quotes should likely be removed from around the variable.

An example could look like this:

{"columnId": {columnId}, "value": ${/pd:AP/pd:formFields/pd:RequesterName}}

 

If you continue to have trouble using AgilePoint's variables in the POST body I would suggest reaching out to their support if you can.