Inbound email action to store user list

[GD00609570]

Hi Team,

Requirement is - customer send an email with an .csv attachment with user list (they will send only user ID in the attachment), once we received the email, we need to read that attachment and store the user list in the company's (already we have a company name "xyz") user's.

how to write inbound email action for the above scenario,  Write the code but not working

var excp = ""; // Store exception

gs.log("Enter email");

// TCAP

if (email.subject.indexOf("PingCo NOC User List for Visibility Group Automation") !== -1) {

  var emailBody = email.body_text;

gs.log("read email subject");

// Exception handling for Wrong Body Text

  var firstLine = /PingCo Users for Visibility Groups attached/;

  var firstLineisMatching = emailBody.match(firstLine);

   // Read the data from CSV attachment

    var userList = [];

    var emailGR = new GlideRecord("sys_email");

    emailGR.addQuery("subject", "LIKE", "%PingCo NOC User List for Visibility Group Automation%");

    emailGR.orderByDesc("sys_created_on");

    emailGR.setLimit(1);

    emailGR.query();

gs.addMessage("Read email");

    if (emailGR.next()) {

      var emailSysId = emailGR.getUniqueValue();

      var attachmentGR = new GlideRecord("sys_attachment");

      attachmentGR.addQuery("table_name", "sys_email");

      attachmentGR.addQuery("table_sys_id", emailSysId);

      attachmentGR.orderByDesc("sys_created_on");

     attachmentGR.addQuery("content_type", "!=", "image/jpg");

      attachmentGR.addQuery("content_type", "!=", "image/png");

      attachmentGR.addQuery("content_type", "!=", "image/jpeg");

      attachmentGR.query();

        if (attachmentCount === 0) {

          excp += "There are no Attachments for this email record \n";

        } else {

        if (attachmentGR.next()) {

          var contentType = attachmentGR.getValue("content_type");

          var fileName = attachmentGR.getValue("file_name");

           if (contentType == "text/csv" || fileName.endsWith(".csv")) {

                 var gsu =

                typeof GlideStringUtil != "undefined"

                  ? GlideStringUtil

                  : Packages.com.glide.util.StringUtil;

              var gsa =

                typeof GlideSysAttachment != "undefined"

                  ? new GlideSysAttachment()

                  : new Packages.com.glide.ui.SysAttachment();

              var attachmentData = gsa.getBytes(attachmentGR);

              var attachment = String(

                Packages.java.lang.String(attachmentData)

              );

                userList.push();

                }

                if (excp.length > 0) {

                  gs.info("Exception: " + excp);

                  desStr += "Incorrect Data Format for the CSV file. \n";

                                }                }

        }

 // Store users in sys_user table

Your help required to complete this task.

Thanks in Advance.

[Vishal Jaswal]

Hello @GD00609570 

Try below:

var excp = ""; // Store exceptions
gs.log("Enter email processing");

// Check email subject
if (email.subject.indexOf("PingCo NOC User List for Visibility Group Automation") !== -1) {
gs.log("Matching email subject found.");

var userList = [];

// Query the sys_attachment table directly using the email's sys_id
var attachmentGR = new GlideRecord("sys_attachment");
attachmentGR.addQuery("table_name", "sys_email");
attachmentGR.addQuery("table_sys_id", email.sys_id);
attachmentGR.addQuery("content_type", "text/csv"); // Ensure it's a CSV
attachmentGR.orderByDesc("sys_created_on");
attachmentGR.query();

if (!attachmentGR.next()) {
gs.log("No CSV attachment found in email.");
return;
}

// Read the CSV attachment
var gsa = new GlideSysAttachment();
var attachmentData = gsa.getContent(attachmentGR);
var csvContent = String(attachmentData);

// Parse CSV content
var lines = csvContent.split("\n");
for (var i = 0; i < lines.length; i++) {
var userID = lines[i].trim();
if (userID) {
userList.push(userID);
}
}

gs.log("Extracted user IDs: " + JSON.stringify(userList));

if (userList.length === 0) {
gs.log("No valid user IDs found in CSV.");
return;
}

// Store users in sys_user table under company 'xyz'
var companySysId = getCompanySysId("xyz");

for (var j = 0; j < userList.length; j++) {
var userId = userList[j];

// Check if user already exists
var userGR = new GlideRecord("sys_user");
userGR.addQuery("user_name", userId);
userGR.query();

if (userGR.next()) {
gs.log("User already exists: " + userId);
} else {
// Create new user
var newUser = new GlideRecord("sys_user");
newUser.initialize();
newUser.user_name = userId;
newUser.company = companySysId;
newUser.insert();
gs.log("Created new user: " + userId);
}
}
}

/**
* function to get the sys_id of a company by name
*/
function getCompanySysId(companyName) {
var companyGR = new GlideRecord("core_company");
companyGR.addQuery("name", companyName);
companyGR.query();
if (companyGR.next()) {
return companyGR.getUniqueValue();
}
return "";
}

[Ankur Bawiskar]

@GD00609570 

try this

var excp = ""; // Store exception
gs.log("Enter email");

// Check if the email subject matches
if (email.subject.indexOf("PingCo NOC User List for Visibility Group Automation") !== -1) {
    var emailBody = email.body_text;
    gs.log("Read email subject");

    // Exception handling for wrong body text
    var firstLine = /PingCo Users for Visibility Groups attached/;
    var firstLineisMatching = emailBody.match(firstLine);

    if (firstLineisMatching) {
        // Read the data from CSV attachment
        var userList = [];
        var emailGR = new GlideRecord("sys_email");
        emailGR.addQuery("subject", "LIKE", "%PingCo NOC User List for Visibility Group Automation%");
        emailGR.orderByDesc("sys_created_on");
        emailGR.setLimit(1);
        emailGR.query();

        if (emailGR.next()) {
            var emailSysId = emailGR.getUniqueValue();
            var attachmentGR = new GlideRecord("sys_attachment");
            attachmentGR.addQuery("table_name", "sys_email");
            attachmentGR.addQuery("table_sys_id", emailSysId);
            attachmentGR.orderByDesc("sys_created_on");
            attachmentGR.addQuery("content_type", "text/csv");
            attachmentGR.query();

            if (attachmentGR.next()) {
                var gsa = new GlideSysAttachment();
                var attachmentData = gsa.getBytes(attachmentGR);
                var attachment = String(Packages.java.lang.String(attachmentData));
                var lines = attachment.split('\n');

                for (var i = 0; i < lines.length; i++) {
                    var userId = lines[i].trim();
                    if (userId) {
                        userList.push(userId);
                    }
                }

                // Store users in the company's user list
                for (var j = 0; j < userList.length; j++) {
                    var userGR = new GlideRecord("sys_user");
                    userGR.addQuery("user_name", userList[j]);
                    userGR.query();
                    if (userGR.next()) {
                        userGR.company.setDisplayValue("xyz");
                        userGR.update();
                    }
                }
            } else {
                excp += "There are no CSV attachments for this email record \n";
            }
        }
    } else {
        excp += "Incorrect email body format \n";
    }

    if (excp.length > 0) {
        gs.info("Exception: " + excp);
    }
}

Also check this

Loading data from an email attachment

If my response helped please mark it correct and close the thread so that it benefits future readers.