Custom Data Source - Load By Script - trouble loading data into import set?

[Daniel Shock]

Good Morning - I have been struggling with this all week. I thought it would be easy... but alas, it is not.

I have a jot form (electronic form).  We typically process the submissions  into ServiceNow via email.  But, Jotform no longer supports plain text which makes getting through all the HTML a pain.   I have discovered that they have an api which seem to work really well, although the payload seems to be in a form that ServiceNow finds difficult to parse. I have created a custom data source that sends a get request to the api.  It pulls in data and I have been able to get..something into the import sets...but no rows are being created. I'm not sure what I am missing or even where to look.  Here is my script:

(function loadData(import_set_table, data_source, import_log, last_success_import_time) {
    var jotFormAPIKey = 'my_api_key';
    var formId = '233303679353155';
    var endpoint = 'https://api.jotform.com/form/' + formId + '/submissions?apiKey=' + jotFormAPIKey + '&limit=3';

    var restMessage = new sn_ws.RESTMessageV2();
    restMessage.setHttpMethod('get');
    restMessage.setEndpoint(endpoint);

    try {
        var response = restMessage.execute();
        var responseBody = response.getBody();
        var httpStatus = response.getStatusCode();
        gs.info("HTTP Status: " + httpStatus);

        if (httpStatus == 200) {
            var jsonData = JSON.parse(responseBody);
            gs.info("Number of submissions: " + jsonData.content.length);

            jsonData.content.forEach(function(submission) {
                var gr = new GlideRecord('u_pfr_insight_2024_orders');
                gr.initialize();

                // Standard field mappings
                gr.u_address = ''; // Add logic to extract address if available
                gr.u_city = submission.answers['9'].answer.city || '';
                gr.u_email = submission.answers['11'].answer || '';
                gr.u_first_name = submission.answers['6'].answer.first || '';
                gr.u_last_name = submission.answers['6'].answer.last || '';
                gr.u_phone_number = submission.answers['10'].answer.full || '';
                gr.u_state = submission.answers['4'].answer || '';
                gr.u_submission_id = submission.id;
                gr.u_total_number_of_attendees = parseInt(submission.answers['146'].answer) || 0;

                // Checking for meeting location
                var meetingLocationFields = {
                    "5": "arLocationsdates6",
                    "7": "iaDatetimelocation",
                    "8": "ilDatetimelocation",
					"18": "inDatetimelocation",
					"19": "ksDatetimelocation",
					"20": "kyDatetimelocation",
					"21": "miDatetimelocation",
					"8": "mnDatetimelocation",
					"22": "moDatetimelocation",
					"23": "msDatetimelocation",
					"24": "neLocationsdates24",
					"25": "ohLocationsdates25",
					"26": "sdLocationsdates26",
					"27": "tnLocationsdates27",
					"46": "wiDatetimelocation",
					
                    // Add the rest of the field numbers and their corresponding field identifiers
                };

                var meetingLocationFound = false;
                for (var fieldNumber in meetingLocationFields) {
                    if (submission.answers[fieldNumber] && submission.answers[fieldNumber].answer) {
                        gr.u_collapsed_column = submission.answers[fieldNumber].answer;
                        gs.info("Meeting Location Found: " + gr.u_collapsed_column);
                        meetingLocationFound = true;
                        break;
                    }
                }

                if (!meetingLocationFound) {
                    gs.info("No meeting location found for this submission");
                }

                try {
                    var sys_id = gr.insert();
                    gs.info("Record inserted with sys_id: " + sys_id);
                } catch (insertError) {
                    gs.error("Error inserting record: " + insertError.message);
                }
            });
        } else {
            import_log.error('Error in REST call: ' + httpStatus);
        }
    } catch (ex) {
        import_log.error('Error in loadData script: ' + ex.message);
        gs.error('Error in loadData script: ' + ex.message);
    }
})(import_set_table, data_source, import_log, last_success_import_time);

I have the limit set to 3 so that I am not creating 1000's of records every time I run to test. 

So, when I click to load all records - this is the result: 

When I click import sets, I see this is created:

but when you look in there....there are no rows... I would have expected 3.  There is also nothing under Import Set Runs or Import Log.

But, when you click on 'Loaded Data' in the first screenshot, it shows you that three more rows have been added to the ISET

I'm not sure why it's 1 and not 3.  I don't know why it's ignored:

But when you click on one of the 3 row numbers added to the ISET, it does show that it somewhere got the submission data... There is a comment that said it was ignored by an onbefore script... I don't know where that is coming from:

Can anyone tell me what I am missing? fill in the gaps? I would really appreciated it!! Thank you!!

[Chris Swetnam]

I had the same issue and found some easy IntegrationHub solutions with data stream actions, but either licensing or ignorance prevented this implementation.

Maybe someone else can use the following solution for a response of JSON arrays:

1. Create the import set table and all the fields you want to populate from your GET.

2. Create a datasource with  type = Custom (Load by Script)

function loadData(import_set_table, data_source, import_log, last_success_import_time, partition_info) {

try { 
 var r = new sn_ws.RESTMessageV2('yourREST', 'yourMethodHere');
 r.setMIDServer('yourMID');

 var response = r.execute();
 var responseBody = response.getBody();
 var httpStatus = response.getStatusCode();
}
catch(ex) {
 var message = ex.message;
}

var parser = new JSONParser();
var parsedData = parser.parse(responseBody);
var length = parsedData.length;

for(var i=0;i<length;i++){
	import_set_table.insert({
	'importSetColumnName1': parsedData[i].yourParsedElementName1,
	'importSetColumnName2': parsedData[i].yourParsedElementName2,
	'importSetColumnName3': parsedData[i].yourParsedElementName3	
 });
}

})(import_set_table, data_source, import_log, last_success_import_time, partition_info);

This should populate your import set table with your records. Then create your transform map and hopefully you're good to go. Perhaps not the most elegant solution but worked for me.

[Sharique Azim]

This is the solution not the solution marked and written by the author themselves for their specific case.

The idea is an object of data should be inserted through the  api in this format.

	import_set_table.insert(object);