Incrementing +1 through range

Question

I am trying to randomise a set of increments over a large number of variables.

So for example, I have 5,000 people, each of these people has a base value of 1, but adding 1 has a variable affect to each of these people. Example adding 1 to Person A, means there base number increments by 1.1. Adding 1 to person b increments their base by 1.4 and so on.

I have figured out how to do the different values assigned to each +1 action with vlookups, but I now need to loop the +1 increments through the range.

I have base values setup in column B:B but can't figure out how to loop the script. This is what I have so far:

    function increment(){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets()[1];
  
  

  var range = sheet.getRange("b1");
   var value = range.getValue(); 

  for (var i = 1; i >= range; i++);
  
   range.setValue(value + 1);
  
}

But this only increments 1 to "b1". How do I loop it so it runs continously, say every second through b1, b2, b3, b4 etc adding +1 each time?

Answer 1

Simply by adding range name using "b" + i in getRange() function, and move it into for loop.
Or you can use offset() function instead.

the code

for (var i = 1; i >= range; i++);
    range.setValue(value + 1);

is loop without running the command range.setValue(value + 1); because first line was ending with semi-colon.

Finally, the code should be as below.

function increment()
{
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheets()[1];
    //var range = sheet.getRange("b1"); 
    //var value = range.getValue();

    // change 5000 to amount of you need
    for (var i = 1; i <= 5000; i++)
    {
        var range = sheet.getRange("b" + i); 
        var value = range.getValue();
        range.setValue(value + 1);
    }
}

if using offset function code would be as below.

function increment()
{
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheets()[1];
    var range = sheet.getRange("b1"); 
    //var value = range.getValue();

    // change 5000 to amount of you need
    for (var i = 1; i <= 5000; i++)
    {
        var value = range.offset(i, 0).getValue();
        range.offset(i, 0).setValue(value + 1);
    }
}

Edit for improve execute time

function increment()
{
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheets()[1];

    // change 5000 to amount of you need
    var formulas = new Array(5000);

    for (var i = 0; i < 5000; i++)
    {
        formulas[i] = "=R[0]C[-1]+1";
    }

    // Assume that column C is not used
    var ranges = sheet.getRange("C1:C5000");
    ranges.setFormulasR1C1(formulas)
    ranges.copyValuesToRange(sheet, 2, 2, 1, 5000);
    ranges.clearContent();
}