How to Automatically Sync Your ERP Data to Google Sheets with OData
Every day, countless managers and analysts perform the same ritual: log into the company ERP, run a report, export it to CSV, and then painstakingly copy-paste the data into a Google Sheet. It’s a tedious, time-consuming process that's prone to human error and keeps your reports perpetually out of date.
What if your vital business data—inventory levels, sales figures, or customer orders—could appear in your Google Sheet automatically, refreshed and ready for analysis every single morning? Today, we’re peeling back the curtain to show you a powerful technique that can bridge the gap between your robust ERP and the flexible world of Google Sheets using a technology called OData. And yes, we're even giving you the code to do it yourself for free.
Why Connect Your ERP to Google Sheets?
Connecting your ERP to Google Sheets unlocks a cascade of benefits, starting with the immediate elimination of manual data handling. Many modern ERPs, such as Acumatica, SAP, and Microsoft Dynamics, offer a secure OData feed which acts as a live pipeline for your business data. By linking this feed to a simple script, you can schedule automatic updates, saying goodbye to the "download, copy, paste" routine forever. This ensures your dashboards always reflect the most current information, reducing the risk of making decisions on stale data. Furthermore, it empowers your wider team with the insights they need without granting them direct—and potentially risky—access to the core ERP system.
Once your data is live and automated in Google Sheets, you can move beyond the often-rigid reporting tools of your ERP. The spreadsheet becomes a flexible analysis playground, allowing you to build complex pivot tables, create vibrant, customized charts for presentations, and leverage powerful functions like QUERY and VLOOKUP to craft sophisticated forecasts.
Taking this a step further, the auto-updating sheet can serve as a live intermediate database for tools like Google AppSheet. This is where the magic happens for forward-thinking SMEs, enabling you to rapidly build powerful, custom mobile apps—for example, an inventory lookup tool for your warehouse team—that complement your main ERP without the high cost and complexity of traditional development.
Your Free Google Apps Script
Open the Google Sheet you want to populate.
Go to Extensions > Apps Script.
Delete any placeholder code in the editor and paste the entire script below.
Crucially, update the FIXED VALUES section at the top with your own details.
Click the "Save project" icon.
To run it manually, select the importAcumaticaData function from the dropdown and click Run. The first time you run it, you will need to authorize the script's permissions.
// --- FIXED VALUES (REPLACE THESE WITH YOUR DETAILS) ---
const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID_HERE'; // Find this in your sheet's URL
const SHEET_NAME = 'YourTargetSheetName'; // e.g., 'Inventory' or 'Sales Data'
const ODATA_URL = 'YOUR_FULL_ODATA_URL_HERE'; // The secure link from your ERP provider
const ODATA_USER = 'your_odata_username';
const ODATA_PASS = 'your_odata_password';
// --- END OF FIXED VALUES ---
/**
* Main function to trigger the OData fetch and sheet update process.
* You can run this function directly from the Apps Script editor.
*/
function importAcumaticaData() {
Logger.log(`Starting data import for sheet: ${SHEET_NAME} in Spreadsheet ID: ${SPREADSHEET_ID}`);
try {
const allRecords = fetchAllODataRecords(ODATA_URL, ODATA_USER, ODATA_PASS);
if (!allRecords || allRecords.length === 0) {
Logger.log('No records fetched from OData endpoint or an error occurred during fetch.');
return;
}
Logger.log(`Successfully fetched ${allRecords.length} records.`);
writeDataToSheet(SPREADSHEET_ID, SHEET_NAME, allRecords);
Logger.log('Successfully wrote data to Google Sheet.');
} catch (error) {
Logger.log(`ERROR in importAcumaticaData: ${error.toString()}\nStack: ${error.stack}`);
// Optional: Send an email alert on failure
// MailApp.sendEmail('admin@yourcompany.com', 'ERP Import Failed', `Error: ${error.toString()}`);
}
}
/**
* Fetches all records from an OData endpoint, handling pagination.
* @param {string} initialUrl The initial OData URL.
* @param {string} username The username for Basic Authentication.
* @param {string} password The password for Basic Authentication.
* @return {Array<Object>|null} An array of record objects, or null on failure.
*/
function fetchAllODataRecords(initialUrl, username, password) {
let allRecords = [];
let nextUrl = initialUrl;
const maxPages = 100; // Safety break to prevent infinite loops
let pageCount = 0;
const encodedCredentials = Utilities.base64Encode(`${username}:${password}`);
const options = {
method: 'GET',
headers: {
'Authorization': `Basic ${encodedCredentials}`,
'Accept': 'application/json'
},
muteHttpExceptions: true // Handle HTTP errors manually
};
while (nextUrl && pageCount < maxPages) {
pageCount++;
try {
const response = UrlFetchApp.fetch(nextUrl, options);
const responseCode = response.getResponseCode();
const responseBody = response.getContentText();
if (responseCode === 200) {
const responseData = JSON.parse(responseBody);
const records = responseData.value;
if (records && Array.isArray(records)) {
allRecords = allRecords.concat(records);
}
nextUrl = responseData['@odata.nextLink'] || responseData.nextLink || null;
if (nextUrl && !nextUrl.toLowerCase().startsWith('http')) {
const baseUrl = initialUrl.substring(0, initialUrl.indexOf('/odata/'));
nextUrl = baseUrl + nextUrl;
}
} else {
Logger.log(`HTTP Error ${responseCode} fetching ${nextUrl}: ${responseBody}`);
return null; // Stop on error
}
} catch (e) {
Logger.log(`Network or UrlFetchApp error fetching ${nextUrl}: ${e.toString()}`);
return null; // Stop on error
}
}
return allRecords;
}
/**
* Writes an array of record objects to a specified Google Sheet.
* @param {string} spreadsheetId The ID of the Google Spreadsheet.
* @param {string} sheetName The name of the sheet to write to.
* @param {Array<Object>} records The array of record objects.
*/
function writeDataToSheet(spreadsheetId, sheetName, records) {
if (!records || records.length === 0) {
Logger.log('No records to write to the sheet.');
return;
}
try {
const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
let sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
}
sheet.clearContents();
const headers = Object.keys(records[0]);
const dataToWrite = [headers];
records.forEach(record => {
const row = headers.map(header => {
let cellValue = record[header];
if (cellValue === null || typeof cellValue === 'undefined') return '';
if (typeof cellValue === 'object') return JSON.stringify(cellValue);
return cellValue;
});
dataToWrite.push(row);
});
sheet.getRange(1, 1, dataToWrite.length, dataToWrite[0].length).setValues(dataToWrite);
} catch (e) {
Logger.log(`Error writing to Google Sheet: ${e.toString()}\nStack: ${e.stack}`);
throw e;
}
}Automating the Sync: Set It and Forget It
To make the script run automatically, you need to set up a "trigger."
In the Apps Script editor, click on the Triggers icon (looks like an alarm clock) on the left sidebar.
Click + Add Trigger in the bottom right.
Choose function to run: importAcumaticaData
Choose which deployment should run: Head
Select event source: Time-driven
Select type of time-based trigger: Day timer (or Hour timer)
Select the time of day you want it to run (e.g., 1am to 2am to refresh overnight).
Click Save.
That's it! Your script will now run automatically on the schedule you set.
At Grassr Solutions, we help Philippine SMEs move beyond spreadsheets and unlock the true potential of their business data.
If this guide has sparked an idea but you'd rather have experts handle the heavy lifting, we're here to help.
