MS Project import (XML) how to deal with Contacts/Resources

ITSteveg

I'm stuck and I'm hoping that someone can help me out.

I'm importing an MS Project XML file into SS and everything works out except for the resource to contact.

The "assigned To" field is filled with comma delimited names (name only no email) and I need to find a way to convert that into contacts. I can go line by line manually but this specific project has thousands of tasks and some tasks have 50+ contacts/resources. I should note that some of those resources aren't individuals but roles, such as "Training Captain".

I'm 100% ok with those contacts to have a non-real email address, I don't use notifications or anything that will care if all the emails are the same or meaningless.

Any automation possible here or am I dead in the water?

Ryan Kramer

Hey there! I can definitely help you out with this. So, you're importing an MS Project XML file into Smartsheet, but you're facing an issue with the "assigned to" field, which contains a list of names separated by commas. What you're looking for is a way to convert those names into contacts.

Given that you have thousands of tasks and some tasks have more than 50 contacts/resources, doing this manually would be quite time-consuming. But don't worry, automation is possible!

What you can do is write a custom script or use a scripting tool (like Google Apps Script) to automate the process. The script could go through each task, extract the names from the "assigned to" field, and create contacts for them. Even if the contacts don't have real email addresses, that's okay for your purposes.

You mentioned that some of the resources are roles like "Training Captain." In that case, you can create contacts with those role names, and again, the email addresses can be something generic or non-real.

By automating this process with a script, you'll be able to save a lot of time and effort. Just make sure to test the script thoroughly before running it on your actual data.

I hope this solution works well for you! If you have any further questions, feel free to ask. Best regards!

Ryan Kramer

Have you backed up your solution lately? https://www.smartsheetbackups.com/

ITSteveg

Hey Ryan,

So that sounds like a good solution. I have done zero scripting with Smartsheets. I'll start googling but if you have any quick links for what that could look like that would be appreciated.

I'm guessing this is API scripts not scripts built-in to the sheet?

Thanks for the answer!

Ryan Kramer

Yes, API scripts.

I would just recommend Smartsheet API docs and then some basic docs for how to process XML in your language of choice.

We do this and usually more complex use cases for our business.

Ryan Kramer

Have you backed up your solution lately? https://www.smartsheetbackups.com/