CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
limonvnx
limonvnx

Reputation: 83

Method for incrementing all values in a Google Sheets range for a daily counter

I've been struggling with what I thought would be a very straightforward script and have tried from a few different angles without success. What I'd like is a script that runs daily and increments all cells in a given range by 1, serving as a very basic counter that will be reset manually.

So far the script I have is this:

function dailyCounter() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Sheet1");

  var range = sheet.getRange("A2:K2");
  var array = range.getValues();
  var count = array.join().split(',');


  for (var i = 0; i < array.length; i++){
    var newCount = Number(count[i])+1;
    range.setValue(newCount);
   }  
}

The problem with this is that it finds the increment of the first cell but then applies it to the whole range instead of incrementing the first cell and continuing the loop for the rest. I figured this is to do with range.setValue(newCount) - what I really want there is range[i].setValue(newCount) but this isn't possible and I couldn't figure out how to reference an individual part of range with the getRange method.

If the range wasn't going to scale I would have written something like this.

var count1 = sheet.getRange("A2").getValue();
var newCount = count1+1
sheet.getRange("A2").setValue(newCount);

Can anyone point me in the right direction?

Thanks

Upvotes: 0

Views: 192

Answers (1)

Cooper
Cooper

Reputation: 64062

Increment all values in a range

function incrementRange() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Sheet1");
  var range=sheet.getRange("A2:K2");
  var array=range.getValues();
  for(var i=0;i<array.length;i++) {
    for(var j=0;j<array[i].length;j++) {
      array[i][j]+=1;
    }
  }
  range.setValues(array); 
}

Upvotes: 1

Related Questions