Auto populate catalog item variables with excel import (portal)

[Marcel Hartmann]

Hi all!

I try to auto populate catalog item variables (in portal) with an excel import. The reason is that I end up with almost 50 variables in the catalog item. To simplify things, I created an item with 3 variables in my PDI. I am trying to add an 'import from excel' button in the portal to load all the variables for the catalog item from the excel. As an example, I have chosen the following variables: Name (Reference), Group (Select Box) and Additional Information (Multi Line Text).

Excel:

Does anyone have an idea/approach for implementing my example?

[Riya Verma]

Hi @Marcel Hartmann ,

Hope you are doing great. 

1. Create an Import Set Table. Define the appropriate fields in the Import Set Table to match the columns in your Excel file.

2. Configure Data Source and Transform Map:

   • Configure a data source for the Import Set Table to specify the Excel file as the source.
   • Define a Transform Map that maps the columns from the Excel file to the corresponding variables in the catalog item.

3. Create a Scripted Button in the Portal and write java script fucntion that handles the import functionality, including reading the Excel file data and inserting it into the Import Set Table.

function importFromExcel() {
   var importSet = new GlideImportSet();
   importSet.setTargetTable('your_import_set_table_name');
   importSet.setImportTable('your_catalog_item_table_name');
   importSet.setMap('your_transform_map_name');
   importSet.setRunWorkflow(false);
   importSet.loadImportSetData('sys_id_of_the_import_set');

   // Optionally, you can validate and transform the data before inserting it
   // into the catalog item table using the transform map.

   importSet.transformAll();

   // Optionally, you can perform additional operations after the import, such
   // as sending notifications or logging import results.

   importSet.execute();
}
​

• Add the Scripted Button to the Portal:
  • Modify the catalog item form in the portal to include the newly created scripted button.
  • This can be done by customizing the catalog item's portal view and adding the button element, associating it with the importFromExcel function.

[Marcel Hartmann]

The scenario is that an inbound email comes with an Excel file as an attachment. The agent should then create a request and fill in the information (variables fields) in a catalog item using the 'Import via Excel' button.

[Riya Verma · Accepted solution]

Hi @Marcel Hartmann ,

Hope you are doing great.

if you want to update the excel files received in email as attachement with catalog item variable , You can achieve this with inbound email action using email parser API. 

1. Create a catalog item in ServiceNow that includes the desired variable fields to capture the information from the Excel file.

2. Ensure that the catalog item has the 'Import via Excel' button enabled, allowing for Excel file import.

3. Configure the Email Inbound Action:

   • Set up an email inbound action to process incoming emails with Excel file attachments.
   • Configure the email inbound action to create a request for the desired catalog item when an email with an Excel attachment is received.
   • Extract the necessary information from the Excel file to populate the variable fields of the catalog item.

4. Extract Data from Excel File:

   • Utilize ServiceNow's Excel Parser API or similar libraries to extract data from the Excel file in the email attachment.
   • Parse the Excel file and retrieve the required information from the appropriate columns and rows.
   • Map the extracted data to the corresponding variable fields of the catalog item.

5. Create Request and Populate Variables.

// Parse the Excel file and extract data
var excelData = ExcelParser.parseExcel(fileAttachment);
var columnAData = excelData.getColumn('A');
var columnBData = excelData.getColumn('B');

// Create a request for the catalog item
var request = new GlideRecord('sc_request');
request.initialize();
request.cat_item = 'YOUR_CATALOG_ITEM_ID';
request.insert();

// Populate the variable fields with extracted data
request.variables.variable1 = columnAData[0];
request.variables.variable2 = columnBData[0];
// Set values for additional variable fields as needed

request.update();

[alicja01]

 I created an item with 3 variables in my PDI. I am trying to add an 'import from excel' button in the portal to load all the variables for the catalog item from the excel.