1. The Scheduled Report Approach

• How it works:
  • Set up a recurring scheduled report in NetSuite. This will automatically email the report as a CSV attachment to a designated address.
  • Develop a program (in your preferred language) that runs on a dedicated machine. This program will:
    • Monitor the specified email inbox.
    • Extract the CSV attachment.
    • Upload the CSV file back into the NetSuite file cabinet.

Here is how this may look using Node.js

1. Required Libraries

JavaScript

const imaps = require('imap-simple'); // For email retrieval
const csv = require('csv-parser'); // For parsing CSV data
const fs = require('fs'); // For working with the downloaded attachment
const fetch = require('node-fetch'); // For NetSuite API requests 

2. Connect to Email Server

JavaScript

const config = {
    imap: {
        user: 'your_email@example.com',
        password: 'your_email_password',
        host: 'imap.example.com',
        port: 993,
        tls: true
    }
};

imaps.connect(config).then(connection => {
    // ... (code to search and fetch email) 
});

3. Search for the Email

JavaScript

connection.openBox('INBOX').then(() => {
    const searchCriteria = ['SUBJECT "CSV Report"']; // Adjust as needed
    connection.search(searchCriteria, { bodies: ['HEADER', 'TEXT', ''] })
        .then(messages => {
            // ... (code to find and download attachment) 
        });
});

4. Fetch the CSV Attachment

JavaScript

messages.forEach(message => {
    const parts = imaps.getParts(message.attributes.struct);
    parts.forEach(part => {
        if (part.disposition && part.disposition.type === 'ATTACHMENT' && part.filename.endsWith('.csv')) {
            connection.getPartData(message, part)
                .then(data => fs.writeFileSync('report.csv', data)); 
        }
    });
});

5. Parse the CSV

JavaScript

const csvData = [];
fs.createReadStream('report.csv')
    .pipe(csv())
    .on('data', row => csvData.push(row))
    .on('end', () => {
        // ... (code to send data to NetSuite)
    });

6. Send to NetSuite API

JavaScript

const netsuitePayload = formatDataForNetsuite(csvData); // Format as per NetSuite API
const netsuiteUrl = 'https://your-netsuite-api-endpoint';

fetch(netsuiteUrl, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(netsuitePayload)
})
.then(response => console.log(response)) 
.catch(error => console.error('NetSuite API error:', error)); 

• Pros:
  • High level of automation once the initial setup is complete.
  • Flexibility to customize the program’s behavior.
• Cons:
  • Requires programming knowledge and setup of a dedicated machine for script execution.

Option 2: Excel Web Queries and Google Sheets

1. Enable Excel Web Queries:
   • Within NetSuite, navigate to your saved report.
   • Edit the report and check the “Allow Web Query” option.
   • Save the report.
2. Download the .iqy File:
   • Run the saved report.
   • A link to download an .iqy file will appear. Download this file.
   • Open the .iqy file in a text editor. Observe the URL within the file; this contains the query parameters NetSuite uses to fetch the report data.
3. Utilize IMPORTXML in Google Sheets:
   • Create a new Google Sheet.
   • In a cell (e.g., A1), use the IMPORTXML function along with the URL you extracted from the .iqy file: =IMPORTXML("https://your-netsuite-instance.com/app/....", "//table") (Replace the URL with the actual link from the .iqy file)
4. NetSuite Scheduled Script:
   • Create a scheduled script in NetSuite. Its purpose will be to make an API call to Google Sheets to refresh or extract data.
   • Important: You’ll need to research and implement appropriate authentication and API usage for Google Sheets from within your NetSuite script.

Basic Scheduled Script Syntax Example (Conceptual)

JavaScript

function scheduledScript() {
    // Obtain Google Sheets API Key, Sheet ID, etc. (securely stored)
    const apiKey = 'your_google_sheets_api_key';
    const sheetId = 'your_google_sheet_id';

    // Construct API call to trigger refresh or extract CSV
    const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/...`; 
    // ...(API endpoint and details for refresh/extract action)

    // Implement NetSuite's 'nlapiRequestURL' or other suitable method
    // to make the API call with necessary authentication headers 
}

Considerations:

• Authentication: Securely handling authentication between NetSuite and Google Sheets is essential.
• Google Sheets API: Familiarize yourself with the Google Sheets API endpoints for refreshing data and potentially extracting CSV.
• Error Handling: Implement robust error handling in your NetSuite script.

Pros

• Relatively Simple Setup: No complex programming is initially required. Configuring the web query in NetSuite and using the IMPORTXML function within Google Sheets is relatively straightforward.
• No Dedicated Server: You avoid the need for a separate machine to run scripts, as you would with the scheduled report approach.
• Leverages Google Sheets: If you’re already comfortable with Google Sheets, data manipulation and potential further transformations can be done within a familiar environment.

Cons

• Limited Automation: While the NetSuite scheduled script could trigger updates, the refresh within Google Sheets might still require manual intervention (depending on your chosen Google Sheets API methods).
• Potential for Fragility: Changes to NetSuite’s report output format or the way the .iqy file is generated could break the IMPORTXML function’s parsing.
• Authentication Challenges: Setting up secure authentication between your NetSuite scheduled script and the Google Sheets API can add complexity.
• Google Sheets API Knowledge: While simpler than coding a full script, you’ll still need to understand the relevant endpoints of the Google Sheets API to trigger refreshes and potentially extract the CSV data.

Overall Suitability

The Excel Web Query approach can be a decent option if:

• You need a relatively quick and less technical solution for occasional report conversions.
• You’re comfortable with handling potential updates to the .iqy format and Google Sheets API usage.