如何编写一个满足三个约束条件的随机单元格生成器？

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

How to script Random Cell Generator given 3 constraints?

问题

问题：我想获取用户选择的“楼层”瓷砖，楼层1、2或3。在这之后，脚本随机从A列中选择第一个瓷砖类型，然后从该类型中选择瓷砖形状，然后从“堆叠”中减去该瓷砖的数量。

因此，使用图像作为参考，用户输入“楼层2”，脚本运行并返回“常规，死胡同”。它将将“楼层2，常规，死胡同”的值从6更改为5，并返回一个提示：“楼层2，常规，死胡同”。

我的问题是，我不知道如何在应用脚本中设置我想要的限制参数。现在我正在尝试将其设置为数组，然后在该数组内滚动，但即使在那样做的情况下，我也难以找到正确的语法。任何指导或帮助都将不胜感激。

编辑以添加解决方案，以便在其他人也感兴趣的情况下找到解决方案：

function drawCardColumnC() {
  drawCardFromDeck(2, "C");
}

function drawCardColumnD() {
  drawCardFromDeck(3, "D");
}

function drawCardColumnE() {
  drawCardFromDeck(4, "E");
}

function drawCardFromDeck(column, targetColumn) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange("A3:E30");
  var data = dataRange.getValues();
  var quantities = data.map(function(row) {
    return row[column];
  });

  var validCardIndices = [];
  for (var i = 0; i < quantities.length; i++) {
    if (quantities[i] > 0) {
      validCardIndices.push(i);
    }
  }

  if (validCardIndices.length > 0) {
    var randomIndex = Math.floor(Math.random() * validCardIndices.length);
    var drawnCardIndex = validCardIndices[randomIndex];
    var drawnCard = data[drawnCardIndex];

    updateCardQuantities(drawnCardIndex, sheet, column);
    sheet.getRange(targetColumn + "32").setValue(drawnCard[0]);
    sheet.getRange(targetColumn + "33").setValue(drawnCard[1]);
  }
}

function resetGame() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange("A3:E30");
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; i++) {
    data[i][2] = data[i][5];
    data[i][3] = data[i][6];
    data[i][4] = data[i][7];
  }

  sheet.getRange("C32:E33").clearContent();
  sheet.getRange("C3:E30").setValues(data);
}

function updateCardQuantities(cardIndex, sheet, column) {
  var quantity = sheet.getRange(cardIndex + 3, column + 3).getValue();
  if (quantity > 0) {
    sheet.getRange(cardIndex + 3, column + 3).setValue(quantity - 1);
  }
}
英文:

Problem: I want to get a user's input of chosen "Floor" tile, Floor 1 2 or 3. Upon that, the script randomly rolls first Tile Type from the A column, then rolls Tile Formation from that type, then subtracts that tile from the total number of tiles left in the "pile".

So using the image for reference, user inputs Floor 2, script runs and returns Regular, Dead End. It changes Floor 2, Regular, Dead End value from 6 to 5 and returns a prompt "Floor 2, Regular, Dead End".

My issue is I have no idea how to set the limiting parameters within Apps Script that I want. Right now I'm attempting to make it an array, then rolling within that array, but even with that I'm having trouble finding the correct syntax. Any direction or assistance would be greatly appreciated.

Edited to add solution, so figured it out in case anyone else is interested:

function drawCardColumnC() {
drawCardFromDeck(2, &quot;C&quot;);
}
function drawCardColumnD() {
drawCardFromDeck(3, &quot;D&quot;);
}
function drawCardColumnE() {
drawCardFromDeck(4, &quot;E&quot;);
}
function drawCardFromDeck(column, targetColumn) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getRange(&quot;A3:E30&quot;);
var data = dataRange.getValues();
var quantities = data.map(function(row) {
return row[column];
});
var validCardIndices = [];
for (var i = 0; i &lt; quantities.length; i++) {
if (quantities[i] &gt; 0) {
validCardIndices.push(i);
}
}
if (validCardIndices.length &gt; 0) {
var randomIndex = Math.floor(Math.random() * validCardIndices.length);
var drawnCardIndex = validCardIndices[randomIndex];
var drawnCard = data[drawnCardIndex];
updateCardQuantities(drawnCardIndex, sheet, column);
sheet.getRange(targetColumn + &quot;32&quot;).setValue(drawnCard[0]);
sheet.getRange(targetColumn + &quot;33&quot;).setValue(drawnCard[1]);
}
}
function resetGame() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getRange(&quot;A3:E30&quot;);
var data = dataRange.getValues();
for (var i = 0; i &lt; data.length; i++) {
data[i][2] = data[i][5];
data[i][3] = data[i][6];
data[i][4] = data[i][7];
}
sheet.getRange(&quot;C32:E33&quot;).clearContent();
sheet.getRange(&quot;C3:E30&quot;).setValues(data);
}
function updateCardQuantities(cardIndex, sheet, column) {
var quantity = sheet.getRange(cardIndex + 3, column + 3).getValue();
if (quantity &gt; 0) {
sheet.getRange(cardIndex + 3, column + 3).setValue(quantity - 1);
}
}
</details>
# 答案1
**得分**: 1
发布编辑为答案。
```javascript
function drawCardColumnC() {
drawCardFromDeck(2, "C");
}
function drawCardColumnD() {
drawCardFromDeck(3, "D");
}
function drawCardColumnE() {
drawCardFromDeck(4, "E");
}
function drawCardFromDeck(column, targetColumn) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getRange("A3:E30");
var data = dataRange.getValues();
var quantities = data.map(function(row) {
return row[column];
});
var validCardIndices = [];
for (var i = 0; i < quantities.length; i++) {
if (quantities[i] > 0) {
validCardIndices.push(i);
}
}
if (validCardIndices.length > 0) {
var randomIndex = Math.floor(Math.random() * validCardIndices.length);
var drawnCardIndex = validCardIndices[randomIndex];
var drawnCard = data[drawnCardIndex];
updateCardQuantities(drawnCardIndex, sheet, column);
sheet.getRange(targetColumn + "32").setValue(drawnCard[0]);
sheet.getRange(targetColumn + "33").setValue(drawnCard[1]);
}
}
function resetGame() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getRange("A3:E30");
var data = dataRange.getValues();
for (var i = 0; i < data.length; i++) {
data[i][2] = data[i][5];
data[i][3] = data[i][6];
data[i][4] = data[i][7];
}
sheet.getRange("C32:E33").clearContent();
sheet.getRange("C3:E30").setValues(data);
}
function updateCardQuantities(cardIndex, sheet, column) {
var quantity = sheet.getRange(cardIndex + 3, column + 3).getValue();
if (quantity > 0) {
sheet.getRange(cardIndex + 3, column + 3).setValue(quantity - 1);
}
}
英文:

Posting the edit as answer.

function drawCardColumnC() {
drawCardFromDeck(2, &quot;C&quot;);
}
function drawCardColumnD() {
drawCardFromDeck(3, &quot;D&quot;);
}
function drawCardColumnE() {
drawCardFromDeck(4, &quot;E&quot;);
}
function drawCardFromDeck(column, targetColumn) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getRange(&quot;A3:E30&quot;);
var data = dataRange.getValues();
var quantities = data.map(function(row) {
return row[column];
});
var validCardIndices = [];
for (var i = 0; i &lt; quantities.length; i++) {
if (quantities[i] &gt; 0) {
validCardIndices.push(i);
}
}
if (validCardIndices.length &gt; 0) {
var randomIndex = Math.floor(Math.random() * validCardIndices.length);
var drawnCardIndex = validCardIndices[randomIndex];
var drawnCard = data[drawnCardIndex];
updateCardQuantities(drawnCardIndex, sheet, column);
sheet.getRange(targetColumn + &quot;32&quot;).setValue(drawnCard[0]);
sheet.getRange(targetColumn + &quot;33&quot;).setValue(drawnCard[1]);
}
}
function resetGame() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getRange(&quot;A3:E30&quot;);
var data = dataRange.getValues();
for (var i = 0; i &lt; data.length; i++) {
data[i][2] = data[i][5];
data[i][3] = data[i][6];
data[i][4] = data[i][7];
}
sheet.getRange(&quot;C32:E33&quot;).clearContent();
sheet.getRange(&quot;C3:E30&quot;).setValues(data);
}
function updateCardQuantities(cardIndex, sheet, column) {
var quantity = sheet.getRange(cardIndex + 3, column + 3).getValue();
if (quantity &gt; 0) {
sheet.getRange(cardIndex + 3, column + 3).setValue(quantity - 1);
}
}

huangapple
  • 本文由 发表于 2023年5月28日 09:10:15
  • 转载请务必保留本文链接：https://go.coder-hub.com/76349597.html
  • google-apps-script
  • google-sheets
  • spreadsheet
匿名

发表评论

匿名网友

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

确定