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.

/**
 * @fileoverview ODATA TO GOOGLE SHEETS IMPORTER
 * 
 * SETUP INSTRUCTIONS:
 * 1. Go to "Services" in the left sidebar of the Apps Script Editor.
 * 2. Click "+", scroll to "Google Sheets API", and click "Add".
 *    (This script requires the Advanced Sheets Service to function).
 * 3. Update the CONFIGURATION CONSTANTS section below with your details.
 */

// --- CONFIGURATION CONSTANTS ---

// The ID of the Google Sheet. Use 'ScriptApp.getScriptId()' if attached to the sheet, 
// or copy the string between /d/ and /edit in the URL.
const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID_HERE'; 

// The name of the tab where data will be imported.
const SHEET_NAME = 'Acumatica Data'; 

// Your Acumatica OData URL.
// Tip: Test this URL in a browser first to ensure it loads data.
const ODATA_URL = 'https://YOUR_INSTANCE.acumatica.com/odata/YOUR_ENDPOINT';

// Credentials
// Note: For better security, consider using PropertiesService.getScriptProperties().
const ODATA_USER = 'YOUR_USERNAME';
const ODATA_PASS = 'YOUR_PASSWORD';

// Performance Tuning
const BATCH_SIZE = 10000; // Rows per write operation.
// --- END OF CONFIGURATION CONSTANTS ---

/**
 * Main function to trigger the OData fetch and sheet update process.
 */
function importAcumaticaData() {
  const startTime = new Date();
  Logger.log(`Starting Data Import for sheet: ${SHEET_NAME}`);

  try {
    // 1. Fetch Data
    const allRecords = fetchAllODataRecords(ODATA_URL, ODATA_USER, ODATA_PASS);

    if (!allRecords || allRecords.length === 0) {
      Logger.log('No records fetched. Clearing the sheet.');
      const sheetId = ensureSheetExists(SPREADSHEET_ID, SHEET_NAME);
      if (sheetId) clearSheetWithAdvancedService(SPREADSHEET_ID, sheetId);
      return;
    }
    
    const fetchTime = new Date();
    Logger.log(`Fetched ${allRecords.length} records in ${(fetchTime - startTime) / 1000} seconds.`);

    // 2. Write Data
    writeDataUsingAdvancedService(SPREADSHEET_ID, SHEET_NAME, allRecords);
    
    const writeTime = new Date();
    Logger.log(`Wrote all data in ${(writeTime - fetchTime) / 1000} seconds.`);

    // 3. Optional: Update a timestamp log (Uncomment if needed)
    // logLastRunTime(); 

  } catch (error) {
    Logger.log(`FATAL ERROR: ${error.toString()}\nStack: ${error.stack}`);
    SpreadsheetApp.getActiveSpreadsheet().toast("Import Failed. Check Logs.");
  } finally {
    const endTime = new Date();
    Logger.log(`Total execution time: ${(endTime - startTime) / 1000} seconds.`);
  }
}

/**
 * Fetches all records from a paginated OData endpoint.
 * Handles pagination automatically via '@odata.nextLink'.
 */
function fetchAllODataRecords(initialUrl, username, password) {
  let allRecords = [];
  // Ensure we request JSON format
  let nextUrl = initialUrl + (initialUrl.includes('?') ? '&' : '?') + '$format=json';
  const maxPages = 200; // Safety limit to prevent infinite loops
  let pageCount = 0;

  const encodedCredentials = Utilities.base64Encode(`${username}:${password}`);
  const options = {
    method: 'GET',
    headers: { 'Authorization': `Basic ${encodedCredentials}` },
    muteHttpExceptions: true
  };

  while (nextUrl && pageCount < maxPages) {
    pageCount++;
    Logger.log(`Fetching page ${pageCount}...`);
    
    try {
      const response = UrlFetchApp.fetch(nextUrl, options);
      const responseCode = response.getResponseCode();
      
      if (responseCode === 200) {
        const responseText = response.getContentText();
        const responseData = JSON.parse(responseText);
        
        if (responseData.value && Array.isArray(responseData.value)) {
          // Use concat for safe array merging
          allRecords = allRecords.concat(responseData.value);
        }
        nextUrl = responseData['@odata.nextLink'] || null;
      } else {
        Logger.log(`HTTP Error ${responseCode}: ${response.getContentText()}`);
        return null;
      }
    } catch (e) {
      Logger.log(`Network/Parsing error: ${e.toString()}`);
      return null;
    }
  }
  return allRecords;
}

/**
 * Resizes the sheet to exact dimensions and writes data in batches.
 * This method is significantly faster than standard SpreadsheetApp calls.
 */
function writeDataUsingAdvancedService(spreadsheetId, sheetName, records) {
  try {
    const sheetId = ensureSheetExists(spreadsheetId, sheetName);
    if (sheetId === null) throw new Error(`Could not access sheet "${sheetName}"`);

    const headers = Object.keys(records[0]);
    const requiredRows = records.length + 1; // +1 for header
    const requiredCols = headers.length;

    // --- Step 1: Resize and Clear ---
    const requests = [{
      updateSheetProperties: {
        properties: {
          sheetId: sheetId,
          gridProperties: { rowCount: requiredRows, columnCount: requiredCols }
        },
        fields: 'gridProperties(rowCount,columnCount)'
      }
    }, {
      updateCells: {
        range: { sheetId: sheetId },
        fields: '*' // Clears values and formatting
      }
    }];

    Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);

    // --- Step 2: Prepare Data ---
    const dataRows = records.map(record =>
      headers.map(header => {
        const val = record[header];
        if (val === null || val === undefined) return '';
        if (typeof val === 'object') return JSON.stringify(val);
        return val;
      })
    );
    const dataToWrite = [headers, ...dataRows];

    // --- Step 3: Write in Batches ---
    for (let i = 0; i < dataToWrite.length; i += BATCH_SIZE) {
      const batch = dataToWrite.slice(i, i + BATCH_SIZE);
      const startRow = i + 1;
      const range = `'${sheetName}'!A${startRow}`;
      
      Sheets.Spreadsheets.Values.update({ values: batch }, spreadsheetId, range, {
        valueInputOption: 'USER_ENTERED'
      });
      Logger.log(`Batch written: rows ${startRow} to ${startRow + batch.length - 1}`);
    }
  } catch(e) {
    Logger.log(`Write Error: ${e.toString()}`);
    throw e;
  }
}

/**
 * Utility: Checks if sheet exists, creates it if not. Returns Sheet ID.
 */
function ensureSheetExists(spreadsheetId, sheetName) {
  try {
    const spreadsheet = Sheets.Spreadsheets.get(spreadsheetId, { fields: 'sheets(properties(title,sheetId))' });
    const sheet = spreadsheet.sheets.find(s => s.properties.title === sheetName);

    if (sheet) {
      return sheet.properties.sheetId;
    } else {
      const addSheetRequest = { addSheet: { properties: { title: sheetName } } };
      const response = Sheets.Spreadsheets.batchUpdate({ requests: [addSheetRequest] }, spreadsheetId);
      return response.replies[0].addSheet.properties.sheetId;
    }
  } catch (e) {
    Logger.log(`Error checking sheet existence: ${e.message}`);
    return null;
  }
}

/**
 * Utility: Clears a sheet using Advanced Service (fallback method).
 */
function clearSheetWithAdvancedService(spreadsheetId, sheetId) {
  const request = {
    updateCells: {
      range: { sheetId: sheetId },
      fields: 'userEnteredValue'
    }
  };
  Sheets.Spreadsheets.batchUpdate({ requests: [request] }, spreadsheetId);
}

Automating the Sync: Set It and Forget It

To make the script run automatically, you need to set up a "trigger."

1. In the Apps Script editor, click on the Triggers icon (looks like an alarm clock) on the left sidebar.

2. Click + Add Trigger in the bottom right.

3. Choose function to run: importAcumaticaData

4. Choose which deployment should run: Head

5. Select event source: Time-driven

6. Select type of time-based trigger: Day timer (or Hour timer)

7. Select the time of day you want it to run (e.g., 1am to 2am to refresh overnight).

8. 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.