Inbound action to Store user list from .csv attachment

[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

} else {

            excp +=

              "Wrong Email Body Text. \n";

            }

} else {

  excp += "Email Subject is Wrong. \n";

}

Your help required to complete this task.

Thanks in Advance.

[pranita-24]

Hello

Fetching Email Attachments Incorrectly:

You're querying sys_email separately and then using its sys_id to fetch attachments. However, email in an inbound action already refers to the incoming email, so you can directly use email.attachments.

Missing attachmentCount Variable:

You're checking attachmentCount === 0, but attachmentCount is not defined anywhere.
You're only initializing userList.push(); without passing any data.

Try the below code

var excp = ""; // Store exceptions

// Log email entry
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");

// Validate Email Body Text
var emailBody = email.body_text;
var firstLinePattern = /PingCo Users for Visibility Groups attached/;
if (!emailBody.match(firstLinePattern)) {
gs.log("Invalid email body text");
excp += "Wrong Email Body Text. \n";
} else {
gs.log("Valid email body text. Proceeding with attachment processing...");

// Process CSV Attachment
var attachments = email.attachments;
if (!attachments || attachments.length === 0) {
gs.log("No attachments found");
excp += "There are no Attachments for this email record. \n";
} else {
gs.log("Found " + attachments.length + " attachment(s)");

for (var i = 0; i < attachments.length; i++) {
var attachment = attachments[i];

// Check if the attachment is a CSV file
if (attachment.contentType == "text/csv" || attachment.fileName.endsWith(".csv")) {
gs.log("Processing CSV file: " + attachment.fileName);

// Read CSV content
var gsa = new GlideSysAttachment();
var csvData = gsa.getContent(attachment);
gs.log("CSV Data: " + csvData);

var lines = csvData.split("\n"); // Split into lines
var userList = [];

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

gs.log("Parsed User List: " + JSON.stringify(userList));

// Store users in "xyz" company
var companyName = "xyz";
var companyGR = new GlideRecord("core_company");
companyGR.addQuery("name", companyName);
companyGR.query();

if (companyGR.next()) {
var companySysId = companyGR.getUniqueValue();
gs.log("Company found: " + companyName);

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

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

if (!userGR.next()) {
userGR.initialize();
userGR.user_name = userId;
userGR.company = companySysId;
userGR.insert();
gs.log("User created: " + userId);
} else {
gs.log("User already exists: " + userId);
}
}
} else {
gs.log("Company not found: " + companyName);
}
} else {
gs.log("Ignoring non-CSV attachment: " + attachment.fileName);
}
}
}
}
} else {
gs.log("Email subject does not match. Skipping email processing.");
}

Please mark my answer correct or helpful if I resolved your query.
Thanks.
Pranita D.