RM to Smartsheet using Bridge: Smartsheet Add Row (bulk)
Hi,
I am reading Time Entries from Smartsheet Resource Management (RM) and writing them to a Smartsheet. In RM, the project is divided up into phases, which results in Time Entries being associated with each of the phases. The number of phases can be different for each project.
Since there are hundreds of Time Entries to be synced, using Smartsheet's Add Row is not an option due to the speed. I am resorting to HTTP Call to add the rows in bulk.
- I want to perform the following operation:
- Get the Time Entries from all the phases (in the form of arrays)
- Join the arrays together
- Sort the arrays by date
- Write the arrays into Smartsheet using HTTP Call (bulk operation)
The pseudo code of the operation is:
funtion prepare_time_entries
1. Run for each project phase
a. Get time entries for phase
b. Put entries in array (keep on adding to the same array for all phases)
2. Sort the array by date
3. Write the sorted data into Smartsheet
Now, prepare_time_entries could be a workflow and so could Run for each project phase, but I am not sure if I can use "Utilities > Array Management > Add Object to Array" in this scenario to build the array from nothing.
Any ideas how to go about this? Or do I have to use Javascript?
2. The other question is about getting the phases related to a project in RM. Currently I am using "Resource Management > Get Assignable". Is that the right way to get the?
3. Lastly, I want to read the Assignments from Resource Management. I could not find any integration that supported it, so I have used Javascript for it. Is that the right way to read Assignments from RM?
Regards,
Aman
Answers
-
Hey @akhalid
You will need to use Javascript to construct the request body the way Smartsheet's API expects it. @Brian_Richardson has a fantastic post that has a lot of information regarding using javascript and Bridge to import rows. Check it out, here: Use Javascript in Bridge to efficiently import data from an API
For getting Phases from Resource Management, there are two options that I can think of:
1) Resource Management > List Projects (check Include Phases)
Note that you would get multiple entries at the same level (X objects for the same project based on the number of phases)
or 2) Use the HTTP module and this endpoint: https://10kft.github.io/10kft-api/#list-phases-for-a-project
For your third question, I would probably use the HTTP module again, with this endoint: https://10kft.github.io/10kft-api/#assignments
I hope that helps!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
@Genevieve P. thanks for the callout :-)
@akhalid if the post I made doesn't make sense, please feel free to tag me on a reply and I can connect with you and help explain it. It's fairly lengthy to setup, but works very, very consistently and very quickly (ie a minute or so to process 1000 rows) once it's working. Be sure to check at the bottom for an update on a method to clear your sheet on a schedule, as the Javascript read and post does an Add Row as you mentioned... so you'll want to clean out the sheet beforehand.
-
@Brian_Richardson thank you for your reply.
I will certainly look into how you have achieved it (copied into my knowledgebase for now).
As for me, as you can see that I posted my question on 09/01 and I could not wait for a response that long. So being thrown in the deep end, I somehow swam across back to the shore. I did come across your post during my struggle (the clearing sheet one).
Regards,
AK
-
Usually the forums are pretty good at getting questions answered but the more advanced the question, the smaller the audience who can answer. Genevieve does a good job of catching up to some of these advanced questions, and there’s a few folks on the forums that will answer, but yes sometimes it’s a waiting game.
-
BTW if you are going to ENGAGE 2023, that’s where I got some of these advanced answers from Smartsheet folks and passed them on. So I recommend hitting the booths with complicated questions!
-
Have been doing TONS of these over the past couple years now as more enterprise customers are coming into SMAR with external integration requirements.
Recommend using the above solution to explicitly map columns 1:1.
When we get massive data sets though (like 100+ columns) We also have an auto mapping version of this with an override key subroutine so as long as all KEYS (column names) are one to one it will map without needing to be told where explicitly. Then if an key matches the override list you can 1:1 map just those fields. This also takes into account you cannot map into system fields, dates require ISO format handling, and contacts must be set as objects not as primitives.Nathaniel Kam | Principal Consultant
Prime Consulting Group -
That's great Nathaniel, I was thinking of doing something similar… having a read of the fields from RM API and then reading columns from Smartsheet, matching the two through Javascript, and using some kind of for-next cycle to build the results dynamically for Add Row.
Also I'd love to adjust this to work for other data where you want to Update a row instead of always clearing and adding.
But… we only have the 1 case where we're doing this with RM data at Iron Mountain, so I don't have a lot of incentive to make this robust like that. Sounds like you have done so though!
Did you see the announcement that SS is bringing RM reports into SS natively as synced reports? That may remove the need for all this Bridge work. We'll see. Right now RM reports in tool don't have enough data, and the detailed export has too MUCH data, so I rely on the API reporting to give me the Goldilocks version that's just right. Hopefully the native integrated reports will have similar options but we'll find out!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives