[ServiceNow Flow] Read email attachment file and update the ServiceNow table

[Kishore32]

Hello,

I've a requirement to read the inbound email with attachment in flow and I'll get list of users in attachment and read the attachment line by line and activate the user by checking active field in user table for the user record.

This should happen completely through flow.

Is there any action that handles this situation? Do let me know if you need more info.

Thanks,

Kishore.

[Snehangshu Sark]

Hi @kishore,

I've been using the below Inbound action and Script Include to load data programmatically from an email attachment to table data, you can try this.

At a high level, the process is - 

- Using inbound action, create a new Data source and attach the email attachment to it

- Using Script Include, we parse the attachment line by line and load it to an import set table.

- Using a transform map, update the table data.

In your case, you need to set the target table as sys_user in the transform map and do the field mapping correctly to update the user fields.

Few things you need to change here - 

- make sure the import set table name is updated in the inbound action.

- In the script, dataSourceID is the sys_id of that data source (that you want to load)

- create a transform map beforehand.

- In the script, transformMapID is the sys_id of the transform map (that you want to use to map and load data)

Inbound action:

(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {
      var importSetTableName = "IMPORT SET TABLE NAME";
      var transformMapIDs = "SYS-ID(s) OF YOUR TRANSFORM MAP TO UTILIZE";   //Use a comma to specify multiple transform maps
      var applicatonScope = "Global";

      // Create the datasource record
      current.name = "File import from: " + email.from;   //Feel free to rename this as appropriate
      current.import_set_table_name = importSetTableName;
      current.file_retrieval_method = "Attachment";
      current.type = "File";
      current.format = "Excel"; // For Excel Files
      //current.format = "CSV"; // For CSV Files
      current.header_row = 1;
      current.sheet_number = 1;
      current.sys_package.setDisplayValue(applicatonScope);
      current.sys_scope.setDisplayValue(applicatonScope);
      var dataSourceID = current.insert();
     
      /*
       * Schedule Load of Attachment
       *
       * This inbound email action will generate an import data source, however the attachment isn't copied to the data source until
       * after the insert of the record.   Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
       */
      new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);
})(current, event, email, logger, classifier);

Script Include:

var EmailFileImportUtils = Class.create();
EmailFileImportUtils.prototype = {

    scheduleImport: function(dataSourceID, transformMapID) {
        var schRec = new GlideRecord("sys_trigger");
        schRec.name = "Load Data Source: " + dataSourceID;
        schRec.trigger_type = 0; // Run Once  
        schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapID + "')";

        var nextAction = new GlideDateTime();
        nextAction.addSeconds(30); // 30 seconds should be enough time however this can be changed.  
        schRec.next_action = nextAction;
        schRec.insert();
    },

    loadImportSet: function(dataSourceID, transformMapID) {
        // Get Datasource Record  
        var dataSource = new GlideRecord("sys_data_source");
        dataSource.get(dataSourceID);

        // Process data source file  
        var loader = new GlideImportSetLoader();
        var importSetRec = loader.getImportSetGr(dataSource);
        var ranload = loader.loadImportSetTable(importSetRec, dataSource);
        importSetRec.state = "loaded";
        importSetRec.update();

        // Transform import set  
        var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapID);
        transformWorker.setBackground(true);
        transformWorker.start();
    },
};

Regards,

Snehangshu Sarkar

Please mark an appropriate response as correct if my answer replied to your question.

[Wong69]

You will have create flow with inbound email trigger, then create script action to get attachment table. After that you can read attachment by epayitonline.com