在按钮按下时从列表中选择随机数据的脚本 [英] Script for Selecting random data from a list on button press

问题描述

我正在创建一个电子表格，当我按下按钮时，该电子表格可以自动从特定列中选择随机单元格数据.但是，我无法弄清楚该脚本.

I'm creating a spreadsheet which can automatically select random cell data from a particular column when I press a button. However, I cannot figure out the script.

感谢一些朋友，我尝试了一些使用附加组件的变体，但理想情况下，我不想使用任何类似的东西，因为如果人们想在没有附加组件的情况下进行复制，则它必须是可用的.

Thanks to some friends I've tried a few variations that involve using add-ons but ideally I don't want to use anything like that as it needs to be usable if people wanted to make a copy without the addons.

我想做的是单击一个特定的按钮，然后相邻的单元格从列表中给定的数据集中显示一个随机值.

What I'm looking to do is click a particular button and then the adjacent cell displays a random value from the data set given in the list.

我有两张纸.接口"和引擎盖下"-不言而喻，界面具有按钮，引擎盖下包含数据.

I have two sheets. "Interface" and "Under the Hood" - Pretty self-explanatory, Interface has the buttons and Under the hood contains the data.

以下是我在网上其他地方找到的一些代码，试图以此为基础.

Here is some code that I've found elsewhere online, trying to use this as a base.

  function random() {
      var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
      var val=ss.getRange("A1").getValue()
      var clr =ss.getRange("A1").clearContent()
      ss.getRange("A1").setValue(val)
    }

举个例子-

A2-A33是我们的数据集列表(在高级信息页下)

A2-A33 is our data setlist (Under the Hood sheet)

我已经绘制了一个按钮，我希望随机选择出现在单元格D17(界面)中.

I Have drawn a button and I would like the random selection to appear in cell D17 (interface).

虚拟数据: https://docs.google.com/spreadsheets/d/1HMmT_dx2_zeDKozEeDElYLaZutc4ruSwjjvVjyGg-Ls/edit?usp = sharing

有人可以引导我完成此操作吗?

Can anybody walk me through how to do this?

推荐答案

尝试一下:

function getRandomValue(searchColumn,resultCellRow,resultCellColumn) {
  var ss=SpreadsheetApp.getActive();
  var dsh=ss.getSheetByName('Interface');
  var ssh=ss.getSheetByName('UnderTheHood')
  var rg=ssh.getRange(2,searchColumn,ssh.getLastRow()-1,1);
  var vA=rg.getValues().map(function(r){return r[0]});
  while(vA[vA.length-1].length==0) {
    vA.splice(vA.length-1,1);
  }
  dsh.getRange(resultCellRow,resultCellColumn).setValue( vA[Math.floor(Math.random()*vA.length)]);
}

function generateCoach() {
  getRandomValue(10,3,4)
}

function coachBackStory() {
  getRandomValue(11,5,4)
}

function contracts() {
  getRandomValue(7,5,8);
}

我认为您可以弄清楚各个按钮的其余功能.所有您需要做的就是在"UnderTheHood"中为其提供搜索列，并在"Interface"中为该单元格添加答案

I presume that you can figure out the rest of the functions for the various buttons. All you need to do is give it the column to search in "UnderTheHood" and the cell to put answer in "Interface"

这篇关于在按钮按下时从列表中选择随机数据的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！