Google表格脚本 - 重复多个值X次 [英] Google Sheet Script - Repeat Multiple Values X Times

查看：110
本文介绍了Google表格脚本 - 重复多个值X次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我制作了一个工作脚本（如下），重复1-50个数字，每个单列（AB）8次。我只是对行进行编号。



我对此很陌生（就像有史以来第三天一样），我确信有一种更有效的方式来做到这一点，我只是不知道什么那是。

初始设置（以及变化的变量）不会真正节省时间，而且可以节省大量时间。虽然有内置的函数（如REPT和ROW）会关闭，但它仍然需要复制/粘贴50次并更改单元格引用（由于锁定单元格和相对单元格引用的限制）。



如果任何人都可以帮助（并希望解释）如何以更好的方式做到这一点，那将是值得赞赏的！ 

 函数numberGameAtA30（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet2 = SpreadsheetApp.setActiveSheet（ss.getSheetByName（'GAMES2'））; 
 
 var value1 = 1; 
 
 var value2 = 2; 
 
 var value3 = 3; 
 
 var value4 = 4; 
 
 var value5 = 5; 
 
 var value6 = 6; 
 
 var value7 = 7; 
 
 var value8 = 8; 
 
 var value9 = 9; 
 
 var value10 = 10; 
 
 var value11 = 11; 
 
 var value12 = 12; 
 
 var value13 = 13; 
 
 var value14 = 14; 
 
 var value15 = 15; 
 
 var value16 = 16; 
 
 var value17 = 17; 
 
 var value18 = 18; 
 
 var value19 = 19; 
 
 var value20 = 20; 
 
 var value21 = 21; 
 
 var value22 = 22; 
 
 var value23 = 23; 
 
 var value24 = 24; 
 
 var value25 = 25; 
 
 var value26 = 26; 
 
 var value27 = 27; 
 
 var value28 = 28; 
 
 var value29 = 29; 
 
 var value30 = 30; 
 
 var value31 = 31; 
 
 var value32 = 32; 
 
 var value33 = 33; 
 
 var value34 = 34; 
 
 var value35 = 35; 
 
 var value36 = 36; 
 
 var value37 = 37; 
 
 var value38 = 38; 
 
 var value39 = 39; 
 
 var value40 = 40; 
 
 var value41 = 41; 
 
 var value42 = 42; 
 
 var value43 = 43; 
 
 var value44 = 44; 
 
 var value45 = 45; 
 
 var value46 = 46; 
 
 var value47 = 47; 
 
 var value48 = 48; 
 
 var value49 = 49; 
 
 var value50 = 50; 
 
 //开始行和列
 var startRow = 30 
 var startColumn = 1; 
 var numRows = 8; 
 var numColumns = 1; 
 
 //将放置值的范围
 
 var target1 = sheet2.getRange（startRow，startColumn，numRows，numColumns）.setValue（value1）; 
 var target2 = sheet2.getRange（startRow + 8，startColumn，numRows，numColumns）.setValue（value2）; 
 var target3 = sheet2.getRange（startRow + 16，startColumn，numRows，numColumns）.setValue（value3）; 
 var target4 = sheet2.getRange（startRow + 24，startColumn，numRows，numColumns）.setValue（value4）; 
 var target5 = sheet2.getRange（startRow + 32，startColumn，numRows，numColumns）.setValue（value5）; 
 var target6 = sheet2.getRange（startRow + 40，startColumn，numRows，numColumns）.setValue（value6）; 
 var target7 = sheet2.getRange（startRow + 48，startColumn，numRows，numColumns）.setValue（value7）; 
 var target8 = sheet2.getRange（startRow + 56，startColumn，numRows，numColumns）.setValue（value8）; 
 var target9 = sheet2.getRange（startRow + 64，startColumn，numRows，numColumns）.setValue（value9）; 
 var target10 = sheet2.getRange（startRow + 72，startColumn，numRows，numColumns）.setValue（value10）; 
 var target11 = sheet2.getRange（startRow + 80，startColumn，numRows，numColumns）.setValue（value11）; 
 var target12 = sheet2.getRange（startRow + 88，startColumn，numRows，numColumns）.setValue（value12）; 
 var target13 = sheet2.getRange（startRow + 96，startColumn，numRows，numColumns）.setValue（value13）; 
 var target14 = sheet2.getRange（startRow + 104，startColumn，numRows，numColumns）.setValue（value14）; 
 var target15 = sheet2.getRange（startRow + 112，startColumn，numRows，numColumns）.setValue（value15）; 
 var target16 = sheet2.getRange（startRow + 120，startColumn，numRows，numColumns）.setValue（value16）; 
 var target17 = sheet2.getRange（startRow + 128，startColumn，numRows，numColumns）.setValue（value17）; 
 var target18 = sheet2.getRange（startRow + 136，startColumn，numRows，numColumns）.setValue（value18）; 
 var target19 = sheet2.getRange（startRow + 144，startColumn，numRows，numColumns）.setValue（value19）; 
 var target20 = sheet2.getRange（startRow + 152，startColumn，numRows，numColumns）.setValue（value20）; 
 var target21 = sheet2.getRange（startRow + 160，startColumn，numRows，numColumns）.setValue（value21）; 
 var target22 = sheet2.getRange（startRow + 168，startColumn，numRows，numColumns）.setValue（value22）; 
 var target23 = sheet2.getRange（startRow + 176，startColumn，numRows，numColumns）.setValue（value23）; 
 var target24 = sheet2.getRange（startRow + 184，startColumn，numRows，numColumns）.setValue（value24）; 
 var target25 = sheet2.getRange（startRow + 192，startColumn，numRows，numColumns）.setValue（value25）; 
 var target26 = sheet2.getRange（startRow + 200，startColumn，numRows，numColumns）.setValue（value26）; 
 var target27 = sheet2.getRange（startRow + 208，startColumn，numRows，numColumns）.setValue（value27）; 
 var target28 = sheet2.getRange（startRow + 216，startColumn，numRows，numColumns）.setValue（value28）; 
 var target29 = sheet2.getRange（startRow + 224，startColumn，numRows，numColumns）.setValue（value29）; 
 var target30 = sheet2.getRange（startRow + 232，startColumn，numRows，numColumns）.setValue（value30）; 
 var target31 = sheet2.getRange（startRow + 240，startColumn，numRows，numColumns）.setValue（value31）; 
 var target32 = sheet2.getRange（startRow + 248，startColumn，numRows，numColumns）.setValue（value32）; 
 var target33 = sheet2.getRange（startRow + 256，startColumn，numRows，numColumns）.setValue（value33）; 
 var target34 = sheet2.getRange（startRow + 264，startColumn，numRows，numColumns）.setValue（value34）; 
 var target35 = sheet2.getRange（startRow + 272，startColumn，numRows，numColumns）.setValue（value35）; 
 var target36 = sheet2.getRange（startRow + 280，startColumn，numRows，numColumns）.setValue（value36）; 
 var target37 = sheet2.getRange（startRow + 288，startColumn，numRows，numColumns）.setValue（value37）; 
 var target38 = sheet2.getRange（startRow + 296，startColumn，numRows，numColumns）.setValue（value38）; 
 var target39 = sheet2.getRange（startRow + 304，startColumn，numRows，numColumns）.setValue（value39）; 
 var target40 = sheet2.getRange（startRow + 312，startColumn，numRows，numColumns）.setValue（value40）; 
 var target41 = sheet2.getRange（startRow + 320，startColumn，numRows，numColumns）.setValue（value41）; 
 var target42 = sheet2.getRange（startRow + 328，startColumn，numRows，numColumns）.setValue（value42）; 
 var target43 = sheet2.getRange（startRow + 336，startColumn，numRows，numColumns）.setValue（value43）; 
 var target44 = sheet2.getRange（startRow + 344，startColumn，numRows，numColumns）.setValue（value44）; 
 var target45 = sheet2.getRange（startRow + 352，startColumn，numRows，numColumns）.setValue（value45）; 
 var target46 = sheet2.getRange（startRow + 360，startColumn，numRows，numColumns）.setValue（value46）; 
 var target47 = sheet2.getRange（startRow + 368，startColumn，numRows，numColumns）.setValue（value47）; 
 var target48 = sheet2.getRange（startRow + 376，startColumn，numRows，numColumns）.setValue（value48）; 
 var target49 = sheet2.getRange（startRow + 384，startColumn，numRows，numColumns）.setValue（value49）; 
 var target50 = sheet2.getRange（startRow + 392，startColumn，numRows，numColumns）.setValue（value50）;

}

解决方案 

 函数numberGameAtA30（）使用for循环在括号内执行50次。 {
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet2 = SpreadsheetApp.setActiveSheet（ss.getSheetByName（'GAMES2'））; 
 
 //开始行和列
 var currentRow = 30 
 var startColumn = 1; 
 var numRows = 8; 
 var numColumns = 1; 
 
 
 //其中值将被放置
的范围（i = 1; i <= 50; i ++）{
 var target1 = sheet2.getRange（ currentRow，startColumn，numRows，numColumns）.setValue（i）; 
 currentRow = currentRow + 8; 
}


I made a working script (below) that repeats the numbers 1-50, 8 times each in a single column (AB). I'm just numbering rows.

I'm new to this (like third day ever trying this) and I'm certain there is a more efficient way to do this, I just have no idea what that is.

The initial set-up (and changing variables) wouldn't really save time over copy and paste a ton of times. While there are built-in functions (like REPT and ROW) which would get close, it would still require copy/paste 50 times AND changing cell references (due to the limits of locked cells and relative cell references).

If anyone could help (and hopefully explain) how to do this a better way, it would be appreciated!

function numberGameAtA30() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet2 = SpreadsheetApp.setActiveSheet(ss.getSheetByName('GAMES2'));

var value1 = 1;

var value2 = 2;

var value3 = 3;

var value4 = 4;

var value5 = 5;

var value6 = 6;

var value7 = 7;

var value8 = 8;

var value9 = 9;

var value10 = 10;

var value11 = 11;

var value12 = 12;

var value13 = 13;

var value14 = 14;

var value15 = 15;

var value16 = 16;

var value17 = 17;

var value18 = 18;

var value19 = 19;

var value20 = 20;

var value21 = 21;

var value22 = 22;

var value23 = 23;

var value24 = 24;

var value25 = 25;

var value26 = 26;

var value27 = 27;

var value28 = 28;

var value29 = 29;

var value30 = 30;

var value31 = 31;

var value32 = 32;

var value33 = 33;

var value34 = 34;

var value35 = 35;

var value36 = 36;

var value37 = 37;

var value38 = 38;

var value39 = 39;

var value40 = 40;

var value41 = 41;

var value42 = 42;

var value43 = 43;

var value44 = 44;

var value45 = 45;

var value46 = 46;

var value47 = 47;

var value48 = 48;

var value49 = 49;

var value50 = 50;

// start columns and rows
  var startRow = 30
  var startColumn = 1;
  var numRows = 8;
  var numColumns = 1;

// ranges where values will be placed

var target1 = sheet2.getRange(startRow, startColumn, numRows, numColumns).setValue(value1);
var target2 = sheet2.getRange(startRow+8, startColumn, numRows, numColumns).setValue(value2);
var target3 = sheet2.getRange(startRow+16, startColumn, numRows, numColumns).setValue(value3);
var target4 = sheet2.getRange(startRow+24, startColumn, numRows, numColumns).setValue(value4);
var target5 = sheet2.getRange(startRow+32, startColumn, numRows, numColumns).setValue(value5);
var target6 = sheet2.getRange(startRow+40, startColumn, numRows, numColumns).setValue(value6);
var target7 = sheet2.getRange(startRow+48, startColumn, numRows, numColumns).setValue(value7);
var target8 = sheet2.getRange(startRow+56, startColumn, numRows, numColumns).setValue(value8);
var target9 = sheet2.getRange(startRow+64, startColumn, numRows, numColumns).setValue(value9);
var target10 = sheet2.getRange(startRow+72, startColumn, numRows, numColumns).setValue(value10);
var target11 = sheet2.getRange(startRow+80, startColumn, numRows, numColumns).setValue(value11);
var target12 = sheet2.getRange(startRow+88, startColumn, numRows, numColumns).setValue(value12);
var target13 = sheet2.getRange(startRow+96, startColumn, numRows, numColumns).setValue(value13);
var target14 = sheet2.getRange(startRow+104, startColumn, numRows, numColumns).setValue(value14);
var target15 = sheet2.getRange(startRow+112, startColumn, numRows, numColumns).setValue(value15);
var target16 = sheet2.getRange(startRow+120, startColumn, numRows, numColumns).setValue(value16);
var target17 = sheet2.getRange(startRow+128, startColumn, numRows, numColumns).setValue(value17);
var target18 = sheet2.getRange(startRow+136, startColumn, numRows, numColumns).setValue(value18);
var target19 = sheet2.getRange(startRow+144, startColumn, numRows, numColumns).setValue(value19);
var target20 = sheet2.getRange(startRow+152, startColumn, numRows, numColumns).setValue(value20);
var target21 = sheet2.getRange(startRow+160, startColumn, numRows, numColumns).setValue(value21);
var target22 = sheet2.getRange(startRow+168, startColumn, numRows, numColumns).setValue(value22);
var target23 = sheet2.getRange(startRow+176, startColumn, numRows, numColumns).setValue(value23);
var target24 = sheet2.getRange(startRow+184, startColumn, numRows, numColumns).setValue(value24);
var target25 = sheet2.getRange(startRow+192, startColumn, numRows, numColumns).setValue(value25);
var target26 = sheet2.getRange(startRow+200, startColumn, numRows, numColumns).setValue(value26);
var target27 = sheet2.getRange(startRow+208, startColumn, numRows, numColumns).setValue(value27);
var target28 = sheet2.getRange(startRow+216, startColumn, numRows, numColumns).setValue(value28);
var target29 = sheet2.getRange(startRow+224, startColumn, numRows, numColumns).setValue(value29);
var target30 = sheet2.getRange(startRow+232, startColumn, numRows, numColumns).setValue(value30);
var target31 = sheet2.getRange(startRow+240, startColumn, numRows, numColumns).setValue(value31);
var target32 = sheet2.getRange(startRow+248, startColumn, numRows, numColumns).setValue(value32);
var target33 = sheet2.getRange(startRow+256, startColumn, numRows, numColumns).setValue(value33);
var target34 = sheet2.getRange(startRow+264, startColumn, numRows, numColumns).setValue(value34);
var target35 = sheet2.getRange(startRow+272, startColumn, numRows, numColumns).setValue(value35);
var target36 = sheet2.getRange(startRow+280, startColumn, numRows, numColumns).setValue(value36);
var target37 = sheet2.getRange(startRow+288, startColumn, numRows, numColumns).setValue(value37);
var target38 = sheet2.getRange(startRow+296, startColumn, numRows, numColumns).setValue(value38);
var target39 = sheet2.getRange(startRow+304, startColumn, numRows, numColumns).setValue(value39);
var target40 = sheet2.getRange(startRow+312, startColumn, numRows, numColumns).setValue(value40);
var target41 = sheet2.getRange(startRow+320, startColumn, numRows, numColumns).setValue(value41);
var target42 = sheet2.getRange(startRow+328, startColumn, numRows, numColumns).setValue(value42);
var target43 = sheet2.getRange(startRow+336, startColumn, numRows, numColumns).setValue(value43);
var target44 = sheet2.getRange(startRow+344, startColumn, numRows, numColumns).setValue(value44);
var target45 = sheet2.getRange(startRow+352, startColumn, numRows, numColumns).setValue(value45);
var target46 = sheet2.getRange(startRow+360, startColumn, numRows, numColumns).setValue(value46);
var target47 = sheet2.getRange(startRow+368, startColumn, numRows, numColumns).setValue(value47);
var target48 = sheet2.getRange(startRow+376, startColumn, numRows, numColumns).setValue(value48);
var target49 = sheet2.getRange(startRow+384, startColumn, numRows, numColumns).setValue(value49);
var target50 = sheet2.getRange(startRow+392, startColumn, numRows, numColumns).setValue(value50);

}

解决方案

Try this: It uses a for loop to go around 50 times within the brackets.

function numberGameAtA30() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet2 = SpreadsheetApp.setActiveSheet(ss.getSheetByName('GAMES2'));

// start columns and rows
  var currentRow = 30
  var startColumn = 1;
  var numRows = 8;
  var numColumns = 1;


// ranges where values will be placed
for(i = 1;i <= 50;i++){
var target1 = sheet2.getRange(currentRow, startColumn, numRows, numColumns).setValue(i);
currentRow = currentRow + 8;
}

这篇关于Google表格脚本 - 重复多个值X次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆