How do I import a service catalog item's attached spreadsheet into its multi row variable set?

[mike275]

I have a service catalog item with an MVRS with a few fields.  I have a link on the page to a spreadsheet template I want to allow users to fill out and upload to populate the MVRS.

Can I do this?

[Jace Benson]

Not easily.  You would have to allow the user to upload the attachemnt, and read the attachemnt and if it matched your stuff stuff, then set the MRVS to a array of JSON based on what they put in.  If you have any references, you'd have to do a lookup per variable.

IMO not worth it.

[mike275 · Accepted solution]

It's possible - I did it like so:

1. Create a workflow script task that
2. If an attachment exists for the SC item, I create an Excel attachment datasource
3. Copy the attachment from the SC item to the Excel data source
4. Import from the data source created in #3 into an Import Set
5. Query the Import Set table and read the imported rows, converting them into Javascript objects and pushing them onto an array
6. Set the MVRS value to the JSON.stringify(...) value of the array created in step 5

I do data validation and all that, and add comments in the task if rows in the spreadsheet contain invalid data.

[Jace Benson]

If you could make a simple POC showing this with demo data, I'd love to see it.

[mike275]

Well - here you go.

I guess I could post the HOWTO in a blog or something, if that's what you meant, but this is the idea...