1

I am trying to write a script in google sheets to update my 3commas bots. The API requires a number of mandatory fields are passed even when there's only 1 item that needs to be updated.

The code I have is below and it uses the values already read from the platform updating only the base_order_volume value. This works perfectly except for when the pairs value is long (more the 2k chars) and then I get an error from the UrlFetchApp call because the URL is too long.

var sheet = SpreadsheetApp.getActiveSheet();

var key = sheet.getRange('F4').getValue();
var secret = sheet.getRange('F5').getValue();

var baseUrl = "https://3commas.io"; 
var editBots = "/ver1/bots/"+bots[botCounter].id+"/update";
var patchEndPoint = "/public/api"+editBots+"?";

.
.
[loop around values in sheet]
.
.

var BaseOrder=Number(sheet.getRange(rowCounter,12).getValue().toFixed(2));

var botParams = {
            "name": bots[botCounter].name,
            "pairs": bots[botCounter].pairs,
            "max_active_deals": bots[botCounter].max_active_deals,
            "base_order_volume": BaseOrder,
            "take_profit": Number(bots[botCounter].take_profit),
            "safety_order_volume": bots[botCounter].safety_order_volume,
            "martingale_volume_coefficient": bots[botCounter].martingale_volume_coefficient,
            "martingale_step_coefficient": Number(bots[botCounter].martingale_step_coefficient),
            "max_safety_orders": bots[botCounter].max_safety_orders,
            "active_safety_orders_count": Number(bots[botCounter].active_safety_orders_count),
            "safety_order_step_percentage": Number(bots[botCounter].safety_order_step_percentage),
            "take_profit_type": bots[botCounter].take_profit_type,
            "strategy_list": bots[botCounter].strategy_list,
            "bot_id": bots[botCounter].id
          };

var keys = Object.keys(botParams); 
var totalParams = keys.reduce(function(q, e, i) {
            q += e + "=" + encodeURIComponent(JSON.stringify(botParams[e])) + (i != keys.length - 1 ? "&" : ""); 
            return q;
          },endPoint);

var signature = Utilities.computeHmacSha256Signature(totalParams, secret);

signature = signature.map(function(e) {return ("0" + (e < 0 ? e + 256 : e).toString(16)).slice(-2)}).join("");

var headers = { 
            'APIKEY': key,
            'Signature': signature,
          };

try {
            var params = { 
              'method': 'PATCH',
              'headers': headers,
              'muteHttpExceptions': true
            };

var response = JSON.parse(UrlFetchApp.fetch(baseUrl + totalParams, params).getContentText());

I have tried to set the botParams as a payload in the params but when I do the signature is incorrect.

I anyone knows how to use sheets to make a call using extensive length of parameters I'd appreciate any help at all

Some sample data for the bots array would be

{
"name": "TestBot",
"base_order_volume": 0.001,
"take_profit": 1.5,
"safety_order_volume": 0.001,
"martingale_volume_coefficient": 2,
"martingale_step_coefficient": 1,
"max_safety_orders": 1,
"active_safety_orders_count": 1,
"safety_order_step_percentage": 2.5,
"take_profit_type": "total",
"stop_loss_percentage": 0,
"cooldown": 0,
"pairs": ["BTC_ADA","BTC_TRX"],
"trailing_enabled":"true",
"trailing_deviation":0.5,
"strategy_list": [{"strategy":"cqs_telegram"}]
}

Thanks in advance

Steve
  • 175
  • 1
  • 3
  • 13
  • I have found this post that has a script the passes the values in the payload which is exactly what I need but it errors saying the signature is invalid. https://stackoverflow.com/questions/54495794/convert-query-string-to-request-body-response-error – Steve May 23 '21 at 12:43
  • I noticed your this question from [your comment](https://stackoverflow.com/questions/54495794/convert-query-string-to-request-body-response-error#comment119591369_54499840) at [this answer](https://stackoverflow.com/a/54499840). In your script, can you provide the sample value of `bots` for checking the values? Of course, please replace your personal information with other characters. – Tanaike May 24 '21 at 00:47
  • Hi, thanks for your reply! As a test i've been using the values in the other post. You mention in the other post that the op said the answer worked but I couldn't get it going. I kept kept getting and signature invalid error What doing in the script above is pulling in the values for botParams from the platform, just changing a single item and then calling the api to update the bot – Steve May 24 '21 at 08:08
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, from your replying, I couldn't understand about the sample value of `bots` and your current situation. But, I would like to try to understand it. When I could correctly understand it and find the solution, I would like to answer it. I deeply apologize I cannot resolve your issue soon. – Tanaike May 25 '21 at 01:59
  • Hi, I have updated the question with sample data but this works perfectly because passing the data doesn't make the URL too long. I think I need to move the values being passed in to the params and still create the signature successfully – Steve May 25 '21 at 15:07
  • Thank you for replying. I understood your replying. At [this thread](https://stackoverflow.com/q/54495794), it seems that the parameter can be included in the request body. But, is seems that the query parameter is included in the URL. For example, when you tested that when the parameter is included in the request body without including the parameter in the URL, what result will you obtain? I'm worry that when that didn't work, this method might not be able to be used. And also, OP says to use the url shortener api. How about this? – Tanaike May 26 '21 at 01:35
  • Hi. Putting the the parameters in the request body results in the signature being invalid. I have tried to use bit.ly to generate a short URL but this has the same problem because the long url is passed in the URL call to bit.ly – Steve May 27 '21 at 15:48
  • Thank you for replying. From your replying, I thought that in the OP's situation, I'm worry that the parameter in the request body might not be used while the parameter in the query parameter is used. So, I would like to confirm your situation. In your situation, when you remove the parameter from the query parameter and use it to the request body, an error occurs. Is my understanding correct? Because I thought that `the signature being invalid` might occur for the value of `signature`. – Tanaike May 28 '21 at 00:06
  • Hi. Yes i'm sure you are correct and the value of signature is invalid but I don't know how to generate it correctly – Steve May 28 '21 at 09:59
  • Thank you for replying. In order to confirm about the API you want to use, I would like to propose the following test. When you have already confirmed that the script using your parameters works, when using that script, the parameter is included in the request body without the query parameter to the URL, what result will you obtain? If you cannot understand about my comment, please tell me. – Tanaike May 28 '21 at 11:46
  • The original issue seems a too long URL. A solution is proposed [here](https://stackoverflow.com/questions/43142607/urlfetchapp-fetch-with-very-long-urls/43196448) – Constantin Konstantinidis May 29 '21 at 07:35

1 Answers1

1

I'd consider using a Cloud Function to either do the heavy lifting, or, if you're worried about costs, use it as a proxy. You can then call the cloud function from Google Sheets. Cloud Functions can be written in whatever language you're most comfortable with, including Node.

Check the GCP pricing calculator to see what the cost would be. For many cases it would be completely free.

This should give you a sense of how to use cloud functions for CSV creation:

https://codelabs.developers.google.com/codelabs/cloud-function2sheet#0

Here is a SO question with an answer that explains how to query cloud functions with authentication.

smcjones
  • 5,490
  • 1
  • 23
  • 39