Create Bulk RITM and single Request from Excel

Michael51 · ‎05-18-2023

Hello All,

I have an Requirement to create Bulk RITM' from Excel Sheet data , in Excel sheet we get Variable values

1. Name of the Moniter - variable backend value - u_name_moniter

2. Description - u_description

So now I need to get the data from Excel sheet and then create one Request for each excel and then create RITM as per the columns in excel , if we have 20 columns we need to create 20 RITM's

can anyone help me with this

@jaheerhattiwale

Voona Rohila · ‎05-19-2023

Hi @Michael51

You can load the excel into staging table and transform using transform map.

In your transform map use below logic in onComplete script.

Try this code :

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var impSet = import_set.sys_id ;

    var cartId = GlideGuid.generate(null);
    var cart = new Cart(cartId);
    var gr = new GlideRecord('u_ritmload'); // give your staging table here
    gr.addQuery('sys_import_set='+impSet);
    gr.query();
    while (gr.next()) {

       var item = cart.addItem('1391a4db070630100b36f6fd7c1ed0c2', 1); //give the catalog item sysid
        cart.setVariable(item, 'mutliline', gr.u_col1 ); //add all your variable details here
        cart.setVariable(item, 'mutliline', gr.u_col2 ); //add all your variable details here

    }
    var req_id = cart.placeOrder();
    gs.info("number"+req_id.number);

})(source, map, log, target);

I've tried this and it's working.

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Rohila V
2022-25 ServiceNow Community MVP

View solution in original post

Manmohan K · ‎05-18-2023

Hi @Michael51 ,

You can use the excel sheet as data source and create import set which maps to request table. You can then create request items via transformation script.

You can use on before Transform script similar to below to create RITM

(function transformRow(source, target, map, log, isUpdate) {

var cart = new Cart();

// add in cart, substitute with the catalog item sys_id
var item = cart.addItem('81232158027580125120501728');

//set RITM variables 
 cart.setVariable(item, 'u_description', 'source.description');
 cart.setVariable(item, 'u_name_moniter',source.monitor_name);

ignore=true;
var rc = cart.placeOrder();

})(source, target, map, log, action==="insert");

Voona Rohila · ‎05-18-2023

Hi @Michael51

This will not include all the RITM's under one request, It will create multiple requests too.

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Rohila V
2022-25 ServiceNow Community MVP

Michael51 · ‎05-18-2023

Hi Rohila , for each import it has to create only single request and Multiple RITM based on the excel column count and also it is not one time activity they are chances to do 10 to 15times ,

Voona Rohila · ‎05-18-2023

Hi @Michael51

If this is a one time activity then try below approach

Include all your data in JSON format and use below logic accordingly

https://www.servicenow.com/community/developer-blog/multiple-ritms-in-same-request-using-cartapi/ba-...

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Rohila V
2022-25 ServiceNow Community MVP