How to Integrate ERPNext with Google Sheets

Introduction

In today’s data-focused world, connecting different tools can boost your productivity and help you make smarter decisions. A great way to achieve this is by integrating ERPNext with Google Sheets. Google Sheets is not just a simple spreadsheet; it is an easy-to-use tool that can help you create reports and dashboards without the need for extensive technical skills. In this post, we will learn how quickly integrate Google sheets with with ERPNext.

Step 1 - ERPNext Server Script

Develop a new server script by choosing the API type and assigning a name to the method. Your script will be displayed in ERPNext windows as follows. You can copy and paste the script from the snippet given below.

ERPNext Server Script Page

def Sales_Order_Test():

    data = frappe.db.sql("""

    SELECT

        `tabSales Order`.name,

        `tabSales Order`.creation,

        `tabSales Order`.customer_name,

        `tabSales Order`.status

    FROM

        `tabSales Order`

    WHERE

        `tabSales Order`.status <> "Cancelled"

    """, as_dict=1)

    return data

frappe.response['message'] = Sales_Order_Test()

Step 2 - Testing it on Browser

https://your-domain.frappe.cloud/api/method/Sales_Order_Test

Paste the URL above into your browser's address bar (modify the domain name) and hit Enter. This will send a GET request to the API, and if everything is set up correctly, the result will display directly in the browser, usually in JSON format. You won't need API keys as long as you're using the same browser where the application is running.

Testing it POSTMAN

To test it on POSTMAN with API keys, you must first generate API keys for the logged-in user.

In ERPNext navivage to User->settings->API Access

Step 3 - App Script

You need to setup the workbook before using this script

1. Generate a new workbook containing two sheets named DATA and API.

2. Revise your API Key, secret key, and method.

3. Go to your sheet and follow Menu-> Extensions-> AppScript, then insert the code provided below.

4. Authorize the execution of your script (initial time).

5. Execute the script and get the data.

6. Utilize Pivot, Query, and SUMIFS to create sophisticated dashboards

function FetchData() {
  // Get the active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the necessary sheets
  var dataSheet = ss.getSheetByName("Data");
  var apiSheet = ss.getSheetByName("API");
  var apiMethod = ss.get
  var paramSheet = ss.getSheetByName("Param");
  
  // Retrieve API credentials
  var apiKey = apiSheet.getRange("B1").getValue();
  var secretKey = apiSheet.getRange("B2").getValue();
  var api_method = apiSheet.getRange("A5").getValue();  

  // Use query parameter in URL
  var url = api_method;
        
  var options = {
    "method": "get",
    "headers": {
      "Authorization": "token " + apiKey + ":" + secretKey
    },
    "muteHttpExceptions": true
  };
  
  try {
    // Make the API request
    var response = UrlFetchApp.fetch(url, options);
    var responseCode = response.getResponseCode();
    var responseText = response.getContentText();
    
    // Clear existing data in the Data sheet
    dataSheet.clear();
    
    // Check if we got a valid response
    if (responseCode >= 200 && responseCode < 300 && responseText) {
      // Parse the response
      var responseData = JSON.parse(responseText);
      
      // Check if we have data to display
      if (responseData && responseData.message && Array.isArray(responseData.message) && responseData.message.length > 0) {
        // Get the keys from the first object to use as headers
        var headers = Object.keys(responseData.message[0]);
        
        // Write headers to the first row
        dataSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
        
        // Prepare data for writing
        var data = responseData.message.map(function(item) {
          return headers.map(function(header) {
            return item[header];
          });
        });
        
        // Write data starting from row 2
        dataSheet.getRange(2, 1, data.length, headers.length).setValues(data);
      } else if (responseData && responseData.message && Array.isArray(responseData.message) && responseData.message.length === 0) {
        // Handle empty array
        dataSheet.getRange(1, 1).setValue("Status");
        dataSheet.getRange(1, 2).setValue("Message");
        dataSheet.getRange(2, 1).setValue("No Data");
        dataSheet.getRange(2, 2).setValue("No results found for project: " + projectParam);
      } else {
        // Handle other types of responses
        dataSheet.getRange(1, 1).setValue("Status");
        dataSheet.getRange(1, 2).setValue("Message");
        dataSheet.getRange(2, 1).setValue("Invalid Response");
        dataSheet.getRange(2, 2).setValue("Unexpected data format returned from API");
      }
    } else {
      // Handle error response
      dataSheet.getRange(1, 1).setValue("Error");
      dataSheet.getRange(1, 2).setValue("Details");
      dataSheet.getRange(2, 1).setValue("HTTP Error " + responseCode);
      dataSheet.getRange(2, 2).setValue(responseText);
    }
  } catch (error) {
    // Handle exceptions
    dataSheet.getRange(1, 1).setValue("Error");
    dataSheet.getRange(1, 2).setValue("Details");
    dataSheet.getRange(2, 1).setValue("Exception");
    dataSheet.getRange(2, 2).setValue(error.toString());
  }
}

To run the script from the menu, you can include the following code.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('ERP Integration')
    .addItem('Order Refresh', 'FetchData')
    .addToUi();
}

Learn the easiest tool on Earth to master data analysis.

I have experience with various data analysis tools such as PowerBI, Qlik, Looker Studio, Microsoft Excel, Apache Superset etc. In my opinion, Google Sheets is the simplest and most effective tool for generating MIS.

Discover the capabilities of Google Sheets today and gain the expertise that the business community genuinely desires.