CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Adam Clarke
Adam Clarke

Reputation: 13

Trying to perform a setValue operation on a range without a for loop (GoogleAppsScripts)

I have created a spreadsheet for tracking points we assign to our raiders in world of warcraft and created buttons with scripts that add specific amounts of these points to the raiders, however, using a for loop, the functions are incredibly slow.

It looks like the issue is that I am using a for loop to store a points value, add the specific amount of points, and then repeat the loop.

function twentyDKP() {

  alertBox20DKP()

}

function alertBox20DKP() {
  var sh=SpreadsheetApp.getUi();
  var response=sh.alert("Add 20 DKP to all raiders?", sh.ButtonSet.YES_NO);
  if(response==sh.Button.YES) {

     var app = SpreadsheetApp;
     var ss = app.getActiveSpreadsheet();
     var activeSheet = ss.getActiveSheet();
     var raiders = activeSheet.getRange(1, 12).getValue()+4;
     //This is just grabbing a countif formula then offsetting it ensure the for loop counts from row 4 downwards.

       for(var i=4;i<raiders;i++){
        var DKP = activeSheet.getRange(i,2).getValue() 
        activeSheet.getRange(i,2).setValue(DKP+20)
       }
  var complete=sh.alert("20 DKP has been added to all raiders.", sh.ButtonSet.OK);
  }
}

I tried replacing the for loop with the following:

var i = 4;
var DKP = activeSheet.getDataRange(i,2,raiders,1).getValue() 
activeSheet.getDataRange(i,2,raiders,1).setValue(DKP+20)

The issue I have with the above is that it grabs the first available value, in my spreadsheet this is 60, and then stores that as the only DKP value, then adds 20 to it, giving me 80, and fills that in every single box, rather than looking at each individual value and adding 20 to it.

How can I fix this, I am not particularly experienced or knowledgable on GoogleAppScripts or code in general.

Upvotes: 1

Views: 455

Answers (2)

Cooper
Cooper

Reputation: 64032

Try this:

function runOne() {
  var ui=SpreadsheetApp.getUi();
  var response=ui.alert("Add 20 DKP to all raiders?", ui.ButtonSet.YES_NO);
  if(response==ui.Button.YES) {
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getActiveSheet();
    var raiders=sh.getRange(1,12).getValue()+4;
    for(var i=4;i<raiders;i++){
      sh.getRange(i,2).setValue(Number(sh.getRange(i,2).getValue())+20)
    }
    ui.alert("20 DKP has been added to all raiders.", ui.ButtonSet.OK);
  }
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201378

  • Your script works fine. But the process speed is slow.
    • You want to speed up the process speed of your script.
  • Your situation is as follows.
    • A value is retrieved from the cell "L1".
    • Retrieve values from row 4 of the column "B" to the retrieved value.
    • 20 is added to each value.
    • Put the values to the same range which was used for retrieving values of the column "B".

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

  • When setValue() is used in the for loop, the process cost becomes very high. This is mentioned by your question.
    • In order to reduce the cost, it uses setValues.
    • In order to use setValues in your situation, at first, it is required to create the values for putting to the cells.
    • After the values were created, the values are put to the cells.

When this is reflected to your script, it becomes as follows.

Modified script:

Please modify your script as follows.

From: 
var raiders = activeSheet.getRange(1, 12).getValue()+4;
//This is just grabbing a countif formula then offsetting it ensure the for loop counts from row 4 downwards.

for(var i=4;i<raiders;i++){
 var DKP = activeSheet.getRange(i,2).getValue() 
 activeSheet.getRange(i,2).setValue(DKP+20)
}
To: 
var raiders = activeSheet.getRange(1, 12).getValue();

// In your situation, the range is the same. So "range" is declared here.
var range = activeSheet.getRange(4, 2, raiders - 4);

// Create values for putting to the range.
var values = range.getValues().map(function(row) {return [row[0] + 20]});

// Put the created values to the range.
range.setValues(values);

// Update the cells. Before "alert" is shown.
SpreadsheetApp.flush();

References:

At first, please check whether my understanding is correct. If I misunderstood your question and this was not the result you want, I apologize. At that time, can you provide your sample Spreadsheet? By this, I would like to modify it.

Upvotes: 1

Related Questions