Script shuffles cell values instead of formulas-CodePudding

I got the following code to shuffle multiple columns with a script.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetToShuffle');
  var ranges = ['A1:A32','B1:B47','C1:C39','D1:D87'];

  ranges.forEach(r => shuffleColumns(r, sheet));

function shuffleColumns(r, sheet) {
  var range = sheet.getRange(r);
  range.setValues(shuffleArray(range.getValues()));  
}

The problem with this is that it shuffles the values of the cell instead of the formulas. Is there a way to change this? I want it to shuffle the cells, to keep the formulas in the cells.

CodePudding user response：

I suppose I can be done this way:

function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('SheetToShuffle');
  const ranges = ['A1:A32','B1:B47','C1:C39','D1:D87'];
  ranges.forEach(r => shuffleColumn(r, sheet));
}

function shuffleColumn(rng,sheet) {
  const range    = sheet.getRange(rng);
  const col      = range.getColumn();
  const values   = range.getValues().flat();
  const formulas = range.getFormulas().flat();

  // get indexes and suffle them
  const indexes = shuffleArray(values.map((_,i) => i));

  // order the values by the indexes and put them on the sheet
  range.setValues(indexes.map(i => [values[i]]));

  // order the formulas by the indexes and put them on the sheet
  indexes.map(i => formulas[i]).forEach((frm,row) =>
    { if (frm) sheet.getRange(row 1, col).setFormula(frm) });
}

// https://stackoverflow.com/a/12646864/14265469
function shuffleArray(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i   1));
    [array[i], array[j]] = [array[j], array[i]];
  }
  return array;
}

The code shuffles cells with values and formulas.

Update

If your ranges have only formulas the function shuffleColumn() can be much shorter add much more effecient:

function shuffleColumn(rng,sheet) {
  const range = sheet.getRange(rng);
  const formulas = range.getFormulas().flat();

  // get indexes and suffle them
  const indexes = shuffleArray(formulas.map((_,i) => i));

  // order the formulas by the indexes and put them on the sheet
  range.setFormulas(indexes.map(i => [formulas[i]]));
}