Documentation
HomeGithubDiscordBlog
  • What is OpenAlgo?
  • OpenAlgo Architecture
  • Mini FOSS Universe
  • Community Support
  • OpenAlgo GPT
  • New Features
    • Fast Scalper
    • API Analyzer
    • Traffic/Latency Monitor
    • Chartink Integration
  • Monetization
  • Connect Brokers
    • Brokers
      • 5Paisa
      • 5paisa (XTS)
      • AliceBlue
      • AngelOne
      • Compositedge
      • Dhan
      • Firstock
      • FlatTrade
      • Fyers
      • Groww
      • IIFL (XTS)
      • Jainam Retail (XTS)
      • Jainam Dealer (XTS)
      • Kotak Securities
      • Paytm
      • Pocketful
      • Shoonya
      • Upstox
      • Wisdom Capital
      • Zebu
      • Zerodha
  • Installation Guidelines
  • Getting Started
    • Windows Installation
      • Pre-Requesites
      • Setup
      • Install Dependencies
      • Ngrok Config
      • Environmental Variables
      • Start OpenAlgo
      • SSL Verification Failed
      • Accessing OpenAlgo
    • Windows Server Installation
    • Mac OS Installation
      • Pre-Requesties
      • Setup
      • Install Dependencies
      • Ngrok Config
      • Environmental Variables
      • Start OpenAlgo
      • Install certifi
      • Accessing OpenAlgo
    • Amazon Elastic Beanstalk
    • Ubuntu Server Installation
    • Docker Development
    • Testing OpenAlgo in Cloud
    • Upgrade
  • Latency
  • API Documentation
    • V1
      • Accounts API
        • Funds
        • Orderbook
        • Tradebook
        • PositionBook
        • Holdings
      • Orders API
        • Placeorder
        • PlaceSmartOrder
        • BasketOrder
        • SplitOrder
        • ModifyOrder
        • CancelOrder
        • CancelAllOrder
        • ClosePosition
        • OrderStatus
        • OpenPosition
      • Data API
        • Quotes
        • Depth
        • History
        • Intervals
        • Symbol
        • Ticker
      • Order Constants
      • HTTP Status Codes
      • Rate Limiting
      • API Collections
  • Symbol Format
  • MCP
  • Trading Platform
    • Amibroker
      • AmiQuotes
      • Button Trading Module
      • Button Trading with Split Orders
      • Button Trading with Stoploss
      • SmartOrder Chart Module
      • Trailing Stoploss Execution Module
      • Line Trading Module
      • Equity Exploration Module
      • CSV Exploration Module
      • Options Button Trading Module
      • Spot/Futures to Options Module (Single Leg)
      • Spot/Futures to Options Module (Two Leg)
      • Time Based Execution
    • Tradingview
    • ChartInk
    • Python
      • Strategy Management
      • EMA Crossover Strategy
      • Supertrend Strategy
      • Supertrend Strategy with yfinance data
      • Voice Based Orders
    • NodeJS
    • Metatrader 5
      • Download & Install Library
      • OpenAlgo MQL5 Functions
      • Include the Header File
      • Sample Expert Advisor
    • Excel
    • Google Spreadsheets
    • N8N
    • Chrome Extension
  • Strategy Management
  • Developers
    • Design Documentation
      • Architecture
      • API Layer
      • Broker Integerations
      • Database Layer
      • Authentication Platforms
      • Configuration
      • Utilities
      • Broker Integration Checklist
  • Change Log
    • Version 1.0.0.24 Launched
    • Version 1.0.0.23 Launched
    • Version 1.0.0.22 Launched
    • Version 1.0.0.21 Launched
    • Version 1.0.0.20 Launched
    • Version 1.0.0.19 Launched
    • Version 1.0.0.18 Launched
    • Version 1.0.0.17 Launched
    • Version 1.0.0.16 Launched
    • Version 1.0.0.15 Launched
    • Version 1.0.0.14 Launched
    • Version 1.0.0.13 Launched
    • Version 1.0.0.12 Launched
    • Version 1.0.0.11 Launched
    • Version 1.0.0.10 Launched
    • Version 1.0.0.9 Launched
    • Version 1.0.0.8 Launched
    • Version 1.0.0.7 Launched
    • Version 1.0.0.6 Launched
    • Version 1.0.0.5 Launched
    • Version 1.0.0.4 Launched
    • Version 1.0.0.3 Launched
    • Version 1.0.0.2 Launched
    • Version 1.0.0.1 Launched
    • Version 1.0.0.0 Launched
Powered by GitBook
On this page
  • Quick Start Guide
  • Troubleshooting
  • Security Notes
  1. Trading Platform

Google Spreadsheets

PreviousExcelNextN8N

Last updated 4 months ago

Quick Start Guide

Step 1: Clone the Template Spreadsheet

  1. Click "File" > "Make a copy" to create a duplicate of the spreadsheet.

  2. In the "Make a copy" dialog, enter a name for your copy of the spreadsheet, such as "My OpenAlgo Trading Sheet".

  3. Ensure the "Folder" is set to your desired location, such as "My Drive".

  4. Click the "Make a copy" button to create the new spreadsheet.

Step 2: Configure API Settings

  1. Open the cloned spreadsheet

  2. Go to Extensions > AppScript editor

  3. Locate these lines in the script:

  4. var apikey = "<your-openalgo-apikey>";
    var hostServer = "https://your-ngrok-domain.ngrok-free.app"; 
  5. Replace with your OpenAlgo credentials:

    • apikey: Your unique OpenAlgo API key

    • HostSefver: Your Custom Domain or Free Ngrok Domain where OpenAlgo is hosted

Spreadsheet Structure

The template contains two main sheets:

  • PlaceOrder: For entering trade details

  • Logs: Automatic logging of trade orders

PlaceOrder Sheet Columns

Column
Description
Example

C3

Exchange

NSE, BSE, NFO, MCX, CDS

D3

OpenAlgo Symbol

INFY, RELIANCE

E3

Action

BUY/SELL

F3

Price Type

MARKET/LIMIT/SL/SL-M

G3

Total Quantity

100

H3

Price

1500.50 (for LIMIT/STOP)

I3

Trigger Price

1480.00 (for STOP)

J3

Split Size

0 - No SplitOrder Specific Number - Send Split order with split size quantity

K3

Product Type

INTRADAY/DELIVERY

Split Order Functionality

  • Split Size 0: Entire quantity in one order

  • Split Size > 0: Multiple orders of specified size

Execution

  1. Fill in trade details in the PlaceOrder sheet

  2. Press the PlaceOrder function

  3. Check response in cell C9

  4. View detailed logs in the Logs sheet

function openalgo() {
  var apikey = "<your-openalgo-apikey>"; // Replace with your OpenAlgo API key
  var hostServer = "https://your-ngrok-domain.ngrok-free.app"; // REPLACE WITH YOUR HOST SERVER
  
  var baseUrl = hostServer + "/api/v1/splitorder"; // Constructed base URL

  
  // Fetching parameters from the active sheet
  var strategy = "Google Sheet";
  var exchange = SpreadsheetApp.getActiveSheet().getRange('C3').getValue();
  var symbol = SpreadsheetApp.getActiveSheet().getRange('D3').getValue();
  var action = SpreadsheetApp.getActiveSheet().getRange('E3').getValue();
  var pricetype = SpreadsheetApp.getActiveSheet().getRange('F3').getValue();
  var quantity = SpreadsheetApp.getActiveSheet().getRange('G3').getValue().toString();
  var price = SpreadsheetApp.getActiveSheet().getRange('H3').getValue().toString();
  var trigger_price = SpreadsheetApp.getActiveSheet().getRange('I3').getValue().toString();
  var splitsize = SpreadsheetApp.getActiveSheet().getRange('J3').getValue().toString();
  var product = SpreadsheetApp.getActiveSheet().getRange('K3').getValue();

  // Modify payload based on splitsize
  var payload = {
    "apikey": apikey,
    "strategy": strategy,
    "exchange": exchange,
    "symbol": symbol,
    "action": action,
    "quantity": splitsize === "0" ? quantity : quantity,
    "splitsize": splitsize === "0" ? quantity : splitsize,
    "pricetype": pricetype,
    "product": product,
    "price": price,
    "trigger_price": trigger_price
  };

  Logger.log("Request Payload: " + JSON.stringify(payload)); // Log request payload

  // Sending the split order request
  var response = sendSplitOrder(baseUrl, payload);

  // Format the response for frontend
  var formattedResponse = formatResponse(response);

  Logger.log("Formatted Response: " + formattedResponse); // Log formatted response

  // Update the response in the sheet with formatted response
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PlaceOrder").getRange('C9').setValue(formattedResponse);
  
  // Log order details
  logSplitOrder(response, strategy, exchange, symbol, action, pricetype, quantity, product, price, trigger_price);

  return formattedResponse;
}

function formatResponse(response) {
  try {
    var parsedResponse = JSON.parse(response);
    
    // If response is successful
    if (parsedResponse.status === "success") {
      var results = parsedResponse.results;
      
      // Format the response for frontend display
      var formattedLines = [
        "Order Placement Status: " + parsedResponse.status.toUpperCase(),
        "Total Orders: " + results.length
      ];
      
      // Add details for each order
      results.forEach(function(order, index) {
        formattedLines.push(
          "Order " + (index + 1) + ":",
          "  Order ID: " + order.orderid,
          "  Quantity: " + order.quantity,
          "  Status: " + order.status
        );
      });
      
      return formattedLines.join("\n");
    } else {
      // Handle error response
      return "Order Placement Failed:\n" + 
             "Error Status: " + parsedResponse.status + "\n" +
             "Error Message: " + (parsedResponse.message || "Unknown error");
    }
  } catch (e) {
    // Handle parsing error
    return "Error Formatting Response:\n" + e.message;
  }
}

function sendSplitOrder(url, payload) {
  try {
    var options = {
      'method': 'post',
      'contentType': 'application/json',
      'payload': JSON.stringify(payload)
    };

    var result = UrlFetchApp.fetch(url, options);
    return result.getContentText();
  } catch (e) {
    Logger.log("Error sending split order: " + e.message);
    return JSON.stringify({
      status: "error",
      message: e.message
    });
  }
}

function logSplitOrder(response, strategy, exchange, symbol, action, pricetype, quantity, product, price, trigger_price) {
  try {
    var parsedResponse = JSON.parse(response);
    if (parsedResponse.status === "success") {
      var results = parsedResponse.results;
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Logs");

      results.forEach(function(order) {
        var row = [
          strategy,
          exchange,
          symbol,
          action,
          pricetype,
          quantity,
          product,
          order.orderid,
          order.quantity,
          order.status,
          price,
          trigger_price,
          new Date()
        ];
        sheet.appendRow(row);
      });
    } else {
      Logger.log("Error in response: " + response);
    }
  } catch (e) {
    Logger.log("Error logging split order: " + e.message);
  }
}

function clearLogs() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Logs");
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
  }
}

Troubleshooting

Common Issues

  1. API Key Invalid

    • Verify key in OpenAlgo dashboard

    • Check network connectivity

    • Ensure API key has trading permissions

  2. Host URL Incorrect

    • Confirm exact URL from OpenAlgo platform

    • Check for any trailing slashes

    • Verify ngrok or custom endpoint

  3. Permissions

    • Accept Google Sheets script permissions

    • Verify API key authorization

Security Notes

  • Keep API key confidential

  • Use secure, unique API keys

  • Regularly rotate credentials

  • Monitor trading activities

Open the

OpenAlgo Trading Template
OpenAlgo ExcelSheets