Excel Setup for Dynamic connection

Requirement – Need to Connect a report in NS to excel dynamically

Solution – Create a saved search the same as the report in NS. Create a suitelet link to load the search to excel. Connect the saved search with excel using the suitelet external URL.

The excel setup is as follows

On Windows PC 

The process of Excel dynamic connection will work as follows 

1. We have created a saved search for AP balance in NetSuite.  

2. Copy the following link – https://7183817.extforms.netsuite.com/app/site/hosting/scriptlet.nl?script=826&deploy=1&compid=7183817&h=f49850caa8eb7910e0cb&custscript_jj_custrec_id=1&custscript_jj_secreat_key=1111 
    
   This link can be used to connect the saved search to the excel. This link is specific to the AP Balance Saved search. 
    

3. We have included a Secret key for authentication purposes. A custom record is used to store the secret Key. 
    

4. Open Excel and click the option “From Web” under “Data” tab. 
   

5. Paste the URL in the message box and click OK 
    

6. Click the “Connect” button 
    
   

7. And finally click on “Load” button to export the search data to the excel 
    
   

8. The final export data will look like the screenshot below 
    

9. If secret key is Not valid, following is displayed. 
    

10. If custom record Id is invalid, following is displayed. 
     

Note : It is mandatory to add the search ID along with the secret Key in the custom record. 

The user can connect any saved search in NetSuite with the Excel sheet.  For this, the user needs to create a custom record by mentioning the Saved search ID and a Secret Key. 
 
The custom record will look as follows 

To create a custom record – Custom record for Excel setup 

Enter the desired saved search ID and a Secret Key. And in the URL that specified in the point No. 2, need to change the Custom record ID and Secret Key to load the corresponding Saved search.  
In the URL, give the custom record internal ID (custscript_jj_custrec_id= <custom record internal ID>) and secret key (custscript_jj_secreat_key= <secret key>). 

On MAC 

Because Excel for macOS, even the one that comes with Office 365, doesn’t have an option to import data from the web, you’ll need to do a little workaround that involves creating a query in Microsoft Word first. 

1. Copy and paste the URL to a word document –  
   URL – https://7183817.extforms.netsuite.com/app/site/hosting/scriptlet.nl?script=826&deploy=1&compid=7183817&h=f49850caa8eb7910e0cb&custscript_jj_custrec_id=1&custscript_jj_secreat_key=1111 
    

click the small icon that appears after pasting and select Keep Text Only. 

2. Save the document in txt format in Quries folder. 
    

3. Enter a name for your file and choose the .txt file extension. You’ll see a warning message in a window called “File Conversion.” 
    
   Under “Text encoding,” select MS-DOS. 
   Under “Options,” check the box next to “Insert line breaks.” The other options should be blank. 
    
   

4. Open Finder and navigate to the Queries folder. it’s at Applications > Microsoft Office 365 > Office > Queries. Or locate the document. 
    
   Change the file extension from .txt to .iqy. 
   – Right-click the file you created and select Get Info. 
   – In the file name, replace .txt with .iqy. 
   – When prompted, confirm that you want to change the file extension. 
    

5. Open the file and erase the unwanted spaces from the URL. There may be extra spaces in the URL. After cleaning the spaces in the URL all the red underlines will be cleared from the URL. 
    

6. Open your spreadsheet in Excel. Now that you have a query to work with, you can import it into Excel. 
    
   Click the Data menu. It’s in the menu bar at the top of the screen. 
   Select the Get external data menu. Additional options will expand. 
    
   Click Run web query. This will open a Finder window to the Queries folder, which is where you saved your query. 
    
   

7. Select your IQY file and click Get Data. This will import the data into your Excel file. 
    
   

8. The loaded values will be in the consolidated format. Need to split the columns. Navigate to Data -> Text to Columns. Select “Delimited” option 
    

9. Select the Delimiter – Comma and Text Qualifier – “ 
    

9. Click the Next button and Finish buttons 

10. The final result will be as in the screenshot below