Best answer

Jira to Smartsheet Integration

  • 19 February 2020
  • 9 replies
  • 529 views

Userlevel 2

I'd like to create a zap that updates the status of any jira tickets tracked in smartsheet. Ex. I have a project plan with 5 agile dependencies. I'd like to have a zap that auto-updates the status of that issue in jira. Ex. jira ticket XXXX-123 is updated from backlog to in progress in jira. I'd like the zap to update the smartsheet line for issue key XXX-123 to reflect the new status of in progress. Is this possible? If so, where can I find best practices on how to implement this automation?


If there are 6 rows that represent 6 issues in smartsheet, can one zap crawl and update them all in one, or do I need to create a separate zap for each issue/row? Thanks!


icon

Best answer by Danvers 28 February 2020, 12:04

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

9 replies

Userlevel 7
Badge +9

Hey @EJones great questions!

For this workflow, you will need 1 Zap to trigger on new Jira updates, search your Smartsheet for the row that matches the ticket/issue, and then update that row.

I'm not sure which Jira app you're using but you can view all of the available Triggers, Seasrches, and Actions for those apps at the bottom of the page, here:

As for Smartsheet, if you're curious, you can view a list of what's possible there as well: https://zapier.com/apps/smartsheet/integrations

I might suggest giving this a try and then coming back to the Community and let us know if you have any questions or run into any issues during setup.

We also have both general and app-specific help documentation on getting started in our Help Center: https://zapier.com/help.

Hope that helps to get you pointed in the right direction!


Userlevel 7
Badge +12

Hi @EJones!

It is possible to update a Smartsheet row using a Zap, but the tricky part is that you need the row ID to do it. If you don't have many issues, then this should be possible if you use a Formatter step to add a lookup table.

So (assuming that you're using Jira Software Cloud the Zap would look like this:

  1. Jira - Update issue
  2. Formatter Lookup table - takes issue number and matches it to the row that it's in in Smartsheets
  3. Smartsheets - update row. Select the row using the row ID from the Formatter

Do you think that would work here?



@EJones  I’m trying to do the same thing! 

@jesse  The New Row Update action (update an existing row with new values. Requires a Row ID) for smartsheet is based on a row ID rather than matching something dynamically in the row like, say, the ticket ID #. I don’t see how anyone would be able to use a zap like that...I’d have to make a new zap for every single ticket! (If Ticket #1344 is updated, then update row # 1344 in smartsheet - or whatever row it’s actually found on given it is unlikely they will always match). 

Looks like the “New Row Update” was meant to be paired with the trigger “Update Row” in smartsheet. To be honest Smartsheet is pretty good at internal automations (huge improvements in the last year) so I highly doubt there are many uses for a trigger in Smartsheet, then going to Zapier, then going back into Smartsheet. 

What about all of the use cases between Smartsheet and another product Zapier supports? Jira and Smartsheet, Zendesk and Smartsheet, outlook and Smartsheet, etc. 
 

@Danvers  Cool idea! Almost worked...

Looks like you do have to program in the lookup table valuables in Formatter statically. Again, can’t be dynamic. 

So my ticket ID scheme goes like this...NSD-1, NSD-2, etc. and we get thousands of tickets per month. 

With this method I’d have to write in a lookup table that gave the output for every single potential ticket number and output a single number for the row!
 

NSD-8, output “8”
NSD-9, output “9“

NSD-114, output “113”

etc. 

I wish it could match based off something other than the numeric Row ID….hmmm.

Sigh. 
@jesse  what are the chances of getting an update made to the backend of the “New Update Row” action in Smartsheet so the row can be matched based off of a different unique column. 

 

 

 

Userlevel 7
Badge +12

Hi @Morgan

I’m sorry that the Formatter solution doesn’t work in this case. 

 

First things first, there is a feature request open for the addition of a Find Row step - that would solve the issue here as you could search in the sheet for the right row, which would then give you the ID to use in the Update Row step. I’ve added you to that feature request, which lets the Smartsheet team know how many folks are interested in seeing it and also means that you’ll get an email if we have an update on that. 

 

If you have lot of tickets, then one way of creating a more dynamic lookup table is to use Google Sheets. Whenever an issue is added to Smartsheet, create a new row in a Google Sheet with the Jira ID and the Row ID. Then when you need to update a row, use the Lookup Spreadsheet Row action that’s in Google Sheets to get the Row ID for Smartsheet. I know it’s kinda clunky to have to use another sheet app just to find the row ID, so I’m sorry about that but it should do what you need!

@Danvers  AWESOME, “Find Row” step is what I need! I’m new to the community, I will see how I can go upvote this. Excellent.
 

On the Google Sheets workaround (and around and around, haha) - 

I can’t get it to work. Care to take a look?
Seems like the New Row Update step cannot handle the custom option for the Row ID. Could be PICNIC, too, of course! (Problem In Chair, Not In Computer). I tested the whole flow with a specific row ID (not custom), and it works - updating my sheet in Smartsheet with the new data. When I grab a custom field from Google Sheets, I get this error:

“The app returned "Not Found". This usually happens when a field value used in this step is missing or we're unable to find some information in your connected app or service. Make sure that the information exists and that the field values used in this step are entered correctly. We made a request to api.smartsheet.com and received (404) Not Found.”

When choosing a custom option from Google Sheets I can choose “2. ID” which has a value of 6. (that is correct, I need row 6 for this data match). Or I can choose “2. Row” which also has a value of 6. I tried both and get the same error. No idea where this #2 is coming from…

 



I suspect there is another piece of data Zapier is looking at for Smartsheet’s row ID and that’s why choosing “Row ID” works and not "Custom”….when I go back to the Row ID function that runs successfully - the manually selected row -- this is what I can see. The row 6 but then there are also these grey numbers below. Perhaps this is a special unique ID for that row? And that is the number I need to be matching to, not 6? Starts with 1701…

How do I know this number?

 


THANK YOU COMMUNITY!!!

Userlevel 7
Badge +12

Hi @Morgan! I’m really sorry for the delay in getting back to you on this!

 

 I think there’s a couple of things at play here. First, in your Google Sheet, make sure that you name the column with the Smartsheet Row ID as Smartsheet ID. That will remove any confusion between the row ID in Google Sheets (which you will also get with a find row step) and the Smartsheet Row ID that you need. 

 

Second, you’re right that you need the long number in grey underneath single digit number. That’s ID of the row, rather than the row number, and that’s what you need in the Zap to tell Smartsheet which row it should update.

 

I hope that helps!

Hi @Danvers Thank you so much for the reply! 

Got it working with the long row ID. 

 

THANK YOU!!!
 

Userlevel 7
Badge +12

Woo! Nice work @Morgan! 🙌 

 

I’m really glad that did the trick :slight_smile:

I have same issue where I do not want to every row/rowid on my smart sheet to receive the update.  I am integrating Hubspot updates to Smartsheet.   Is there a workaround [findRow] available to choose which row gets updated based on DealId or some common id between two systems?