I want to edit a Google Script so it will shuffle a specific row at a specific sheet

Question

In this example I have tried this code:

function shuffleRows() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var lr=sh.getLastRow();
  sheet.sort(function(a,b){return Math.floor(Math.random()*range)-Math.floor(Math.random()*range);
  });
  rg.setValues(sheet);
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Shuffle ZOO",
    functionName : "shuffleRows",
  }];
  sheet.addMenu("Shuffle ZOO", entries);
};

It works fine when it comes to shuffle an entire sheet. However I need the script to shuffle a specific range, i.e I2:I8 in a specific sheet, i.e. Ark 5.

I think I have to replace with these line:

  var sheet = SpreadsheetApp.getActive().getSheetByName("Ark5");
  var range = sheet.getRange("I2:I8");

What do I need to edit?

*It works fine* Not possible. `sheet` is `undefined` inside `shuffleRows`. *I think I have to replace with these line* Why don't you try and see? — TheMaster, Mar 19 '20 at 14:42

Cooper · Accepted Answer · 2020-03-19T21:15:12.120

1

Try this:

function runOne(rs,re) {
  var rs=rs||2;//row start
  var re=re||8;//row end
  var ss=SpreadsheetApp.getActive()
  var sh=ss.getSheetByName("Ark5");
  var rg=sh.getRange(rs,1,re-rs+1,sh.getLastColumn());
  var vA=rg.getValues();
  var iA=[];//The index array
  vA.forEach(function(r,i){iA.push(i)});
  var oA=[];
  iA.sort(function(a,b){return Math.floor(Math.random()*iA.length)-Math.floor(Math.random()*iA.length);});//apply sort to index array
  iA.forEach(function(e,i){oA.push(vA[iA[i]]);});//reposition rows based upon sorted index array
  sh.getRange(rs,1,oA.length,oA[0].length).setValues(oA);//post repositioned rows
}

edited Mar 19 '20 at 21:15

answered Mar 19 '20 at 21:09

Cooper

59,616
6
23
54

Thank You. It works as intended, but on the way I ended up with the solution underneath while I needed to swap numbers with names in a certain row and only triggered by a button. You did answer the original question though :-) – Allan Bech Mar 19 '20 at 22:29
Know sure what you mean by swapping numbers with names so when you ask the question please provide an example. – Cooper Mar 19 '20 at 23:39

score 0 · Answer 2 · answered Mar 19 '20 at 14:42

0

I finally got it though I'm not sure it's the best solution.

function shuffleRows() {
//  var sheet = SpreadsheetApp.getActive().getSheetByName("Ark5");
//  var range = sheet.getRange("I2:I8");


  var ss=SpreadsheetApp.getActive().getSheetByName("Ark5");
  var sh=ss;
  var rg=sh.getRange("I2:I8");
  var vA=rg.getValues();
  var lr=sh.getLastRow();
  vA.sort(function(a,b){return Math.floor(Math.random()*lr)-Math.floor(Math.random()*lr);
  });
  rg.setValues(vA);
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Bland alle dyrene",
    functionName : "shuffleRows",
  }];
  sheet.addMenu("Bland alle dyrene", entries);
};

answered Mar 19 '20 at 14:42

Allan Bech

391
1
6

Your [sort algorithm is non deterministic](https://stackoverflow.com/a/18650169/) – TheMaster Mar 19 '20 at 15:03
What does that mean? And can I optimize it then? – Allan Bech Mar 19 '20 at 15:20
I mean - I can read, but I can't translate it into this specific matter. – Allan Bech Mar 19 '20 at 15:29
Can't help you with that. If you have a specific/focussed issue implementing the algorithm, ask a new question with [mcve] after trying multiple times. – TheMaster Mar 19 '20 at 15:35

I want to edit a Google Script so it will shuffle a specific row at a specific sheet

2 Answers2