)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Standard trial balance exports rarely match your custom reporting requirements. You can map NetSuite GL accounts to any custom Google Sheets format while maintaining automated data synchronization through API connections.
This guide shows you how to create flexible account mapping with custom categories, field selection, and automated refresh capabilities.
Create custom GL account mapping using Coefficient
Coefficient provides extensive GL account mapping capabilities when pulling NetSuite trial balance data. You can customize field selection, create custom column headers, and build complex account groupings while maintaining automated synchronization.
How to make it work
Step 1. Configure flexible field selection and customization.
Choose specific GL account fields to import including account number, name, type, and balance. Use drag-and-drop column reordering in the import preview and create custom column headers to match your reporting format. Include NetSuite custom fields in your mapping for additional account classifications.
Step 2. Set up advanced filtering for account grouping.
Filter by account type such as Asset, Liability, Revenue, or Expense to create focused imports. Create separate imports for different account ranges and use AND/OR logic for complex account selection. Apply department and class-based filtering for segmented views of your trial balance.
Step 3. Build custom mapping with SuiteQL queries.
Write custom SuiteQL queries for complete control over account categorization. Example query: SELECT CASE WHEN account.accountnumber LIKE ‘1%’ THEN ‘Current Assets’ WHEN account.accountnumber LIKE ‘2%’ THEN ‘Fixed Assets’ ELSE ‘Other’ END as custom_category, account.accountnumber, account.displayname, account.balance FROM account WHERE account.isinactive = ‘F’ ORDER BY account.accountnumber.
Step 4. Implement multi-import mapping strategies.
Create separate imports for each GL account category and map to specific sheet locations. Use consistent naming conventions for automated consolidation and maintain source data integrity while customizing presentation. This approach allows you to transform data in Google Sheets while preserving the original account structure.
Step 5. Maintain mapping consistency and audit trails.
Create reference sheets linking NetSuite accounts to custom categories and preserve audit trails by keeping original account numbers alongside custom descriptions. Regular refreshes automatically catch new GL accounts, and you can document mapping logic for consistency across reporting periods.
Transform your GL account reporting
Custom mapping gives you complete control over how NetSuite GL data appears in your reports while maintaining automated synchronization. This approach eliminates manual reformatting while ensuring your trial balance matches your specific reporting requirements. Start customizing your GL account mapping today.
