Cannot add table to import set

[Hm10]

This BR runs until this log gs.log("File name inserted into sys_data_source for " + latestFilename + " by user " + currentUserId);
, and then it gives me false in if(ranout). I am trying to get file from user attachment in Change Request and then parse it into a table,

(function executeRule(current, previous /* , g */) {
  // Get the current user's sys_id
  var currentUserId = gs.getUserID();
  gs.log("Business Rule Running" + currentUserId);

  var gr = new GlideRecord('sys_attachment');

  var firstName = gs.getUser().getFirstName().toLowerCase();
  gs.log("FirstName" + firstName);
  gr.addQuery('sys_updated_by', firstName);
  gr.orderByDesc('sys_created_on');
  gr.query();

  if (gr.next()) {
    var latestFilename = gr.file_name;

    // Set the retrieved filename to a field or perform other actions here
    current.latest_inserted_filename = latestFilename;

    // Log the message
    gs.log("Latest inserted filename by user " + currentUserId + ": " + latestFilename);

    var dataSourceGR = new GlideRecord('sys_data_source');
    dataSourceGR.initialize();
    dataSourceGR.setValue('name', latestFilename); // Set the file name in the 'name' field
    dataSourceGR.insert();
    gs.log("File name inserted into sys_data_source for " + latestFilename + " by user " + currentUserId);

    var loader = new GlideImportSetLoader();
    var importSetRec = loader.getImportSetGr(dataSourceGR);
    var ranload = loader.loadImportSetTable(importSetRec, dataSourceGR);
    importSetRec.state = "loaded";
    importSetRec.update();

    // Ensure the Import Set has been successfully loaded before creating a Transform Map
    if (ranload) {
      // Create a new Transform Map
      var transformMapGR = new GlideRecord('sys_transform_map');
      transformMapGR.initialize();
      transformMapGR.setValue('name', 'Excel_transform_map'); // Set the name of your Transform Map
      transformMapGR.setValue('source_table', importSetRec.getUniqueValue()); // Set the source table to the Import Set sys_id
      transformMapGR.setValue('target_table', 'u_excel_data'); // Set the target table
      transformMapGR.insert();

      gs.log("Transform Map 'Excel_transform_map' created.");

      // Trigger the transformation (uncomment and modify as needed)
      /*
      var transformMapSysID = transformMapGR.getUniqueValue();
      var importSetRecSysID = importSetRec.getUniqueValue();
      var transformWorker = new GlideImportSetTransformerWorker(importSetRecSysID, transformMapSysID);
      transformWorker.setBackground(true);
      transformWorker.start();
      gs.log("Transformation started for " + latestFilename + " by user " + currentUserId);
      */
    } else {
      gs.log("Import Set could not be loaded. Check your data source.");
      gs.log("ranload: " + ranload);
      gs.log("Error message, if any: " + importSetRec.getLoadErrorMessage());
    }
  } /* else {
    gs.log("Attachment not found for user " + currentUserId);
  } */
})(current, previous);

[palanikumar]

You can create import set and transform map manually. Refer them created one in the code.

[Hm10]

Thanks, but If I create it manually, I wont be able to get a file from user at run time. I will have to select an excel file here.  

[palanikumar]

Create a Data Source and attach a sample Excel file. Create Import Set and Transform map from the Data Source. Once created, this import set and transform map can be used in this script.

Thank you,

Palani

[Hm10]

So when there is different excel file everytime to be uploaded?

[palanikumar]

You need to maintain the same template (Headers inside the sheet). Excel file can be different and data can be different