我得到以下代碼來使用腳本對多列進行洗牌。
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()));
}
這樣做的問題是它打亂了單元格的值而不是公式。有沒有辦法改變這個？我希望它洗牌單元格，將公式保留在單元格中。
uj5u.com熱心網友回復：
我想我可以這樣做：
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;
}
該代碼使用值和公式對單元格進行洗牌。
更新
如果您的范圍只有公式，則函式shuffleColumn()可以更短，添加更有效：
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]]));
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/481070.html
標籤：谷歌应用脚本