RM to Smartsheet using Bridge: Smartsheet Add Row (bulk)

akhalid

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.

1. I want to perform the following operation:
   1. Get the Time Entries from all the phases (in the form of arrays)
   2. Join the arrays together
   3. Sort the arrays by date
   4. 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

Genevieve P.

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

akhalid

Hi @Genevieve P. ,

Thank you.

Regards,

AK

Brian_Richardson

@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.

akhalid

@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

Brian_Richardson

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.

Brian_Richardson

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!

prime_nathaniel

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.

Brian_Richardson

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!