Bulk users licensing via excel sheet upload at catalog item [validate excel for correct UPN before]

[Sachin Gavhane]

hi Team,

1. Current State (As-Is)

• A Service Catalog item uses a List Collector to manually select multiple users.

• The selected users are processed in a Flow Desinger /Integration where:

  • User identifiers (UPN/email) are passed as comma-separated values.

  • An external API is invoked to allocate licenses for each user.

• This solution works correctly for manually selected users.

---

2. Business Requirement (To-Be)

• The business wants to replace manual user selection with Excel-based bulk upload.

• Users should upload an Excel file via the Service Catalog form.

• The system must:

  1. Read and validate all user records from the Excel file

  2. Reject the entire request if even one user is invalid

  3. Proceed only if 100% of the records are valid

  4. Execute the same downstream logic currently used by the List Collector (API-based license allocation per user).
     
     

     3. Functional Requirements

     3.1 Excel Upload

     • Catalog item must allow upload of an Excel (.xlsx) file.

     • Excel file will contain user data (minimum required fields):

       • User Identifier (e.g., UPN / Email ID)

       • (Optional) Additional attributes if required in future.

     3.2 Validation Rules (All-or-Nothing)

     The catalog request must not be submitted if any of the following validations fail:

     • Excel file is missing or unreadable

     • Required column(s) are missing

     • Any user record:

       • Has an invalid UPN/email format

       • Does not exist in ServiceNow sys_user table (or authoritative source)

       • Is inactive or not eligible for license assignment

     • Duplicate users exist in the Excel file

     Important: Partial processing is not allowed.
     Even a single invalid record must block submission and show an error to the requester.

     3.3 Successful Processing

     • Once validation passes:

       • Extract all valid users

       • Convert them into the same logical structure used by the List Collector

       • Invoke the existing API logic to allocate licenses for each user

     ---

     4. Non-Functional Requirements

     • Validation must occur before request submission or immediately on submit

     • Error messages must be clear and user-friendly

       • Example: “Invalid user UPN found: abc@xyz.com”
         
         
         Please help me with the optimal solution with some screenshots if possible
         Your help for this is highly appreciated
         
         cc: @Ankur Bawiskar 

[Ankur Bawiskar]

@Sachin Gavhane 

this looks like a complete requirement/story

what part you started and where are you stuck?

I shared solution on how to validate file when file is attached to attachment variable, refer and enhance

Validate Excel Data and populate in MRVS 

Validations on MRVS - validate Employee ID 

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Sachin Gavhane]

Hi @Ankur Bawiskar 

I have implemented the functionality using a list collector, and the API integration is complete. The remaining task is to modify the current implementation to read user values directly from the uploaded Excel sheet instead of constructing a comma-separated list from the list collector. This change will ensure that the system processes the users as specified in the Excel upload."
I just need user UPN [only 1 field]. Only one thing is: I need to validate whether that UPN exists in sys_user table and it is correct upn [I mean excel should be first validated for the correct data]

[Ankur Bawiskar]

@Sachin Gavhane 

I already shared the approach

check and enhance for your requirement

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏