在条件下列出单元格的值的Sheets脚本

huangapple go评论56阅读模式
英文:

Sheets script to list cell value with a condition

问题

我的脚本当前在交易编号提示中显示V2:V,但我希望它在金额提示中根据所选的交易编号也显示对应的X2:X值。以下是数据集/脚本。

英文:

My script currently displays V2:V in the tradeId prompt, but I want it to also display the corresponding X2:X value depending on the tradeId selected in V2:V, in the amount prompt. Below is the data set/ script.

var amount = validateNumericInput('Enter the amount:');

在条件下列出单元格的值的Sheets脚本

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

function closeTrade() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var tradeId = Browser.inputBox(&#39;Enter the Trade ID to close:&#39; + sheet.getRange(&quot;V2:V&quot;).getValues().filter(String), Browser.Buttons.OK_CANCEL);
  if (tradeId == &quot;cancel&quot;) {
    return;
  }
  var range = sheet.getDataRange();
  var values = range.getValues();
  var openAmount = 0; // initialize open amount to zero
  var closeAmount = 0; // initialize close amount to zero
  var columnKValues = sheet.getRange(&quot;K:K&quot;).getValues(); // get values in column K

  for (var i = 0; i &lt; values.length; i++) {
    if (values[i][0] == tradeId) {
      if (values[i][2].toLowerCase() == &quot;open&quot;) {
        openAmount += values[i][3]; // add open amount to the total open amount
      } else if (values[i][2].toLowerCase() == &quot;close&quot;) {
        closeAmount += values[i][3]; // add close amount to the total close amount
      }
    }
  }


  
  for (var i = 0; i &lt; values.length; i++) {
    if (values[i][0] == tradeId) {
      if (values[i][2].toLowerCase() == &quot;open&quot;) {
        var openRow = sheet.getRange(i+1, 1, 1, values[0].length).getValues();
        var amount = validateNumericInput(&#39;Enter the amount:&#39;);
        if (amount == null) {
          return;
        }
        var correspondingKValue = columnKValues[i][0];
        if (correspondingKValue &lt; amount) { // check if Column K value is less than the close amount
          Browser.msgBox(&#39;Close amount cannot be greater than the remaining amount.&#39;);
          return;
        }
        var exitPrice = validateNumericInput(&#39;Enter the Exit Price (numerical only):&#39;);
        if (exitPrice == null) {
          return;
        }
        
        var description = Browser.inputBox(&#39;Description:&#39;, Browser.Buttons.OK_CANCEL);
        if (description == &quot;cancel&quot;) {
          return;
        }
        sheet.appendRow([tradeId, openRow[0][1], &quot;close&quot;, amount, openRow[0][4], exitPrice, description]);
        return;
      } else if (values[i][2].toLowerCase() == &quot;close&quot;) {
        Browser.msgBox(&#39;This trade has already been closed.&#39;);
        return;
      }
    }
  }
  Browser.msgBox(&#39;Trade ID not found.&#39;);
}

<!-- end snippet -->

I'm stumped on the changes I would have to make.

答案1

得分: 1

I believe that the best way to do this would be to build a message first, this can be done like this:

  var ss = SpreadsheetApp.getActiveSheet()
  var data1 = sheet.getRange("V2:V").getValues().filter(String)
  var data2 = sheet.getRange("X2:X").getValues().filter(String)
  var message  = "Open Trades ----- Amount \\n \\n"
    for (let i = 0; i < data1.length; i++){
    message += data1[i] + " ----- " + data2[i] + "\\n \\n"
  }
  Browser.inputBox(message)

While doing the script I noticed that for some reason the inputBox does not recognize multiple spaces or the tab scape sequence (/t or //t) so I added the dashes (-----) to separate each number in a way that's easier to understand, feel free to change that in any way you prefer.

Here is the full script which includes all of the the data you had in your question:

function closeTrade() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data1 = sheet.getRange("V2:V").getValues().filter(String) //Obtaining data from the "Open Trades" row
  var data2 = sheet.getRange("X2:X").getValues().filter(String) //Obtaining data from the "Amount" row
  var message  = ""         //Declaring the "Message" variable
    for (let i = 0; i < data1.length; i++){
    message += data1[i] + " ----- " + data2[i] + "\\n \\n" //Building the full message to be used on Browser.inputBox
  }
  var tradeId = Browser.inputBox('Enter the Trade ID to close: \\n \\n Open Trades ----- Amount \\n \\n' + message, Browser.Buttons.OK_CANCEL);
  if (tradeId == "cancel") {
    return;
  }
  var range = sheet.getDataRange();
  var values = range.getValues();
  var openAmount = 0; // initialize open amount to zero
  var closeAmount = 0; // initialize close amount to zero
  var columnKValues = sheet.getRange("K:K").getValues(); // get values in column K

  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == tradeId) {
      if (values[i][2].toLowerCase() == "open") {
        openAmount += values[i][3]; // add open amount to the total open amount
      } else if (values[i][2].toLowerCase() == "close") {
        closeAmount += values[i][3]; // add close amount to the total close amount
      }
    }
  }
  
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == tradeId) {
      if (values[i][2].toLowerCase() == "open") {
        var openRow = sheet.getRange(i+1, 1, 1, values[0].length).getValues();
        var amount = validateNumericInput('Enter the amount:');
        if (amount == null) {
          return;
        }
        var correspondingKValue = columnKValues[i][0];
        if (correspondingKValue < amount) { // check if Column K value is less than the close amount
          Browser.msgBox('Close amount cannot be greater than the remaining amount.');
          return;
        }
        var exitPrice = validateNumericInput('Enter the Exit Price (numerical only):');
        if (exitPrice == null) {
          return;
        }
        
        var description = Browser.inputBox('Description:', Browser.Buttons.OK_CANCEL);
        if (description == "cancel") {
          return;
        }
        sheet.appendRow([tradeId, openRow[0][1], "close", amount, openRow[0][4], exitPrice, description]);
        return;
      } else if (values[i][2].toLowerCase() == "close") {
        Browser.msgBox('This trade has already been closed.');
        return;
      }
    }
  }
  Browser.msgBox('Trade ID not found.');
}
英文:

I believe that the best way to do this would be to build a message first, this can be done like this:

  var ss = SpreadsheetApp.getActiveSheet()
var data1 = sheet.getRange(&quot;V2:V&quot;).getValues().filter(String)
var data2 = sheet.getRange(&quot;X2:X&quot;).getValues().filter(String)
var message  = &quot;Open Trades ----- Amount \\n \\n&quot;
for (let i = 0; i &lt; data1.length; i++){
message += data1[i] + &quot; ----- &quot; + data2[i] + &quot;\\n \\n&quot;
}
Browser.inputBox(message)

While doing the script I noticed that for some reason the inputBox does not recognize multiple spaces or the tab scape sequence (/t or //t) so I added the dashes (-----) to separate each number in a way that's easier to understand, feel free to change that in any way you prefer.

Here is the full script which includes all of the the data you had in your question:

function closeTrade() {
var sheet = SpreadsheetApp.getActiveSheet();
var data1 = sheet.getRange(&quot;V2:V&quot;).getValues().filter(String) //Obtaining data from the &quot;Open Trades&quot; row
var data2 = sheet.getRange(&quot;X2:X&quot;).getValues().filter(String) //Obtaining data from the &quot;Amount&quot; row
var message  = &quot;&quot;         //Declaring the &quot;Message&quot; variable
for (let i = 0; i &lt; data1.length; i++){
message += data1[i] + &quot; ----- &quot; + data2[i] + &quot;\\n \\n&quot; //Building the full message to be used on Browser.inputBox
}
var tradeId = Browser.inputBox(&#39;Enter the Trade ID to close: \\n \\n Open Trades ----- Amount \\n \\n&#39; + message, Browser.Buttons.OK_CANCEL);
if (tradeId == &quot;cancel&quot;) {
return;
}
var range = sheet.getDataRange();
var values = range.getValues();
var openAmount = 0; // initialize open amount to zero
var closeAmount = 0; // initialize close amount to zero
var columnKValues = sheet.getRange(&quot;K:K&quot;).getValues(); // get values in column K
for (var i = 0; i &lt; values.length; i++) {
if (values[i][0] == tradeId) {
if (values[i][2].toLowerCase() == &quot;open&quot;) {
openAmount += values[i][3]; // add open amount to the total open amount
} else if (values[i][2].toLowerCase() == &quot;close&quot;) {
closeAmount += values[i][3]; // add close amount to the total close amount
}
}
}
for (var i = 0; i &lt; values.length; i++) {
if (values[i][0] == tradeId) {
if (values[i][2].toLowerCase() == &quot;open&quot;) {
var openRow = sheet.getRange(i+1, 1, 1, values[0].length).getValues();
var amount = validateNumericInput(&#39;Enter the amount:&#39;);
if (amount == null) {
return;
}
var correspondingKValue = columnKValues[i][0];
if (correspondingKValue &lt; amount) { // check if Column K value is less than the close amount
Browser.msgBox(&#39;Close amount cannot be greater than the remaining amount.&#39;);
return;
}
var exitPrice = validateNumericInput(&#39;Enter the Exit Price (numerical only):&#39;);
if (exitPrice == null) {
return;
}
var description = Browser.inputBox(&#39;Description:&#39;, Browser.Buttons.OK_CANCEL);
if (description == &quot;cancel&quot;) {
return;
}
sheet.appendRow([tradeId, openRow[0][1], &quot;close&quot;, amount, openRow[0][4], exitPrice, description]);
return;
} else if (values[i][2].toLowerCase() == &quot;close&quot;) {
Browser.msgBox(&#39;This trade has already been closed.&#39;);
return;
}
}
}
Browser.msgBox(&#39;Trade ID not found.&#39;);
}

huangapple
  • 本文由 发表于 2023年4月20日 01:35:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76057362.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定