Google Sheets API

[jmbartho]

I was hoping to be able to send data from my strategy to google sheet, using the google sheets api. This will allow me to do a lot more analysing in the backtesting process. It will also allow me to keep a more accessible record of what's going on during live trading. I really want this.

I have this code working independently, that is, it will work and successfully write to a google spreadsheet using a service account when I run

node googleSheets.js

//here is my code
var google = require('googleapis');
var googleAuth = require('google-auth-library');
var fs = require('fs');

const _ = require('lodash');
const config = require('./config.js');

var sheets = google.sheets('v4');

var googleSheets = function(next) {
  _.bindAll(this);
  next();
}

googleSheets.prototype.init = function(data) {

    console.log(require('util').inspect(this.jwtClient, { depth: null }));
  // configure a JWT auth client
  this.jwtClient = new google.auth.JWT(
         config.googleSheets.privateServiceAccount.client_email,
         null,
         config.googleSheets.privateServiceAccount.private_key,
         ['https://www.googleapis.com/auth/spreadsheets',
          'https://www.googleapis.com/auth/drive',
          'https://www.googleapis.com/auth/calendar']
        );

  console.log(require('util').inspect(this.jwtClient, { depth: null }));
};

googleSheets.prototype.write = function( action, range, values, gekkoMode) {


  var request = {

    // The ID of the spreadsheet to update.
    spreadsheetId: (gekkoMode === 'backtest' ? config.googleSheets.backtestingDOCId : config.googleSheets.livetradingDOCId),

    // The A1 notation of the values to update.
    range: (gekkoMode === 'backtest' ? config.googleSheets.backtestingSheetName : config.googleSheets.livetradingSheetName) + '!' + range,

    // How the input data should be interpreted.
    valueInputOption: 'USER_ENTERED',

    resource: {
      "majorDimension": "ROWS",
      "values": values
    },

    auth: this.jwtClient,

  };

  sheets.spreadsheets.values[action](request, function(err, response) {
    if (err) {
      console.error(err);
      return;
    }

    // TODO: Change code below to process the response object:
    console.log(JSON.stringify(response, null, 2));
  });
};

var gs = new googleSheets(()=>{});

gs.init();

console.log(gs.write('update', "A2:B", [
        [ '9/5/2017 12:0:0', '4270'],
        [ '9/5/2017 13:0:0', '4345'],
        [ '9/5/2017 14:0:0', '4370'],
        [ '9/5/2017 15:0:0', '4423'],
        [ '9/5/2017 16:0:0', '4418'],
        [ '9/5/2017 17:0:0', '4381'],
        [ '9/5/2017 18:0:0', '4444'],
        [ '9/5/2017 19:0:0', '4386'],
        [ '9/5/2017 20:0:0', '4432'],
        [ '9/5/2017 21:0:0', '4486'],
        [ '9/5/2017 22:0:0', '4454'] ], "backtest"));

module.exports = googleSheets;

No matter what I do, it won't work running it from the strategy. I'd like to be able to call this from

.end in the trading strategy like this:

method.init = function() {

     this.gekkoMode = util.gekkoMode();

}

method.end = function(){

  if (this.gekkoMode === 'backtest') {

    gs = new googleSheets(()=>{});
    gs.init();

    gs.write('update', "A2:B", [[10,"string"]], "backtest");

  }

};

Even if I paste the code directly into the file. I get no errors, no response at all. It's like Gekko is selectively not running the code from

sheets.spreadsheets.values

onward

What am I missing? Am I asking too much of gekko's trading strategy module? or is there something wrong with my code? I could take a stab at making a google sheets plugin, but it's probably a stretch for me.

(sorry about the formatting, I can't get github to work like I want)

EDITED: Fixed the formatting, thanks for the tip!

[BenVlodgi]

use three back ticks at the start and end of your code
(edit:) nice

[jmbartho]

OK.

So I found a work around. I was able to get it to work with synchronous code, which is definitely less than ideal for making server to server requests.

But I noticed that other async code doesn't work in a strategy, for example: global.setTimeout() doesn't work, nor does anything from the require('request') library <= heart of my problem. Anything async just fails silently...

My work around was annoying, but seems solid.

Is this expected behavior for gekko? or did I configure something incorrectly?

[erasmuswill]

Why don't you open a Pull Request? This sounds really useful.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. If you feel this is very a important issue please reach out the maintainer of this project directly via e-mail: gekko at mvr dot me.