Learn how to use the sFTP module to create a customization to upload and download files

Learn how to use the sFTP module to create a customization to upload and download files

• 04/06/2023
• Jaime Requena
• 0

Introduction

NetSuite’s sFTP module lets you send and receive files from an sFTP server.  In this article I will detail a customization to send and receive files using the sFTP module.  The customization will use a custom record to save the connection details. We will be using a key file to login to the sFTP server.  This customization will not detail using a password to login.

Step 1: Create a custom record to house the sFTP configuration

The custom record will allow you to connect to multiple sFTP servers.  The scheduled script that sends/receives files (detailed later) will have a reference to the specific sFTP configuration.

• Go to Customization->List, Records, & Fields->Record Types->New.  Create the custom record with fields so it matches the image below.  Here is the description of each field:
• sFTP User: The user name used to login to the sFTP server.
• URL: The sFTP server URL.
• Port: The sFTP server port (Default is 22).
• sFTP Host Key:   The host key for the sFTP server.
• Directory:  The directory on the sFTP server where the files are downloaded from or uploaded to.
• To Process Folder ID:  The internal ID of the folder where files are waiting to be uploaded to the sFTP server.
• Complete Folder ID: The internal ID of the folder where files are put after they have been uploaded.  When downloading files, this is folder they will be saved to.
• Key Internal ID: The internal ID of the keyfile used to login to the sFTP server.

 

 

Step 2: Create the Key in NetSuite

In your sFTP server you should have the option to create an sFTP key. Save the key to a file. Now upload the key to NetSuite:

1. Go to Setup->Company->Keys.
2. Click “Create New”.
3. Under “Details” give it Name and ID.

 

1. Click the “Files” tab and select the Key file.

 

Click Save.  You now see the key.  Make a note of the ID as it will be used when creating the sFTP configuration record

 

Step 3:  Create the Host Key

Go to a command prompt in Windows and type:

ssh-keyscan -t rsa -p <port> rsa <URL of sFTP server> (Example: ssh-keyscan -t rsa -p 22 rsa sftp.yourdomain.com). It will print out something like this:

 

 

Copy this text and put it in a file to reference when creating the sFTP configuration record.

Step 4: Create the sFTP configuration record

1. Go to Customization->Records, Lists, & Fields->Records Types.  Select “New Record” for sFTP Configuration.
2. Fill out the record:

 

Step 5: Create the Schedule Script to Upload and Download files

Add and deploy the script (code shown below).  Reference my article “Quick Guide to Adding and Deploying a Script in NetSuite” if needed.  You will need to create a script parameter “sFTP Configuration Internal ID” of type Integer.  When deploying the script you will need to set this to the ID of the record you created which should be.

The script below does the following:

• Gets the sFTP configuration using a script parameter.  The script parameter is the internal ID of the sFTP configuration record.
• Sends files located in the “To Process” folder to the sFTP server.  After the files are sent, the files are moved to the “Complete” folder.
• Downloads files from the sFTP server.  It gets a listing of all files in the root folder and then downloads them into the “Complete” folder in NetSuite.

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/runtime', 'N/search', 'N/file', 'N/sftp'],
    (runtime, search, file, sftp) => {

        const execute = (scriptContext) => {
            log.debug('==START==');
            try {
                // get the sFTP config
                const sftpConfig = getsFTPConfig();
                const files = getFilesToSend(sftpConfig);
                // send files to sftp site
                sendTosFTPServer(files, sftpConfig);
                // download files from sftp site
                downloadFromsFTPServer(sftpConfig);
            }
            catch(e) {
                log.error('Error', JSON.stringify(e));
            }
            log.debug('==END==');
        }
        const getFilesToSend = (sftpConfig) => {
            let files = [];
            let folderSearchObj = search.create({
                type: "folder",
                filters: [
                    ["internalidnumber","equalto", sftpConfig.toprocessDirectory]
                ],
                columns:
                [
                    search.createColumn({
                        name: "internalid",
                        join: "file",
                        label: "Internal ID"
                     })
                ]
            });
            folderSearchObj.run().each(function(result){
                let fileId = result.getValue({
                    name: "internalid",
                    join: "file",
                    label: "Internal ID"
                });
                files.push(fileId);
               return true;
            });
            log.debug('Files to send',JSON.stringify(files));
            return files;
        }
        const sendTosFTPServer = (files, sftpConfig) => {
            const connection = sftp.createConnection({
                username: sftpConfig.username,
                keyId: sftpConfig.myKey,
                url: sftpConfig.url,
                directory: sftpConfig.directory,
                hostKey: sftpConfig.myHostKey,
                port: sftpConfig.port
            });
            log.debug('connection', "connected");
            let fileName = '';

            for (let i = 0; i < files.length; i++) {
                let fileId = files[i];
                errorMessage = '';
                let myFileToUpload = file.load({
                    id: fileId
                });
                fileName = myFileToUpload.name;
                connection.upload({
                    filename: fileName,
                    file: myFileToUpload,
                    replaceExisting: true
                });
                log.debug('uploaded file', fileName);
                if (sftpConfig.completeDirectory) {
                    // move to complete folder
                    myFileToUpload.folder = sftpConfig.completeDirectory;
                    let fileId = myFileToUpload.save();
                    log.debug('moved file to complete folder');
                }
            }
        }
        const downloadFromsFTPServer = (sftpConfig) => {
            const connection = sftp.createConnection({
                username: sftpConfig.username,
                keyId: sftpConfig.myKey,
                url: sftpConfig.url,
                directory: sftpConfig.directory,
                hostKey: sftpConfig.myHostKey,
                port: sftpConfig.port
            });
            log.debug('connection', "connected");
            // get the list of files in the directory
            const files = connection.list({
                path: ''
            });
            // download each file
            for (let i = 0; i < files.length; i++) {
                const downloadedFile = connection.download({
                    directory: '',
                    filename: files[i].name
                });
                downloadedFile.folder = sftpConfig.completeDirectory;
                downloadedFile.save();
                log.debug('saved file to complete folder', files[i].name);
            }
        }
        const getsFTPConfig = () => {
            // use a parameter to get the internal ID of the sFTP configuration record to use
            const scriptObj = runtime.getCurrentScript();
            const serverConfigId = scriptObj.getParameter({
                name: 'custscript_config_id'
            });
            log.debug('serverConfigId=', serverConfigId);

            const conn = search.lookupFields({
                type: 'customrecord_sftp_configuration',
                id: serverConfigId,
                columns: [
                    'custrecord_sftp_config_user',
                    'custrecord_sftp_config_key',
                    'custrecord_sftp_config_url',
                    'custrecord_sftp_config_port',
                    'custrecord_sftp_config_host_key',
                    'custrecord_sftp_config_directory',
                    'custrecord_sftp_config_to_process',
                    'custrecord_sftp_config_complete'
                ]
            });

            let sftpConfig = {};
            sftpConfig.myKey = conn.custrecord_sftp_config_key;
            sftpConfig.myHostKey = conn.custrecord_sftp_config_host_key;
            sftpConfig.username = conn.custrecord_sftp_config_user;
            sftpConfig.port = conn.custrecord_sftp_port;
            sftpConfig.url = conn.custrecord_sftp_config_url;
            sftpConfig.directory = conn.custrecord_sftp_config_directory;
            sftpConfig.toprocessDirectory = conn.custrecord_sftp_config_to_process;
            sftpConfig.completeDirectory = conn.custrecord_sftp_config_complete;
            log.debug('username=', sftpConfig.username);
            log.debug('port=', sftpConfig.port);
            log.debug('url=', sftpConfig.url);
            log.debug('myKey=', sftpConfig.myKey);
            log.debug('myHostKey=', sftpConfig.myHostKey);
            log.debug('directory=', sftpConfig.directory);
            log.debug('toprocessDirectory=', sftpConfig.toprocessDirectory);
            log.debug('completeDirectory=', sftpConfig.completeDirectory);

            return sftpConfig;
        }
        return {execute}
    });