Parsing Zoom Attendee Lists

[Will Hallam]

Here's an example of custom actions which can parse a Zoom attendee report CSV file into a JSON payload for use in flows and subflows.

DISCLAIMER: This example code is provided as-is, with no support or warranty (explicit or implied), in the hopes it makes someone else's life easier. I recognize I could combine the two actions into one, code them more efficiently, etc., but they do the job I need them to do so other priorities take precedence for the moment. Cheers!

These actions are part of an app I created to track the webinars which I host, send out follow-up emails and invites for new sessions, etc.

The first custom action, "Extract CSV", takes an attachment sysId as an input; it retrieves the attachment, which is expected to be an Excel CSV format Zoom attendee report, and parses it into a JSON payload which it returns in string form as an output named "data".

Here's the script step code for "Extract CSV":

(function execute(inputs, outputs) {

    var attachment = new GlideSysAttachment();
    var attachGr = new GlideRecord('sys_attachment');
    attachGr.get(inputs.attachSysId);

    var raw=attachment.getContent(attachGr);
    // gs.info("DEBUG: raw contents of attachment "+inputs.attachGr.sys_id+" is "+raw);

    // CSV parsing code
    var lines = raw.split(/(?:\r\n|\n)+/).filter(function(el) {return el.length != 0});
    // gs.info("DEBUG: lines are "+lines);

    // var valuesRegExp = /(?:\"([^\"]*(?:\"\"[^\"]*)*)\")|([^\",]*)/g;
    var valuesRegExp = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g;

    var elements = [];
    var headers=[];
    var foundHeaders=0;

    for (var i = 0; i < lines.length; i++) {
        var element = {};
        var j = 0;
        // gs.info("DEBUG: parsing line "+lines[i]);

        // detect the header row
        if (lines[i] == "Attendee Details,") {
          headers = lines[i+1].split(",");
          i+=2;
          foundHeaders=1;
          gs.info("DEBUG: found headers: "+headers);
        }
        // bail on the for loop if the "Other Attended" header is found
        if (lines[i]=="Other Attended,") {
          break;
        }
        if (foundHeaders) {
            while (matches = valuesRegExp.exec(lines[i])) {

                var value = matches[1] || matches[2] || 'empty';
                // gs.info("DEBUG: value is "+value);
                value = value.replace(/\"\"/g, "\"");

                element[headers[j]] = value;
                j++;
            }
            // gs.info("DEBUG: element is "+JSON.stringify(element));
            elements.push(element);
        }
    }
    // gs.info("DEBUG: elements are "+JSON.stringify(elements));

  outputs.data=JSON.stringify(elements);

})(inputs, outputs);

The second custom action, "Parse Attendee JSON", takes the JSON payload as a string from the output of "Extract CSV", feeds it through a script step for some massaging, then into a JSON parser step to generate the final data object to be used by downstream flow/subflow actions.

The script step takes a single string input named "json", massages it and outputs a single string named "attendees". Here is the code:

(function execute(inputs, outputs) {
    var inObject = JSON.parse(inputs.json);
      var outList=[];

      for (var i=0;i<inObject.length;i++) {
      var outObject={};
      outObject["email"]=inObject[i]["Email"];
      outObject["first_name"]=inObject[i]["First Name"];
      outObject["last_name"]=inObject[i]["Last Name"];
      outObject["company"]=inObject[i]["Organization"];
      outList.push(outObject);
      gs.info("DEBUG: appending "+JSON.stringify(outObject));
    }

  outputs['attendees']=JSON.stringify(outList);
  // gs.info("DEBUG: attendee list looks like "+JSON.stringify(outList));
})(inputs, outputs);

The JSON parser step uses the "attendees" output from the script step as the Source data and the following example payload to construct the data object:

[{"email":"one","first_name":"two","last_name":"three","company":"four"}]

These actions made routine processing/importing of Zoom attendee lists much easier for me. Hopefully they can provide useful for others seeking to automate similar tasks.