jeudi 1 octobre 2020

Google Sheets Script - How to select a random selection of cells according to a predefined set of rules

Hey guys I'm a beginner in Google Apps Script and I'm having trouble writing a script.

Here's my problem : I have a sheet B with with a list of 77 companies, their employee count and their size ("very small, small, medium, big") and I would like to select a random selection of companies according to a predefined set of rules, and display them in sheet A. (here's my sheet)

For example, I'd like to select 2 very small companies, 3 small companies, 4 medium companies, and 1 big company every time I click on a button.

When I click on the button, I'd like the list to reset and select new companies.

Here's my code so far , but I have trouble figuring out to complete it :

var companyList = Spreadsheet.getSheetByName("Company List");
var lastrow = getLastPopulatedRow(companyList);
var dataCompanyList = companyList.getDataRange().getValues();

var verysmall = 0;
var small = 0;
var medium = 0;
var big = 0;

var verysmallAlternate = 0;
var smallAlternate = 0;
var mediumAlternate = 0;
var bigAlternate = 0;

var breakLoop = 90000;
var companyList = []; 

function gethashtags() {
  
  while (verysmall<2)
  {
    randomNum = Math.floor(Math.random() * (77));
    if (dataCompanyList[randomNum][2] == "verysmall")

{ 
company = dataCompanyList[randomNum][0];
verysmall++;
companyList.push(dataCompanyList[randomNum][0]);
}

{
verysmallAlternate++;
if(verysmallAlternate>breakLoop) {
verysmall;
}

while (small<3)
  {
    randomNum = Math.floor(Math.random() * (77));
    if (dataCompanyList[randomNum][2] == "verysmall")

{ 
company = dataCompanyList[randomNum][0];
small++;
companyList.push(dataCompanyList[randomNum][0]);
}

{
smallAlternate++;
if(smallAlternate>breakLoop) {
small++;
}

while (medium<4)
  {
    randomNum = Math.floor(Math.random() * (77));
    if (dataCompanyList[randomNum][2] == "medium")

{ 
company = dataCompanyList[randomNum][0];
medium++;
companyList.push(dataCompanyList[randomNum][0]);
}

{
mediumAlternate++;
if(mediumAlternate>breakLoop) {
medium++;
}

while (big<1)
  {
    randomNum = Math.floor(Math.random() * (77));
    if (dataCompanyList[randomNum][2] == "big")

{ 
company = dataCompanyList[randomNum][0];
big++;
companyList.push(dataCompanyList[randomNum][0]);
}

{
bigAlternate++;
if(bigAlternate>breakLoop) {
big++;
}
}

What I can't figure out is how to make this work and display the companyList Array in the cell in the sheet A.




Aucun commentaire:

Enregistrer un commentaire