How can I make this google sheets script code shorter/faster?

Question

The code is pretty self-explanatory. Just have a lot of ranges that I need to be randomized independently. For example, range('W1:W4') should not get mixed up with range('W5:W8'), so therefore I cannot just randomize range('W1:W80'). Any help or advice would be great! Thanks a lot. Currently, the code takes a bit longer to run than I would like (14 seconds).

function clickStart() {

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('T2:T21').randomize();
  spreadsheet.getRange('Y1:Y20').randomize();
  spreadsheet.getRange('Y21:Y40').randomize();
  spreadsheet.getRange('Y41:Y60').randomize();
  spreadsheet.getRange('Y61:Y80').randomize();
  spreadsheet.getRange('W1:W4').randomize();
  spreadsheet.getRange('W5:W8').randomize();
  spreadsheet.getRange('W9:W12').randomize();
  spreadsheet.getRange('W13:W16').randomize();
  spreadsheet.getRange('W17:W20').randomize();
  spreadsheet.getRange('W21:W24').randomize();
  spreadsheet.getRange('W25:W28').randomize();
  spreadsheet.getRange('W29:W32').randomize();
  spreadsheet.getRange('W33:W36').randomize();
  spreadsheet.getRange('W37:W40').randomize();
  spreadsheet.getRange('W41:W44').randomize();
  spreadsheet.getRange('W45:W48').randomize();
  spreadsheet.getRange('W49:W52').randomize();
  spreadsheet.getRange('W53:W56').randomize();
  spreadsheet.getRange('W57:W60').randomize();
  spreadsheet.getRange('W61:W64').randomize();
  spreadsheet.getRange('W65:W68').randomize();
  spreadsheet.getRange('W69:W72').randomize();
  spreadsheet.getRange('W73:W76').randomize();
  spreadsheet.getRange('W77:W80').randomize();
  spreadsheet.getRange('H10:H12').randomize();
  spreadsheet.getRange('H14:H16').randomize();
  spreadsheet.getRange('H18:H20').randomize();
  spreadsheet.getRange('H22:H24').randomize();
  spreadsheet.getRange('H26:H28').randomize();
  spreadsheet.getRange('H30:H32').randomize();
  spreadsheet.getRange('H34:H36').randomize();
  spreadsheet.getRange('H38:H40').randomize();
  spreadsheet.getRange('H42:H44').randomize();
  spreadsheet.getRange('H46:H48').randomize();
  spreadsheet.getRange('H50:H52').randomize();
  spreadsheet.getRange('H54:H56').randomize();
  spreadsheet.getRange('H58:H60').randomize();
  spreadsheet.getRange('H62:H64').randomize();
  spreadsheet.getRange('H66:H68').randomize();
  spreadsheet.getRange('H70:H72').randomize();
  spreadsheet.getRange('H74:H76').randomize();
  spreadsheet.getRange('H78:H80').randomize();
  spreadsheet.getRange('H82:H84').randomize();
  spreadsheet.getRange('H86:H88').randomize();
};

Emiel Zuurbier · Accepted Answer · 2020-02-13 20:22:11Z

2

Put all the values that you put in the getRange method inside an array and loop over it. With each range call the getRange and randomize methods.

function clickStart() {

  var ranges = ['T2:T21', 'Y1:Y20', 'Y21:Y40', 'Y41:Y60']; // Add all cells to this array.
  var spreadsheet = SpreadsheetApp.getActive();
  for (let range of ranges) {
    spreadsheet.getRange(range).randomize();
  }

};

But at this point the code would still take a long time and block your thread while working. Wrap it in a Promise that resolves when the loop is ready to make the code asynchronous. This will ensure that the rest of your code will still run.

function clickStart() {

  return new Promise(resolve => {

    var ranges = ['T2:T21', 'Y1:Y20', 'Y21:Y40', 'Y41:Y60']; // Add all cells to this array.
    var spreadsheet = SpreadsheetApp.getActive();
    for (let range of ranges) {
      spreadsheet.getRange(range).randomize();
    }
    resolve(spreadsheet);

  });

};

answered Feb 13, 2020 at 20:22

Emiel Zuurbier

21.2k3 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jrob11 Over a year ago

This worked perfectly! Yes, the code still takes a long time, but it is much cleaner! Exactly what I was wanting, so thank you!

Collectives™ on Stack Overflow

How can I make this google sheets script code shorter/faster?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related